Troubleshooting huge MySQL temp files


Tonight I got an alert about the disk usage on db1.idc1, its root partition usage was higher than 96% and was increasing.

[root@db1.idc1 ~]# df -hP
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mylvm-root 102G 92G 4.5G 96% /
tmpfs 32G 0 32G 0% /dev/shm
/dev/sda1 240M 30M 198M 13% /boot
/dev/mapper/mylvm2-var 1.1T 152G 893G 15% /var

After a quick troubleshoot, I found some MySQL temp files took most spaces.

[root@db1.idc1 ~]# cd /tmp
[root@db1.idc1 tmp]# ls -lh | grep G
total 78G
-rw-rw---- 1 mysql mysql 32G Aug 5 07:28 #sql_4862_0.MYD
-rw-rw---- 1 mysql mysql 7.5G Aug 5 07:28 #sql_4862_0.MYI
-rw-rw---- 1 mysql mysql 32G Aug 5 07:28 #sql_4862_2.MYD
-rw-rw---- 1 mysql mysql 7.5G Aug 5 07:28 #sql_4862_2.MYI

[root@db1.idc1 tmp]# lsof | grep /tmp/#sql_4862_0.MYD
mysqld 18530 mysql 282u REG 253,0 33339280800 2883602 /tmp/#sql_4862_0.MYD

[root@db1.idc1 tmp]# ps aux | grep 18530
root 16365 0.0 0.0 103304 916 pts/0 S+ 07:30 0:00 grep --colour=auto 18530
mysql 18530 66.5 4.2 15718552 2795960 ? Sl May20 73373:06 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/db1.idc1.err --pid-file=/var/lib/mysql/db1.idc1.pid --socket=/var/lib/mysql/mysql.sock --port=3306

Then found that two slow queries were running on db1.idc1.
And those two queries were selecting data from 52320599850 == 52 billion rows, from the host 10.8.8.88.

[root@db1.idc1 tmp]# mysql -uusername -ppassword -e 'show full processlist\G'
...
*************************** 10. row ***************************
Id: 34770751
User: username
Host: 10.8.8.88:50657
db: dbname
Command: Query
Time: 303040
State: Sending data
Info: Select ...
Rows_sent: 0
Rows_examined: 52320599850
Rows_read: 52320599850

*************************** 11. row ***************************
Id: 34824847
User: username
Host: 10.8.8.88:50829
db: dbname
Command: Query
Time: 302476
State: Sending data
Info: Select ...
Rows_sent: 0
Rows_examined: 52240222853
Rows_read: 52240222853
...

I killed those two queries then saved the db1.idc1, asked the developers to improve the queries.

[root@db1.idc1 tmp]# mysql -uusername -ppassword
mysql> kill 34770751;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 34824847;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


[root@db1.idc1 tmp]# df -hP
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/mylvm-root 102G 12G 85G 12% /
tmpfs 32G 0 32G 0% /dev/shm
/dev/sda1 240M 30M 198M 13% /boot
/dev/mapper/mylvm2-var 1.1T 152G 892G 15% /var

  1. No comments yet.
(will not be published)
*


Fork me on GitHub