Created_tmp_disk_tables very high

MySQL & MariaDB Other MySQL® Questions
1

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?

2

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

3

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.

4

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.

5

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.

6

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.

7

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.