How to optimize size of MySQL .ibd data files

MySQL Optimize table

An IBD file is a MySQL table created by the InnoDB database engine. It contains a table-specific tablespace and index data. IBD files are created when MySQL’s innodb_file_per_table option is enabled, which it is by default. more details here.

It appears that *.ibd files by default never shrink, they can only grow. This takes up the memory of the disk.

Reclaim the disk space

Option 1:

When using innodb_file_per_table, 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.

OPTIMIZE TABLE <db.tablename>

If mysql is unable to optimize it, it will analyze and recreate it automatically for you.

Example:

MySQL [conductor]> select count(*) from workflow;
+----------+
| count(*) |
+----------+
|     6769 |
+----------+
1 row in set (0.00 sec)

Now check the size of ipd files.

root@fsbmysqlconprod01:/var/lib/mysql/conductor# ls -Salh
total 5.5G
-rw-r----- 1 mysql mysql  3.2G Jan 12 22:52 workflow.ibd
-rw-r----- 1 mysql mysql 1000M Jan 12 22:52 workflow_to_task.ibd 
-rw-r----- 1 mysql mysql  640M Jan 12 22:52 task_scheduled.ibd

You can see the size of workflow.ibd file is 3.2G and even after deleting records from table the size remains the same.

MySQL [conductor]> optimize table workflow;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| conductor.workflow | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| conductor.workflow | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+ 
82 rows in set (1 min 1.59 sec)

check ipd size again.

root@fsbmysqlconprod01:/var/lib/mysql/conductor# ls -Salh
total 2.4G
-rw-r----- 1 mysql mysql  120G Jan 12 22:52 workflow.ibd
-rw-r----- 1 mysql mysql 1000M Jan 12 22:52 workflow_to_task.ibd 
-rw-r----- 1 mysql mysql  640M Jan 12 22:52 task_scheduled.ibd

After OPTIMIZE, you will be able to reclaim the space. As you can see, workflow.ibd file size is decreased from 3.2G to 120M.

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.

Option 2:

So if you don’t want to lock the table then you can use ALTER without locking tables. You can run ALTER TABLE with ENGINE=INNODB which will re-create the table and reclaim the space. ALTER does the same thing as OPTIMIZE and reclaims the disk space.

ALTER TABLE <db.tablename> ENGINE=InnoDB;