High created_tmp_disk_tables

You should monitor this, along with several other status vars regularly. I have an automated report create bar charts to rank all the servers I watch.

Why is this important?

Most MySQL DBs lately are configured using the InnoDB engine. Don’t get me wrong, I like MyISAM just the same, I’m just staging an observation.
Knowing many MySQL DBs are InnoDB, they are usually also configured my.cnf to optimize that engine, and not MyISAM

According to  http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_tmp_table_size

If a temp table exceeds the current space allocated it will convert that temp table to an on disk MyISAM table…. this is a big EEEK!!! if the my.cnf is not configured well with MyISAM.

Check out what you have configured by doing this:

CONCAT(round(@@tmp_table_size/1028/1028/1028,2),’G') AS tmp_table_size,
CONCAT(round(@@max_heap_table_size/1028/1028/1028,2),’G') AS max_heap_table_size;

Most servers I’ve clicked should have at least 4G configured for each. If you have tons more ram then up it.

But that isn’t the end of it. Also consider max_connections to determine if you’re over allocating the memory. Too much allocation is also bad.

show global status where
Variable_name like ‘%connections’;

select @@max_user_connections, @@max_connections


Posted in MySQL

Leave a Reply