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
Related:
Posted May 1, 2007 by Casey
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
User contributed tags for this post:
problem SHOW TABLES (85) - temp disk table (78) - How to change mysql temp directory (74) - mysql temp dir (74) - mysql out of space (59) - change temp dir mysql (56) - mysql error 2002 (46) - mysql error 28 (46) - inurl MySQL Error (42) - out of disk space in mysql (38) - mysql tmp directory (37) - temp disk tables (32) - error 28 (31) - mysql temp directory (26) - temp tables (24) - optimize mysql table temp dir (23) - mysql tmp space (21) - mysql change temp directory (16) - mysql temp table (15) - mysql out of disk space (15) - mysql tempdir (15) - mysql temp (15) - MySQL Temp Tables (14) - iphone running out of disk space (13) - iphone mysql (12) - mysql iphone (12) - space (12) - mysql tmpdir size (12) - out of hard drive space math error (11) - mysql change tmpdir (11) - change mysql temp directory (11) - mysql temporary directory (11) - mysql temp folder (11) - mysql changing tmp table size (10) - mysql temp space (10) - mysql disk space (10) - mysql tmpdir dev shm (10) - Temp directory tmp is out of disk space (9) - mysql tmpdir (9) - mysql tmp size (9) - mysql tmpdir change (8) - mysql tmpdir error 28 (8) - mysql error (8) - F (7) - mysql temp table location (7) - mysql tmpdir shm (7) - mysql optimize error (7) - change mysql tmpdir (7) - mysql tmp table location (7) - mysql tmp folder (7) - you are running out of disk space iphone (7) - mysql temp table directory (6) - iphone you are running out of disk space (6) - mysql tmp table (6) - iphone out of disk space (6) - mysql show temporary tables (6) - mysql running out of table space (5) - mss (5) - change mysql tmp directory (5) - mysql optimize temp directory (5) - mysql optimize disk space (5) - change mysql tmp folder (5) - mysql change tmp directory (5) - mysql disk temporary table location (5) - mysql temp table space (5) - mysql temp table size (4) - mysql tmp tables (4) - mysql out of disk (4) - mysql change tmp dir (4) - mysql /dev/shm (4) - change mysql temp folder (4) - mysql error 152 (4) - mysql change temp dir (4) - my disk space if full in iphone (4) - mysql temp directory change (4) - MySQL changing temporary directory (4) - mysql show temp table (4) - mysql show temp tables (4) - MySQL Error 11 (4) - 8090387420133 (4) - mysql error 28 tmp (4) - iphone run out of disk space (4) - error 28 mysql (4) - mysql tmp table size (4) - where mysql write temp tables (4) - changing mysql tmp directory (4) - running out of disk space on iphone (4) - mysql temp tables disk (4) - cache 8N1nr1Ffx9UJ maisonbisson com blog post 10744 inf (4) - Mysql show space used (4) - mysql using tables problem disk space (4) - Disk is full writing TEMP in MySQL (4) - mysql optimization (4) - mysql tmpdir table directory (3) - mysql optimize table out of space (3) - error (1) iphone (3) - mysql out of space error (3) - temp directory is out of disk space (3) - changing mysql temp folder (3) - iphone out of disk (3) - mysql error disk space (3) - mysql show temporary table (3) - how to change mysql tmpdir (3) - mysql tmp dir (3) - mysql temporary table size (3) - mysql temporary table location (3) - mysql query disk space (3) - Created tmp disk tables (3) - mysql optimize error 28 (3) - mysql writing to /tmp (3) - Temporary tables created on disk (3) - mysql running out of disk space (3) - mysql error writing file /dev/shm (3) - setting tmp space for mysql (3) - mysql temp table is full (3) - running out of disk space - iphone (3) - mysql temporary path (3) - tmpDSK (3) - mysql error 28 on write (3) - leopard temp folder (3) - write error (disk full?) iphone (3) - mysql out of disk space error (3) - information is sexy (3) - Temporary tables created on disk mysql (3) - mysql created tmp disk tables (3) - mysql temporary folder (3) - mysql temporary table (3) - mysql changing temp directory (3) - Mysql table disk space (2) - mysql out of temp disk space (2) - tmpdir=/dev/shm (2) - Wordpress Error 28 (2) - change tmpdir MySQL Windows (2) - tempdir wordpress (2) - mysql temp space query (2) - mysql error Error writing file /tmp/ 28 (2) - mysql show space (2) - mysql running out of space (2) - mysql temporary tables /tmp (2) - mysql error code 28 out space is available (2) - database size disk space available mysql (2) - mysql table is full temporary (2) - temporary tables get created under which folder mysql (2) - mysql changing the temp dir (2) - mysql run out of disk space (2) - tmp size mysql (2) - tmp disk space mysql (2) - running out of disk space error (2) - iphone running out of space (2) - AR (2) -
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!