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

Leave a comment

Name (required)

Mail (will not be published) (required)

Website

 

User contributed tags for this post:

problem SHOW TABLES (85) - mysql temp dir (80) - temp disk table (78) - How to change mysql temp directory (75) - mysql error 28 (74) - mysql out of space (62) - change temp dir mysql (56) - mysql error 2002 (46) - inurl MySQL Error (42) - mysql tmp directory (40) - out of disk space in mysql (38) - error 28 (36) - mysql temp directory (36) - temp disk tables (32) - mysql tmp space (25) - temp tables (24) - optimize mysql table temp dir (23) - mysql change tmpdir (21) - mysql out of disk space (20) - space (19) - mysql tempdir (19) - mysql change temp directory (16) - mysql temp table (16) - mysql temp (16) - change mysql temp directory (16) - mysql temp folder (16) - mysql iphone (15) - MySQL Temp Tables (15) - mysql disk space (14) - iphone mysql (13) - mysql temporary directory (13) - iphone running out of disk space (13) - mysql tmp table location (13) - mysql tmpdir size (12) - mysql temp space (12) - mysql temp table location (12) - out of hard drive space math error (11) - mysql changing tmp table size (10) - mysql tmpdir dev shm (10) - change mysql tmpdir (10) - mysql tmpdir (10) - mysql temp table size (10) - mysql tmp size (10) - mysql temp table directory (9) - Temp directory tmp is out of disk space (9) - mysql show temporary tables (9) - mysql tmp folder (9) - mysql tmpdir error 28 (8) - mysql error (8) - error 28 mysql (8) - mysql change tmp directory (8) - mysql tmpdir change (8) - mysql temporary folder (8) - mysql optimize error (7) - F (7) - mysql tmpdir shm (7) - you are running out of disk space iphone (7) - change mysql temp folder (7) - mysql tmp dir (7) - mysql error 152 (6) - mysql temp table space (6) - change mysql tmp directory (6) - iphone out of disk space (6) - iphone you are running out of disk space (6) - mysql tmp table (6) - change mysql tmp folder (6) - mysql temporary table location (6) - mysql running out of table space (5) - mysql change temp dir (5) - mysql out of disk (5) - mysql change tmp dir (5) - changing mysql tmp directory (5) - mysql optimize temp directory (5) - mysql temp directory change (5) - mss (5) - mysql optimize disk space (5) - mysql disk temporary table location (5) - my disk space if full in iphone (4) - mysql optimization (4) - 8090387420133 (4) - cache 8N1nr1Ffx9UJ maisonbisson com blog post 10744 inf (4) - mysql error 28 tmp (4) - MySQL Error 11 (4) - mysql temp tables disk (4) - MySQL changing temporary directory (4) - mysql show temp tables (4) - temp directory is out of disk space (4) - mysql show temp table (4) - mysql optimize error 28 (4) - mysql tmpdir /dev/shm (4) - Created tmp disk tables (4) - running out of disk space on iphone (4) - Mysql show space used (4) - iphone run out of disk space (4) - mysql running out of disk space (4) - mysql tmp tables (4) - mysql /dev/shm (4) - mysql temporary table size (4) - mysql tmp table size (4) - how to change mysql tmpdir (4) - mysql temporary table directory (4) - Disk is full writing TEMP in MySQL (4) - where mysql write temp tables (4) - mysql using tables problem disk space (4) - tmp table size mysql (4) - shm filesystem mysql (4) - mysql temporary table is full (4) - write error (disk full?) iphone (3) - information is sexy (3) - mysql changing temp directory (3) - change tmpdir mysql (3) - change mysql tmp dir (3) - tmpDSK (3) - mysql out of disk space error (3) - mysql error disk space (3) - mysql optimize table out of space (3) - mysql tmpdir table directory (3) - running out of disk space error (3) - mysql query disk space (3) - iphone out of disk (3) - running out of disk space - iphone (3) - mysql show temporary table (3) - mysql writing to /tmp (3) - changing mysql temp folder (3) - mysql change temp folder (3) - Temporary tables created on disk (3) - error (1) iphone (3) - mysql temp table is full (3) - setting tmp space for mysql (3) - Mysql temporary tables (3) - mysql created tmp disk tables (3) - Out of disk space writing temporary sort file (3) - mysql temporary table (3) - leopard temp folder (3) - Temporary tables created on disk mysql (3) - mysql error 28 on write (3) - mysql temporary path (3) - mysql out of space error (3) - mysql run out of disk space (3) - mysql change tmp folder (3) - mysql error writing file /dev/shm (3) - Temp directory (/tmp) is out of disk space (3) - temp table default location mysql (3) - innodb_max_dirty_pages_pct (3) - mysql tmpdir windows (3) - windows mysql, change temp directory (2) - MySQL TMPDIR space (2) - cache O843F obTjIJ maisonbisson com blog post 10795 xxx (2) - my sql temp table (2) - slave load tmpdir mysql (2) -