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.

We currently have a MySQL data set that was started about 10 years ago that aggregates a log of data, and accumulates millions of rows each month. The people that developed our system started separating the data into months, however they did this by creating an entirely new database with that month name (e.g. jan2001, mar2009, aug2012), and one table in each database named "detail". Each month a cronjob kicks off and spins up and entirely new database, with the table "detail", and the application starts writing the data to said table.

At this point the combined number of rows in all the "detail" tables is 448,231,722. This data is only used for analytics purposes at this point, so the speed of the query returning the data does not need to be instantaneous. One assumption we have is that the data will never be deleted.

I am wondering if you could suggest a better method of condensing or consolidating the data into a more manageable schema?

share|improve this question
    
you could organize your data in a partitioned table –  miracle173 Aug 27 '12 at 21:48
    
Seems like with the amount of rows you have after 10 years you could 1) fit it one database or 2) only create a new database every year. Also, see this article: Loading half a billion rows into MySQL –  User Aug 27 '12 at 22:13
    
I like the idea of having one table for all those rows. The article was interesting, however the author really did not discuss his query performance, ongoing maintenance, or challenges supporting that many rows, which is unfortunate. –  somecallmemike Aug 29 '12 at 1:56
add comment

1 Answer

When it comes to databases, less is more.

You are doing the right thing by archiving off old data as this will speed up the performance and reduce the maintenance time of the active database.

The archived data needs to be considered. Keeping it in a separate database instance will be beneficial because backups/restoration and other maintenance activities will be completed separately - reducing the effect on the live data.

You should consider keeping the archived data on a physically different disk to the live data - this will avoid conflicts if both are being used at the same time. Also consider the type of disk this the archive is on as this has cost implications - does it really need to be on a raid type array or is a single disk and backup tape/dvd all that is needed? Storage may be relatively cheap, but it is still a cost in $, IO, time and network.

Next take a look at the archived data - do you really need to keep all the records? do you really need to keep all the columns of data? Would changing the structure of the data result in a smaller disk footprint/faster read query time? Can you summarize the data? Archived data for data mining does not always need to have the same structure as live data.

Keeping separate months data in separate databases may be a good way to go, but there other options that may make the analysis easier/quicker. Your options here include keeping the data in one database instance, but with table per month. Another is to have one set of archive tables and use table partitioning (read the manuals).

share|improve this answer
add comment

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.