niutech niutech - 28 days ago 7
MySQL Question

Which database to store big metric data on Raspberry Pi?

I want to use Raspberry Pi as an independent sensor, which will measure some value each second and store this metric data into a local database. Then I would like to query the database based on the date range. Which database should I use, taking into account the limited resources of RPi and that there will be ca. 30758400 records/year? Are there any RPi-specific lightweight database engines especially for this purpose?

pdw pdw
Answer Source

I think SQLite will perform well in this role. You might need to tune the pragma settings a bit for the rPi (e.g. set journal_mode=WAL), but SQLite can easily handle multi-gigabyte databases. (SQLite's main weakness is concurrent access, but that won't be a problem for your application.)

If you only need to store timestamp/value data, and only query on timestamp ranges, you might opt to use a key/value store such as LevelDB. You lose the flexibility of a SQL engine, but you gain performance.

What storage medium are you planning to use? An ACID database will write to the disk for every transaction. Continuous I/O like that can quickly kill an SD-card.