MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

Wesley13
• 阅读 307

背景描述

# Time: 2019-01-24T00:08:14.705724+08:00
# User@Host: **[**] @  [**]  Id: **
# Schema: sentrymeta  Last_errno: 0  Killed: 0
# Query_time: 0.315758  Lock_time: 0.001693  Rows_sent: 9664  Rows_examined: 36413  Rows_affected: 0
# Bytes_sent: 1616970  Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 16384
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 1085

total       used       free     shared    buffers     cached
Mem:           125         38         87          0          0         19
-/+ buffers/cache:         18        107
Swap:           31          0         31

root@(none)04:33:02>select version();
+---------------+
| version()     |
+---------------+
| 5.7.19-17-log |
+---------------+
1 row in set (0.00 sec)

root@(none)04:33:07>show variables like '%table_size%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 134217728 |
| tmp_table_size      | 16777216  |
+---------------------+-----------+
2 rows in set (0.00 sec)

问题分析

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

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

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

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

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

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

MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

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倍。但是这个和数据行数并不是线性增长关系的,原因嘛就是磁盘的访问方式。

show profile for query 8;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000082 |
| checking permissions | 0.000003 |
| checking permissions | 0.000001 |
| checking permissions | 0.000003 |
| Opening tables       | 0.000015 |
| init                 | 0.000024 |
| System lock          | 0.000010 |
| optimizing           | 0.000010 |
| statistics           | 0.000098 |
| preparing            | 0.000014 |
| Creating tmp table   | 0.000033 |
| executing            | 0.000002 |
| Sending data         | 0.000131 |
| end                  | 0.000003 |
| query end            | 0.000005 |
| removing tmp table   | 0.000049 |
| query end            | 0.000002 |
| closing tables       | 0.000015 |
| freeing items        | 0.000030 |
| cleaning up          | 0.000017 |
+----------------------+----------+
20 rows in set, 1 warning (0.00 sec)

show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000165 |
| checking permissions      | 0.000005 |
| checking permissions      | 0.000002 |
| checking permissions      | 0.000006 |
| Opening tables            | 0.000027 |
| init                      | 0.000057 |
| System lock               | 0.000015 |
| optimizing                | 0.000025 |
| statistics                | 0.000235 |
| preparing                 | 0.000031 |
| Creating tmp table        | 0.000066 |
| executing                 | 0.000003 |
| Sending data              | 0.011001 |
| converting HEAP to ondisk | 0.005307 |
| Sending data              | 0.059461 |
| end                       | 0.000004 |
| query end                 | 0.000011 |
| removing tmp table        | 0.000137 |
| query end                 | 0.000004 |
| closing tables            | 0.000026 |
| freeing items             | 0.000026 |
| cleaning up               | 0.000022 |
+---------------------------+----------+
22 rows in set, 1 warning (0.00 sec)
点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
1年前
030 SSM综合练习06
1.权限操作涉及的三张表(1)用户表信息描述users!(https://oscimg.oschina.net/oscnet/a4a2b1f943cbc2db1c8ddd613e7ed00a9ae.png)sql语句:CREATETABLEusers(idVARCHAR2(32)DEFAU
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
1年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Wesley13 Wesley13
1年前
oracle:ORA
报ORA25153,这一定是临时表空间逻辑名还在,但是找不到临时文件的原因。解决方法:1、查询临时表空间状态状态selecttablespace\_name,statusfromdba\_tablespaces;TABLESPACE\_NAMESTATUS\SYSTEMONL
helloworld_34035044 helloworld_34035044
5个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
3A网络 3A网络
2个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
京东云开发者 京东云开发者
1星期前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
3A网络 3A网络
2个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t