Re: [sqlite] Optimizing concurrency with sql query - locks?
The original question was about 300 queries, which I took to mean selects. If the database is in memory, will 300 selects still cause synchronous disk I/O? Jim On 5/6/09, John Stanton wrote: > Sqlite is an ACID database - it ensures that data is written to disk, so > a database in memory still shares a single disk resource. > > Jim Wilcoxson wrote: >> I'm not sure what you are considering a massive slowdown, but let's >> assume that the entire database fits into memory and disk I/O isn't >> the bottleneck. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing concurrency with sql query - locks?
Many thanks to all of you for your responses. Helped a great deal. I
think I'm experiencing a "duh" moment.
:)
-rosemary.
On May 6, 2009, at 10:34 AM, Olaf Schmidt wrote:
>
> "Rosemary Alles" 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
Re: [sqlite] Optimizing concurrency with sql query - locks?
Sqlite is an ACID database - it ensures that data is written to disk, so a database in memory still shares a single disk resource. Jim Wilcoxson wrote: > I'm not sure what you are considering a massive slowdown, but let's > assume that the entire database fits into memory and disk I/O isn't > the bottleneck. You said you're running 300 instances of the query on > several processors. If several means 3 CPUs, then in a perfect world, > running 300 instances will be 100 times slower than running just 1. > This assumes you are getting linear scalability, which no one ever > does, so the slowdown will be more than 100x. If you have 4 > processors, running 300 queries simultaneously should still be 75x > slower (best case) than running 1. > > You also mentioned seeing a wide variance in response times. This is > typical, because most schedulers in multi-user systems won't perfectly > distribute CPU time to 300 processes. If the scheduler decides to run > task 1 to completion, then task 2, etc., your last task will appear to > take much longer than the first task. For example, let's say that > each task by itself takes 1 second of CPU time and zero I/O time, > assuming the database is all in memory. 300 queries will take 300 > seconds to complete. If the system scheduler runs each task, in > order, to completion, then the first task will take 1 second and the > last task will take 300 seconds to complete. Wide variance. Or, the > system scheduler could decide to give each task 1/100th of a second. > It will take 3 seconds for all tasks to get a timeslice. In this > scenario, it will still take 300 seconds to complete all 300 jobs, but > they will complete within 1/100th of a second of each other, and each > job will report that it took 300 seconds to complete. No variance. > The price you will pay for no variance is that you increase the > multiprocessing overhead because now instead of doing just 300 task > switches to execute each job to completion, you are doing 300x100 = > 30,000 task switches. Task switches aren't free, so the "no variance" > schedule will take longer overall than the wide variance. This is a > classic fairness vs high throughput dilemma; you can't have both. > > If you are seeing something like 1000x slower performance, then as > others have mentioned you could have a disk I/O or locking bottleneck. > > Jim > > On 5/6/09, Igor Tandetnik wrote: > >> "Rosemary Alles" wrote >> in message news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu >> >>> Run on a single processor, the following query is quite fast: >>> >>> When concurrency is introduced (simply running the query on several >>> processors against the same database - say 300 instances of it) causes >>> a massive slow down >>> >> Well, you may have multiple CPUs, but you only have a single hard drive. >> That drive head can't be in multiple places simultaneously. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing concurrency with sql query - locks?
"Rosemary Alles" 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 ) 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
Re: [sqlite] Optimizing concurrency with sql query - locks?
I'm not sure what you are considering a massive slowdown, but let's assume that the entire database fits into memory and disk I/O isn't the bottleneck. You said you're running 300 instances of the query on several processors. If several means 3 CPUs, then in a perfect world, running 300 instances will be 100 times slower than running just 1. This assumes you are getting linear scalability, which no one ever does, so the slowdown will be more than 100x. If you have 4 processors, running 300 queries simultaneously should still be 75x slower (best case) than running 1. You also mentioned seeing a wide variance in response times. This is typical, because most schedulers in multi-user systems won't perfectly distribute CPU time to 300 processes. If the scheduler decides to run task 1 to completion, then task 2, etc., your last task will appear to take much longer than the first task. For example, let's say that each task by itself takes 1 second of CPU time and zero I/O time, assuming the database is all in memory. 300 queries will take 300 seconds to complete. If the system scheduler runs each task, in order, to completion, then the first task will take 1 second and the last task will take 300 seconds to complete. Wide variance. Or, the system scheduler could decide to give each task 1/100th of a second. It will take 3 seconds for all tasks to get a timeslice. In this scenario, it will still take 300 seconds to complete all 300 jobs, but they will complete within 1/100th of a second of each other, and each job will report that it took 300 seconds to complete. No variance. The price you will pay for no variance is that you increase the multiprocessing overhead because now instead of doing just 300 task switches to execute each job to completion, you are doing 300x100 = 30,000 task switches. Task switches aren't free, so the "no variance" schedule will take longer overall than the wide variance. This is a classic fairness vs high throughput dilemma; you can't have both. If you are seeing something like 1000x slower performance, then as others have mentioned you could have a disk I/O or locking bottleneck. Jim On 5/6/09, Igor Tandetnik wrote: > "Rosemary Alles" wrote > in message news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu >> Run on a single processor, the following query is quite fast: >> >> When concurrency is introduced (simply running the query on several >> processors against the same database - say 300 instances of it) causes >> a massive slow down > > Well, you may have multiple CPUs, but you only have a single hard drive. > That drive head can't be in multiple places simultaneously. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing concurrency with sql query - locks?
Igor Tandetnik wrote: > "Rosemary Alles" wrote > in message news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu > >> Run on a single processor, the following query is quite fast: >> >> When concurrency is introduced (simply running the query on several >> processors against the same database - say 300 instances of it) causes >> a massive slow down >> > > Well, you may have multiple CPUs, but you only have a single hard drive. > That drive head can't be in multiple places simultaneously. > > Igor Tandetnik > > Further to Igor's point, the machines we use today almost always have one hard disk and database activity involves sharing that single resource between users. If there is heavy disk activity you will get maximum throughput by having one process running at a time. If there is sporadic disk activity interspersed with other processing you will get more throughput with concurrent processes. Remember that all multi processing and multi threading involves a substantial overhead. The throughput on old, slow computers with a simple monitor instead of a multi tasking O/S was very impressive. because there was no task switching overhead Machines optimized for multi-user database activity have many disk spindles with the database distributed between them so that the disk farm is no longer a single resource. > _ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizing concurrency with sql query - locks?
"Rosemary Alles" wrote in message news:af79a266-b697-4924-b304-2b1feccba...@ipac.caltech.edu > Run on a single processor, the following query is quite fast: > > When concurrency is introduced (simply running the query on several > processors against the same database - say 300 instances of it) causes > a massive slow down Well, you may have multiple CPUs, but you only have a single hard drive. That drive head can't be in multiple places simultaneously. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimizing concurrency with sql query - locks?
Hullo all,
Run on a single processor, the following query is quite fast:
// Select Statement
sprintf(sql_statements,
"select lp.%s, lp.%s, lp.%s, lp.%s, pb.%s from %s lp, %s pb "
"where lp.%s > ? and lp.%s=pb.%s "
"order by lp.%s, lp.%s, pb.%s",
ARTID_LATENT_PARENT_XPOS,
ARTID_LATENT_PARENT_YPOS,
ARTID_LATENT_PARENT_RADIUS_OF_INFLUENCE,
ARTID_LATENT_PARENT_FR_TIME_STAMP,
ARTID_LATENT_PARENT_PIXEL_BIN_NUM,
ARTID_LATENT_PARENT_TABLE,
ARTID_LATENT_PARENT_PIXEL_BIN_TABLE,
ARTID_LATENT_PARENT_FR_TIME_STAMP,
ARTID_LATENT_PARENT_SOURCE_ID,
ARTID_LATENT_PARENT_SOURCE_ID,
ARTID_LATENT_PARENT_XPOS,
ARTID_LATENT_PARENT_YPOS,
ARTID_LATENT_PARENT_PIXEL_BIN_NUM);
When concurrency is introduced (simply running the query on several
processors against the same database - say 300 instances of it) causes
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
The database is opened as follows:
//sql_rc = sqlite3_open(database_name, &sql_db);
sql_rc = sqlite3_open_v2(database_name,
&sql_db,
SQLITE_OPEN_READONLY,
NULL);
if (sql_rc != SQLITE_OK) {
fprintf(stderr, "Function:%s can't open database:%s, error:%s\n",
FLAG_ON_LATENT, database_name,
sqlite3_errmsg(sql_db));
sqlite3_close(sql_db);
return(FAIL);
}
-rosemary.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
