List:General Discussion« Previous MessageNext Message »
From:Rick James Date:June 25 2013 10:55pm
Subject:RE: space gone after MyISAM REPAIR TABLE
View as plain text  
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: 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" <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

Thread
space gone after MyISAM REPAIR TABLEnixofortune24 Jun
  • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
    • Re: space gone after MyISAM REPAIR TABLEReindl Harald24 Jun
      • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
      • Re: space gone after MyISAM REPAIR TABLEnixofortune24 Jun
        • RE: space gone after MyISAM REPAIR TABLERick James25 Jun
          • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
            • Re: space gone after MyISAM REPAIR TABLEJay Ess26 Jun
              • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman26 Jun
                • RE: space gone after MyISAM REPAIR TABLERick James26 Jun
                  • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
                    • Re: space gone after MyISAM REPAIR TABLEJay Ess27 Jun
                    • Re: space gone after MyISAM REPAIR TABLEDivesh Kamra27 Jun
            • Re: space gone after MyISAM REPAIR TABLEhsv26 Jun