Best Database specifications for the IOT solution

database-designqueuestored-procedures

i'm working on a IOT environment that basically is a sensor that can measure more then 300 times a second. We receive this measurements remotely using MQTT protocol. I want to set up a painel where i can watch in real time the measures my sensor is doing, i can do this with MQTT with no problem at all. But someone have ever done this using some database function? I know there are something called "Queue watch" for database that can do something similar.

The problem starts when i need to store those measurements, because i need to do analysis on them, draw graphics, and things like that.

As i'm not familiar with databases, there is a lot of data to store ( i imagine ) what would be the best database environment for this kind of thing?

—-UPDATE1 —–

How many do you need to store? Last hour? Last day? Last decade? – Basil Bourque

As it's a service for clients, i would say that the lest month of measurements would be "hot data", i want to make them easily accessible and as fast as possible and anything older then that i can store as "cold data" just for eventual analysis, or any request for old measurements from clients.

Best Answer

You have two question, let's start with the easiest.

Math: rows / hours is: 300 Hz * 3600 s / hour = 1.08 Million rows

Which database can handle ~1 Million rows / hour?

Just about all of them.

You just need to use the correct technique in order to achieve those numbers. Single row insert then commit can potential kill performance.

This blog describes the different Oracle method to insert 30 seconds worth of data (10000 rows). The fastest method is done in 6ms.

https://geraldonit.com/2012/03/31/loading-data-fast-regular-insert-vs-bulk-insert/

MySQL can insert 3 seconds worth of data (1000 rows) in 10ms.

http://brian.pontarelli.com/2011/06/21/jdbc-batch-vs-multi-row-inserts/

Don't pick your RDBMS based off of those posts. The point of those blogs is to show you that a performance difference exists based on HOW you insert the data.

What you really need to look for in an RDBMS is to see if it supports partitioning (and still fits in your budget ).

Partitioning allows you to break up the data into smaller chunks. You can then toss those smaller chunks away ( truncate ) in milliseconds when you no longer need them.

Can I use a Database Queue instead of MQTT

You could try.

The only one I'm familiar with uses actual tables under-the-hood. That means you'll need to commit after every row is added in order for you clients to see the data. As mentioned before, this could be bad for performance.

Personally, I'd stick with the MQTT method for the Live Stream of data. You got it to work. No sense in trying other methods.

You'll need to write a subscriber to the MQTT so that it can buffer the inserts using one of the methods mentioned in the blogs.

Related Solutions

Why would I need a third table in a one-to-many relationship

I don't know what your professor has really said I am not sure if I understand your model. But maybe the following is interesting.

In the article A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model by T.J Teory, D. Yang and J.P. Fry is described how entity relationship diagrams can be transformed to relations (and tables). In Fig.8f (on the p 13 of the pdf) there is the following example for an 1:n relationship:

Each engineer can have at most one secretary. One secretary could work for several engineers.

There is a relationship "works-for" between the entities "secreatry" and "engineer".

A "secretary" "works-for" an "engineer".
Each "engineer" has at most one "secretary" that "works-for" her. It may be that an "engineer" has not "secretary" that "works-for" her.
A "secretary" can "work-for" one, more or no "engeneer"

In the article the authors propose to represent this as

ENGINEER(*EMP-NO,...,SEC-EMP-NO)
SECRETAY(*EMP-NO,...)

the fields marked by * are keys. The field SEC-EMP-NO referenes a EMP-NO from SECRETARY and is nullabel.

But there are authors that have the opinon that null values should not be allowed in relations. If one wants to avoid null values one needs a third relation to create the following model:

ENGINEER(*EMP-NO,...)
SECRETAY(*EMP-NO,...)
WORKS-FOR(*ENG-EMP-NO,SEC-EMP-NO)

ENG-EMP-NO references an EMP-NO in ENGINEER and SEC-EMP-NO references an EMP-NO in SECRETARY.

If an "engineer" always has a "secretary" that "works-for" her then the first representation with two relations does not contain null values.

Edit: I now found the comment in your post that links to the diagrams (http://i.imgur.com/gIG2D3h.jpg) of your professor: Actually he introduces a third table to avoid null values as described here.

PostgreSQL – Database Design for Varying Relationships

What you are describing is called a subtype. A subtype is a common data structure used for things that are very similar but have some differences. For example, and object Named Clothing can be used to describe many types of personal coverings but T-Shirts and Shorts have very different properties. With a Subtype, you store all the common properties in one table and have a related table of the properties that are different. That main table will have a FK to a Type table that will denote which of the subtype tables this object is.

Entitty: Clothing ClothingID ClothingTypeID -> ClothingType Name Size Color

Type Entitty: ClothingType ClothingTypeID Name

Now for the subtype tables. You will have one for each value in the Type table.

Entity: TShirt ClothingID (Subtypes always have the same primary key value as the parent object) SleeveLength ColorType

Entity: Shorts ClothingID HasPockets LegLength IsSwimSuit