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.

I have a database which contains large amounts of measurements (20+ instruments, each recording 60 measurements per second, each measurement containing 10+ complex values). All measurements are stored in a single table, normalized, and indexes are used to allow filtering by instrument, time and other columns.

Needless to say, every 6 months, the database gets so large that scheduled index maintenance tasks take more than a day to complete. So far, I've had to turn off the db twice, for almost two days, to run scripts which would backup and then delete old data in order to "refresh" it.

My colleagues suggest a (logical, IMHO) modification to physically separate the database into multiple databases, by instrument and time. Furthermore, different clients will access different instruments, so keeping each instrument separated will simplify multi-tenant scenarios (providing backup to customers, migrating to other machines, etc.).

In other words, I would go from this:

  Big database
     - AllMeasurements table

to this:

  Instrument001 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

  Instrument002 database
     - Measurements-2012-01 table
     - Measurements-2012-02 table
     - Measurements-2012-03 table

Since my DAL is abstracted through the repository pattern, it would be easy to swap the underlying implementation to switch databases and tables on demand, so coding this shouldn't a problem.

But I am now wondering if this is really the best way?

Does it make sense?

[Edit] I'm using SQL Server 2008, if that's relevant.

share|improve this question

migrated from stackoverflow.com Oct 5 '12 at 15:33

This question came from our site for professional and enthusiast programmers.

    
I don't know what is your db type, but some of them (IBM, Oracle, PostgreSQL, ...) supports database partitioning. Witch db type you use? –  azendh Oct 3 '12 at 12:00
    
Another option would be to seperate to one database for collecting results and another for reporting / analysis. At the moment an extensive query for a report could take out your collection, or make it stutter, which would be, well bad... –  Tony Hopkinson Oct 3 '12 at 12:12

4 Answers 4

up vote 4 down vote accepted

If the problem is this single table getting huge, you should consider partitioning it (by date, since your data seems to be time-based).

This way the SQL engine query planner may be able to ignore some partitions (if they contain data too old to appear in your query results) when querying and thus perform better.

Additionally, partitionning will be handled transparently by your database engine, so you have nothing to change in your code.

Another advantage would be that you can imagine to move the 'old' partitions, containing data that is rarely accessed, to slower (and cheaper) storage.

You can also set up your indexes to be partitioned so you can only rebuild a part of an index: http://msdn.microsoft.com/en-us/library/ms187526.aspx

Index partitioning is a different feature from table partitioning, it can avoid you to have to rebuild an entire index if it's not needed.

share|improve this answer
    
From what I've heard, partitioning doesn't improve query performance (nor rebuilds). –  Dilbert Oct 3 '12 at 12:06
1  
It does, because the SQL engine is able to skip some partitions if they are not needed. But of course if 90% of you queries doesn't select data according to the measurement date, it's useless to partition by date. –  mbarthelemy Oct 3 '12 at 12:09
    
Most SQL engines will also do things like index rebuilds only on one/some of the partitions, which can save time. –  mbarthelemy Oct 3 '12 at 12:15
    
But, (again @RemusRusanu) this thread says that Queries that opt to look up only one partition do so solely based on the index ranges, and would scan exactly the same number of records with or without partitioning. And furthermore, it doesn't reduce locking, it worsens parallelism, and index maintenance jobs are not done on individual partitions. I know, it seems counterintuitive, but most posts on the web state that partitioning only helps when you need to swap partitions often. –  Dilbert Oct 3 '12 at 12:25
    
If you're sure that partitioning is not what you need, then I would think that your own proposed solution (splitting the big table to multiple tables/databases) is the best alternative. –  mbarthelemy Oct 3 '12 at 12:31

At that rate, you're adding roughly 500 million rows per month, right? 6 billion rows per year. To my way of thinking, this puts you firmly in very-large database (VLDB) territory.

VLDB is really a special case. dba.stackexchange.com doesn't even have a tag for it. (I'll try to add one. You'll become our expert.) You have to make lots of really sound decisions to get great performance.

When I hear about VLDB problems these days, my first question is always about hardware. Is this beast running on hardware that's appropriate for a database of that size? A lot of companies kind of drift into VLDBs from a modest start. A lot of companies. The biggest jump in performance might come from new decisions about hardware.

share|improve this answer

I have worked with simular database structure. (Big databases). As I understand the data you fill in is static. First of all. you must be sure that your indexes is at an absolut minimum. And if you are not already doing it, then try out different fill factors for your indexes (fill factor=additional space in the index to "grow/work").

One way to handle the re-indexing problem in big databases, is not to re-index it, but to create another same index, and delete the old one. (unless your database handles re-indexing without locking the index?). And dont use clustered index unless data is ascending values.

I dont know exactly how you access your data, but making more databases (one per month) could be a solution. When gathering data, you could gather the static dataresult in another database. Let say you are adding up results per day in another database. And stats pulling queryes from that database.

(Sorry, english is not my main language)

share|improve this answer

If the tables are continiously growing it is best practice to seperate/partition them. I would however not implement a database for each instrument, rather a new table for each instrument which stores the measurements. You could also incorporate a MeasurementDates table that references the Instrument, which could reduce the size significantly rather than having a repeating Measurements-2012-NN table for an individual instrument.

http://msdn.microsoft.com/en-us/library/ms188730.aspx

http://msdn.microsoft.com/en-us/library/ms188730(v=sql.105).aspx

share|improve this answer
1  
I'd have gone tables as well, unless there was some requirement to swap archived older data in and out the system based on adhoc reporting requirments. –  Tony Hopkinson Oct 3 '12 at 12:09

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.