0

I need r/w access to a sqlite database of several GBytes. Putting it on the SD card is out of the question as the writing would wear out the SD too quickly. This is why I was thinking of network file systems, like sshfs, samba or nfs. Now, I know this is a no-no for sqlite, due to latency and locking issues, but I'd be surprised if some of hadn't tried nonetheless. Which of the available network file systems on the RPi works best with sqlite, even though it's a no-no to use one? Please, don't suggest switching away from sqlite.

closed as primarily opinion-based by Steve Robillard, Milliways, Ingo, goldilocks Dec 4 '18 at 19:13

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    so, locally attached storage (USB hard drive for example) is not an option? – Jaromanda X Dec 3 '18 at 3:53
  • 1
    also, what works best for someone else may not work best for you ... unless someone happens to be using exactly the same environment to "host" the data as you – Jaromanda X Dec 3 '18 at 4:19
  • Host the data? I'm doing some stuff on the data, not just hosting. Attaching a USB drive seems a waste of money to me. 4 RPI cores to process data and im barely using 1 most of the time, that's the idea. – user1095108 Dec 3 '18 at 9:00
  • You're caught up on the most innocuous word ... host ... i.e. the system that contains the data – Jaromanda X Dec 3 '18 at 9:05
  • 2
    I log data to an SQLite3 database on a NFS filesystem with no problems. The only rule is one writer, multiple readers (to avoid the locking/locked database problem). To enforce that my reader programs use PRAGMA query_only; – Dougie Dec 3 '18 at 12:07
1

As far as network file system go, reasonable options are AFAIK limited to NFS and SMB, and unless you need SMB features (like Windows compatibility) you should really go with NFS. It's much faster (and CPU time is something you'd rather spare on an RPi) and somewhat more stable than SMB. NFS v4 supports file locking.

Also,

Attaching a USB drive seems a waste of money to me

When I have a problem that can be solved by throwing some $30 on it, I usually buy my way out.

  • Say I had 10 RPis working on a problem. You'd buy 10 USB drives for each Pi? – user1095108 Dec 4 '18 at 15:13
  • @user1095108 No, I would have spent those $300 of HDD budget plus $300 saved by not buying 10 RPis to buy something substantially more performing, perhaps a good DSP or FPGA board. I get your point, but perhaps you should have mentioned in your question that you have 10 RPi boards. – Dmitry Grigoryev Dec 5 '18 at 9:23
  • And how would you interface the boards with a database? – user1095108 Dec 5 '18 at 10:39
  • @user1095108 That would depend on the project at hand and the board. There are FPGA-based mining boards which have no problem connecting to blockchain servers. Why would a database connection be impossible? – Dmitry Grigoryev Dec 5 '18 at 13:50
  • Its certainly not impossible, just complicated. I've never heard of it being done. – user1095108 Dec 5 '18 at 14:45
1

I log data to an SQLite3 database on a NFS filesystem with no problems. The only rule is one writer, multiple readers (to avoid the locking/locked database problem). To enforce that my reader programs use PRAGMA query_only;

There's some code at https://github.com/DougieLawson/RaspberryPi that demonstrates that.

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