innodb_file_per_table

innodb_file_per_table
Recently I attempted to use this parameter in one of our InnoDB table and had an experience to make a note of.

So this is what actually happened.
As we know InnoDB writes all the table information into one tablespace file ibdata1.
Obviously this lead to a disk space issue, since the ibdata1 file grew to 90G+ and it was not possible to defragment tablespace using the Alter method (Infact is is hardly possible to do in a production environment, since it leads to a huge downtime.)

So I decided to go for innodb_file_per_table on our other production database server which was being configured from scratch.

Now this database had around 15 tables and it had very high inserts/deletes/updates. For few days, things went ok. But in few days, I started getting complains from development team regarding performance on the tables and queue being backed up. Apparently whenever a delete / update was made on a table, all the other tables slow down and queries started taking more than 4 to 5 secs to complete.

Dug around, ran some more optimisations , flush tables but not much difference. I was able to get 660 to 661 qps tops.

On digging further and discussing with developers, we figured out that innodb_file_per_table is the culprit.

InnoDB uses fsync() instead of fdatasync() to flush both data and log files on non-Windows system. fdatasync() is like fsync(), except it flushes only files data, not its metadata (last modified time etc.). Therefor fsync() can cause more IO.

innodb_file_per_table causes each file to be fsync()ed separately which means writes to multiple tables can't be combined into a single IO operation. This requires InnoDB to perform higher total number of fsync() operations, leading to a IO contention in my case.

Finally we had to migrate the database to another machine without innodb_file_per_table since it is also not possible to undo the existing tables for which files are created per table.

As quoted in MySQL documentation :

The --innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

Once we migrated the performance started going up , almost upto 1500 qps.

So moral of the story : Better test the parameter innodb_file_per_table in a test environment, with the load higher than expected on production. I have read many blogs and many people are benefited by the use of this parametere, but as a general practice I would not favor its use. Sure it can be useful to reclaim some disk space, but I dont think it would be worth at the cost of performance.

Reference:
http://umangg.blogspot.ca/2010/02/innodbfilepertable.html