Using MySQL InnoDB as an Archive

Question

My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily basis. These archived rows are processed once a week for stuff like finding trends.

This archive table consequently grows at 1 million new rows every week, and querying it can get really slow. Is MySQL suited for archiving data, or is my strategy very flawed?

Please advise, thank you!

Answer 1

I would be very tempted to store in a no-SQL data store, like Mongo or Couch. Writes are incredibly fast, scales well, etc.

You might even archive in a mongo collection, then store "processed" results in an RDBMS, which you can then query very quickly with SQL.

To stay in MySQL, you're looking at some sort of partitioning scheme to get this to scale at all.

Answer 2

Let's just say that InnoDB is not suited for archival purposes although you may choose to do so. You have a Clustered Index which bloats the data. Archival tables in InnoDB do not benefit from it since MVCC and ACID compliance are rendered useless. Data Pages and Index Pages are loaded into the InnoDB Buffer Pool. Accessing archival data from InnoDB would potentially push working set data out of the Buffer Pool as often as as you access it.

Think of these alternatives

ALTERNATIVE #1

If the archival data is read often via indexed queries, you could put such data into a table that uses the MyISAM Storage Engine. You can then do one of the following things to the MyISAM table:

• compressed to save space
• change ROW_FORMAT=Fixed to increase read speed

You could also configure key_buffer_size for the indexes of the MyISAM tables. Data pages for MyISAM are never cached, thus saving on RAM. You could then reduce the size of the InnoDB Buffer Pool in favor of the actual working set you need on hand.

ALTERNATIVE #2

If the archival data is read in bulk via SELECTs without indexes, you could stored the data in tables that use the ARCHIVE Storage Engine.

CAVEAT

Please do not perform any JOINs between heterogeneous storage engines. In other words, do not join InnoDB to MyISAM for any reason.

Answer 3

If you have similar amount of data coming every week and you have to run queries on large set of data. Be it any store, it will take some time based on the size of data and the kind of query you are doing on that data. As extracting trends might want you to run complex aggregations.

You might have to do sharding as well when the data will become really huge on mysql.

I would suggest to look at an option of having this data stored in hadoop and then query the same using hive, which gives capabilities of adhoc SQL queries. Which can run on very large data but again it cant be just real time. But will give you huge scalable solution for archival