Reclaiming Your InnoDB Tablespace

Published October 29th, 2008

Problem Statement:

When you use InnoDB table engine for tables that require concurrent INSERT/UPDATE type of access, you need to be careable about the table space used by InnoDB engine by default. It appears that by default, MySQL creates the InnoDB tables in a single tablespace file called /var/lib/mysql/ibdata1.

Unfortunately, as you use your InnoDB tables the size of the table space file continues to grow even when you delete records or truncate one or more InnoDB tables. There is no easy way to recover this space using some vaporware innodb defragmentation tool.

Here we will discuss two solutions that we have used in the recent past.

Solution A: Dealing with Your Future InnoDB Tables

If you have not yet used InnoDB but planning to start soon, you are in good shape! You can simply tell MySQL to create separate tablespace files for each of your tables; this gives you much more control over deleting such files when you no longer need the table in a specific database. Here is how you can setup MySQL to create individual InnoDB tablespace files for each of your future InnoDB tables:

1. Edit your /etc/my.cnf MySQL configuration file to have innodb_file_per_table in the [mysqld] section
2. Now create a new InnoDB table in one of your database
3. Check the /var/lib/mysql/<yourdatabase>/ directory and you will notice a new InnoDB tablespace file

Solution B: Dealing with Your Existing InnoDB Tables

When you already have a large /var/lib/myslq/ibdata1 file, you are in a bit of a maintenance pickle.  Your option is as follows:

• Backup your database as-is
• Temporarily, alter your InnoDB table engine from InnoDB to MYISAM
• Dump your database
• Drop the database
• Stop MySQL server
• Move your /var/lib/mysql/ibdata1 file out of your MySQL server
• Restart MySQL
• Recreate the database
• Reload your tables from your MySQL dump file

For example, say you have a database called my_db and you want to reclaim the disk space lost due to the ever growing ibdata1 file.
Here is what you would do:

• Backup using: mysqldump -u root -p --opt my_db > my_db.as-is.sql
• Change InnoDB tables to MYISAM using: ALTER TABLE [tablename] TYPE=MYISAM
• Like the first step, create a dump called my_db.dump.sql using mysqldump command
• Drop your database using: DROP database my_db
• Stop MYSQL server using: service mysql stop
• Move the /var/lib/mysql/ibdata1 file out of your MySQL server space
• Start MySQL server using: service mysql start
• Recreate the database using: create database my_db
• Load data to your database using: mysql -u root -p -D my_db < my_db.dump.sql
• Alter the table engine back to ALTER TABLE [tablename] TYPE=INNODB

Disclaimer

Please make sure you take extra care to backup your database before you perform the above-mention steps. We are never responsible for any data loss. All your actions are your responsibility.

Category: InnoDB | Tags: