MySQL INNODB_FILES_PER_TABLE Explained

Source: dev.mysql.com

Using Per-Table Tablespaces

You can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces”because in effect each table has its own tablespace.

Advantages of Per-Table Tablespaces

• You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.
• The TRUNCATE TABLE operation is faster when run on individual .ibd files.
• You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes.
• You can run OPTIMIZE TABLE to compact or recreate a tablespace. When you run an OPTIMIZE TABLE, InnoDBwill create a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new .ibd file. If the previous.ibd file had grown significantly but actual data only accounted for a portion of its size, running OPTIMIZE TABLEallows you to reclaim the unused space.
• You can move individual InnoDB tables rather than entire databases.
• You can enable more efficient storage for tables with large BLOB or text columns using the dynamic row format.
• Using innodb_file_per_table may improve chances for a successful recovery and save time if a corruption occurs, a server cannot be restarted, or backup and binary logs are unavailable.
• You can back up or restore a single table quickly, without interrupting the use of other InnoDB tables.
• File-per-table mode allows you to excluded tables from a backup. This is beneficial if you have tables that require backup less frequently or on a different schedule.
• File-per-table mode is convenient for per-table status reporting when copying or backing up tables.
• File-per-table mode allows you to monitor table size at a file system level, without accessing MySQL.
• Common Linux file systems do not permit concurrent writes to a single file when innodb_flush_method is set toO_DIRECT. As a result, there are possible performance improvements when using innodb_file_per_table in conjunction with innodb_flush_method.
• If innodb_file_per_table is disabled, there is one shared tablespace (the system tablespace) for tables, the data dictionary, and undo logs. This single tablespace has a 64TB size limit. If innodb_file_per_table is enabled, each table has its own tablespace, each with a 64TB size limit. See Section E.7.3, “Limits on Table Size”for related information.

Potential Disadvantages of Per-Table Tablespaces

• With innodb_file_per_table, each table may have unused table space, which can only be utilized by rows of the same table. This could lead to more rather than less wasted table space if not properly managed.
• fsync operations must run on each open table rather than on a single file. Because there is a separate fsyncoperation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations.
• mysqld must keep 1 open file handle per table, which may impact performance if you have numerous tables.
• More file descriptors are used.
• If backward compatibility with MySQL 5.1 is a concern, be aware that enabling innodb_file_per_table means that ALTER TABLE will move InnoDB tables from the system tablespace to individual .ibd files.
• If many tables are growing there is potential for more fragmentation which can impede DROP TABLE and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance.
• The buffer pool is scanned when dropping a per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the shared tablespace are not affected.
• The innodb_autoextend_increment variable, which defines increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full, does not apply to per-table tablespace files. Per-table tablespace files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

Enabling and Disabling Multiple Tablespaces

To enable multiple tablespaces, start the server with the --innodb_file_per_table option. For example, add a line to the [mysqld] section of my.cnf:

[mysqld]
innodb_file_per_table

With multiple tablespaces enabled, InnoDB stores each newly created table into its own tbl_name.ibd file in the database directory where the table belongs. This is similar to what the MyISAM storage engine does, but MyISAMdivides the table into a tbl_name.MYD data file and an tbl_name.MYI index file. For InnoDB, the data and the indexes are stored together in the .ibd file. The tbl_name.frm file is still created as usual.

You cannot freely move .ibd files between database directories as you can with MyISAM table files. This is because the table definition that is stored in the InnoDB shared tablespace includes the database name, and because InnoDB must preserve the consistency of transaction IDs and log sequence numbers.

If you remove the innodb_file_per_table line from my.cnf and restart the server, InnoDB creates tables inside the shared tablespace files again.

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.

Note

InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The.ibd files are not sufficient for InnoDB to operate.

To move an .ibd file and the associated table from one database to another, use a RENAME TABLE statement:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a “clean” backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows:

1. Issue this ALTER TABLE statement to delete the current .ibd file:

   ALTER TABLE tbl_name DISCARD TABLESPACE;
   

2. Copy the backup .ibd file to the proper database directory.
3. Issue this ALTER TABLE statement to tell InnoDB to use the new .ibd file for the table:

   ALTER TABLE tbl_name IMPORT TABLESPACE;
   

In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied:

• There are no uncommitted modifications by transactions in the .ibd file.
• There are no unmerged insert buffer entries in the .ibd file.
• Purge has removed all delete-marked index records from the .ibd file.
• mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

You can make a clean backup .ibd file using the following method:

1. Stop all activity from the mysqld server and commit all transactions.
2. Wait until SHOW ENGINE INNODB STATUS shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file.

Another method for making a clean copy of an .ibd file is to use the commercial InnoDB Hot Backup tool:

1. Use InnoDB Hot Backup to back up the InnoDB installation.
2. Start a second mysqld server on the backup and let it clean up the .ibd files in the backup