Why innodb_file_per_table is important

Last updated: 30 June 2021

Although the InnoDB storage engine has many advantages over MyISAM, there are some downsides as well. One disadvantage you may have encountered is that InnoDB doesn’t automatically release space when you delete data. Unless you configure and manage InnoDB tables they will just keep growing. This article looks at how you can configure InnoDB so that you can optimise tables.

What innodb_file_per_table does

InnoDB stores table data and indexes in .ibd files. The innodb_file_per_table setting defines how tables are stored. If the setting is “on” (or “1”) then each table has its own .ibd file, and if it is “off” (“0”) all tables are kept in a single file.

On most operating systems innodb_file_per_table is set to “on” by default. cPanel servers also add the option to the /etc/.my.cnf configuration file, just to make sure it is definitely on. This is a good default, as it prevents the “all eggs in one basket” issue. If something goes wrong then it will be easier to troubleshoot the issue. And equally important, it also makes it possible to reclaim space after you delete data from a table table.

To demonstrate this I will use an “employees” sample database. I have imported the database on an AlmaLinux server running MariaDB 10.3.28. By default, innodb_file_per_table is set to “on”:

MariaDB [none]> SHOW variables LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

The employees database has six tables, and so you got six .ibd files. The largest file is the salaries table:

# ls -lhS /var/lib/mysql/employees/*.ibd
-rw-rw----. 1 mysql mysql 104M Jun 29 13:17 /var/lib/mysql/employees/salaries.ibd
-rw-rw----. 1 mysql mysql  27M Jun 29 13:17 /var/lib/mysql/employees/titles.ibd
-rw-rw----. 1 mysql mysql  25M Jun 29 13:17 /var/lib/mysql/employees/dept_emp.ibd
-rw-rw----. 1 mysql mysql  22M Jun 29 13:17 /var/lib/mysql/employees/employees.ibd
-rw-rw----. 1 mysql mysql 112K Jun 29 13:17 /var/lib/mysql/employees/departments.ibd
-rw-rw----. 1 mysql mysql 112K Jun 29 13:17 /var/lib/mysql/employees/dept_manager.ibd

The table has over 2.8 million rows. Most of the salaries seem quite decent, as there are under 12,000 rows left when you delete all salaries greater than or equal to 40,000:

MariaDB [employees]> SELECT COUNT(emp_no) FROM salaries;
+---------------+
| COUNT(emp_no) |
+---------------+
|       2844047 |
+---------------+

MariaDB [employees]> DELETE FROM salaries WHERE salary >= 40000;

MariaDB [employees]> SELECT COUNT(emp_no) FROM salaries;
+---------------+
| COUNT(emp_no) |
+---------------+
|         11711 |
+---------------+

That’s 2,832,336 rows gone. However, the salaries.ibd file hasn’t changed in size. It is still 104MB:

# ls -lhS /var/lib/mysql/employees/salaries.ibd
-rw-rw----. 1 mysql mysql 104M Jun 22 13:36 /var/lib/mysql/employees/salaries.ibd

Optimising InnoDB tables

To reclaim the space you can run OPTIMIZE TABLE on the table. When you run the command you will get a “Table does not support optimize” warning, which is fine. Although InnoDB doesn’t support the OPTIMIZE statement it knows what you want to do, and it achieves the same result by recreating the table. The main thing to look for in the output is the status line (which should say “OK”).

MariaDB [employees]> OPTIMIZE TABLE salaries;
+--------------------+----------+----------+-------------------------------------------------------------------+
| Table              | Op       | Msg_type | Msg_text                                                          |
+--------------------+----------+----------+-------------------------------------------------------------------+
| employees.salaries | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| employees.salaries | optimize | status   | OK                                                                |
+--------------------+----------+----------+-------------------------------------------------------------------+

The salaries.ibd file should now be much smaller:

# ls -lhS /var/lib/mysql/employees/salaries.ibd
-rw-rw----. 1 mysql mysql 512K Jun 29 13:36 /var/lib/mysql/employees/salaries.ibd

As mentioned, the main thing to remember is that innodb_file_per_table should be enabled. Optimising an InnoDB table will fail if all your eggs are in the same basket.