Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]
Absolutely. For what I'm trying to do, and given my experiments thus far, I would love to replicate the performance of the one-query/thread-per-process concurrency in the multithreaded case, foregoing the resource optimisations (shared cache etc.) and just having each query/thread do whatever is happening in the process case. -- Luke On 2010-03-14, at 6:54 AM, Olaf Schmidt wrote: > If my assumption is right, that running such a multithreaded > scenario against a singlethreaded compiled library performs > at the same level as the multiple-processes-scenario, > then the question remains, how one could make this mode > "secure and usable" in the same way as with the "naturally isolated" > process-memory. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]
Just to be clear, from my point of view at least, the big difference is between: - Multiple threads performing concurrent queries (with or without the shared cache) vs - Multiple processes performing concurrent queries In the latter case (for a number of queries chosen to lie within the expected number of hardware cores/hyperthreading on the test machine) the time taken to process all the queries is just a tiny fraction longer than the time it takes to process a single one. Whereas in the former case I find that the time to process all the queries is commensurate with having lost much of the concurrency - much more akin to the time taken if you had serialised the processing of all the queries. On 2010-03-12, at 12:57 AM, Marcus Grimm wrote: > I'm not able to reproduce the reported behavior that a single process > queries faster than a single thread. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]
Hi Marcus, Well, I'd certainly be interested in looking at your code. Can you mail a zip, or post to a web or file hosting site? Thanks. Your results seem to broadly agree with mine: multithreaded querying can save a percentage of time (10-30%?) over the same queries issued serially with no wait interval. My queries are a little more complicated, so that may explain why I come in nearer a 13% saving, whereas your best case is more like 28%. It's interesting how little the shared cache size seems to affect actual throughput, and even really whether it is on at all makes a relatively small difference here (ignoring the perceived benefits on memory pressure). I guess, for me the question boils down to why running these queries in their own process (which admittedly will use more memory resources) is so much more beneficial to overall throughput - when there (probably) ought to be a way to get the same partitioning between threads/connections in the multithreaded model. In other words, I'm expecting/hoping for a way to get each thread to behave as if the query was in a separate process - getting its own private resources so that there's absolutely no need for synchronisation with activity on other threads - particularly for this read-only case. For this small number of threads/queries (relative to the number of processing cores I have), the difference is between 1.6s and 8.34s, which is really quite significant. -- Luke On 2010-03-11, at 5:58 AM, Marcus Grimm wrote: > I have followed the discussion about this issue with interest since > my usage of sqlite involves threads and sharedcache mode as well. > I have also generated a little speed test program that uses > multible threads and shared cache to read *some* rows out of the > sqlite DB. It might not help here but here are my results: > > Test cenario: > + DB is has two tables and one index, DB size is 1.5Gb, main > table contains 150 rows, a child table has 20 * 150 rows. > + Windows 7, intel i7 processor > + Query is simple like "SELECT * FROM TABLE WHERE ID = ?", > ID is the primary key and thus has an index on it. > The result is used to query in a 2nd table. > + page size is not changed, thus 1024 bytes on windows > + Each threads opens its own DB connection, thus time > for sqlite3_open_v2 is included in the running time. > + PRAGMA read_uncommitted = True; > + Uses 3.6.22 - SEE Version > > > Test-1: shared cache on, cache size = 10 > a) 8 queries, distributed over 8 threads: 4.6 sec > b) 8 queries, distributed over 4 threads: 5.8 sec > c) 8 queries, single thread: 6.3 sec > > Test-2: shared cache off, cache size = 10 > a) 8 queries, distributed over 8 threads: 5.6 sec > b) 8 queries, distributed over 4 threads: 6.0 sec > c) 8 queries, single thread: 6.3 sec > > Personally, I wouldn't call it a bug (bugs in sqlite are extremely > rare), but it looks a bit disappointing how sqlite scales when > multiple threads/processors are involved. I was expecting a much > higher effect on read speed when shared-cache on/off is compared. > > I tried to play with different cache sizes - the differences are > marginal, even down to cache size = 5000, I have similar numbers > in Test-1. > > On the bottom line: I have the impression that the major > benefit of shared-cache is the reduced memory requirement > when opening a number of connections to the same DB, and > the different (optional) locking style in that mode. > It does not dramatically affect the speed of fetching data. > > For me not an issue at all, sqlite is doing extremely well anyhow, > but it is worthwhile to understand why. > > I'm not able to attach my c-code of the test program, but if > one is interested to take a look, let me know. Maybe I made > a mistake or the test procedure is not reasonable, who knows... > > Marcus > > > Luke Evans wrote: >> Well, I guess this is looking more an more like a bug then. >> >> I just went to the sqlite.org site to find out how I log a bug and it >> referred me back to here. Perhaps this means I just need to *DECLARE BUG* >> here ;-) >> Actually, I'll probably have some time soon to try out a profiler to see if >> I can figure out which mutexes are involved in causing the synchonising >> behaviour I seem to be experiencing. >> >> >> >>> I'm out of ideas about this. I understand that it shouldn't work this >>> way and I don't understand why it does. >>> Is it possible for you to run application in some sort of profiler? >>> Obviously it should show that a lot of time is spent in waiting on >>> some mutex and it will be able to show where this mutex is held >>> from... &g
Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]
Well, I guess this is looking more an more like a bug then.
I just went to the sqlite.org site to find out how I log a bug and it referred
me back to here. Perhaps this means I just need to *DECLARE BUG* here ;-)
Actually, I'll probably have some time soon to try out a profiler to see if I
can figure out which mutexes are involved in causing the synchonising behaviour
I seem to be experiencing.
>
> I'm out of ideas about this. I understand that it shouldn't work this
> way and I don't understand why it does.
> Is it possible for you to run application in some sort of profiler?
> Obviously it should show that a lot of time is spent in waiting on
> some mutex and it will be able to show where this mutex is held
> from...
>
>
> Pavel
>
> On Tue, Mar 9, 2010 at 1:02 PM, Luke Evans <luk...@me.com> wrote:
>> Hi guys,
>>
>> Had to take a break for a couple of days from my SQLite experiments, but
>> back on it now.
>>
>> Pavel, regarding the question about VFS, I'm not using one to my knowledge
>> and have set the "name of VFS module" to NULL in sqlite3_open_v2. Maybe
>> NULL means I'm using the standard VFS, but in any case, not a "non-standard"
>> one.
>> I'm selecting from a real table.
>>
>> Here are some more timings...
>>
>> Shared cache ON. 6 of the same query issued (as before) within the
>> multithreaded scenario. All the queries bunch up and report complete more
>> or less together.
>>
>> 2010-03-09 09:32:33.013 SQLiteTest[16208:a0f] SQLite says multithreading is
>> ON
>> 2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SQLite's multithreading value
>> is 2
>> 2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SHARED CACHE IS ON
>> 2010-03-09 09:32:33.017 SQLiteTest[16208:1b03] Starting query on thread
>> {name = (null), num = 3}
>> 2010-03-09 09:32:33.018 SQLiteTest[16208:5603] Starting query on thread
>> {name = (null), num = 4}
>> 2010-03-09 09:32:33.017 SQLiteTest[16208:4403] Starting query on thread
>> {name = (null), num = 2}
>> 2010-03-09 09:32:33.018 SQLiteTest[16208:5503] Starting query on thread
>> {name = (null), num = 5}
>> 2010-03-09 09:32:33.019 SQLiteTest[16208:5703] Starting query on thread
>> {name = (null), num = 6}
>> 2010-03-09 09:32:33.019 SQLiteTest[16208:5c03] Starting query on thread
>> {name = (null), num = 7}
>> 2010-03-09 09:32:41.359 SQLiteTest[16208:5603] Finished query on thread
>> {name = (null), num = 4} in 8.34s
>> 2010-03-09 09:32:41.359 SQLiteTest[16208:1b03] Finished query on thread
>> {name = (null), num = 3} in 8.34s
>> 2010-03-09 09:32:41.359 SQLiteTest[16208:4403] Finished query on thread
>> {name = (null), num = 2} in 8.34s
>> 2010-03-09 09:32:41.359 SQLiteTest[16208:5503] Finished query on thread
>> {name = (null), num = 5} in 8.34s
>> 2010-03-09 09:32:41.360 SQLiteTest[16208:5703] Finished query on thread
>> {name = (null), num = 6} in 8.34s
>> 2010-03-09 09:32:41.360 SQLiteTest[16208:5c03] Finished query on thread
>> {name = (null), num = 7} in 8.34s
>> 2010-03-09 09:32:41.360 SQLiteTest[16208:a0f] Finished all queries in 8.34s
>>
>>
>>
>> 6 of the same query executed in separate processes.
>> The query time is essentially the same as when you issue just a single query
>> in the 'multithreaded' configuration above - as one would expect.
>>
>> 2010-03-09 09:47:02.729 SQLiteTest[17083:a0f] SQLite says multithreading is
>> ON
>> 2010-03-09 09:47:02.731 SQLiteTest[17083:a0f] SQLite's multithreading value
>> is 2
>> SHARED CACHE IS OFFRead uncommitted is 1
>> SHARED CACHE IS OFFRead uncommitted is 1
>> SHARED CACHE IS OFFRead uncommitted is 1
>> SHARED CACHE IS OFFRead uncommitted is 1
>> SHARED CACHE IS OFFRead uncommitted is 1
>> SHARED CACHE IS OFFRead uncommitted is 1
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> Cache size is 10
>> Page size is 32768
>> Temp store is 2
>> Synchronous is 0
>> On pid 17089, 3717 rows read in approx 2s
>> On pid 17088, 3717 rows read in approx 2s
>> On pid 17093, 3717 rows read in approx 2s
>> On pid 17090, 3717 rows read in approx 2s
>> On pid 17091, 3717 rows read in approx 2s
>> On pid 17092, 3717 rows read in approx 2s
>> 2010-03-09 09:47:04.323 SQLiteTest[17083:a0f] Finished all queries in 1.59s
>>
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses)
Hi guys,
Had to take a break for a couple of days from my SQLite experiments, but back
on it now.
Pavel, regarding the question about VFS, I'm not using one to my knowledge and
have set the "name of VFS module" to NULL in sqlite3_open_v2. Maybe NULL means
I'm using the standard VFS, but in any case, not a "non-standard" one.
I'm selecting from a real table.
Here are some more timings...
Shared cache ON. 6 of the same query issued (as before) within the
multithreaded scenario. All the queries bunch up and report complete more or
less together.
2010-03-09 09:32:33.013 SQLiteTest[16208:a0f] SQLite says multithreading is ON
2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SQLite's multithreading value is 2
2010-03-09 09:32:33.015 SQLiteTest[16208:a0f] SHARED CACHE IS ON
2010-03-09 09:32:33.017 SQLiteTest[16208:1b03] Starting query on thread
{name = (null), num = 3}
2010-03-09 09:32:33.018 SQLiteTest[16208:5603] Starting query on thread
{name = (null), num = 4}
2010-03-09 09:32:33.017 SQLiteTest[16208:4403] Starting query on thread
{name = (null), num = 2}
2010-03-09 09:32:33.018 SQLiteTest[16208:5503] Starting query on thread
{name = (null), num = 5}
2010-03-09 09:32:33.019 SQLiteTest[16208:5703] Starting query on thread
{name = (null), num = 6}
2010-03-09 09:32:33.019 SQLiteTest[16208:5c03] Starting query on thread
{name = (null), num = 7}
2010-03-09 09:32:41.359 SQLiteTest[16208:5603] Finished query on thread
{name = (null), num = 4} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:1b03] Finished query on thread
{name = (null), num = 3} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:4403] Finished query on thread
{name = (null), num = 2} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:5503] Finished query on thread
{name = (null), num = 5} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5703] Finished query on thread
{name = (null), num = 6} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5c03] Finished query on thread
{name = (null), num = 7} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:a0f] Finished all queries in 8.34s
6 of the same query executed in separate processes.
The query time is essentially the same as when you issue just a single query in
the 'multithreaded' configuration above - as one would expect.
2010-03-09 09:47:02.729 SQLiteTest[17083:a0f] SQLite says multithreading is ON
2010-03-09 09:47:02.731 SQLiteTest[17083:a0f] SQLite's multithreading value is 2
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
SHARED CACHE IS OFFRead uncommitted is 1
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
On pid 17089, 3717 rows read in approx 2s
On pid 17088, 3717 rows read in approx 2s
On pid 17093, 3717 rows read in approx 2s
On pid 17090, 3717 rows read in approx 2s
On pid 17091, 3717 rows read in approx 2s
On pid 17092, 3717 rows read in approx 2s
2010-03-09 09:47:04.323 SQLiteTest[17083:a0f] Finished all queries in 1.59s
Same as above (6 queries in separate processes), but now each process has
SHARED CACHE=ON.
There's no significant difference in timing (within normal variance).
22010-03-09 09:49:41.217 SQLiteTest[17272:a0f] SQLite says multithreading is ON
2010-03-09 09:49:41.219 SQLiteTest[17272:a0f] SQLite's multithreading value is 2
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
SHARED CACHE IS ONRead uncommitted is 1
Cache size is 10
Cache size is 10
Page size is 32768
Page size is 32768
Temp store is 2
Temp store is 2
Synchronous is 0
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 10
Page size is 32768
Temp store is 2
Synchronous is 0
On pid 17278, 3717 rows read in approx 1s
On pid 17281, 3717 rows read in approx 1s
On pid 17280, 3717 rows read in approx 1s
On pid 17277, 3717 rows read in approx 1s
On pid 17276, 3717 rows read in approx 1s
On pid 17279, 3717 rows read in approx 1s
2010-03-09 09:49:42.697 SQLiteTest[17272:a0f] Finished all queries in 1.48s
On 2010-03-05, at 12:45 PM, Pavel Ivanov wrote:
> Just a bit of thought here: if opening was at fault then 5 queries
> started at the same time would finish in different times (first open
>
Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)
Thanks very much for your notes Olaf.
I've done as you suggest... I already had SQLITE_THREADSAFE=2 defined, but
didn't have the SQLITE_OMIT_SHARED_CACHE asserted (assuming private cache to be
the default).
The Objective-C method that each thread calls to do the actual querying
(bracketed only by timing logic) is:
/*!
* Get the array of all results of the agents query
*/
- (NSArray *)agentDetails {
sqlite3_stmt *statement;
NSMutableArray *rowsArray = nil;
// TTT
sqlite3 *database;
if (sqlite3_open_v2([DBFILE UTF8String], , 0, NULL) !=
SQLITE_OK)
NSAssert1(0, @"Failed to open database with message '%s'.",
sqlite3_errmsg(database));
if (sqlite3_prepare_v2(database, [AGENT_DETAILS_SQL UTF8String], -1,
, NULL) == SQLITE_OK) {
rowsArray = [NSMutableArray arrayWithCapacity:1000];
while (sqlite3_step(statement) == SQLITE_ROW) {
NSString *login =
stringOrEmpty(sqlite3_column_text(statement, 0));
NSString *datasetID =
stringOrEmpty(sqlite3_column_text(statement, 1));
NSString *fullName =
stringOrEmpty(sqlite3_column_text(statement, 2));
NSString *lastName =
stringOrEmpty(sqlite3_column_text(statement, 3));
NSString *firstName =
stringOrEmpty(sqlite3_column_text(statement, 4));
// Make a row dictionary
NSDictionary *rowDict = [NSDictionary
dictionaryWithObjectsAndKeys:
login,
@"login",
datasetID, @"datasetID",
fullName, @"fullName",
lastName, @"lastName",
firstName, @"firstName",
nil];
[rowsArray addObject:rowDict];
}
} else {
NSAssert1(0, @"Failed to run query.\nError message '%s'.",
sqlite3_errmsg(database));
}
// Finish statement
sqlite3_finalize(statement);
// TTT
sqlite3_close(database);
return rowsArray;
}
The results are similar to before:
010-03-05 09:08:57.916 SQLiteTest[76760:a0f] SQLite says multithreading is ON
2010-03-05 09:08:57.918 SQLiteTest[76760:a0f] SQLite's multithreading value is 2
2010-03-05 09:08:57.921 SQLiteTest[76760:5503] Starting query on thread
{name = (null), num = 4}
2010-03-05 09:08:57.921 SQLiteTest[76760:1b03] Starting query on thread
{name = (null), num = 3}
2010-03-05 09:08:57.921 SQLiteTest[76760:5603] Starting query on thread
{name = (null), num = 5}
2010-03-05 09:08:57.921 SQLiteTest[76760:4403] Starting query on thread
{name = (null), num = 2}
2010-03-05 09:08:57.921 SQLiteTest[76760:5703] Starting query on thread
{name = (null), num = 6}
2010-03-05 09:09:08.561 SQLiteTest[76760:5803] Starting query on thread
{name = (null), num = 7}
2010-03-05 09:09:08.619 SQLiteTest[76760:5603] Finished query on thread
{name = (null), num = 5} in 10.70s
2010-03-05 09:09:08.620 SQLiteTest[76760:1b03] Finished query on thread
{name = (null), num = 3} in 10.70s
2010-03-05 09:09:08.620 SQLiteTest[76760:4403] Finished query on thread
{name = (null), num = 2} in 10.70s
2010-03-05 09:09:08.620 SQLiteTest[76760:5503] Finished query on thread
{name = (null), num = 4} in 10.70s
2010-03-05 09:09:08.620 SQLiteTest[76760:5703] Finished query on thread
{name = (null), num = 6} in 10.70s
2010-03-05 09:09:10.446 SQLiteTest[76760:5803] Finished query on thread
{name = (null), num = 7} in 1.88s
2010-03-05 09:09:10.449 SQLiteTest[76760:a0f] Finished all queries in 12.53s
I'm not familiar with the details of your last paragraph, specifically "dynamic
access-mode-switches", but at some cost of time I could try a similar
experiment on Linux to see if the behaviour differs. As noted, this is
currently all running on Mac OS X 10.6 (Snow Leopard).
Perhaps there are few people trying to get truly concurrent querying across
multiple threads in this manner, but naturally I'd be interested to hear from
any others running on the Mac who might have some related experience.
-- Luke
On 2010-03-05, at 3:50 AM, Olaf Schmidt wrote:
>
> "Luke Evans" <luk...@me.com> schrieb im Newsbeitrag
> news:3be16206-d0c6-4041-a3a6-ca3c069ee...@me.com...
>
>> It's Objective-C, but all the SQLite interfacing bits are pure C
>> directly driving the SQLite API compiled into t
Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)
It's Objective-C, but all the SQLite interfacing bits are pure C directly driving the SQLite API compiled into the program (3.6.22 amalgamation). On 2010-03-04, at 11:25 AM, Pavel Ivanov wrote: > What language is that? Probably wrapper for this language introduces > its own mutexes. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fastest concurrent read-only performance (+ threads vs processes)
Thanks guys.
Well, I had played with turning off the shared cache, and have just done so
again.
What I'm actually seeing is serialisation (or at least some apparently quite
coarse-grained synchronisation).
Here's some logging output on my Mac. The start/finished messages are logged
by each thread, bracketing the query.
010-03-04 10:41:41.490 SQLiteTest[19641:5603] Starting query on thread
{name = (null), num = 6}
2010-03-04 10:41:41.490 SQLiteTest[19641:420b] Starting query on thread
{name = (null), num = 2}
2010-03-04 10:41:41.490 SQLiteTest[19641:5403] Starting query on thread
{name = (null), num = 4}
2010-03-04 10:41:41.490 SQLiteTest[19641:1b03] Starting query on thread
{name = (null), num = 3}
2010-03-04 10:41:41.490 SQLiteTest[19641:5503] Starting query on thread
{name = (null), num = 5}
2010-03-04 10:41:47.640 SQLiteTest[19641:5703] Starting query on thread
{name = (null), num = 7}
2010-03-04 10:41:52.524 SQLiteTest[19641:420b] Finished query on thread
{name = (null), num = 2} in 10.97s
2010-03-04 10:41:52.524 SQLiteTest[19641:5403] Finished query on thread
{name = (null), num = 4} in 10.97s
2010-03-04 10:41:52.532 SQLiteTest[19641:5503] Finished query on thread
{name = (null), num = 5} in 10.98s
2010-03-04 10:41:52.534 SQLiteTest[19641:5603] Finished query on thread
{name = (null), num = 6} in 10.98s
2010-03-04 10:41:52.534 SQLiteTest[19641:1b03] Finished query on thread
{name = (null), num = 3} in 10.98s
2010-03-04 10:41:53.088 SQLiteTest[19641:5703] Finished query on thread
{name = (null), num = 7} in 5.45s
2010-03-04 10:41:53.089 SQLiteTest[19641:a0f] Finished all queries in 11.60s
With the shared cache turned on, these threads (which are all doing the exact
same query in this test) behave in the same 'serial' way, but the total time is
more like 4.5s.
Is there any other config that could cause this kind of entanglement between
separate threads doing simple reads (albeit with aliases, sorting and
grouping), or is there anything in the database file that might induce this?
Thanks again.
-- Luke
> On 2010-03-04, at 10:24 AM, Pavel Ivanov wrote:
>> The process experiment seems to confirm that very fast independent parallel
>> queries can be made on the same database, but clearly I would like the same
>> behaviour using multiple threads (and probably connections) in the same
>> process. Is this possible with some specific configuration of SQLite?
>
> Do not use shared cache if you want completely independent connections
> in each thread. Otherwise each call to sqlite3_step() will block other
> threads trying to call sqlite3_step() on the same database. Without
> shared cache they will be completely independent and work as if they
> were executed in different processes (including amount of I/O involved
> and amount of memory consumed).
On 2010-03-04, at 10:26 AM, Dan Kennedy wrote:
>> The process experiment seems to confirm that very fast independent
>> parallel queries can be made on the same database, but clearly I
>> would like the same behaviour using multiple threads (and probably
>> connections) in the same process. Is this possible with some
>> specific configuration of SQLite?
>
> Maybe, if you disable shared-cache. Of course then you will
> use more memory, make more read() calls etc.
>
> Each shared-cache has associated with it a mutex. The mutex
> is held for the duration of each sqlite3_step() call on a
> statement handle that accesses that shared-cache.
>
> Dan.
>
> ___
> 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
[sqlite] Fastest concurrent read-only performance (+ threads vs processes)
Hi SQLiters, We're currently investigating SQLite in an application that needs to issue a batch of queries (SELECTs) before doing some work with all the data returned. I have been trying to figure out the fastest way to get the results. Given there are no writes involved, I figured there might be some advantages in having the queries run on separate threads, with a connection each, but with a shared cache and the read uncommitted flag set ON. This works fine, but the cumulative time for all the queries is in the same ballpark as if the queries were serialised, despite having SQLITE_CONFIG_MULTITHREAD set, SQLLITE_CONFIG_MEMSTATUS off, with SQLITE_OPEN_SHAREDCACHE and SQLITE_OPEN_NOMUTEX used on open. So, I'm not sure exactly how to configure (build and runtime) SQLite so it allows database reads to be essentially independent and lock-free. As an experiment, I wrote a simple program to do the same kind of query and ran several of these in parallel as processes (against the same database file). In this case, I'm seeing very parallel behaviour, and all queries complete in just over the time it would normally take for a single one. The process experiment seems to confirm that very fast independent parallel queries can be made on the same database, but clearly I would like the same behaviour using multiple threads (and probably connections) in the same process. Is this possible with some specific configuration of SQLite? Cheers! -- Luke ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
