InnoDB per-table tablespaces – file for each innodb table

May 7th, 2009 6 Comments   Posted in databases, mysql

Lot has been discussed over the usefulness and power of various mysql engines. (Viz. MyISAM or InnoDB or Archive)

In basic format, MyISAM is faster in SELECT operations and running `fulltext indexes/searches`.

Whereas InnoDB has transaction support and ROW-Level locking which helps in database locking issue.

MyISAM engine stores data and indexes for each table in separate files as follows :
MYI = index file, MYD = data file, FRM = format (schema) file

Whereas default installation of InnoDB engine stores most of the information in single file called ibdata1. (usually /var/lib/mysql/ibdata1 )

This default behaviour of InnoDB engine has few drawbacks: (Its a well known fact that InnoDB needs good tuning to give much solid performance.)
1) Single large file
2) Even if the tables/data is deleted from InnoDB tables, the size taken by ibdata1 does not shrink  (eventually space crunch on server)
3) Fragmentation

To avoid above situations, it is always wise to add per-table tablespace option in InnoDB config parameters in /etc/my.cnf
Add following parameter to conf file :


Read here about the innodb_file_per_table config parameter.

What this parameter will ensure that all new tables (Please note: Only new InnoDB tables) will have individual file for data instead of single large file for all innodb tables.

Now real question is what will happen to existing tables which are already InnoDB/MyISAM tables.

Applying anyone of the following method will ensure conversion of existing InnoDB tables in using multiple tablespaces.

1) Export all databases, delete ibdata1 and import everything back
This is sure way of converting everything to per-table tablespaces along with fresh ibdata1 file

2) Export only InnoDB tables, drop them, delete ibdata1 and import InnoDB tables back into respective databases
If there are lots of databases and dumping all DB is not an option then this step can be performed.
Note: Please ensure backup at every stage

3)     run  following command in your shell

mysqlcheck –optimize –all-databases

4) Convert all InnoDB tables to MyISAM using ALTER TABLE command
for each InnoDB table run following command at mysql prompt

mysql>ALTER TABLE tablename ENGINE=InnoDB;

However STEP 3 and 4 does not shrink the exising size of ibdata1 file.  To regain the space used earlier by ibdata1, please use step1 or step2

And most important after every mysql installation pls add the ‘innodb_file_per_table’ parameter in your /etc/my.cnf