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 1500000 rows, a child table has 20 * 1500000 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 = 100000
a) 80000 queries, distributed over 8 threads: 4.6 sec
b) 80000 queries, distributed over 4 threads: 5.8 sec
c) 80000 queries, single thread: 6.3 sec
Test-2: shared cache off, cache size = 100000
a) 80000 queries, distributed over 8 threads: 5.6 sec
b) 80000 queries, distributed over 4 threads: 6.0 sec
c) 80000 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...
>>
>>
>> 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
>>>
>>>
>
> _______________________________________________
> 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
