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.

Ghanima
  • 15,855
  • 15
  • 61
  • 119
user1095108
  • 239
  • 1
  • 9
  • 1
    so, locally attached storage (USB hard drive for example) is not an option? – Jaromanda X Dec 03 '18 at 03: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 03 '18 at 04: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 03 '18 at 09:00
  • You're caught up on the most innocuous word ... host ... i.e. the system that contains the data – Jaromanda X Dec 03 '18 at 09: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 03 '18 at 12:07
  • Which boils down to: which of the filesystems implements file locking best? – user1095108 Dec 03 '18 at 17:03
  • @Dougie You should write an answer: other people may not know about PRAGMA query_only, and your comment may not stay on the site forever. – Dmitry Grigoryev Dec 04 '18 at 09:09
  • "as the writing would wear out the SD too quickly" -> Unlikely. If this is an assumption based on empirical experience, ignore me, but I don't think it is -- there's an excessive amount of online hysteria that seems to haunt RPi users in particular here (implying it really is just a viral meme). So, you may be tilting at windmills with that as a fundamental premise. A decent quality SD card will probably give you something like 5-10x better performance than a network mount, and is more (not less) reliable in general. – goldilocks Dec 04 '18 at 19:17
  • @goldilocks you're wrong, there are testimonies on this site of people who had their SD trashed by MySQL, for example. If MySQL can trash the SD, so can sqlite. – user1095108 Dec 04 '18 at 20:12
  • Ah, the anecdotal evidence! Some more: Having moderated here for a long time, I would be impressed if you could point out one of the 20 questions than include "sd card" and "mysql" in them which seriously led you to this belief, keeping in mind telling someone the moon is made of cheese does not make it so. It is one of those almost religiously contentious tech topics for some strange reason, usually bundled around an XY problem where the problem is just a rumour. – goldilocks Dec 04 '18 at 20:50
  • @goldilocks here's the relevant post. – user1095108 Dec 05 '18 at 09:03
  • @user1095108 I'm with goldilocks on this one. I'm using the SD card exclusively, with /var directory on it, occasional swap when I compile, and journalling FS. It still took me 3 years to wear out my first SD card, and it didn't crash, just became read-only, at which point I just copied my data to a new one. Perhaps fake SD cards from no-name Chinese shops do die within a month. – Dmitry Grigoryev Dec 05 '18 at 09:55

2 Answers2

2

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.

Dmitry Grigoryev
  • 27,928
  • 6
  • 53
  • 144
  • Say I had 10 RPis working on a problem. You'd buy 10 USB drives for each Pi? – user1095108 Dec 04 '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 05 '18 at 09:23
  • And how would you interface the boards with a database? – user1095108 Dec 05 '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 05 '18 at 13:50
  • Its certainly not impossible, just complicated. I've never heard of it being done. – user1095108 Dec 05 '18 at 14:45
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;

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

Dougie
  • 5,301
  • 10
  • 19
  • 28