Leveraging the InnoDB Plugin

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

  • Backup all InnoDB tables via mysqldump
  • Drop InnoDB tables
  • Verify InnoDB not used
  • Stop MySQL
  • Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
  • Remove ibdata? files
  • Start MySQL
  • Create Tables
  • Reload Data
  • Verify InnoDB Operation
    • The primary reason is we are moving from using a common tablespace to a tablespace per table. InnoDB wil not shrink the common tablespace so this process is necessary in order to purge the diskspace currently being used. You should also reduce your innodb_data_file_path options if specified. For example if currently set to :

      innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
      

      I would suggest you change to

      innodb_data_file_path = ibdata1:100M:autoextend
      

      InnoDB still requires this common tablespace, however now each table has it’s own disk file the volume required is signficantly less.

Comments

  1. Daniƫl van Eeden says

    This could also be a nice moment to recreate the InnoDB logfiles and adjust the size of them. The parameters are innodb_log_file_size and innodb_log and innodb_log_files_in_group. I believe that the default is 2 files of 5MB, which is very small. Changing this to 2 files of 1G could improve performance.

  2. Holger Thiel says

    This is not a migration issue.

    The innodb_file_per_table is stable since MySQL 5.0.

    You can use the same InnoDB tables/files from MySQL 5.1 in MySQL 5.5.

    Beware: To many tables are not good with using innodb_file_per_table.

  3. says

    It should probably be noted that in tests with InnoDB 1.1.4 for both Drizzle and MySQL 5.5 we have found that innodb_file_per_table leads to around a 15% performance hit on read/write OLTP Sysbench. We are guessing that this is due to the extra fsyncs required for the common tablespace as well as the tablespace-per-table files but we haven’t looked too deeply into it yet.

Trackbacks