SQL: SOS: MySQL indexes overloads disk

Question

My server is down now, 'couse indexes of 84GB DB are overloads disc.

Here is output of df-h:

/dev/ploop58097p1     193G  187G   16M 100% /
tmpfs                 2,0G     0  2,0G   0% /lib/init/rw
tmpfs                 2,0G  4,0K  2,0G   1% /dev/shm

DB retuns #1030 - Got error 28 from storage engine every time. I can login in phpmyadmin, but don't know how to delete indexes of tables.

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

I am glad you mentioned the table was MyISAM.

You can now run this

OPTIMIZE TABLE mytable;

on the 14.8G table.

So why did you get #1030 - Got error 28 from storage engine ?

It wasn't so much the indexes in themselves. Sometimes, a temp table may need to be written to disk. If there is no space for the temp table on disk, mysqld will not crash. It will freeze until space becomes available. I wrote about this before:

Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop
Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

If you want to compress every table regardless of storage engine, run this code in a script

MYSQL_USER=root
MYSQL_PASS=password
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
echo "SET SQL_LOG_BIN = 0;" > /tmp/CompressTables.sql
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN')"
SQL="${SQL} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/CompressTables.sql
less /tmp/CompressTables.sql

If the file looks good to you, run this

mysql ${MYSQL_CONN} -A < /tmp/CompressTables.sql

If all your tables are huge, run this over the weekend

SQL: SOS: MySQL indexes overloads disk

1 Answers1