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;
controller_id=123
will be millions of rows? What will the client do with that flood of traffic? Perhaps you could do more of the work in SQL, and deliver a smaller list.SELECT
would probably benefit fromINDEX(parameter_id, add_time)
. But do you add things after "today"?