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
<NSThread: 0x10061e450>{name = (null), num = 3}
2010-03-09 09:32:33.018 SQLiteTest[16208:5603] Starting query on thread
<NSThread: 0x117e071c0>{name = (null), num = 4}
2010-03-09 09:32:33.017 SQLiteTest[16208:4403] Starting query on thread
<NSThread: 0x117e06760>{name = (null), num = 2}
2010-03-09 09:32:33.018 SQLiteTest[16208:5503] Starting query on thread
<NSThread: 0x10061ea70>{name = (null), num = 5}
2010-03-09 09:32:33.019 SQLiteTest[16208:5703] Starting query on thread
<NSThread: 0x10061e5a0>{name = (null), num = 6}
2010-03-09 09:32:33.019 SQLiteTest[16208:5c03] Starting query on thread
<NSThread: 0x10061e680>{name = (null), num = 7}
2010-03-09 09:32:41.359 SQLiteTest[16208:5603] Finished query on thread
<NSThread: 0x117e071c0>{name = (null), num = 4} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:1b03] Finished query on thread
<NSThread: 0x10061e450>{name = (null), num = 3} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:4403] Finished query on thread
<NSThread: 0x117e06760>{name = (null), num = 2} in 8.34s
2010-03-09 09:32:41.359 SQLiteTest[16208:5503] Finished query on thread
<NSThread: 0x10061ea70>{name = (null), num = 5} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5703] Finished query on thread
<NSThread: 0x10061e5a0>{name = (null), num = 6} in 8.34s
2010-03-09 09:32:41.360 SQLiteTest[16208:5c03] Finished query on thread
<NSThread: 0x10061e680>{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 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
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 100000
Cache size is 100000
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 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
Page size is 32768
Temp store is 2
Synchronous is 0
Cache size is 100000
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
> executes, then while first query executes second open can be executed,
> then first query already executed while second is still in works etc).
> So blocking delays are coming from somewhere inside and they should be
> evenly spread over opening or execution of the query so that it could
> guarantee nearly simultaneous finish of all 5 queries. The only things
> that I can think now:
> - I/O: either file system or VFS inside SQLite is implemented so that
> every call to it acquires some global mutex.
> - if query is actually made over the same virtual table and virtual
> table implementation again acquires some global mutex for each call to
> its functions.
>
> Luke, does any of these applies to your situation? Do you register any
> non-standard VFS in SQLite? Do you select from real table or virtual
> one? Could you also post timings with shared cache turned on and when
> the same query is executed from different processes?
>
> Pavel
>
> On Fri, Mar 5, 2010 at 3:25 PM, Olaf Schmidt <s...@online.de> wrote:
>>
>> "Pavel Ivanov" <paiva...@gmail.com> schrieb im
>> Newsbeitrag
>> news:f3d9d2131003051131k23c7b61cueda0bcc72e6aa...@mail.gmail.com...
>>
>> Oops, pressed send unintentionally...
>>
>>>> Long story short - I suspect the open-call, to be the "blocker"
>>>> in your "SharedCache-Mode=Off"-scenario.
>>
>>> If database file is pretty large, query reads a lot of data while
>>> executing and all data read fit into database cache configured
>>> then I think I/O will be the main difference between "with-shared-
>>> cache" and "without-shared-cache" scenarios.
>>
>> The rest of your reply was probably meant for Luke,
>> but the statement above is from my post, so ...
>>
>> From his timing-results Luke clearly reports blocking
>> behaviour with Shared-Cache=Off (although the blocking
>> should only be reasonable with Shared-Cache=On) ...
>> So, since everybody seems to agree about, that without
>> Shared-Cache the blocking on sqlite3_step() should
>> *not* happen - I suspect the open-calls (in each threaded
>> request) to be the culprit.
>>
>> So, I don't mean my post with regards to the additional
>> overhead from the open-call, doing its "real work" (schema-
>> info-parsing and stuff) - instead from the results Luke has posted,
>> I'd think, that there's an active mutex in the open-sequence-
>> actions of SQLite, which prevents the parallelism.
>>
>> Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
