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:nixofort...@gmail.com] > Sent: Monday, June 24, 2013 12:35 PM > To: mysql@lists.mysql.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" <nixofort...@gmail.com> > >>> > >>> 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
