Hi Luke,
Luke Evans wrote: > 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. ok, please try this: http://www.exomio.de/SqliteSpeedTest.c I havent tried yet to compare the numbers when using one main process rather than one single thread. Would be in fact really strange if that makes any difference. I'll try to do this using my test code soon. In any case: yes, if we all understand share-cache correctly we could assume a nicer effect when using more than one reader thread, but since DRH hates threads anyhow it is unlikely that there will be any change... :-) Marcus > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
