MySQL’s innodb_file_per_table is a wonderful thing – most of the time. Having every table use its own .ibd file allows you to easily reclaim space when dropping or truncating tables. But in some use cases, it may cause significant performance issues.
Many of you in the audience are responsible for running automated tests on your codebase before deploying to production. If you are, then one of your goals is having tests run as quickly as possible so you can run them as frequently as possible. Often times you can change specific settings in your test environment that don’t affect the outcome of the test, but do improve throughput. This post discusses how innodb_file_per_table is one of those settings.
I recently spoke with a customer whose use case involved creating hundreds of tables on up to 16 schemas concurrently as part of a Jenkins testing environment. This was not in production, so performance was far more important than durability. They’d run their tests, and then drop the schemas. This process took close to 20 minutes. They asked “How can we make this faster?”
Due to the number of tables involved innodb_file_per_table seemed a likely culprit.
It’s been noted here on the MySQL Performance Blog that innodb_file_per_table can cause table creation and drops to slow down. But what exactly is the performance hit? We wanted to find out.
The innodb_file_per_table Test:
On a test server running CentOS release 6.5, xfs filesystem, and 5.6.22-71.0-log Percona Server, I ran the following homemade benchmark bash script:
[root@host ~]# time $(for db in {1..16};
do mysql -e "create database bench$db";
$(for tb in {1..500}; do $(mysql bench$db -e "create table tab${tb} (i int) engine=innodb"); done) & done)
If you open the mysql client in another screen or terminal, you should see something like this:
... +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+ | 80013 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | | 89462 | root | localhost | bench5 | Query | 0 | creating table | create table tab95 (i int) engine=innodb | 0 | 0 | | 89466 | root | localhost | bench8 | Query | 0 | creating table | create table tab81 (i int) engine=innodb | 0 | 0 | | 89467 | root | localhost | bench1 | Query | 0 | creating table | create table tab91 (i int) engine=innodb | 0 | 0 | | 89468 | root | localhost | bench13 | Query | 0 | creating table | create table tab90 (i int) engine=innodb | 0 | 0 | | 89469 | root | localhost | bench15 | Query | 0 | creating table | create table tab90 (i int) engine=innodb | 0 | 0 | | 89472 | root | localhost | bench9 | Query | 0 | creating table | create table tab86 (i int) engine=innodb | 0 | 0 | | 89473 | root | localhost | bench10 | Query | 0 | creating table | create table tab94 (i int) engine=innodb | 0 | 0 | | 89474 | root | localhost | bench11 | Query | 0 | creating table | create table tab80 (i int) engine=innodb | 0 | 0 | | 89475 | root | localhost | bench3 | Query | 0 | creating table | create table tab80 (i int) engine=innodb | 0 | 0 | | 89476 | root | localhost | bench2 | Query | 0 | creating table | create table tab82 (i int) engine=innodb | 0 | 0 | | 89478 | root | localhost | bench4 | Query | 0 | creating table | create table tab91 (i int) engine=innodb | 0 | 0 | | 89479 | root | localhost | bench16 | Query | 0 | creating table | create table tab88 (i int) engine=innodb | 0 | 0 | | 89481 | root | localhost | bench12 | Query | 0 | creating table | create table tab90 (i int) engine=innodb | 0 | 0 | | 89483 | root | localhost | bench6 | Query | 0 | creating table | create table tab96 (i int) engine=innodb | 0 | 0 | | 89484 | root | localhost | bench14 | Query | 0 | creating table | create table tab95 (i int) engine=innodb | 0 | 0 | +-------+------+-----------+---------+---------+------+----------------+------------------------------------------+-----------+---------------+ ...
After creating the tables, I dropped all schemas concurrently:
[root@host ~]# time $(for db in {1..16};
do mysql -e "drop database bench${db}" & done)
So what was the difference with innodb_file_per_table ON vs OFF?
- With innodb_file_per_table=ON
- Schema and table creation = 1m54.852s
- Schema drops = 1m21.682s
- With innodb_file_per_table=OFF
- Schema and table creation = 0m59.968s
- Schema drops = 0m54.870s
So creation time decreased by 48%, drop time decreased by 33%.
I think its worth noting that this benchmark creates and drops empty tables. Dropping InnoDB tables created with innodb_file_per_table=ON can take much longer if they have large amounts of data.
Please also be aware that there are always trade-offs when modifying your InnoDB settings. That is outside the scope of this post, so please research and test before making changes. The MySQL documentation discusses that here. In 5.6.6 and up, innodb_file_per_table is ON by default. MySQL 5.6 will also create temp tables as InnoDB, as noted here.
So there you have it. If your primary goal is to improve create and drop table time, turning OFF innodb_file_per_table will save significant amounts of time.
The post Is MySQL’s innodb_file_per_table slowing you down? appeared first on MySQL Performance Blog.