How to lower "Created_tmp_disk_tables" - temporary tables count created on DISK?

I have a ubuntu, mysql 5.6 DB that is heavy used by thousands of users. After 10 days of running I have this values:

| Created_tmp_disk_tables | 894170 |
| Created_tmp_files       | 26068  |
| Created_tmp_tables      | 914511 |

My config values are:

| tmp_table_size | 268435456 |
| max_heap_table_size | 1073741824 |

when I had tmp_table_size = 16MB and max_heap_table_size=16MB it was the same. Than I changed it to tmp_table_size = 256MB and max_heap_table_size = 1GB but Created_tmp_disk_tables did not stop growing. The rate is exactly the same.

I have 26GB heap. All tables are INNODB.

What am I missing?

Thanks

What do you mean by a "26GB Heap"?

tmp_table_size = 256M is dangerously high. If multiple connections decide to need tmp tables, you could run out of RAM. Swapping is worse for performance than lowering various settings.

Tmp tables are necessary in many situations. Don't fear them. But do check on them.

DISTINCT, GROUP BY, ORDER BY and UNION often require a tmp table. If the tmp table fits in min(tmp_table_size, max_heap_table_size), then the tmp table may be in RAM using Engine=MEMORY. If bigger than that, then the tmp table is Engine=MyISAM and is slower. There are other reasons for using MyISAM, most notably is selecting a TEXT field. More details.

Another common "error" is to blindly use VARCHAR(255) and utf8. When using MEMORY, that becomes a 765-byte CHAR, which hastens the conversion to MyISAM.

The numbers you gave...

• Created_tmp_disk_tables is nearly as large as Created_tmp_tables -- This implies that either tmp_table_size is not big enough (which I doubt), or MEMORY cannot be used (which I suspect). Above 20% is a red flag in my analysis.

• Created_tmp_tables / Uptime -- above 20/second is a red flag

• Created_tmp_disk_tables / Uptime -- above 1/second is a red flag

• Created_tmp_disk_tables / Questions -- above 4% is a red flag

Set long_query_time = 1 and turn on the SlowLog, preferably to FILE. Wait a day, then use pt-query-digest on the slowlog to find the "worst" queries. If you can't figure out how to improve them, ask us.

I'll disagree with Rolando on two items:

• RAM disk is a bad idea -- it is better to give the extra RAM to other caches rather than trying to outsmart MySQL.

• OPTIMIZE TABLE is rarely useful, and not very relevant for the question.

StackExchange Database Administrators Q#115499, answer score: 12