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