MySQL5.6找到临时表用到磁盘的SQL
来源:互联网 发布:吉他扒带软件 编辑:程序博客网 时间:2024/06/01 03:58
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 |
+------------------------------------------+-----+
+------------+
| 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 |
+------------------------------------------+-----+
阅读全文
0 0
- MySQL5.6找到临时表用到磁盘的SQL
- MySQL 找到临时表用到磁盘的SQL
- MySQL5.6 运用临时表做SQL优化
- 找到引起磁盘排序的SQL
- 找到引起磁盘排序的SQL
- 临时用到的文件
- 磁盘临时表与内存临时表的差异
- 磁盘临时表
- 临时表的分页SQL
- SQL 临时表的使用
- SQL 临时表的应用
- sql临时表的创建
- SQL 临时表的作用
- SQL临时表的使用
- sql临时表:SQL SERVER临时表的使用
- 临时表要舍磁盘临时表取内存临时表
- Oracle临时表和SQL Server临时表的…
- sqlserver 用到临时表去除重复列
- 第2章--算法,其实有趣又有用
- gmdate sec to hour minute sec 转换(超过24小时不可以使用,需要另外的代码辅助)
- UVA10237:Bishops(dp)
- Fragment 控件空指针问题
- JQ动画效果
- MySQL5.6找到临时表用到磁盘的SQL
- 安卓-广播技术的入门
- Python中元组,列表,字典的区别
- Javascript模块化编程(二):AMD规范
- react学习笔记
- java关键字 transient 和 关键字 volatile
- JAVA内存区域与内存溢出相关设置
- 代码截屏与禁止截屏
- PHP 性能分析与实验:性能的宏观分析