MySQL /tmp Usage with Optimize Table Command

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.