I am having the following situation:
On an embedded linux board (a Raspberry Pi - but might be replaced in future by a SBC with similar h/w-specs) a Python application is reading sensor data every five seconds.
I want to extend the application by some long-term monitoring which can be accessed through a web-interface showing the long-term activity nicely with graphs, etc.
After doing some research I ended up with a couple of possible databases:
I am however a bit afraid of using one of the above databases on a Raspberry Pi. The reasons are the following:
Data will be stored for one or two years. With having new data every 5 seconds this will make a total of more than 6 million entries! - For a single year only.
I am not sure if the Raspberry Pi (or similar devices) will handle MySQL or SQLite databases of that size.
One solution might be lowering the resolution like you'll find it build-in when using the RRD-Tool as the database. But due to the fact that the RRD-Tool works with a concept of hearbeat and step, data will be averaged every time a new entry is being inserted into the database. And I haven't figured out how to turn that off.
So which database is best for:
- Millions of entries
- Or: lowering resolution after given time but without building averages, etc.
- Working fluently on Raspberry (similar devices) when accessing over Web-Interface an 2-3 Users in parallel
Raspberry is used for:
- Reading the sensors data
- storing it into a database
- providing web-interface to visualize data