0

I have a table of device information feed which is filled by data from remote device units.

There will be like 1000 units each sending an update per minute which will be stored as a row in that table. So each minute there will be like 1000 new rows added so 60,000 rows per hour and 1 440 000 rows per day. And I need to store minimum 3 weeks data.

As I am new to DB design therefore I am out of ideas to how to maintain such big flow of data.

There will be INSERT queries mostly on this table and SELECT queries, no UPDATEs or other queries will not be needed.

Table strucure: There will be 28 columns mostly FLOAT and TINYINT(4) and 1 DATE type. No other types. The table is in 3rd normal form and each row will be unique per device unit. So, columns can't be less then this.

I am using MySQL.

I have other tables but they are not so huge so maintainable. Looking for expert suggestions to how to maintain this database.

  • You seriously need to reset your intuition of huge. in modern terms your intuition is off by at least two, and more probably 3, orders of magnitude. This is a very manageable data volume that could be comfortably handled by a modern laptop, never mind a serious server. – Pieter Geerkens Jul 6 '14 at 18:25
2

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one machine (or even disk) and the compressed data (> 3wks) on another machine (or disk) for analysis. How about the Archive engine for older data? Or maybe you could consider InfiniDB for analysis (or Infobright)? These are columnar based stores optimised for data analysis.

[Edit] You might also want to look at this - link to data compression. I am implicitly assuming that you're on MySQL since you used TINYINT.

  • I must add that numeric data does not compress all that well, and there will be a minor hit on the INSERT performance, although at the rate of 1000 inserts/minute I wouldn't worry about it too much. – mustaccio Jul 6 '14 at 14:22
  • Yes - I did look at this but AIUI, sensor data can be very repetitive (this obviously depends) and that can affect compression. – Vérace Jul 6 '14 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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