How to reclaim space in InnoDB when innodb_file_per_table is ON

Editor’s Note:  This post was written for MySQL 5.1/5.5 and that the default for innodb_file_per_table was turned ON in 5.6 and the behaviors/process noted below may not be necessary for someone running 5.6/5.7.

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables and delete some data then there is no other way to reclaim that unused disk space except dump/reload method.

When Innodb_file_per_table is ON, each table stores data and indexes in its own tablespace file. However, the shared tablespace-ibdata1 can still grow and you can check more information here about why it grows and what are the solutions.

Following the recent blog post from Miguel Angel Nieto titled “Why is the ibdata1 file continuously growing in MySQL?“, and since this is a very common question for Percona Support, this post covers how to free up MySQL tables in use when we are using innodb_file_per_table. Also, I will show you how to do it without causing performance or availability problems with the help of our Percona Toolkit.

When you remove rows, they are just marked as deleted on disk but space will be consumed by InnoDB files which can be re-used later when you insert/update more rows but it will never shrink. Very old MySQL bug: http://bugs.mysql.com/bug.php?id=1341

But, if you are using innodb_file_per_table then you can reclaim the space by running OPTIMIZE TABLE on that table. OPTIMIZE TABLE will create a new identical empty table. Then it will copy row by row data from the old table to the new one. In this process, a new .ibd tablespace will be created and space will be reclaimed.

You can see that after deleting 2M records, the test.ibd size was 168M.

After OPTIMIZE, you will be able to reclaim the space. As you can see, test.ibd file size is decreased from 168M to 68M.

I would like to mention here that during that process the table will be locked, (table locked for just Writes), which can affect the performance when you’ll have a large table. So if you don’t want to lock the table then you can use one of the best utilities by Percona, pt-online-schema-change. It can ALTER without locking tables. You can run ALTER TABLE with ENGINE=INNODB which will re-create the table and reclaim the space.

(It’s always recommended to use the latest version of pt-* utilities)

Same here, you can notice that test .ibd file size decreased from 157M to 56M.

NOTE: Please make sure that you have ample space before you run pt-online-schema-change because it will create a temporary table that contains roughly the size of the original table. By running this on the primary node, the changes will be replicated to your slaves.

Share this post

Comments (12)

  • Jason

    Nice use of pt-online-schema-change, Thanks for one of the obvious but very often forgotten uses: plain old alter to clean up space!

    September 25, 2013 at 2:15 am
  • Ricardo

    Unfortunately, optimize table is not a good solution in general. Try to do that with a table with 50M rows, it will take you several hours if not days. Innodb should really fix it at the engine level, i have tables with triple the size of what they should be taking multi GB of data because of this inefficiency of innodb.

    September 25, 2013 at 2:27 pm
  • Nilnandan Joshi

    Agree Recardo, but until they fix this on engine level, we have to be rely on alternative solutions. For the bigger table, you can also use pt-online-schema-change which will run without locking tables.

    September 25, 2013 at 10:28 pm
  • Gerrit Tamboer

    Hi Joshi,
    Question: How does pt-online-schema-change handle the fact that while performing the action the source table might change (because it is not locked). Does it read the binlogs for changed data in the table?

    January 30, 2014 at 8:28 am
  • Nilnandan Joshi

    Hi Gerrit,

    As you know, pt-online-schema-change creates an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. So while copying into new table, pt-online-schema-change will create triggers on the original table to update the corresponding rows in the new table. So any modifications to data in the original tables during the copy will be reflected in the new table.

    Please check more information here.
    https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

    January 30, 2014 at 9:52 am