> 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.
If you pass NULL you use _default_ VFS, not a standard one. When
non-standard VFS is registered it can be set to be default.
> Here are some more timings...
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
> <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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
