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:

mysql error 28 (90) - problem SHOW TABLES (85) - mysql temp dir (84) - temp disk table (78) - How to change mysql temp directory (76) - mysql out of space (63) - change temp dir mysql (56) - mysql error 2002 (46) - inurl MySQL Error (42) - mysql tmp directory (41) - mysql temp directory (40) - error 28 (38) - out of disk space in mysql (38) - temp disk tables (32) - mysql tmp space (27) - temp tables (24) - mysql out of disk space (24) - optimize mysql table temp dir (23) - mysql change tmpdir (23) - space (21) - change mysql temp directory (21) - mysql tempdir (20) - mysql temp folder (20) - mysql temp table (19) - mysql disk space (18) - mysql temp table location (17) - mysql change temp directory (16) - mysql temp (16) - mysql iphone (15) - MySQL Temp Tables (15) - mysql tmp table location (15) - iphone mysql (14) - mysql temp space (14) - mysql temporary directory (13) - iphone running out of disk space (13) - mysql tmpdir size (12) - mysql tmp folder (12) - out of hard drive space math error (11) - change mysql tmp directory (11) - mysql show temporary tables (11) - mysql temp table size (11) - mysql changing tmp table size (10) - mysql tmpdir dev shm (10) - change mysql tmpdir (10) - mysql tmpdir (10) - mysql tmp table (10) - mysql tmp size (10) - mysql temp table directory (9) - mysql change tmp directory (9) - mysql error 152 (9) - Temp directory tmp is out of disk space (9) - mysql tmpdir shm (9) - mysql tmp dir (9) - mysql tmpdir error 28 (8) - mysql error (8) - error 28 mysql (8) - mysql tmpdir change (8) - mysql optimize disk space (8) - change mysql temp folder (8) - mysql temporary table location (8) - mysql temporary folder (8) - mysql optimize error (7) - F (7) - mysql temp table space (7) - you are running out of disk space iphone (7) - changing mysql tmp directory (6) - iphone out of disk space (6) - iphone you are running out of disk space (6) - change mysql tmp folder (6) - mysql tmp table size (6) - mysql temp table path (6) - change tmpdir mysql (5) - mysql running out of table space (5) - mysql change temp dir (5) - mysql show temp tables (5) - mysql out of disk (5) - mysql change tmp dir (5) - mysql show temp table (5) - mysql optimize temp directory (5) - mysql temp directory change (5) - mss (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) - temp directory is out of disk space (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) - Temporary tables created on disk (4) - mysql created tmp disk tables (4) - Out of disk space writing temporary sort file (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) - mysql change tmp folder (4) - tmp table size mysql (4) - shm filesystem mysql (4) - mysql temporary table is full (4) - mysql tmpdir windows (4) - mysql temp tablespace (4) - write error (disk full?) iphone (3) - information is sexy (3) - mysql changing temp directory (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) - mysql tmp tables 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) - error (1) iphone (3) - mysql temp table is full (3) - setting tmp space for mysql (3) - Mysql temporary tables (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) - change tmpdir MySQL Windows (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 temp location (3) -