[@thebeardedone Member 12 years, 5 months ago]

It was on a 1.5.5 install.

[@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.

[@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, 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!