guys. I'd really appreciate to have your support on this one, as it's driving me crazy…
I have a corrupt table on a MySQL database and I've been trying, unsuccessfully, to repair it on the past few days. The table has 1,2 Gb of information.
I've detected the corruption of the table using "phpMyAdmin" and so I asked it to REPAIR TABLE.
This was on July 20th, 14h47m (GMT). It is now July 22nd, 23h28m (GMT) and the process isn't finished, although my WHM "MySQL Process List" shows:
Time – State – Info
203558 – Repair by sorting – REPAIR
TABLE xyz
(yes, it has been running for more than 200.000 seconds by now…)
The query I executed, that led to this state, was:
SET @@session.myisam_sort_buffer_size := 67108864;
SET @@session.read_buffer_size := 524288;
SET @@session.read_rnd_buffer_size := 524288;
SET @@session.sort_buffer_size := 8388608;
SET @@session.key_buffer_size := 8388608;
SET @@session.tmp_table_size := 67108864;
— Execute query
REPAIR TABLE xyz;
SET @@session.myisam_sort_buffer_size := DEFAULT;
SET @@session.read_buffer_size := DEFAULT;
SET @@session.read_rnd_buffer_size := DEFAULT;
SET @@session.key_buffer_size := DEFAULT;
SET @@session.sort_buffer_size := DEFAULT;
SET @@session.tmp_table_size := DEFAULT;
I've read on some other sites that I should use larger buffer size to speed up the repair process, so I opted to use larger values than my MySQL default values, that I list below:
DEFAULT VALUES
key buffer size: 8,388,600
myisam sort buffer size: 8,388,608
read buffer size: 131,072
read rnd buffer size: 262,144
sort buffer size: 2,097,144
tmp table size: 33,554,432
I'm not familiarized with shell access to the server, so that's not a real option to start with.
What do you suggest to help me speed up the repairing process? Should I terminate the current REPAIR TABLE process? What should I do?
Thanks in advance for your support.
Best Answer
Is your harddrive full ?
The repair procedure will need at least twice as much space as the original table, if there's not enough room it'll just sit waiting for enough space. Repairing a 1.2Gb table should be very fast.