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.
2 Answers
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.
answered Dec 4, 2018 at 9:21
-
Say I had 10 RPis working on a problem. You'd buy 10 USB drives for each Pi? Commented Dec 4, 2018 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. Commented Dec 5, 2018 at 9:23
-
And how would you interface the boards with a database? Commented Dec 5, 2018 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? Commented Dec 5, 2018 at 13:50
-
Its certainly not impossible, just complicated. I've never heard of it being done. Commented Dec 5, 2018 at 14:45
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.
PRAGMA query_only;