3

I just converted several large tables from MyISAM to InnoDB.

When I view the tables in phpMyAdmin, they are showing a significant amount of overhead (One table has 6.8GB).

Optimizing the tables (which isn't a supported command on InnoDB) has no affect like it does on MyISAM.

Is this a result of InnoDB having the ever growing data file that never returns space even after deletes? If that's the case, I've never seen overhead like this before from other InnoDB tables.

Is there a way to clean this up?

Edit: Here are the things I've tried (with no success):

  1. Optimize Table
  2. Reorder table by primary key
  3. Defragment table
Improve this question

1 Answer 1

Reset to default
3

The most common way to shrink InnoDB data files is to do a full backup using mysqldump and restore it. Sadly, unused tablespace can't be directly returned to the OS as it can with MyISAM or in MS SQL, due to the design of InnoDB's shared tablespace.

The InnoDB folks have always been vehement in saying that this was a deliberate design decision, but if it was, it was pretty short-sighted. I completely understand the idea of not auto-shrinking the data files, but to be without a manual option is pretty annoying.

Note that you can configure InnoDB to use one file per tablespace. If you do that, you should be able to OPTIMIZE tables individually to shrink the files, as each will have an individual tablespace file.

Improve this answer

You must log in to answer this question.