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.

7
  • 1
    free some space, and drop some indexes. You can check with pt-duplicate-key-checker if you have any redundant indexes.
    – akuzminsky
    Commented Sep 26, 2014 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 Commented Sep 26, 2014 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. Commented Sep 26, 2014 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 ? Commented Sep 26, 2014 at 15:26
  • @RolandoMySQLDBA if my table is 14,8GB, how much free space I need? Commented Sep 26, 2014 at 15:27

1 Answer 1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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