ARCHIVE-ing Data in MySQL

February 16th, 2006

Over time, there comes a point where you have to make certain choices as a DBA in order to maintain the integrity and efficiency of your database system. In a fair amount of systems, there is information that needs to be archived away, possibly compressed, and then used to do a few lookups on when needed.

In the past, you could compress MyISAM tables, however, this required you to take your MySQL server offline to perform the compression. However, starting with MySQL 4.1.3, there is the new ARCHIVE engine. You can find out if your MySQL distribution support the ARCHIVE format by running the following query:

The ARCHIVE table supports the SELECT and INSERT query handlers only. Nothing else is permitted - which means that this acts as a true archive - you don't really want stuff being deleted from your archive. Furthermore, the contents of the table are compressed on the fly without having to shutdown the MySQL server - an immediate plus!

The only downside to the ARCHIVE engine would be the lack of indexes - albeit that there is support for partitioned indexes in MySQL 5.1 (currently in Alpha release). However, the lack of indexes is not a huge problem - the ARCHIVE engine is designed with the user performing scans that incorporate more than 30% of the rows - as such, indexes would have a limited value here. However, the ARCHIVE engine does support the MySQL Query Cache, meaning that duplicate SELECT statements are recalled from memory (if they still exist in memory) rather than performing another table scan.

The ARCHIVE engine is turning out to be an excellent tool to complement the other table engines in MySQL. Its helping to turn MySQL into an enterprise solution that you would be hard-pressed to find didn't work for your solution.

Some interesting links:

Add to del.icio.us Digg this Technorati