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.
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!
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.
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.
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.
@boonebgorges – Hope this helps… maybe in 1.7 ?
@thebeardedone – Thank you! Percona’s work was done on a BP 1.5.6 install… or?
Wow, very useful. Thanks TheBeardedOne
It was on a 1.5.5 install.