5

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

Libor Havlicek
  • 61
  • 1
  • 1
  • 3

2 Answers2

12

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.
Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 1
    With the "rate" unchanged after increasing those settings -- This implies that setting were already plenty big. – Rick James Sep 18 '15 at 19:37
  • Lovely answer! I didn't know that DISTINCT and GROUP BY will necessitate the creation of temporary tables. On a high traffic website, we currently have "Created_tmp_disk_tables / Uptime" as 5.35. The load on the server is ideal though, but it could be even better if that problem is addressed. – itoctopus Aug 11 '16 at 15:13
  • @itoctopus - If you can identify the main query causing the tmp tables, start a new question with EXPLAIN SELECT ... and SHOW CREATE TABLE. We may have a quick fix. – Rick James Aug 11 '16 at 17:40
  • @RickJames, you mention in your answer "...or MEMORY cannot be used (which I suspect)". What would cause a table to be written to disk instead of in-memory assuming tmp_table_size and max_heap_table_size are of sufficient values? I'm experiencing the same thing, high on-disk temp tables despite having large values for both the settings above. – Ryan Griffith Dec 28 '16 at 18:45
  • @RyanGriffith - There are two cases for switching to MyISAM: (1) certain restrictions (TEXT, etc) (there is a manual page on such), and (2) exceeding either ..._table_size setting (after trying to use MEMORY). There are too many possible cures to try to list them here. So let's look at your case -- open a Question with EXPLAIN SELECT ... and SHOW CREATE TABLE. – Rick James Dec 28 '16 at 19:08
  • when MEMORY can't be used (and other notes). – Rick James Dec 28 '16 at 20:51
1

If the rate is exactly the same, then you have some query or configuration tuning to do. Why ?

I have discussed tweaking tmp_table_size and max_heap_table_size before

You may have to look into ramping up join_buffer_size and sort_buffer_size (Look at my second post)

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520