MYSQL临时表导致根分区爆满问题分析

in 互联网技术 with 0 comment  访问: 159 次

问题现象

突然接到线上Zabbix告警信息,报MYSQL所在的主机/分区不足15%,内容如下:

Trigger: app-ali-prod-db1 / 可用空间不足 15%
Trigger status: PROBLEM
Trigger severity: Warning
Trigger URL: 

Item values:

1. Free disk space on / (percentage) (app-ali-prod-db1:vfs.fs.size[/,pfree]): 12.68 %
2. *UNKNOWN* (*UNKNOWN*:*UNKNOWN*): *UNKNOWN*
3. *UNKNOWN* (*UNKNOWN*:*UNKNOWN*): *UNKNOWN*

接受到告警信息,那就登录到服务器看看怎么回事,什么东西占用了/分区:

[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   15G  3.6G  87% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G  511M  98% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G  359M  99% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G   96M 100% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data
[nock@app-ali-prod-db1 nock]# sudo df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G   19G   67M 100% /
tmpfs           7.8G     0  7.8G   0% /dev/shm
/dev/vdb        394G  124G  251G  34% /data

如上所示/分区的使用率还在一直变大,那就看看具体哪个分区下文件占用的:

[nock@app-ali-prod-db1 ~]# sudo du -csh /*
6.2M  /bin
90M /boot
124G  /data
160K  /dev
29M /etc
20K /home
234M  /lib
23M /lib64
16K /lost+found
4.0K  /media
4.0K  /mnt
68K /opt
0 /proc
316K  /root
16M /sbin
4.0K  /selinux
4.0K  /srv
513M  /swapfile
0 /sys
320K  /tmp
883M  /usr
218M  /var
126G  总用量

但是根据du命令统计,不对啊,那是什么占用了呢,只好去看看最近操作了什么。

原因分析

原来是因为最近在做MYSQL表优化的操作,既然是操作MYSQL引起的,那我就自然让我想起了MYSQL临时表了,那我们就先看看MYSQL产生临时表目录,线上怎么设置的:

mysql> show variables like 'tmpdir';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| tmpdir        | /tmp                 |
+---------------+----------------------+
1 row in set (0.00 sec)

我们再来看看系统情况吧,看看临时文件情况:

[nock@app-ali-prod-db1 ~]# sudo lsof |egrep  'tmp|deleted'
mysqld_sa  9847   root    2u      CHR              136,1         0t0          4 /dev/pts/1 (deleted)
mysqld    11002  mysql    5u      REG              252,1           0     663996 /tmp/ibqhlTQx (deleted)
mysqld    11002  mysql    6u      REG              252,1           0     663997 /tmp/ibw9oKol (deleted)
mysqld    11002  mysql    7u      REG              252,1           0     663998 /tmp/ibdyLBW8 (deleted)
mysqld    11002  mysql    8u      REG              252,1           0     663999 /tmp/ib9Z2RkL (deleted)
mysqld    11002  mysql   12u      REG              252,1           0     664000 /tmp/ibvDAytA (deleted)
mysqld    11002  mysql   60u      REG              252,1  8092909568     661724 /tmp/ibzvPPU1 (deleted)
mysqld    11002  mysql   62u      REG              252,1           0     661734 /tmp/ibam4uXx (deleted)
mysqld    11002  mysql   65u      REG              252,1  4447010816     661735 /tmp/ib9ZmD63 (deleted)
mysqld    11002  mysql   78u      REG              252,1  2729443328     661736 /tmp/ibDgZ9mA (deleted)
mysqld    11002  mysql   88u      REG              252,1  1364197376     661737 /tmp/ibKU9e56 (deleted)

解决问题

原来如此,那只能设置一下tpmdir参数来改变临时表生成目录了:

[nock@app-ali-prod-db1 ~]# sudo mkdir -pv /data/tmp/mysql

mysql> set global tmpdir='/data/tmp/mysql';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable

通过提示可知tmpdir参数只是一个只读变量不能动态修改指定,那看来只能通过在配置文件my.cnf的mysqld下添加tmpdir配置了,配置如下:

[mysqld]
tmpdir = /data/tmp/mysql

在配置文件my.cnf[mysqld]下添加tmpdir = /data/tmp/mysql

重载MYSQL生效:

/etc/init.d/mysqld reload

查看效果:

mysql> show variables like 'tmpdir';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| tmpdir        | /data/tmp/mysql |
+---------------+----------------------+
1 row in set (0.00 sec)

如此看出已经生效,然后线上再也没有出现如此情况,问题得到解决。

总结教训

所以以后大家一定要谨记线上MYSQL一定要设置好tmpdir参数的配置,不要等到发生问题了再来补救;这里对于MYSQL为什么会生成临时表,什么情况下会生成临时表,后面的文章我们再介绍。

WeZan
Responses