"Rosemary Alles" <al...@ipac.caltech.edu> schrieb im
Newsbeitrag news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu...
> Run on a single processor, the following query is quite fast:
> ...snip...
> When concurrency is introduced (simply running the query on several
> processors against the same database - say 300 instances of it) ...
Normally it does not make sense, to start more instances than
available CPU-Cores (as long as a spanned process/thread
gets the chance, to do some real work - and does not have to
"idle around" in a waitstate).
On our Appserver we typically use only a worker-threadpool of
ca. twice the CPU-cores - the worker-pool is then provided with
new jobs from a central server-queue (where incoming requests
are "parked" first).
> ...a massive slow down with significant fluctuations in time between
> instances. Only reads are done (i.e. queries) - no writes. How does
> one optimize concurrency in sqlite3
As others already commented, if your Queries cannot be run within
the SQLite-Cache ("locally visible to one CPU-core" - because
your underlying table was too large to fit in), then the single resource
(your disk) comes into the game and SQLite does not scale well.
In case of smaller DBs or tables (or increased cache-settings for SQLite)
this little engine scales surprisingly well on parallel Read-Requests
(speaking for the mode with a separate connection on each thread,
so the shared-cache-mode is currently not used on our Appserver).
Here come some values (tested against a quad-core here, hosting
only a small NWind.db, which entirely fits into the cache) - each
client running an endless stress-loop with always the same query
("Select * from Invoices" - which stresses the engine a bit,
because that View contains some Joins already, resultset-size
2155 records on 26 columns). No resultset-caching was done
on the Appserver-end - so the query was always performed
against the sqlite-engine directly (so no tricks here).
Server-Responses per second (disconnected and serialized Rs):
1 client: ca. 21
2 clients: ca. 37
3 clients: ca. 51
4 clients: ca. 62
5 clients: ca. 71
6 clients: ca. 80
The reason, that I was not able, to get close to the 80 Responses
per second already with 4 clients was, that the clientside also had
some stress (visualizing the retrieved Resultsets in a DataGrid, before
starting a new request) - and 4 of them were started on a DualCore
XP-machine first (causing enough stress already on that XP-machine) -
the 5th and 6th client were additionally started then on another client-
machine (running on Linux then using the Wine-layer - in the same way
as the RPC-Server was doing on the quad-machine).
Here's a screenshot of this running scenario (6 clients causing stress,
the screenshot was taken on the XP-machine, which was running 4
of the 6 clients - the server is visible in a VNC-session-window).
http://www.datenhaus.de/Downloads/Quad-Stress.png
And as you can see on the KDE4-systemmonitor - the 4 CPU-cores
are pretty equally involved in that scenario (one core a bit more -
due to the WineServer, which apparently was handling the IO-translation
into the Linux-socket-stack "on its own" - also interesting to see,
that Wine apparently has something like a "cutting-point" at ca. 80%
CPU-usage - probably to "play fair" with the underlying linux-OS
or something like that... (at least on that debian-machine here).
As as side-note (becoming somewhat more offtopic now in this
thread) - the network-coverage was at ca. 17MB/sec on eth0 in
this small stress-test (1GBit-network) - the RPC-transfers were
running FastLZ-compressed over the wire - if I would
have switched that off, then we would have seen a ca. 50%
coverage on the GBit-channel (because usually circa factor 3 is
achievable with compression on typical DB-resultsets).
And it's not that easy, to reach ca. 50% GBit-coverage with
"typical DB-Server-Engines" - measured on the serverside under
concurrency-stress (e.g. against Postgres or MySQL or
MS-SQLServer). That's mainly due to the fact, that Appservers
usually don't (have to) support serverside cursors - so typically
larger packets (completely prepared and serialized resultsets)
can be send over the wire (not that much ping-pong necessary
on the protocol).
So for our purposes (having more or less smaller DBs here),
the current sqlite-implementation scales good enough in
these typical, more ReadDirection-related-scenarios -
the currently implemented locking-scheme seems not all
that restrictive (if working against non-shared SQLite-caches) -
so I hope, the sqlite-devs (although they often recommend,
to work singlethreaded wherever possible <g>) keep up their
good work on that front. :-)
Regards,
Olaf Schmidt
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
