Geek Answers Handbook

MYSQL Best way to break huge data over time?

I am currently developing a system that monitors some of the devices that I need to store logs and data for them. Data will be received and stored every 15 seconds from each device. Now I had some experience developing a database schema, but not that size before.

There will be 2 tables. One with information about the device, and the other with statistics. The fact is that if we have 5,000 devices, then we say about 25 million results per day. In a couple of weeks, this table will be quite large.

Now most of the time I will need to run queries regarding the last day, so I was thinking about somehow breaking it all down into several tables.

If you spend all the time and update what you live and say at the end of each week, move all the data to another and save it there if I need to run some queries with old data.

Now should I stick with InnoDB? Only the foreign key that I would use is only for the device identifier in the statistics table.

thank

+4
source share
1 answer

If you usually want to see data for the last day, then my suggestion is to split the data by day. You can learn more about partitioning MySQL here .

Depending on the queries, you will also need indexes on the date and device. With 28.8 million rows per day, many queries should end in a reasonable amount of time.

, .

, 5000 15 - 333 /. , .

+3

More articles:


All Articles