经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
来源:cnblogs  作者:阿尔卑斯山  时间:2019/1/25 9:16:23  对本文有异议

背景描述

  1. # Time: 2019-01-24T00:08:14.705724+08:00
  2. # User@Host: **[**] @ [**] Id: **
  3. # Schema: sentrymeta Last_errno: 0 Killed: 0
  4. # Query_time: 0.315758 Lock_time: 0.001693 Rows_sent: 9664 Rows_examined: 36413 Rows_affected: 0
  5. # Bytes_sent: 1616970 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 16384
  6. # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
  7. # Filesort: No Filesort_on_disk: No Merge_passes: 0
  8. # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
  9. # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
  10. # InnoDB_pages_distinct: 1085
  1. total used free shared buffers cached
  2. Mem: 125 38 87 0 0 19
  3. -/+ buffers/cache: 18 107
  4. Swap: 31 0 31
  1. root@(none)04:33:02>select version();
  2. +---------------+
  3. | version() |
  4. +---------------+
  5. | 5.7.19-17-log |
  6. +---------------+
  7. 1 row in set (0.00 sec)
  8. root@(none)04:33:07>show variables like '%table_size%';
  9. +---------------------+-----------+
  10. | Variable_name | Value |
  11. +---------------------+-----------+
  12. | max_heap_table_size | 134217728 |
  13. | tmp_table_size | 16777216 |
  14. +---------------------+-----------+
  15. 2 rows in set (0.00 sec)

问题分析

Q1:为什么会产生临时表?

这个不多说,SQL写的惹不起,反正就是半个小时看不懂的那种,就是一眼就知道一定会产生临时表的??~~~

Q2:登录到机器上去查看内存使用偏小?

因为这个物理机的内存是125G,但是mysql的总数据量不超过1G,所有实际并不需要多少内存就可以将所有数据都加载都内存中。

Q3:既然内存够用,为啥还要在磁盘上产生临时表?

后面可以看见数据库配置的临时表空间是16M,从慢查询日志上来看每一个临时表的大小是16K,在QPS达到一定量了之后,临时表空间就达到了上限,就会产生临时磁盘表,看图下面的产生的【临时磁盘表/临时表】的比例也是符合预期,现在大概就每3条SQL其中有一条会产生临时表。解决办法就是把tmp_table_size这个参数调大,按照当前的计算,调大一半8M可以解决问题。但是,我现在的机器配置很豪,就开心的调大10倍啦~~~~

Q4:磁盘上产生临时表真的是SQL慢的根本原因吗?

通常我们会认为产生了临时表,就更不用说临时磁盘表,大部分就能确定慢查询的原因了。但是这次我还是怀疑了一下,实在是机器性能太好,想着16K的临时表真的有这么大的影响吗,而且我的磁盘性能【SSD、PCIE】感觉也很棒,O(∩_∩)O哈哈~。所以我统计了一下各个阶段的执行时间,发现 converting HEAP to ondisk 从内存中拷贝数据到磁盘消耗的时间并不多,16K对于这种高配的机器还是小case,真正的时间消耗在sending data上,为啥会这样呢?看上面的慢查询日志发现 Bytes_sent: 1616970 这个是1.54M,消耗时间比较多的是从引擎层发送数据给server层,因为这个SQL最后访问的数据比较多。做个简单测试,右边是原来的SQL执行时间,左边是我limit 5的统计结果,可以很直观的看到sending data时间上的差异,时间上查了0.011001/0.000131 ~ 84倍。但是这个和数据行数并不是线性增长关系的,原因嘛就是磁盘的访问方式。

  1. show profile for query 8;
  2. +----------------------+----------+
  3. | Status | Duration |
  4. +----------------------+----------+
  5. | starting | 0.000082 |
  6. | checking permissions | 0.000003 |
  7. | checking permissions | 0.000001 |
  8. | checking permissions | 0.000003 |
  9. | Opening tables | 0.000015 |
  10. | init | 0.000024 |
  11. | System lock | 0.000010 |
  12. | optimizing | 0.000010 |
  13. | statistics | 0.000098 |
  14. | preparing | 0.000014 |
  15. | Creating tmp table | 0.000033 |
  16. | executing | 0.000002 |
  17. | Sending data | 0.000131 |
  18. | end | 0.000003 |
  19. | query end | 0.000005 |
  20. | removing tmp table | 0.000049 |
  21. | query end | 0.000002 |
  22. | closing tables | 0.000015 |
  23. | freeing items | 0.000030 |
  24. | cleaning up | 0.000017 |
  25. +----------------------+----------+
  26. 20 rows in set, 1 warning (0.00 sec)

 

  1. show profile for query 1;
  2. +---------------------------+----------+
  3. | Status | Duration |
  4. +---------------------------+----------+
  5. | starting | 0.000165 |
  6. | checking permissions | 0.000005 |
  7. | checking permissions | 0.000002 |
  8. | checking permissions | 0.000006 |
  9. | Opening tables | 0.000027 |
  10. | init | 0.000057 |
  11. | System lock | 0.000015 |
  12. | optimizing | 0.000025 |
  13. | statistics | 0.000235 |
  14. | preparing | 0.000031 |
  15. | Creating tmp table | 0.000066 |
  16. | executing | 0.000003 |
  17. | Sending data | 0.011001 |
  18. | converting HEAP to ondisk | 0.005307 |
  19. | Sending data | 0.059461 |
  20. | end | 0.000004 |
  21. | query end | 0.000011 |
  22. | removing tmp table | 0.000137 |
  23. | query end | 0.000004 |
  24. | closing tables | 0.000026 |
  25. | freeing items | 0.000026 |
  26. | cleaning up | 0.000022 |
  27. +---------------------------+----------+
  28. 22 rows in set, 1 warning (0.00 sec)

 



 

 

原文链接:http://www.cnblogs.com/Alpes/p/10315960.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号