We're currently starting to build a version 2 of our system and one of the things we've noticed with version 1 is that we'll have quite a bit of data so we started to rethink our approach to how to store the data.

Basically we have dataloggers which send data back to the server every 5 minutes. With current system we only have few sensors as we've just been starting so we only have approx 250 loggers out there and they are generating data at a pace of 2.5gb per year or about 21M rows per year. The data is mainly just numbers like light levels, temperature, humidity etc. We expect these logger numbers to grow quite fast and we should have thousands of loggers by end of the next year.

However with this next generation platform we'll have multiple sensors that can be used and multiple similar sensors can be connected to one system. System will have about 8 ports for sensor boards but each sensor board can have multiple sensors. As an rough example one system can have anywhere from 5 to 100 different kind of sensor inputs. ( 2-10 times as much as we currently have)

Data is only written once and then only read. 99% of the reads are to data which is maximum of two months old.

So far I've come up with few different options and I'd love to hear if someone has good insights which one might be the most scalable.

  1. Have a main Ping table which contains some basic data for each report and then have separate tables for each sensor board types. If all of the data needs to be shown the select would be at most 8 table join.
  2. Have one very wide table with all the ping data
  3. Fully or partly use NoSQL ideas and insert most of the ping data in JSON or similar format.

Secondly it would be interesting to hear as to what database setup would be the most scalable:

  1. Have a production server which has maximum of 2 months data so it would be easier to keep fully in memory. Offload all historical data to secondary server.

  2. Have everything in one server.

  3. Sharding?
  4. Replication?

We are doing something very similar at my place and this very question was in my head for a while a few weeks back.

After noticing that logs were consuming 98% of our DBs in SQL Server I did some research on how we should handle this problem.

It all depends on the structure of your logs but our logs are not really relational so storing them in a RDBMS and consuming all the resources built for the RDBMS does not make sense.

I started looking into NoSQL stores and found DB Engines Ranking to be very useful to compare the feature sets of each DB engine.

Personally I found that an eventual consistency solution worked for me as it has good sharding, availability, and fast reads on huge data sets.

Check the CAP Theorem wiki entry to learn about the trade offs on different types of NoSQL solution.

We are currently testing a Cassandra deployment for our logs with MapReduce jobs.

| improve this answer | |

The RDMS is great for this sort of time series data as it provides extremely robust feature sets not available in NoSQL solutions.

With your workload being insert heavy there are some very important considerations to avoid performance issues.

Is big table performance with inserts and indexing, and scaling.

It is tempting to write the sensor data directly into the tables that you will then query. I’ve found this to cause problems down the line as insert rates rise and the need to scale up or out becomes necessary.

Receiving the sensor information and then queuing it for insert into the main tables let’s you do this. Also let’s you to index the main tables as you see fit without those slowing inserts and potentially screwing with your timing data if it’s not received from the sensor.

So you can scale out your edge to receive sensor data and then queue for batch insert to a seperate DB/server.

Maintaining the dataset on going is a different issue, and that will come down to the number of pages and those could be huge in number despite sensor being tiny.

One easy method is to create a non-clustered index on a clustered index which will give you a copy of the data. But you will likely get better results with filtered non-clustered index. Therefore the underlying tables can grow huge and yet read performance isn’t affected as you can pull the data for the last few months directly from the index.

Without going on to much further, an RDMS is perfectly ok. Just be sure to position yourself to scale out your data input and reduce the large table read issues.

Storage issues should be workable down the line with archive and multiple servers.

| improve this answer | |

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