Mysql – Transition to innodb_file_per_table for 30+ databases

mariadb-5.5MySQL

Currently I have a MySQL Server with innodb_file_per_table = 0

Based on a number of factors, it seems to make sense for me to change that to innodb_file_per_table = 1

I understand that to make this all work I need to

  1. Use mysqldump to export all databases into individual files
  2. Shutdown mysql
  3. Change the innodb_file_per_table setting
  4. Restart mysql
  5. Import each database

Question is, can I do the following

Day #1

  1. Shutdown mysql
  2. Change the innodb_file_per_table setting
  3. Restart mysql
  4. Export some databases
  5. Import those databases back into mysql

Day #2

  1. Export some databases
  2. Import those databases back into mysql

Until I've exported an re-imported all of the databases?

I'm assuming that during the transition some databases will continue to use the ibdata1 file while the exported and re-imported database will use their own datastores. Will having a mix of datastores cause issues or am I OK doing a phased transition?

Best Answer

Sorry, I don't have a reference for when to set innodb_file_per_table in different ways. Here are some tips:

  • Note: The value of innodb_file_per_table applies only to subsequent CREATE TABLEs or table rebuilding.

  • [Perhaps the main reason for turning it ON:] When you have a "big" table that might be ALTERed, or you might do a lot of deleting, etc, it is somewhat advisable to build the table with file_per_table ON. This allows the operation to replace the .ibd file containing just that one table.

  • Hence, if the table is being shrunk significantly by the operation, the extra space that is freed up is returned to the OS. That is, the only easy way to return free space to the OS is to have file_per_table on and do some operation that rebuilds the table.

  • file_per_table = OFF is slightly better for "small" tables that will stay small. This because of sharing a single file descriptor (for ibdata1) for many tables.

  • To change file_per_table for a table requires (1) setting the flag and (2) rebuilding the table (eg, simply ALTER TABLE t ENGINE=InnoDB;; it may need FORCE). After that, consider changing the setting back.

  • When running tight on disk space, it might help to play games by moving selected tables between ibdata1 (which may have a lot of free space) and separate .ibd files.

  • Moving a file out of ibdata1 leaves a bunch of free space behind. This space may eventually get used by INSERTs and other operations on the tables in ibdata1.

  • Dumping and integrity are not affected by ibdata1 vs .ibd.

  • Most servers should not worry about the setting.

  • [A related topic:] OPTIMIZE TABLE is essentially never necessary for InnoDB tables.

Related Question