space gone after MyISAM REPAIR TABLE

Question

any suggestions? I just repaired 90G MyISAM table with REPAIR TABLE command. the space on the hard drive gone down from 165 Gig to 70 Gig. I understand that during repair process MySQL creates temp file and remove it after the job done. Or removal process executes on the server restart? how can I get that space back? I can't check the table directory as I don't have root perm on that box.

Answer 1

Oops... Can you run [show global variables like 'innodb_file_per_table';] ?

I kind of expect it to be OFF, which means that the temp table would have been created in the main tablespace. If that's the case, that space has been permanently assimilated by the global tablespace; the only way to get it back would be a full dump of all your (innodb) tables, stop server, delete tablespace, start server and import the data again. Be sure to read the documentation carefully before doing such an intrusive operation.

While you're doing that, use the opportunity to set innodb_file_per_table to ON :-p

Answer 2

Switch to InnoDB so you won't have to repair after crashes.
Caution: InnoDB takes 2x-3x the disk space per table. Be sure to use innodb_file_per_table=1.
" Repair by sort." is usually much faster than "repair by keycache"; you probably got 'sort' because of this being big enough: "myisam_sort_buffer_size = 526M"