The BuddyPress activity table is the one that grows

A community site’s posts table stays in the tens of thousands while bp_activity reaches millions, because it records everything: an update, a comment, a friendship, a group join, a profile field edit. On 1.5 and 1.6 it also records a row every time a member is seen, which means the table grows with logins rather than with content.

SELECT component, type, COUNT(*) AS n
  FROM wp_bp_activity
 GROUP BY component, type
 ORDER BY n DESC;

-- members | last_activity     | 2841902
-- groups  | joined_group      |   41220
-- activity| activity_update   |   18744

-- the meta table is usually larger than the activity table itself
SELECT COUNT(*) FROM wp_bp_activity_meta;

-- what a prune looks like, once you know which type dominates
SELECT COUNT(*) FROM wp_bp_activity
 WHERE type = 'last_activity'
   AND date_recorded < DATE_SUB( NOW(), INTERVAL 6 MONTH );

Run the first query before doing anything else, because which type dominates decides the fix and it is rarely the one anyone guesses. last_activity usually is, and no stream ever displays it. Deleting rows straight out of the table leaves orphans behind in bp_activity_meta, so you trade one large table for another unless the meta goes with them — bp_activity_delete() handles both and is worth the slower loop for anything you are not certain about. Prevention is cheaper than pruning: deactivating components that are not used stops the rows being written at all, and that is the only change that holds. Two operational notes. The stream query orders by date_recorded DESC with a hide_sitewide filter, so check EXPLAIN again after a large delete rather than assuming it improved. And on InnoDB the space is not returned to the filesystem until the table is rebuilt, which locks it for the duration — schedule it, do not discover it.