[MYSQL] 一条查询SQL就把文件系统占满了?

导读

环境: mysql-8.0.20, 参数是默认的.

现象: 某文件系统一直涨, 很快就100%了, 然后又降下来了; 但是看不到是哪个文件占用的.

在100%附件挣扎了几下就慢慢降下来了.

分析

由于du和df看到的结果不一样,最开始想到的是预分配问题,但本次并不是.

期间发现数据库存在一条类似如下的SQL:

WITH RECURSIVE cte (id,k) AS
(
  SELECT id,k from db1.sbtest2
  UNION ALL
  SELECT id,k from cte
)
SELECT * FROM cte;

就这么一条SQL, 那锅就是这条SQL的了. 该SQL使用了递归,做了多次union操作,把临时表干满了.

我这里模拟的CTE没有写退出条件, mysql默认的递归深度是1000次, 由参数cte_max_recursion_depth控制.

期间还发现存在大量的DEL文件, 且路径是@@global.tmpdir

那基本上就可以确定是临时表导致的了, 我们知道临时文件创建之后会unlink掉, 故看不到文件,也就看不到大小了.

但看不到大小,说服力不强啊(虽然可能能看到的最后一个文件的大小). 我们知道lsof默认输出字段为:

COMMAND  PID  USER   FD      TYPE             DEVICE   SIZE/OFF      NODE NAME

倒数第三列就是size,但我们看到大部分的size都是空的. 好在倒数第二列是inode. 我们以前解析过xfs, 知道可以通过inode找到文件的基本信息, 比如文件大小, 于是我们简单写了几条命令来快速查看各临时文件的大小:

# xfs_recovery是ibd2sql-v1.x版本的附带品,故下载ibd2sql-v1.x版本即可
wget https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.12.tar.gz
tar -xvf v1.12.tar.gz
cd ibd2sql-1.12

# 设置临时文件前缀
MYSQLTMPDIRPRE='/data/mysql_8020/mysqllog/tmp/mysql_temptable'
# 设置临时文件所在磁盘
MYSQLTMPDIR_DEVNAME=`df -T $(echo ${MYSQLTMPDIRPRE%/*}) | tail -1 | awk '{print $1}'`
# 获取各临时文件大小和最终大小
lsof -p `pidof mysqld` | awk -v "var=${MYSQLTMPDIRPRE}" '{if($NF ~ "^" var){print $(NF-1),$NF} else if($(NF-1) ~ "^" var){print $(NF-2),$(NF-1)}}' | while read inode filename;do filesize=$(python3 xfs_recovery_v0.3.py ${MYSQLTMPDIR_DEVNAME} ${inode} | awk -F ':' '{if ($1=="extent_size") print $2}');echo -e "FILENAME: ${filename}\tINODE: ${inode}\tFILESIZE: ${filesize} bytes";done | awk '{sum += $(NF-1);print $0} END {printf "TOTAL SIZE: %.2f GB\n",sum/1024/1024/1024}'

计算出来的大小正好和df变化的大小匹配上, 说明确实是临时表导致的.

多次观察后发现, 临时表只有一直涨, 若出现某个临时表被干掉的情况,就表示sql处于"removing tmp table"状态了, 然后就是SQL由于磁盘空间不够导致执行失败.

当然mysql.error日志也是能看到相关记录的:

[ERROR] [MY-013132] [Server] The table '/data/mysql_8020/mysqllog/tmp/#sqlb1b_d_4' is full!

临时表

临时表分为用户自建临时表和内部临时表.

用户自建临时表为: 用户显示使用SQLCREATE TEMPORARY TABLE创建的表, 该表默认使用的存储引擎为default_tmp_storage_engine参数决定(默认InnoDB).

内部临时表为: 一些SQL执行中间需要使用的临时表, 为mysql自动创建,自动销毁,用户无法直接控制. 那么哪些情况会自动创建内部临时表呢? 官网有写: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

1. 某些Union语句.
2. 某些视图,比如使用 TEMPTABLE 算法, UNION,或者聚合.
3. 派生表(Derived Tables)
4. CTE(8.0)
5. 子查询或者semijoin 物化
6. ......

后续我们说的临时表均表示内部临时表.

默认使用的临时表是在内存中的,如果内存不够才会使用磁盘, 但是内存临时表和磁盘临时表的存储引擎不一样,就涉及到存储引擎的转换,是比较消耗资源的, 所以可以设置big_tables为ON,这样只要是排序就一定使用磁盘,省去了一个转换的过程. 比如:

(root@127.0.0.1) [(none)]> show global status like '%Created_tmp_disk_tables%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 5     |
+-------------------------+-------+
1 row in set (0.00 sec)

(root@127.0.0.1) [(none)]> set session big_tables=ON; select 3 union select 2 order by 1; set session big_tables=OFF;
Query OK, 0 rows affected (0.00 sec)

+---+
| 3 |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [(none)]> show global status like '%Created_tmp_disk_tables%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 6     |
+-------------------------+-------+
1 row in set (0.00 sec)

缺点也很明显, 就是对IO使用高了,比如就2行数据,内存很快就结束了,走IO的话,就得花点时间.除非业务能缺点它的SQL一定会使用到磁盘临时表,此时可以临时修改下会话级的该参数;否则不要修改该参数.

内存临时表

我们首先来看下内存中的临时表(sql_tables=OFF). 内存临时表使用的存储引擎由internal_tmp_mem_storage_engine 参数控制,可选TempTable(默认)和MEMORY. 该参数是8.0才有的, 因为5.7的内存临时表只支持MEMORY.

TempTable

TempTable对varchar/varbinary或者其它大对象存储效果比较好. 使用的内存由参数 temptable_max_ram控制(默认1GB)

8.0.16 引入了mmap, 使用参数temptable_use_mmap控制. 当启用(默认)该参数时, 内存超过上限后,就会使用mmap(memory-mapped files),内存映射文件,就是拿一块内存来映射文件. 此时既使用内存,又使用磁盘文件. 使用的内存多大呢? 8.0.23引入了参数temptable_max_mmap来限制(默认1GB).所以实际使用的内存上限就是 temptable_max_ram + temptable_max_mmap. 该功能有点鸡架,在8.0.26版本就移除了. 在8.0.28中,tmp_table_size参数也可以控制TempTable引擎使用的最大内存,所以实际上TempTable引擎能使用的最大内存就是min(tmp_table_size, sum(temptable_max_ram+temptable_max_mmap) )

MEMORY

memory听名字就知道只存在与内存中, 该存储引擎的内存上限是min(tmp_table_size,max_heap_table_size)

磁盘临时表

当内存中的临时表空间不够的时候, 就会转到磁盘上. 使用的存储引擎由参数internal_tmp_disk_storage_engine控制, 可选InnoDB和MyISAM, 在8.0.16及其之后就只支持Innodb了, 该参数也就移除了.

当temptable_use_mmap=ON且需要使用磁盘临时文件时: 会先使用内存磁盘映射文件, 该路径使用的是tmpdir, 如果是8.0.23之前, 没有参数temptable_max_mmap限制其内存大小, 就会一直使用,直到文件系统跑满,sql报错(文章开头的案例). sql执行完成/失败会释放空间.

[ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_10.ibt

当temptable_use_mmap=OFF且需要使用磁盘临时文件时: 使用的是innodb_temp_tablespaces_dir参数指定的目录, 该目录下由一堆表空间文件构成,每个连接使用1-2个, 连接断开(非SQL执行完成/失败)就会释放空间. 比5.7版本大家都使用ibtmp1,需要重启才能释放空间好一些.

临时表相关参数

临时表相关参数还是比较多的, 5.7还能使用innodb_temp_data_file_path控制磁盘使用大小, 8.0使用的是会话级别的表空间, 只有会话断开才会释放, 且没得相关参数限制其大小.

这里就简单列出一些常见的临时表相关的参数:

innodb_tmpdir: online ddl使用的目录若值为null,则使用os的TMPDIR;若值为无效目录(比如目录不存在),则使用@@tmpdir目录

slave_load_tmpdir/replica_load_tmpdir: 从库回放load data时使用的临时目录(将relay log中的数据放到slave_load_tmpdir,然后再load),默认为@@tmpdir

tmpdir: 全局临时目录,可支持多个路径(轮询)

default_tmp_storage_engine: 用户创建临时表的默认存储引擎

internal_tmp_mem_storage_engine: 自动创建内存临时表的默认存储引擎 (8.0.27之后设置会话级别值需要session_variable_admin/system_variable_admin权限)

internal_tmp_disk_storage_engine: 自动创建磁盘临时表的默认存储引擎, 8.0.16之前支持innodb和myisam, 之后就只支持innodb.该参数也就没效果了

tmp_table_size: memory存储引擎使用的内存临时表的最大限制,默认16MB. 8.0.28及其之后也适用于temptable存储引擎, 也就是8.0.28及其之后内存超过16MB就使用磁盘(temptable-->innodb).由于temptable_max_mmap和temptable_max_ram也是限制内存的,所以实际生效就是min(tmp_table_size,sum(temptable_max_mmap,temptable_max_ram))

max_tmp_tables: 未使用

innodb_temp_data_file_path:定义全局临时表空间路径,大小之类的信息: 比如:ibtmp1:50M:autoextend:max:30G. 5.7记录临时表数据和临时表回滚段, 8.0就只记录回滚段了(数据放innodb_temp_tablespaces_dir).

temptable_use_mmap: temptable存储引擎是否使用内存文件映射(memory-mapped files). 8.0.16-26默认是ON,建议OFF. 当此参数为ON,且临时表使用内存达到temptable_max_ram, 则使用mmap, 对应磁盘上的文件路径是@@tmpdir; 当此参数为OFF,且临时表使用内存达到temptable_max_ram, 则使用@@innodb_temp_tablespaces_dir目录. 空间分配使用的是posix_fallocate, 8.0.26就移除了,这玩意比较鸡架.超过限制后,mmap磁盘上还是innodb存储引擎

temptable_max_ram: temptable存储引擎使用内存的最大限制,超过之后使用磁盘

temptable_max_mmap: temptable存储引擎使用mmap文件的总大小(默认1G),超过则直接使用磁盘. 8.0.23才引入.

innodb_temp_tablespaces_dir: 会话临时表空间,就是8.0.13开始从ibtmp1里面把数据部分分出来, 每个会话使用1-2个表空间, 默认路径是@@datadir/#innodb_temp目录. 会话(非SQL)结束后就可回收用户使用的临时表空间文件

max_heap_table_size: memory存储引擎使用的内存临时表的最大限制,默认16MB. memory是堆表, 所以可以这个参数限制. 实际上是取 min(tmp_table_size,max_heap_table_size)

big_tables: 内存表转磁盘表,涉及到存储引擎的转换,比较浪费性能, 如果启用big_tables参数, 则直接用磁盘空间存储临时表. 可以理解为内存临时表的开关, 默认OFF(即先使用内存)

临时表监控

可以使用show global status查看创建了多少内存临时表和磁盘临时表(这条show语句本身也会使用临时表), 但是看不到大小. 所以还可以配置performance监控内存:

可以开启Pf的memory/temptable/physical_ram 和 memory/temptable/physical_disk 来监控

(root@127.0.0.1) [performance_schema]> select * from performance_schema.memory_summary_global_by_event_name  WHERE EVENT_NAME
in( 'memory/temptable/physical_ram','memory/temptable/physical_disk')\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 55
                  COUNT_FREE: 49
   SUM_NUMBER_OF_BYTES_ALLOC: 29527900160
    SUM_NUMBER_OF_BYTES_FREE: 26306674688
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 6
             HIGH_COUNT_USED: 49
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 3221225472
   HIGH_NUMBER_OF_BYTES_USED: 26306674688
*************************** 2. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 21
                  COUNT_FREE: 9
   SUM_NUMBER_OF_BYTES_ALLOC: 2146435072
    SUM_NUMBER_OF_BYTES_FREE: 1071644672
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 12
             HIGH_COUNT_USED: 12
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1074790400
   HIGH_NUMBER_OF_BYTES_USED: 1074790400
2 rows in set (0.00 sec)

启用方法:

-- 只临时开启(重启失效)
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/%';

-- 永久开启(修改配置文件)
performance-schema-instrument='memory/%=ON'

总结

本次现象是业务SQL使用临时表太大导致的, 因为使用的是mmap(temptable_use_mmap=on),sql执行失败后,空间就释放了; 但如果使用的是innodb_temp_tablespaces_dir(temptable_use_mmap=off),则需要连接断开才会释放; 如果是5.7,则只能重启才会释放ibtmp1. 当然如果sql上线前有审计,可能就没得这些坑了.

本次案例理论上适用于8.0.13-8.0.23版本

参考:

https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html