Why sqlite3 can't work with NFS?

Question

I switch to using sqlite3 instead of MySQL because I had to run many jobs on a PBS system which doesn't not have mysql. Of course on my machine I do not have a NFS while there exists one on the PBS. After spending lots of time switching to sqlite3, I go to run many jobs and I corrupt my database. Of course down in the sqlite3 FAQ it is mentioned about NFS, but I didn't even think about this when I started.

I can copy the database at the beginning of the job but it will turn into a merging nightmare!

I would never recommend sqlite to any of my colleagues for this simple reason: "sqlite doesn't work (on the machines that matter)"

I have read rants about NFS not being up to par and it being their fault. I have tried a few workarounds, but as this post suggests, it is not possible.

Isn't there a workaround which sacrifices performance?

So what do I do? Try some other db software? Which one?

Answer 1

You are using the wrong tool. Saying "I would never recommend sqlite ..." based on this experience is a bit like saying "I would never recommend glass bottles" after they keep breaking when you use them to hammer in a nail.

You need to specify your problem more precisely. My attempt to read between the lines of your question gives me something like this:

You have many nodes that get work through some unspecified path, and produce output. The jobs do not interact because you say you can copy the database. The output from all the jobs can be merged after they are finished. How do you effectively produce the merged output?

Given that as the question, this is my advice:

Have each job produce its output in a structured file, unique to each job. After the jobs are finished, write a program to parse each file and insert it into an sqlite3 database. This uses NFS in a way it can handle (single process writing sequentially to a file) and uses sqlite3 in a way that is also sensible (single process writing to a database on a local filesystem). This avoid NFS locking issues while running the job, and should improve throughput because you don't have contention on the sqlite3 database.