What should I do when MySQL storage files are getting too large?

As MySQL is the internet and cloud de-facto database, we dedicate another post regarding what should you do before getting to the money time.

Some methods including stored data compression and splitting the InnoDB storage file are covered in this post.

Compressing the File

InnoDB data compression is possible in a way that is very similar to disk compression with the pros and cons of it. This method is defined in the table declaration statement and explained in the MySQL documentation. Will it improve MySQL performance? Fortunately, the answer is yes! a recent post by MySQL@Facebook team shown a 10% boost in response time and improvement in the cache hit ratio.

Splitting the Storage File

The default InnoDB configuration is using a single file named ibdata1. This file stores both data and configuration. The bad news is that this file always expands and cannot be shrinked.

Usually people notice this fact when the database is getting too large and they have some storage or backup issues. Usually in these cases, the system is already in production and cannot suffer downtime. Therefore, you should take care of splitting this file in the early days of the system rather when you are in the money time.

What to do?

1. Configure the MySQL to save the data in various files, one per table. Please notice that this will work only to tables that will be created from now on. Perform that by adding for following lines to the my.cnf file:
1. [mysqld]
2. innodb_file_per_table
2. Stop the server for maintenance and perform one of the following three methods:
1. Convert all InnoDB tables to MyISAM and back
2. Export only InnoDB tables, drop them, delete ibdata1 and import InnoDB tables.
3. Export all databases, delete ibdata1 and import everything back.
3. I recommends you to choose option 2 and perform it according to the following procedure: export-delete-import:
1. mysqldump to the whole database.
2. Stop the MySQL daemon.
3. Delete the ibdata + ilog files.
4. Delete the database folders (all except for MySQL).
5. Change my.cnf file if needed.
6. Restart the MySQL daemon.
7. Import the dumped file back to the system.

Bottom Line

Some times, it wise to perform tasks earlier in order to avoid complex issues in the future.

Keep Performing,

Moshe Kaplan