MySQL excessive tmp disk tables after Upgrade to 5.6

Question

After upgrading two MySQL-Servers which run Master-Slave replication from Percona MySQL 5.5 to Percona MySQL 5.6 an excessive amount of temporary Tables gets created on the slave.

The Master seems to have a similar rate as before.

On the slave we went from an average of roughly 35 per second to about 1000 per second. (these are very rough averages). We have about 3200 Queries per second on this server.

root:/var/lib/mysql# mysql -e "show global status like 'Created_tmp%_tables';" -e "show global status like 'Uptime%';"

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 1485718 |
| Created_tmp_tables      | 1505629 |
+-------------------------+---------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 1523  |
| Uptime_since_flush_status | 1523  |
+---------------------------+-------+

As you can see, nearly all of the temporary tables are created on disk (~98%). Or rather a RAM-Disk for increased performance.

The configuration in my.cnf hasn't really changed. At most some Options have been renamed to the full length name.

Tweaking tmp_table_size, max_heap_table_size, join_buffer_size and sort_buffer_size doesn't seem to make any difference.

So my question now is what could cause this behaviour?

I've read the Upgrade notes, searched this site and generally googled this issue, but didn't come up with anything that would help me.

If I've forgotten some much needed information, please tell me.

Any help is greatly appreciatet.

Answer 1

There are several reasons why that could be happening, and it is very difficult to point one specific cause -it could be because a change on the query optimizer, bad query statistics, parameters whose defaults changed from version to version, the changes on replication format, the data types you are handling, a change on the row format, etc.

The critical point here is to identify the differences on query execution: it would be nice if that would have been identified before the upgrade, but if you say that the master is not showing that behaviour, that may be enough. You need to discover which queries are creating those temporary tables. As you are using Percona Server 5.6, there are 2 main tools:

• use the extended logging available on Percona and using pt-query-digest to identify and summary the offending queries, or
• Use the performance_schema available since 5.6

Both tools can be used for identifying those bad queries. After that, you will be able to profile each query with the standard tools: EXPLAIN, the profiling metrics, the handler statistics, etc. In some cases, you may need to change the configuration, in other the queries, indexes or the structure itself of your database (for example, blobs usually cause problems because temporary tables using them cannot be on memory).

One thing that may actually be useful, independently of all of above is the replication format. If you are suffering from temporary table creation on the slave you are using STATEMENT or MIXED replication format (or your tables do not have primary keys). Row based replication, in some cases (when queries do not modify many records at the same time) may help with both query latency and avoiding slave drifting, so you should at least consider that.

Answer 2

As it turned out the default value of query_cache_type changed to OFF with MySQL 5.6.8. Before that the default was ON.

See https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_type