On Tuesday, October 7, 2014 10:49:36 PM UTC+3, agentzh wrote:
And a real problem is the multi-worker model used by both nginx and
php-fpm while running SQLite. When multiple workers trying to access
the same SQLite database file at the same time. You need *heavy* locks
to ensure consistency.
SQLite does locking on multi-process access. Usually this is only problematic with
multiple-writers scenarios, but you can always start transactions and do you work
in a single batch. Yes, other transactions will wait, but in many apps it is not a
problem to wait for a write access for a few milliseconds (in most scenarios
usually less). SSDs help as well.
BTW, you can always put a network layer atop SQLite, just like
KyotoTycoon atop KyotoCabinet.
There seems to be async, non-blocking SQLite for Node.js:
https://github.com/mapbox/node-sqlite3
So I was thinking about having one for OpenResty as well.
But if you really need performance, then you should probably avoid
using SQLite in the first place :)
I know, but I think that SQLite is great (even better than alternatives in some scenarios, even performance-wise):
http://we-love-php.blogspot.fi/2012/08/mass-inserts-updates-sqlite-vs-mysql.html (not a great test, I know, but still)
And I have found that you can get plenty of "concurrence" in many applications that I have written.
If SQLite DB commands take say a millisecond to perform you can support 1000 writing clients
in one second. Yes, the last one need to wait one second for his turn, but that is okay in many apps
as there are usually a lot more concurrent readers than writers (and readers don't block readers, and
even writers don't block readers in WAL mode).
AFAIK, SQLite itself does not provide socket interface. Am I wrong
here? If SQLite supports sockets, then we don't have to block at all
in OpenResty for we have cosockets :)
No it doesn't support sockets (officially, yet there are many projects that
have added it). What I actually meant was caching SQLite connection
_file_ handles (e.g. Lua module level, so that single worker always uses
the same persistent SQLite connection).
... just thinking about extending OpenResty support for SQLite.
Regards
Aapo