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.