1

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.

  • 1
    free some space, and drop some indexes. You can check with pt-duplicate-key-checker if you have any redundant indexes. – akuzminsky Sep 26 '14 at 14:59
  • PMA don't work too. Output from PT-DUPLICATE-KEY-CHECKER: mysql> # Size Duplicate Indexes 438610236 mysql> # Total Duplicate Indexes 88 mysql> # Total Indexes 1614 – Kirill Danshin Sep 26 '14 at 15:14
  • Dropping indexes does not free up the space. It simply marks the indexes as deleted. Attempting to run OPTIMIZE TABLE requires space for temp table operation. – RolandoMySQLDBA Sep 26 '14 at 15:24
  • @akuzminsky is correct. Free some space. Do you some binary logs that can be deleted ? Can /var/log/messages be truncated ? Do you have any uncompressed mysqldumps that can be deleted ? – RolandoMySQLDBA Sep 26 '14 at 15:26
  • @RolandoMySQLDBA if my table is 14,8GB, how much free space I need? – Kirill Danshin Sep 26 '14 at 15:27
0

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:

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

  • @RonaldoMySQLDBA thank you very much! can you say me how I can optimize this table for search, and how I can optimize my DB? – Kirill Danshin Sep 26 '14 at 15:58
  • You should post that as a separate question – RolandoMySQLDBA Sep 26 '14 at 15:59
  • @RonaldoMySQLDBA I already posted this – Kirill Danshin Sep 26 '14 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.