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

Posted May 1, 2007 by Casey Bisson
Categories: Technology. Tags: disk space, error, error 28, mysql, mysql optimization, query optimization, table size, temp table.

1 Comment(s)

Comments RSS TrackBack Identifier URI

User contributed tags for this post: