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

The eBay way

Hi,

I received from Shachar Zehavi, our new director of R&D, a link to Chris Kasten's presentation regarding eBay

In this presentation Chris presents a simple but very clever solution to support 4 billion events per day on 25 commodity servers. Why instead of using complex in memory databases solutions? not using MySQL as the ultimate grid solution?

What are the key components in eBay solution:
1. In memory database: implementation using MySQL in memory engine
2. HA: implemented using MySQL replication between two different MySQL in memory databases
3. Persistence: implementation using batch process once in 5min. In this case the store is done using InnoDB one.
4. Scalability: can b achieved using horizontal sharding

The number described in this presentation (4 billion requests per day using 25 machines) remind me another presenation of Paul Strong, distinguished research scientist from eBay. This presensation at the IGT2008 - The World Summit of Cloud Computing described eBay numbers and challenges including: 150 Billion request per day which is about 200K requests per second, which is a remarkable number.

Keep Performing,
Moshe Kaplan. RockeTier. The Performance Experts.