1

I'm using a raspberry pi 2B with raspbian lite. I have made a program that communicates with a LoRa module (RFM95, sx1276 chip) and collects data from a sensor. The data it collects, are stored in a file date.log and goes to Google Drive using third party software. Now I want to make an asynchronous TCP service to collect data remotely but storing data in a single file doesn't sound like a good idea. I'm thinking of using a database instead of simple files, like sqlite3 but I'm not sure about its performance.

Is it possible for a database to perform writes from more than one program?

Is it better to integrate my software into one program? Like the LoRa driver with the TCP listener together.

  • What's the throughput of your current program? How many writes per second? – Laiv Jun 4 at 11:51
  • So far I have one sensor. But I'm planning on implementing up to 20 sensors. I think the highest frequency will be a transmission every 10 seconds. – MrBit Jun 4 at 13:02
  • That's low throughput. Where are you planning to roll out the DB? Are you planning to deploy a DB yourself? Perhaps a DBaaS? – Laiv Jun 4 at 15:10
2

Basically yes:

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. (source)

But: As the source says, the file will be locked for the time of a write operation. This will limit the throughput of your application.

Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. (source)

Of course you can optimize this. If the data comes at 1000 Hz (just to illustrate), you could collect the data from 100 ms and batch write it, but a fully fledged RDBS could suit your use case better. If we are talking 10 Hz or something, SQLite might fit, too. Disclaimer: These are guesses at best. I'd go and measure the performance both with SQLite and MySQL.

Some users are arguing that a fast USB stick would make sense for MySQL@RPi2 since the performance of the SD card is mediocre (see here, but it's German, sry), but this should hold for SQLite, too.

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.