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

MySQL. An Internet Standard.

As you probably already know, last week I gave a presentation at the Database2011 conference on how MySQL become an Internet Standard?

Database2011 my sql sharding

View more presentations from Moshe Kaplan.

The answer is simple: great community and good support and the proof of the pudding is in the eating: everyone uses it including Facebook, Twitter and Google (if you want to hear other opinions try follow the xaprb.com comments flow).


MySQL Limitiations
Yet, if you will explore the MySQL capabilities you may find out that it lucks real multi threading capabilities. its table size is limited to effective number of 50-100M rows, its SELECT performance is limited to 50 statements on a single table per second and its INSERT performance is limited to 700 INSERT statements per second (based on standard Amazon instance and InnoDB engine).
So, how can the Internet industry with dozens of thousands of SQL statements per second in a medium site use such a limited database?


Answers
The answer is simple: Use Sharding. Sharding is a method for slashing your tables into smaller ones and storing parts of the data in each one of them. There are several common strategies including:

1. Vertical Sharding: Tear a table into two, store only few columns in first table and the rest in the second table.
2. Horizontal Sharding: Place each group of rows in another table. Horizontal Sharding can be implemented using various algorithms that include static hashing, hashing with directory mapping, key based directory mapping and signature based mapping.

The main issue with Sharding is reporting. Since direct grouping cannot be supported when rows are spread in various tables, you should use Map Reduce based solutions to accomplish this task.


Emerging Products
In the last year several new COTS Sharding products were introduced to the market. These products use two mechanisms in order to get over the MySQL limitations:

1. Gizzard and ScaleBase (founded by Industry experts Doron Levari and Liran Zelkha) use the MySQL Proxy mechanism in order to implement a load balancing and Sharding solutions in front of regular MySQL databases.
2. Xeround and Akiban implemented a new type of storage engine based on the MySQL Storage engine API.

Will MySQL remain an Internet standard in the future?
MySQL will keep being an Internet standard, unless unexpected decisions will be taken by Oracle that owns the firm. Unwise marketing decision could harm the community and may cause a major shift to other solutions including NoSQL.


Bottom Line
If you have a great internet idea, you have all the needed tools to start.


Keep Performing,
Moshe Kaplan