Slow queries on Activity Stream during peak times on site.

[TheBeardedOne]

We have Buddypress 1.5.5 install on WP 3.3.1 that is going into a tail spin during peak times. The load is all DB related, since we have a dedicated web/app server and DB server. At the time of this report, the number of registered users for the site is between 18000-19000 (we've acquired these number of users in less than a month of being online). I'm attaching a cleaned (removed identifiable info) mysqld.slow-query-log snippet. The number of MySQL slow queries that will get logged are in the thousands, but are basically the same query. The DB server is running Gentoo Linux, with MySQL 5.1.62-r1, and has 32 cores and 64GB of RAM. Due to Buddypress not being compatible with HyperDB, I am unable to mitigate this problem by throwing more DB servers at it.

[boonebgorges]

Thanks for the log. We've made significant improvements in BP 1.6. See the extended discussion in ​https://buddypress.trac.wordpress.org/ticket/4045 I encourage you to CC yourself on that ticket and chip in with any additional insights you may have gleaned from your experience.

In the meantime, you're likely to see performance gains by using some sort of persistent object cache, as well as by fine-tuning your MySQL cache.

Due to Buddypress not being compatible with HyperDB, I am unable to mitigate this problem by throwing more DB servers at it.

BuddyPress is definitely compatible with SharDB (and by extension, I believe, with HyperDB), but you're correct that the DB tables themselves cannot be sharded.