Mysql – Store massive values data from remote controllers

database-designMySQL

I implemented telemetry system in order to observe state of remote objects. Every object has a controller collecting data from some sensors (about 10-30 controlled parameters, lets take average value = 20). Controller send stored values to server approximately 100 times per day. There ara about 5000 objects.
I use simple schema to store values:

Params table(id, controller_id, parameter, index) and

Values table(id, parameter_id, value, add_time).

So I've got a rapidly growing Values table. I need to store values for one year minimum and the object count is increasing dramatically.

  • 20 (values row) * 100 (times per day) * 5000 (objects) = 10M rows per day;
  • 3.65B rows per year

I want minimize the used space and amount of rows. Also I've applied partitioning.

The values are used for reporting, usually daily. The report is built by one or two selected parameters.

Question: is it a good idea to store daily values in a single text field? Every new controller params will be appended to existing values field. New Values table(id, date, controller_id, values);

For example:

00:00:01=0:1.234;1:4.44;2:124.4;|00:01:06=0:1.256;1:2.55;2:3.44;|...

So daily values query is very simple SELECT * FROM values WHERE controller_id=123;

Is there a better solution? Or should I use existing one?

EDIT #1:
Controller has multiple controlled params (temperature, pressure etc)
Param changes stored in values table.

So query for report usually will be like:
We show to user available parameter list for controller:

SELECT index FROM parameters WHERE controller_id = 123;

Then user selects needed indexes and submits them to server:

SELECT * FROM values WHERE parameter_id IN (index0, index7,...) AND add_time >= today_date;

Best Answer

3.65B rows a year is a lot. How many year's data will you keep?

PARTITIONing? Don't do that until you have a reason. One reason is that you want to delete data that is over a year old. In which case, use a rolling RANGE of partitions based on month or week.

Another thing to consider -- will you need the details after some period of time? Or can you simply summarize the data, then throw away the raw data. This will potentially save a lot of space.

One text field -- it depends on what queries you will be doing. If you want the "average temperature" for last week, a single text field would make it 'impossible' in SQL.

What do the queries look like? Probably they want summary info. No amount of indexing will salvage the performance of a huge table. You must do incremental summarization.

If controller 123 has data scattered over 3.65B rows, it will take hours, maybe days to fetch all the info for that one controller. Think again whether that is really the query you want to run. And think about the other queries.

You must present all the queries up front in order to design this table optimally.

Related Question