I’m not a db administrator, but the only sure thing I know is that having a wordpress multisite + buddypress installation with more than 10.000 users is very heavy task for a single database.
Right now we use a plugin (multidb) and have our database spilled in 17 databases spread in 4 database servers.
But, there are still cases, where the mysql is slowing down the site .
So we are always on the search for finding tips on improving the mysql performance (If you have some tips please share).
Recently we noticed that in the slow_queries.log that the same query was appearing again and again. This was strange, because the query should be cached on the 1st time.
So, this made us wonder if the MySql Query Cache was misconfigured.
A very usefull presentation of percona about MySql Query Cache http://www.percona.com/files/presentations/MySQL_Query_Cache.pdf helped us to spot what was wrong in our installation.
It turned out that mysql cache was fragmented. In the above presentation a nice “Tuning the Query Cache” helped us to make some configuration decisions.
We ‘ll wait and see if the decisions were in the right direction.