0

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;
5
  • Why do you want to minimize the amount of rows? MySQL is working happily with billions of rows as long as your indexes are good for the queries. Have you considered daily tables? Generally this big blob storage schemas fall short on many aspects against normal forms. Also keep in mind that your text field is going to be stored off-page (assuming InnoDB) as text and not as series of numbers so you're actually bloating your table with that design rather than saving space. Feb 8, 2016 at 14:42
  • The idea was to reduce space usage, dump size and number of queries. Also unfortunately values table crashes sometimes. So repair or dump import may take several hours.
    – imilbaev
    Feb 9, 2016 at 6:15
  • 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.
    – Rick James
    Feb 12, 2016 at 18:26
  • The second SELECT would probably benefit from INDEX(parameter_id, add_time). But do you add things after "today"?
    – Rick James
    Feb 12, 2016 at 18:27
  • first query controller=123 will select parameter indexes (about 10-20 values). User selects necessary params indexes and request report by them. Then second query appears in order to create report by parameter value.
    – imilbaev
    Feb 15, 2016 at 6:50

1 Answer 1

0

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.

1
  • Need store data at least for one year. Used partition by date before on existing system for same reason: to much rows so data retrive became to slow. The data can't be summarized. I will edit question to show queries that would be used to retreive data
    – imilbaev
    Feb 10, 2016 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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