To achieve faster performance of MySQL server with InnoDB engine and for I/O optimization one can choose file-per-table mode.
InnoDB storage engine stores all data for tables in a common tablespace. It is possible to configure InnoDB to store data for each table in separate files.
How to do it:
♦ Add the following line to the “mysqld” section of my.cnf:
♦ Restart database instance
Be aware that this setting will only be applied to the newly created databases.
If you would like to have file-per-table mode for all existing databases, it is better to contact our support team as it is complicated to perform task. They can do that without any problems.
Now, you can map tables to different physical disks or back up and recover certain tables on the file level. If you delete data from a table, the file size will not reduce automatically.
As each table uses its own tablespace, this means that the space that gets freed by deleting from one table cannot be reused by another table, as is the case for the “classical” shared InnoDB tablespace.