I’m currently trying to prune a MyISAM table with 200 million rows down to 100 million rows. As part of this process, I am simply removing any orphaned records. This is a simple tracking table where every record must have an associated record in the users table. The total size on disk is 11G of index data and 9G of table data.
The basic process to prune this table is to:
1. setup replication slave for this purpose
2. batch deletes from the table throughout the day
3. run optimize on the table each night
I was surprised to come in this morning to find that the optimize that I had started last night at 6PM had not finished yet. Looking on the server, there was no load, no swapping, no disk I/O, nothing. It was as if MySQL had fallen asleep at the wheel. I looked at the MySQL error log and discovered the problem, /tmp was full.
-0- josh@mysql-server04 /var/lib/mysql > > tail /var/log/mysql/mysql.err 100210 7:38:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 7:48:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 7:58:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:08:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:18:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:28:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:38:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:48:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 8:58:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs 100210 9:08:09 [ERROR] /usr/sbin/mysqld: Disk is full writing '/tmp/STJSSGYU' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs
The disk had been full since 4:00 AM and had been blocking on this. I quickly ran a df -h and confirmed that the disk was full, then extended the logical volume by 2G to make a total of 4G of space and MySQL continued to process the optimize.
-0- josh@mysql-server04 ~ > > sudo lvextend -L +2G /dev/vg01/lvol03 Extending logical volume lvol03 to 3.97 GB Logical volume lvol03 successfully resized -0- josh@mysql-server04 ~ > > resize2fs !$ resize2fs /dev/vg01/lvol03 resize2fs 1.39 (29-May-2006) Filesystem at /dev/vg01/lvol03 is mounted on /tmp; on-line resizing required Performing an on-line resize of /dev/vg01/lvol03 to 1040384 (4k) blocks. The filesystem on /dev/vg01/lvol03 is now 1040384 blocks long.
I was then curious to see the files that were in /tmp and listed the contents to find nothing there.
-0- josh@mysql-server04 /tmp > > find . . ./.winbindd ./.winbindd/pipe ./.ICE-unix ./lost+found
I then issued another ‘df -h’ to find that the volume was 88% full. Something didn’t add up here. The next step is to use ‘lsof’ to find any deleted files that had not been synced to disk yet.
-0- josh@mysql-server04 /tmp > > lsof | grep -i delet mysqld 1404 mysql 7u REG 253,2 0 12 /tmp/ibERGn68 (deleted) mysqld 1404 mysql 8u REG 253,2 0 13 /tmp/ibX1mdbt (deleted) mysqld 1404 mysql 9u REG 253,2 0 14 /tmp/ibjjh3fN (deleted) mysqld 1404 mysql 10u REG 253,2 0 15 /tmp/ibjHbE38 (deleted) mysqld 1404 mysql 14u REG 253,2 0 16 /tmp/ib3F6Tfw (deleted) mysqld 1404 mysql 68u REG 253,2 3385629393 17 /tmp/STJSSGYU (deleted) hald-addo 5043 haldaemon txt REG 253,3 15720 258081 /usr/libexec/hald-addon-keyboard.#prelink#.RmsJR9 (deleted) hald-addo 5046 haldaemon txt REG 253,3 15720 258081 /usr/libexec/hald-addon-keyboard.#prelink#.RmsJR9 (deleted) hald-addo 5050 haldaemon txt REG 253,3 15720 258081 /usr/libexec/hald-addon-keyboard.#prelink#.RmsJR9 (deleted)
There is the culprit! We have a large file being used by MySQL although it’s been deleted and not synced to disk yet. That explains everything.
Note to self: ensure the /tmp directory on large MySQL servers is greater than the value of myisam_max_sort_file_size. On this server I had this value set to 10G while the /tmp directory volume was 2G.
Leave a Reply