Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm seeing Created_tmp_disk_tables 10 k within the PHPMyAdmin status screen and it's in red. 10k is an awful lot. Is it my queries or server settings that are allowing temporary tables to get this high? Is there any way to log queries that are creating temporary tables (please say yes)?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

This is what you need to know..

If an internal temporary table is created initially as an in-memory table but becomes too large..

MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values.

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

BTW... When the server creates an internal temporary table (either in memory or on disk), it increments the Created_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

What your seeing is an increment.. so when was the last time you restarted your mysql?? :D

share|improve this answer
    
It's been about 48 hours –  Webnet Nov 11 '10 at 17:14
    
Is there a way I can tell which queries are ending up on-disk? –  Webnet Nov 11 '10 at 17:21
    
dev.mysql.com/doc/refman/5.1/en/slow-query-log.html By using the slow-query-log, try to make anything longer than 1 sec fast.. By nature of disk tables.. they could be, and might be above 10seconds.. i have seen queries for 55 seconds from terrible keys and memory management –  Arenstar Nov 11 '10 at 17:25
    
I ran the slow query check for like 6 hours and didn't see any in the logs, so I went back to logging queries without indexes. I guess I might need more time? Is it just long run queries that might end up on the disk? –  Webnet Nov 11 '10 at 17:33
    
Are you sure it was working? i mean.. there is a good chance it wasnt running long enough.. I would restart your mysql server to reset the increment ( or do it manually ) and turn on slow query log at 1 second.. and wait.. You should see logs if that increment changes –  Arenstar Nov 11 '10 at 17:36
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.