InnoDB per-table tablespaces – file for each innodb table

May 7th, 2009 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 :

[mysqld]
innodb_file_per_table

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

 

6 Responses to “InnoDB per-table tablespaces – file for each innodb table”

  1. Kelly Brown Says:

    Hi, gr8 post thanks for posting. Information is useful!



  2. Abul Hassan Says:

    Good Post. Thanks for the information. My question is, what will happen if we delete ibdata1 file after we do step 3 and 4? Could we regain the space again?



  3. Ranjeet Walunj Says:

    @abul : yeah .. but why do u want to delete ibdata1 after step 3 and 4 ?

    Step 1 and 2 will help you regain space … step 3 and 4 will help you optimize tables and have separate innodb files for them



  4. bpelhos Says:

    It works when you:
    * drop the foreign keys of the InnoDB tables
    * alter the InnoDB tables to MyISAM
    * stop mysql server
    * remove/move the ibdata* and ib_logfile* files
    * start mysql server (it should appear a new ibdata1 file with 10M filesize)
    * alter the previously altered tables back to InnoDB
    * create the foreign keys removed at the first step



  5. bpelhos Says:

    And add innodb_file_per_table to my.cnf file, of course.



  6. Team Roster Says:

    Best you should change the post title InnoDB per-table tablespaces – file for each innodb table | Lamp, scalability, opensource to more catching for your subject you create. I loved the post however.



Leave a Reply