Created_tmp_disk_tables very high

This MySQL server has been running for 0 days, 1 hours, 59 minutes and 42 seconds.

Query statistics: Since its startup, 246,498 queries have been sent to the server.

I dont understand why my tmp tables on disk would be so high with a 1G tmp_table_size and Max_heap_table_size

I am running CentOS5.3 and 5.0.82 percona b16… I have 12GB of ram and dual 3.04G CPU. Just upgraded to be able to change my temp directive.

Created_tmp_disk_tables 34 k
Created_tmp_files 5
Created_tmp_tables 49 k

tmp_table_size = 1G
max_heap_table_size = 1G

Any ideas?

Text columns cannot be sorted in memory. So improve your indices.

Im running wordpress 2.7 on all the domains… Not sure if I can improve the indices?

Sounds like its more then just the index since you say the text columns can not be sorted in memory. Almost every query includes a text column.

I have created a tmpfs for the tmp data area I expect that is helping by allowing the tmp files to be created in ram disk.

Wordpress has very bad database utilization. The indices are bad, the queries are bad. Wordpress wasn’t ever designed to handle large blogs.

I have put some efforts in optimizations of Wordpress, you should update to the most recent version. That helps only slightly. You should use supercache or a similar plug-in, and in the long-term look out for better blogging software if you intend to grow.

Well, we have already grown to 1400 blogs… that’s the problem… Backend posting is taking forever. (

I have moved all SQL to a dedicated server and innobd only databases.

Then reduce the size of those in-memory tables to default values (couple of meg each iirc) and increase your innodb buffer pool. And take a look at other innodb tweaking parameters. That should help some, but you have got to live with on disk sorting for Wordpress.

And remove all Wordpress plug-ins or make sure you can optimize them yourself. Most are bad as well.

Thanks for the feedback

You just reconfirmed where my mind was going on all this.

I am not going to waste the 1G on temp tables in ram if they are going to disk anyway. I am also going to put in a second MySQL server and split the load, besides the other items you suggested.

Long term I need to find another blog that manages data and queries better. Its going to be a bear to move everything, but the problem is only going to get worse. For now I just need to throw more hardware at the problem.