How bad is SQLite concurrent access?

SQLite is fairly light – this has been justified by me by running it on a cheap plan which has 512MB memory with a number of other services up and running. At its peak time I had about 5k pageviews/day with each view involving a number of DB reads – I know that traffic is probably nothing, but the point is I didn’t see any type of sluggishness so that’s good.

But that’s only some database SELECT. Now I am building a new website with concurrent read/write. Will SQLite live up to the load?

After reading extensively on Stackoverflow.com I believe SQLite is not suitable if you have multiple users with parallel writes. Since each write will put a lock on it, the data pipeline can easily get congested.

Out of curiosity, I did the following experiment to validate the above statement.

In my backend code I have a function, say insert() that inserts into DB a record of 6 fields, with one field of about 100 chars and others of various types (timestamp, id, etc.). And I have another function foo() that runs this insert() every 10 milliseconds. I made a web link that triggers this function foo() from the browser so I can start as many as I like. I started it in 45 tabs, meaning in theory there are 45 inserts going on every 10 milliseconds. That’s more traffic than 99% of the websites out there I believe. I watched the backend was going like crazy. Concerned by the fact that slow I/O might hinder the overall performance, I grepped ‘ERROR’ only which occurs when something is wrong. I kept it running for a few minutes and about 1 million lines were inserted and I only saw 10 ERRORs (“Error: SQLITE_BUSY: database is locked”). This is not bad at all.

Then I further tested it with benchmark tool ab: ab -n 5 -c 2 [url]. It failed to handle well. Maybe http module from node.js isn’t scalable or SQLite could. I am not sure exactly which one caused it.

Update: turns out there is a bug in Mac’s ab that’s why I  got ‘send request failed’ message. I did another experiment this time visit will Insert one record with a number of Select:

I did it on a Linux box again with -n 1000 -c [10,20,50,100,200,500,1000] and following graph shows the result

Overall MySQL responses faster than SQLite when -c increases and SQLite failed to respond when -c > 200. I’m not sure how many websites will ever reach this level of concurrency although faster response seems always better if you can afford the memory. However, SQLite isn’t too bad consider its light resource requirement and convenience of use. People seem to be against it when it comes to using it for production but I don’t see the reason why.

https://www.sqlite.org/whentouse.html
https://www.sqlite.org/lockingv3.html