List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:June 25 2014 7:00am
Subject:Re: Optimizing InnoDB tables
View as plain text  
----- Original Message -----
> From: "Antonio Fernández Pérez"
> <antoniofernandez@stripped>
> Subject: Re: Optimizing InnoDB tables
> 
> I have enabled innodb_file_per_table (Its value is on).
> I don't have clear what I should to do ...

Then all new tables will be created in their own tablespace now. It's easy to convert an
existing table, too, simply do "alter table <yourtable> engine=innodb" - but that
will of course take a while on large tables.

The problem, however, is that there is no way to shrink the main tablespace afterwards.
Your tables will all be in their own space, but the ibdata1 will still be humoungous,
even though it's close to empty. Don't just delete it, btw, as it still contains
metadata.

The only way to get rid of those, is to export ALL innodb tables, shut down mysqld, delete
all innodb files (iblog0/1, ibdata1 etc, but also <db>/*.ibd and the associated
<db>/*.frm files; then start the server (it'll recreate ibdata1 as specified in
your my.cnf, so shrink there, too, if required) and then import the lot again.

Note that, if you have the space, you don't *have* to do that - the huge ibdata1 file
doesn't do any harm; but do consider that as your dataset grows over the years, it'll
become more and more of a bother to actually do it.

Make sure you have backups when attempting :-)

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.
Thread
Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesWagner Bianchi24 Jun 2014
    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez24 Jun 2014
  • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
    • Re: Optimizing InnoDB tablesReindl Harald24 Jun 2014
      • Re: Optimizing InnoDB tablesshawn l.green24 Jun 2014
        • Re: Optimizing InnoDB tablesAntonio Fernández Pérez25 Jun 2014
          • Re: Optimizing InnoDB tablesJohan De Meersman25 Jun 2014
          • Re: Optimizing InnoDB tablesAndre Matos25 Jun 2014
            • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
              • Re: Optimizing InnoDB tablesReindl Harald27 Jun 2014
                • Re: Optimizing InnoDB tablesAntonio Fernández Pérez27 Jun 2014
                  • Re: Optimizing InnoDB tablesshawn l.green27 Jun 2014
                  • Re: Optimizing InnoDB tablesJohan De Meersman27 Jun 2014
                    • Re: Optimizing InnoDB tablesAntonio Fernández Pérez30 Jun 2014
                      • Re: Optimizing InnoDB tablesReindl Harald30 Jun 2014