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.
Best Answer
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.