Skip to:
Content
Pages
Categories
Search
Top
Bottom

HyperDB Support for Buddypress, or other means of distributing DB load

  • @thebeardedone

    Member

    12 years, 6 months ago

    We have a Buddypress site with over 18,000 registered users (in less than a month) and is still growing. During peak times, the SELECT queries go out of control and render the site unusable for minutes on end, unless we stop Apache and let the DB and PHP “catch up”. When I’ve tried to enable HyperDB, I get an immediate 404 on the home page.

    What are the plans for allowing Buddypress to scale up to a usable social network? I have bug opened up on the Buddypress Trac: http://buddypress.trac.wordpress.org/ticket/4251

    Are there other ways of distributing the DB load that is compatible with Buddypress? We already have the web/app and DB on separate servers, but either the queries/table structure for Buddypress need to be optimized or there needs to allow for multiple dedicated RO/RW servers.

Viewing 8 replies - 1 through 8 (of 8 total)

  • @shanebp

    Moderator

    12 years, 6 months ago

    Myself and others have also had the issues you describe.

    I believe BP 1.6 will reduce those issues.

    Meanwhile, we’ve done these things to mitigate the issue, maybe you’ve already done some of them:
    1. Use a caching plugin like W3 Total Cache – don’t use the page cache
    2. Switch from MyISAM to InnoDB
    3. Use tmpsf on your db server – this really helps

    We thought about going to a master/slave db setup – but doing the above allowed us to handle peak traffic for a site with 30k users. Prior to that, our slowlog looked like the one attached to your ticket.

    @thebeardedone

    Member

    12 years, 6 months ago

    Thanks for the reply shanebp.

    1. I’m using Batcache, https://wordpress.org/extend/plugins/batcache/ and Memcached Object Cache https://wordpress.org/extend/plugins/memcached/ both of these use memcached for caching. We’ve used W3TC in the past on our WP-only sites, but it got be a little to unstable for our tastes… So far these seem to be working good, though we have to remember to bounce memcached in addition to PHP-FPM when we make a code change.
    2. Right now we only have the following tables set to use MyISAM, the rest being InnoDB:
    bp_groups
    bp_groups_groupmeta
    bp_groups_members
    usermeta
    users
    We tried having them all to InnoDB, but due to its poor text searching performance compared to MyISAM, we switch back those 5.
    3. TMPFS is worth its weight in gold. :-)

    Wow, 30K, very impressive! I’m hoping that there is a lot of performance that can be squeeze out of our DB server, which will be looked at soon by actual DBAs (I do not in anyway claim to be one…). If not, I’m looking forward to BP 1.6 for some of those performance enhancements found from bug #4045!

    @shanebp

    Moderator

    12 years, 6 months ago

    Have you looked at this?
    https://buddypress.trac.wordpress.org/ticket/4060

    >2. Right now we only have the following tables set to use MyISAM, the rest being InnoDB:

    Yup, it’s a compromise – but for us the benefit of row-locking in InnoDB outweighed the fulltext issue.
    We don’t generally allow text searches.

    And thanks for sharing your setup info.
    Now that BP is being used for sites with larger and more active memberships, more devs are having these problems.
    I think collecting info re tuning, caching, etc. approaches on a page somewhere in buddypress.org would be very useful and would generate some helpful ( and confusing ) discussion by us non-DBAs.

    @thebeardedone

    Member

    12 years, 6 months ago

    Thanks for the heads up on that link. I’ve added it my watch list.

    As a stop gap measure, on of our developers changed the query to this:

    `SELECT count(a.id) FROM wp_bp_activity a WHERE a.component IN ( ‘groups’ ) AND a.type IN ( ‘new_blog_post’,’new_forum_topic’,’activity_update’,’joined_group’ ) AND a.item_id IN ( 82 ) AND a.hide_sitewide = 0 AND a.type != ‘activity_comment’ ORDER BY a.date_recorded DESC;`

    It’s really helped the load, not a complete fix but it helps.

    @thebeardedone

    Member

    12 years, 5 months ago

    I’ve uploaded a PDF of a report done by Percona to 4045 and 4060 where they were able to pin point the sloppy queries and indentify that using a string literal instead of the Buddypress default, reduced the query time by 93%. There are other recommendations in that PDF also.

    @shanebp

    Moderator

    12 years, 5 months ago

    @boonebgorges – Hope this helps… maybe in 1.7 ?
    @thebeardedone – Thank you! Percona’s work was done on a BP 1.5.6 install… or?

    @djpaul

    Keymaster

    12 years, 5 months ago

    Wow, very useful. Thanks TheBeardedOne

    @thebeardedone

    Member

    12 years, 5 months ago

    It was on a 1.5.5 install.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘HyperDB Support for Buddypress, or other means of distributing DB load’ is closed to new replies.

Topic Info

Topic Tags

Skip to toolbar