Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
While you can archive data in mysql. You may need to transfer/transform the data to another type of store if you want to query it efficiently. Your options largely depend on your data and what you want to do with it. –  datasage Mar 6 '13 at 20:25
    
Do you have an example of transforming the data to another type of store? –  Nyxynyx Mar 6 '13 at 20:27
2  
Columnar stores come to mind, like infinidb or amazon's redshift. These types of stores, store data by column instead of row. Which works well for doing adhoc queries on large data sets. –  datasage Mar 6 '13 at 20:30
    
There are some good recommendations for options other than MySQL here, which as everyone's said, isn't ideal for this use case. I'll recommend another, the Infobright engine. I have seen this in action, and it does a very efficient job of storing and also retrieving data from large "archived" datasets. –  Valerie Parham-Thompson Mar 7 '13 at 13:22

3 Answers 3

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.

share|improve this answer
    
Mongo is not a good option if you want to do adhoc queries. Would be fine for archival though. –  datasage Mar 6 '13 at 20:31
1  
Which is precisely why I recommended it for that. SQL is good at reporting; No-SQL is good at logging. –  landons Mar 6 '13 at 20:32

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.