Here is small advice for those who uses buddy press plugin for WordPress. This is awesome plugin, but if your server is heavy loaded you can notice that your MySQL performance can heavily drop if you are using buddypress for a long time. I was wondering why it’s happening. I tried all possible solutions from increasing the power of MySQL server, like multi-threaded expensive Xeon CPUs, SSD disk, RAM disk later on, but the problem of various testers and performance checkers points me to DB problems issue. So I came to the point that I need some how to remove the data from bp_activity table.
For example my MYSQL database for website powered by buddypress since 2009 for the last 4 years reached the size of 8 GB. The biggest table in ti was bp_activity = 2,5 GB. There was all data since 2009, and every time to select the data MYSQL scanned all this table. So the idea was to remove not everything from this table but the outdated records which are useless after they more than a year old. So the main parameter of this cleaning is the column “date_recorded” in bpactivity table
So here is the example:
DELETE FROM 'w_bp_activity' where 'date_recorded' < '2013-01-01';
Be patient, on my powerful production server it took 14 minutes to remove 3 600 000 records.