I would like to know the correct Archival Strategy for my DB Schema. We are using Mysql 5.5. Here is the full scenario:
We have around 35 tables in DB out of which 10 would be transactional in nature, dependent upon each other by way of foreign keys. We have 3 Main tables (amongst the 10 described above) are having more than 10 million records currently. The data is suppose to grow in main tables as a million record in 5 days. Main tables also have associated history tables where the data will from around 8 times the speed of main tables that is they will have 8 million new records every 5 days. The working set of data is however fairly small in comparison to entire data set The records are part of a workflow and reach a terminal state with in a month or 2. Post reaching the terminal state, records may need to be accessed but not updated for another 6 - 8 months. As per our policy we need to keep data of 2 years back in our DB and keep it accessible. Post 2 years Data can be rolled in to Tape/S3/Somewhere. So that it can be accessed if needed. The need will be rare.
Based on above and to keep my working set fast enough, this is what I am thinking to do: 1. Once a record has moved to terminal state we will push it (along with all its dependent record in related tables) to "cold" tables after certain months. 2. Remove above records from hot tables to keep the hot tables with in certain limit. 3. At the end of every month I will take a dump of tables and zip it and put them in S3 (to keep things simple) 4. Records which are more than 2 years old in cold tables will be removed from cold tables.
Now, I have following questions: 1. Is above approach correct? 2. Should we build a Java (or any other modern language) application or should we do it in Store Procedures? 3. Removal of data can cause overheads on hot tables, will the overhead be manageable with subsequent reads and writes on same tables? 4. Is there a out of box solution already existing which can server our purpose?
Thanks in advance,