MySQL 找到创建临时表到磁盘的SQL

mysql> select version();

+------------+

| version()  |

+------------+

| 5.6.14-log |

+------------+

mysql> show global status like 'Created_%_tables';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 4836110|

| Created_tmp_tables      | 46376366|

+-------------------------+-------+


数据库启动一周,产生的临时表惊人,设置的两个变量tmp_table_size = 16777216, max_heap_table_size=64M。

从nmon上看IO消耗比较大,特别是IOPS已经达到了瓶颈,现在要定位出是哪些SQL导致的这个问题。


官方文档的解释:

Created_tmp_disk_tables

The number of internal on-disk temporary tables created by the server while executing statements.

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 andmax_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.


这些SQL会产生:DISTINCT, GROUP BY, ORDER BY and UNION。如果需要的临时表 大于 min(tmp_table_size, max_heap_table_size),就会需要放到磁盘上。


有一个表是监控产生Created_tmp_disk_tables的SQL。

mysql> use performance_schema

mysql> select * from setup_consumers;

+--------------------------------+---------+

| NAME                           | ENABLED |

+--------------------------------+---------+

| events_stages_current          | NO      |

| events_stages_history          | NO      |

| events_stages_history_long     | NO      |

| events_statements_current      | YES     |

| events_statements_history      | NO      |

| events_statements_history_long | NO      |

| events_waits_current           | NO      |

| events_waits_history           | NO      |

| events_waits_history_long      | NO      |

| global_instrumentation         | YES     |

| thread_instrumentation         | YES     |

| statements_digest              | YES     |

+--------------------------------+---------+


mysql> UPDATE setup_consumers SET ENABLED='YES' WHERE NAME IN ('events_statements_history_long');

mysql> select * from setup_consumers;

+--------------------------------+---------+

| NAME                           | ENABLED |

+--------------------------------+---------+

| events_stages_current          | NO      |

| events_stages_history          | NO      |

| events_stages_history_long     | NO      |

| events_statements_current      | YES     |

| events_statements_history      | NO      |

| events_statements_history_long | YES     |

| events_waits_current           | NO      |

| events_waits_history           | NO      |

| events_waits_history_long      | NO      |

| global_instrumentation         | YES     |

| thread_instrumentation         | YES     |

| statements_digest              | YES     |

+--------------------------------+---------+


history_long表则记录了最近所有线程产生的10000个事件,是可以配置的。

mysql> show variables like 'performance_schema%history%size';

+--------------------------------------------------------+-------+

| Variable_name                                          | Value |

+--------------------------------------------------------+-------+

| performance_schema_events_stages_history_long_size     | 10000 |

| performance_schema_events_stages_history_size          | 10    |

| performance_schema_events_statements_history_long_size | 10000 |

| performance_schema_events_statements_history_size      | 10    |

| performance_schema_events_waits_history_long_size      | 10000 |

| performance_schema_events_waits_history_size           | 10    |

+--------------------------------------------------------+-------+


mysql> SELECT sql_text, COUNT(*) AS cnt  FROM performance_schema.events_statements_history_long

     WHERE  CREATED_TMP_DISK_TABLES > 0;

+------------------------------------------+-----+

| sql_text                                 | cnt |

+------------------------------------------+-----+

| select distinct * from t1  LIMIT 0, 1000 |   2 |

+------------------------------------------+-----+

————————————————

版权声明:本文为CSDN博主「深圳gg」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/stevendbaguo/article/details/73229562


发表评论

必填

选填

选填

必填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。