MySQL created tmp disk tables performance issues

Hi All!

this is my first post here. It’s not about direct support with plugins or themes, but i would like to ask expert people using wordpress mu.

I’ve a blog network (40 blogs) with wordpress mu and more/less 100k visits a day, 150k pageviews/day, webserver and msyql on the same server with varnish as reverse proxy (same server).

The problem is that our server (2xQuad, 4G Ram, SAS HD) is having performance issues on some peaks and mysql use almost all the CPU and some queries takes many seconds so pages load slowly.

Running some mysql tuning tools always get the same report: problems with temporary tables created on disk.

For example, running mysql tuner from http://mysqltuner.com/ i get:

Temporary tables created on disk: 42% (343K on disk / 803K total)

My mysql configuration is:

tmp_table_size = 96M

max_heap_table_size = 96M

table_cache = 2048

Please, based on your experience, what should be a common ratio for temporary tables on disk?

WordPress with no plugins or themes has this problem or is a plugin/theme cause?

i’m looking for a point to start checking :slight_smile:

thanks!