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 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
