MySQL 8.0记一次Temporary file write failure

背景:开发同事反馈数据库有报错Temporary file write failure,咋一看临时文件磁盘满了?然鹅并不是~

处理过程
1.首先要来SQL自己先跑一遍,果然报错复现了ERROR 1878 (HY000): Temporary file write failure.
2.检查临时文件路径以及对应的磁盘使用情况:
mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tmpdir | /opt/my3306/tmp |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> system df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 281M 3.6G 8% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/centos-root 50G 22G 29G 44% /
/dev/sda1 1014M 151M 864M 15% /boot
/dev/mapper/centos-home 46G 19G 27G 42% /home
/dev/mapper/centos-DATA 150G 20G 131G 14% /opt
tmpfs 783M 0 783M 0% /run/user/0

3.思考:磁盘没满,尝试建立起临时表也没问题,看看执行计划:
+----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+--------------------------------------------+
| 1 | SIMPLE | tb1 | NULL | ALL | IDX_mt_hour_dealer_id | NULL | NULL | NULL | 196625 | 0.05 | Using where |
| 1 | SIMPLE | tb2 | NULL | ALL | NULL | NULL | NULL | NULL | 8254 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+-----------------------+------+---------+------+--------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
执行计划中使用了HASH JOIN(依赖于join buffer),会不会是因为join buffer导致的问题呢,赶紧看看:
mysql> show variables like '%join%';
+------------------+----------------------+
| Variable_name | Value |
+------------------+----------------------+
| join_buffer_size | 262144 |
| max_join_size | 18446744073709551615 |
+------------------+----------------------+
2 rows in set (0.00 sec)

mysql> select 262144/1024/1024;
+------------------+
| 262144/1024/1024 |
+------------------+
| 0.25000000 |
+------------------+
1 row in set (0.00 sec)
join buffer在MySQL优化中起着至关重要的作用,设置太小。

4.临时解决方案:
将join_buffer设置调大之后,SQL执行成功,解释join buffer是内存中的一块区域,join buffer不够的情况下,会使用到临时文件,现在将join buffer调大,不就可以一定程度避免使用到临时文件了吗。

5.深入分析:
本身磁盘,以及文件路径权限没有问题,但是临时文件用不了,是否是MySQL open_file_limit参数限制住?
a.看了一下参数文件,发现各参数设置的挺大的,但是数据库层看到的却很小,那就是OS参数有问题:
shell> cat my.cnf |grep open
table_open_cache = 2000
table_open_cache_instances = 4
innodb_open_files = 4000

mysql> show variables like '%open%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| have_openssl | YES |
| innodb_open_files | 400 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 1024 |
| table_open_cache | 400 |
| table_open_cache_instances | 4 |
+----------------------------+-------+

b.看一下操作系统内核参数:
shell> ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 31191
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 4096
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

shell> vim /etc/security/limits.conf
* soft nproc 20480
* hard nproc 20480
* soft nofile 65535
* hard nofile 65535

c.重启数据库
为了排除join_buffer_size的影响,将该参数调回原来的值。

d.验证参数:
mysql> show variables like '%open%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| have_openssl | YES |
| innodb_open_files | 4000 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 65535 |
| table_open_cache | 2000 |
| table_open_cache_instances | 4 |
+----------------------------+-------+

e.验证SQL执行情况(可以执行!)

6.总结:
join buffer在MySQL优化中有很重要的作用,在硬件配置允许的情况下,可以尽量设置大点。
官方文档中对于open_files_limit参数算法的说明:
算法基于系统启动时指定的值(如果有的话)和max_connections和table_open_cache的值
10 + max_connections + (table_open_cache * 2)
max_connections * 5
Operating system limit if that limit is positive but not Infinity
If operating system limit is Infinity: open_files_limit value if specified at startup, 5000 if not
原文链接:https://blog.csdn.net/justaiiit/article/details/111592894

上一篇:MySQL临时表


下一篇:C/C++编程笔记:C++中的std :: get_temporary_buffer