Hacker News Reader

Comment by foft | Hacker News Reader

I’d really love SQLite to work on the nfs and cifs. Is there anything that can be done to make that safe at a kernel level?

View on HN

Wouldn't it just be easier to use MySQL or Postgres in that situation? I mean if you are connecting to a network share, why not use a database over the network instead?

Depends how portable you need it to be.

SQLite basically runs on everything and doesn't need a server to work with it.

If you use MySQL or Postgres, you need a lot more dependencies.

because sqlite is way simpler to deploy. MySQL and Postgres require being set up, allow outside connection but you need to be careful not to open to much, you need to create a database, a user, set a password, grant privileges to the user on a database, configure the clients to use this user and this database.

With a network share, you already configured authentication. SQLite would be easier and maybe even more secure since it does not increase the attack surface?

however, SQLite is not really meant to be used concurrently efficiently.

Can you simulate concurrency with SQLite somehow?

Like, using a front program to control traffic flow for queries to read and write.

You could add a queue and execute the queries one at a time. Probably SQLite or at least popular wrappers for it already do this? But it will be slower than a DBMS that allows you to make hundreds of queries in parallel.

Didn’t SQLite say that their lock/unlock process is really fast? Such that it doesn’t really pose a significant drawback.

To be honest, you are replacing one set of problems with another. The rationale about configuration does not make a lot of sense either, better you learn how to do things right than what seems easier.

I haven't played with shares for a long time. But if I remember only one node can write to a file at once. That's also to say if file read/write performance will be any good for this use case.

NFS or Samba servers have their own issues with attack surface.

Please rethink your idea please.

Sqlite is in loads of things, Firefox, VSCode etc., if you have NFS home directories you can have issues. KDE’s akonadi uses MySQL via localhost, though it can’t handle you being logged in more than one place. My view is that a JSON or XML file would work a lot better.

You could also look into Firebird , there is an embedded server which provides you with more oomph , is easy to use and has a small footprint.

The SQLite docs seem to caution against some broken NFS implementations and mentions Apple explicitly, but otherwise imply that NFS should work.

It’s very slow at writing over NFS. I wrote a simple python script writing 1000 entries, committing after each one, in WAL mode. In Fedora 22 VMs, NFS4.2 default settings, no security, SSD on host machine.

  - local speed = 2 seconds
  - NFS to another VM = 31 seconds
  - NFS via localhost = 17 seconds

Interesting. 17-31 ms per write is about what a write and sync used to cost on spinning disks. Could still be ok for some use cases.

I tried the same test on a different system with spinning drives, it took 1 minute 43 seconds. The resulting file is 12kb (all inserts, no deletes or updates). It’s sync writes that are slow.

CIFS is do-able, but you would need the database to have a special mode for it, and for some extra API to be exposed by the kernel (byte-level locking)

NFS is just about impossible (except in narrowly restricted situations)

Reply on news.ycombinator.com