4

I started a REPAIR TABLE about 4 days ago:

Query | 351804 | Repair by sorting | REPAIR TABLE

It's used all the space on the disk:

/dev/md0 9.2G 8.8G 0 100% /

and as soon as I delete something the space gets used up quickly. I've now run out of things I can delete. Also, I can't work out where all the space has gone:

dispus v2.4 - Reading usage in /
Ignoring mount points: proc sys home

9,133,044 KB used of 9,621,752 KB available (100%)

1.  1,859,308 KB   usr
2.  1,142,836 KB   var
3.    274,692 KB   lib
4.     35,924 KB   root
5.     25,308 KB   boot
6.     19,756 KB   sbin
7.     18,400 KB   lib64
8.     15,936 KB   etc
9.      6,732 KB   bin
etc

The mysql data dir is on a different partition.

Anyone got any ideas how I can get this REPAIR to complete?

*UPDATE**

lsof | grep deleted
mysqld    20862    mysql  189u      REG                9,0  4724886042      81629 /tmp/STqCaElP (deleted)
mysqld    20862    mysql  201u      REG                9,0  1107226624      81633 /tmp/STWfcUNu (deleted)

Seems to be the problem. Now to work out what to do. I'm reluctant to KILL the repair query, but may have to...

rastaboym
  • 65
  • 1
  • 5
  • Did you check [this SO question](http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table)? Not sure but [this SF question](http://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space) might also shed a light. – inhan Jan 21 '13 at 16:34
  • Thanks. Yes, I did see those. It's not the database data that's taking up the space though (that's on a different partition). Also, as the REPAIR TABLE operation is currently running I can't run any other queries. – rastaboym Jan 21 '13 at 19:56

1 Answers1

6

http://dev.mysql.com/doc/refman/5.5/en/kill.html says:

Warning: Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).

So you can kill it, but you will have to run repair again, and there's a risk it will not be repairable. If that happens, you'll have to restore from the most recent backup, and you can try to perform point-in-time recovery with the binary logs.

The REPAIR TABLE operation requires about twice the disk space of the table it's trying to repair.

REPAIR TABLE uses temporary storage in the location defined by the config variable tmpdir. Make sure you change this variable to a partition where there is plenty of disk space before starting REPAIR TABLE. Tmpdir is not a dynamic variable, so you'll have to restart mysqld to change it.

See this similar question: https://dba.stackexchange.com/questions/11352/repairing-myisam-table-when-there-was-no-additional-disk-space-table-corrupted

Another piece of advice: Consider using InnoDB instead of MyISAM.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 2
    Many thanks! Here's what I did: (1) Killed the REPAIR (2) Changed the tmpdir variable (3) Ran the REPAIR again. All was OK. – rastaboym Jan 22 '13 at 11:40
  • @rastaboym, great I'm glad you didn't lose your data. I would still recommend you consider switching to InnoDB. – Bill Karwin Jan 22 '13 at 17:37
  • IMHO this answer is better than the linked similar question's, because it allows you to put an additional disk to the machine and use that disk's entire space for the repair operation, whereas the linked question's answer suggests copying even the original table to a new disk and then repairing it there, which takes much more space and requires much bigger disk. – Mladen B. Jun 07 '19 at 22:09
  • Actually, changing tmpdir doesn't help in the REPAIR TABLE case, unfortunately. MySql will create a TMD file (a repaired copy of the original table) in the same directory as the corrupted table, ignoring tmpdir. More info here: https://bugs.mysql.com/bug.php?id=13141 – Mladen B. Jun 10 '19 at 07:32