"Shawn Anderson" <[EMAIL PROTECTED]> schrieb im
Newsbeitrag news:[EMAIL PROTECTED]
> I've read many things, some conflicting, about concurrent
> users on a Sqlite database.
> The feeling I get is that Sqlite is good for a standalone desktop
> database, but should not be used in a concurrent user situation
> (like a website with many users).
> Is there a definitive answer?
Of course not... ;-)
If your scenario is a "typical one" - with much more reads
than writes against the DB, then SQLite is a wonderful
and fast performing multiuser-engine, if hosted behind
an appropriate server-layer.
>From my experience with the server-layer we use here
(on Windows, or Linux/Wine) - using a ThreadPool, adjusted
to 8 threads, each thread hosting a separate sqlite-connection
(no shared cache) and working only with the default-
"sqlite-locking-helpers" (watching for SQLite_Busy, etc.),
the Read-Direction scales very good with concurrent
requests.
>From 8 concurrent clients (firing requests in an endless-
loop) one can reach ca. 1200 responses/sec on smaller
resultsets (e.g. 1-10 rows on 5 columns).
And for somewhat larger resultsets (ca. 800Rows
on 15 columns as e.g. "Select * from Orders" against
NWind.db) the server can deliver 300-600 responses
per second.
These are roundtrip-results which are based on disconnected
recordsets, fully transferred to and deserialized at the
clientside over sockets (including visualizing in a DataGrid).
All that on a cheap DualCore (2.2GHz AMD), on a server-
machine with 2GB-Ram.
In our tests we found, that the whole thing works ca. 2-3
times faster than MS-SQLServer working against the
same NWind-Database (using disconnected ADO-
Recordsets and the appropriate serializing methods) in
that concurrent-readers-scenario.
In fact it works that fast, that the limiting factor is not the
CPU usage anymore, but the network-bandwidth.
We had to switch to 1GBit-network-stuff, to bring the
Appserver to its CPU-limits (ca. 34MByte/sec then
on the GBit-Network-adapter whilst serving larger
resultsets under full CPU-load).
With an 100MBit-connection the server was reaching
only 30% CPU-load, not getting any higher (but the network-
adapter was reaching its limits with ca. 10-11MByte/sec
which is the maximum on 100MBit-Hardware).
So as said, the multiple readers scenario works very good.
Regarding "full-DB-locks" whilst writing...
That sounds "a bit frightening" at the first look, but since
the insert-performance of sqlite is also blazing fast (e.g.
ca. 10 records per second for "a 6 mixed columns-
insert", using direct binding and wrapped in a transaction)
this is also a "no issue" in my opinion, if you design your
client-apps in a way, that your DB-Updates/Inserts
are done in a "granular" way - meaning more but smaller
transactions are better than single "mega-transactions".
To bring a concrete example:
A client sends an Insert-Job with 100 new Records whilst
the DB-Server is under full "read-stress". From the (ca.) insert-
performance-values I've mentioned above, the DB would
be blocked for only ca. 1-2msec (delivering SQLite_Busy to
the Readers) whilst inserting the 100Records within a
(Begin Immediate) transaction.
Hope that helps, that you get an impression what this engine
is good for in a multiuser-scenario. For me it is by far
underrated in regards to these concurrent scenarios - try to
find a good (threadpool-based) server-implementation
(or write your own) and try it yourself.
Olaf Schmidt
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
