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"
> -----Original Message-----
> From: nixofortune [mailto:nixofortune@strippedom]
> Sent: Monday, June 24, 2013 12:35 PM
> To: email@example.com
> Subject: Re: space gone after MyISAM REPAIR TABLE
> On 24/06/13 19:57, Reindl Harald wrote:
> > Am 24.06.2013 18:47, schrieb Johan De Meersman:
> >> ----- Original Message -----
> >>> From: "nixofortune" <nixofortune@stripped>
> >>> Hi guys,
> >>> 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.
> >> 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
> > he spoke about MYISAM table
> >>> the space on the hard drive gone down from 165 Gig to 70 Gig how can
> >>> I get that space back?
> >>> I can't check the table directory as I don't have root perm
> > well, someone should look at the dadadir and error-log it is not
> > uncommon that a repair to such large tables fails due too small
> > "myisam_sort_buffer_size" and i suspect the operation failed and some
> > temp file is laying around
> Thanks Reindl, It looks like Repair operation completed successfully.
> Overall it took 2Hours to complete with OK massage and some other message
> related to the index size. Repair process went through Repair by sort.
> myisam_sort_buffer_size = 526M.
> Provider runs MySQL on FreeBSD + ZFS file system. Could it be up to
> snapshots as well?
> I will ask them to look inside of datadir as we migrated this DB from
> Solaris just day before. This is a new DB for me and I never worked with
> MyISAM tables of that size.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql