MySQL Error 28: Temp Tables And Running Out of Disk Space

Bam: MySQL error 28, and suddenly my queries came to a stop.

Error 28 is about disk space, usually the disk space for temp tables. The first thing to do is figure out what filesystem(s) the tables are on. SHOW VARIABLES LIKE “%dir%” will return a number of results, but the ones that matter are tmpdir and datadir.

SHOW VARIABLES LIKE “%dir%”;

basedir /
character_sets_dir /usr/share/mysql/charsets/
datadir /var/lib/mysql/
innodb_data_home_dir
innodb_log_arch_dir
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
slave_load_tmpdir /tmp/
tmpdir /tmp/

As it turns out, df shows plenty of space available on all the filesystems:

# df -H
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 195G 11G 175G 6% /
/dev/sda1 98M 16M 78M 17% /boot
none 1.1G 0 1.1G 0% /dev/shm
/usr/tmpDSK 508M 12M 471M 3% /tmp
/tmp 508M 12M 471M 3% /var/tmp

Well, plenty of space if all it needs is 471MB of temp. Unfortunately, that’s not enough. The temp table is for a group/sort operation on a table that was much larger than I expected, something I realized when I looked at the table’s Data_length with SHOW TABLE STATUS.

I could change the temp directory, but unfortunately it can’t be changed dynamically, only at startup. So my choice was either to shutdown MySQL or change my query. As it turned out, it wasn’t a bad time to figure out how to write a better query.

mysql, error 28, error, disk space, temp table, table size, query optimization, mysql optimization

One thought on “MySQL Error 28: Temp Tables And Running Out of Disk Space

  1. That has happened to me so many times when I first transferred over my blog to my new hosting. I also had the problem at work this summer. It was interesting because no one who what the problem was. Thanks for the info!

Comments are closed.