5

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns.

Operations are:

  • Insert 455+ thousand rows per day
  • Query using range filters on cellX, cellY and timeStamp
  • Query is not required to return instantly, we can notify the user when requested data is ready.

Because the data was too large and we required index for query, we used this scheme:

  1. Use SQL Server.
  2. Clustered index on cellX, cellY and timeStamp.
  3. Separate tables for every year, therefore total number of rows in a table remains within limit (~166 million).
  4. Use a custom format for timeStamp. Skip year, keep only month, date and hour. We were able to keep it within 16 bit int.
  5. Use partition on each years table.
  6. Insert one day data at a time. Use partition switching to keep the database live while inserting data.

This has been working good so far. Although we notify the user after data is ready, the delay is never more than a few seconds as long as the query is reasonable.
But recently we got the opportunity to get more precised data and the data volume has been increased by 68 times!. Therefore, now we have:

  • insert 30+ million rows per day.
  • store 11 billion rows in a table for a year. This can be reduced by making quarterly (2.7 billion) or monthly (1 billion) tables.

This is possible that we might be able to receive more precised data in a year or two. Therefore it is possible that data volume will increase again by a significant factor.

The question is, will this scheme we are using sustain? Or we should migrate to another scheme, may be another database system leaving SQL Server?


Edit

The three dimension columns cellX, cellY and timeStamp are very regular in nature. You can define all of them by f(x) = mx + c, for some integer x ranging (0, 1, 2, ..., X).

closed as primarily opinion-based by Philᵀᴹ, Tom V, James Anderson, Marian, Mikael Eriksson Feb 15 '16 at 6:41

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    To me, you seem a PERFECT candidate to switch from DBMS to NOSQL technologies. If I were you, I would start investigating ElasticSearch that works very well with timestamp-based time-series and can scale horizontally much easier then your partitioning-based approach. Maybe the switch will require some important application refactoring but your data are really small-structured so... definitely approachable. There are others NOSQL engines, so you should also check them. But ElasricSearch is a really good start. – Damiano Verzulli Feb 14 '16 at 13:15
  • @DamianoVerzulli: Please check the edit. I use MongoDB for most of my projects and I love it. But that does not mean I should go with it for all the situation. Do you really think I should switch to NOSql in the given situation? If you have some real reasons (excluding costing) in favor of NOSql please add an answer to the question. Thanks :) – Mohayemin Feb 15 '16 at 3:42
6

I've worked with a 30+ billion row monthly partitioned table with page compression and 10 years of history. The table schema was fairly simple with a datetime2(2) clustered index and 3 non-clustered indexes on varchar columns and a couple of non-indexed columns. Storage was about 2TB and it performed reasonably well. SqlBulkCopy was used to insert about 15M rows continuously throughout the day as data was needed in near real time.

Based on this anecdote, I'm confident SQL Server could handle your expected volume with adequately sized hardware. That being said, I completely agree with @DamianoVerzulli that your application is an excellent candidate for a less costly NoSQL solution due to your tolerance for delay.

  • 1. I actually have 15+years of data but as I have split the data based on year, it does not matter for me. 2. I also do bulk insert. 3. Good for me, only one insert per day. 4. My old data was nearly 50GB (3.2 GB/year). New ones will be 68 times (actually much less as per my understanding of the data). So at the worst case, it will be 1TB in 4.5 years. Overall I guess my system is sustainable comparing to yours. Thanks for the answer. – Mohayemin Feb 15 '16 at 4:00
  • About NoSQL. Please check the edit if you can. Do you think I really need NoSQL for this situation (except for the cost)? I use MongoDb for most of my project anyway. I can go for any other NoSQL if it really helps. – Mohayemin Feb 15 '16 at 4:01
  • @Mohayemin, sorry if I wasn't clear but you do not need NoSQL for your situation if cost is not a concern, Again, appropriate hardware is important. – Dan Guzman Feb 15 '16 at 12:14
1

Are you not getting rapid fragmentation of that clustered index? That would negatively impact insert and select.

Consider a different index
You are loading data daily - I assume for the day or prior day
PK cellX, cellY, timeStamp
That is maximum fragmentation

Consider
PK timeStamp, cellX, cellY
And load the data sorted by that order
Even if you have to load it into a staging table to sort
That is minimum fragmentation

If you really need a cellX, cellY index for query performance then put that in another index on another partition with a fill factor < 1. And perform index maintenance. If this is done off hours it might be faster to disable the index, insert, and then rebuild the index (in this case can use a fill factor or 1).

  • I am not a DB guy so I am not sure why I should get a fragmentation. But I guess that PK timeStamp, cellX, cellY should perform better because the inserted data is sequential based on timeStamp. However, one day data is actually one partition, I am not sure if this do any benefit on fragmentation. – Mohayemin Feb 15 '16 at 3:26
  • I did some analysis on the fragmentation using sys.dm_db_index_physical_stats. Maximum value of fragment_count is 16. Maximum value of avg_fragment_size_in_pages is 562.5. I do not understand the significance of these values. – Mohayemin Feb 15 '16 at 3:28
  • Search msdn for sql fragmentation. But it looks like you have an accepted answer so hopefully this is under control. – paparazzo Feb 15 '16 at 18:52

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