Well, I did some profiling and here are the results, if you're interested. Again, 2,000,000 SELECT queries. This time I used only shared connections (i.e. all threads share the same connection).
With 2 threads: Overall hot path: http://imgur.com/DHXQj In thread: http://imgur.com/KX8KY With 20 threads: Overall hot path: http://imgur.com/s28Hs In thread: http://imgur.com/8NBB6 On Thursday, 20. September 2012 at 17:39, Black, Michael (IS) wrote: > What's your threading mode? > http://www.sqlite.org/threadsafe.html > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Advanced GEOINT Solutions Operating Unit > Northrop Grumman Information Systems > > ________________________________________ > From: sqlite-users-boun...@sqlite.org > (mailto:sqlite-users-boun...@sqlite.org) [sqlite-users-boun...@sqlite.org > (mailto:sqlite-users-boun...@sqlite.org)] on behalf of Sebastian Krysmanski > [sql...@lists.manski.net (mailto:sql...@lists.manski.net)] > Sent: Thursday, September 20, 2012 10:25 AM > To: sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > Subject: EXT :Re: [sqlite] Store error messages in thread local memory > > It's the whole process including creating threads, opening database > connections and waiting for the threads to finish. However, startup time is > negligible (as expected). Here are some results where opening and closing of > connections as well as compiling statements is excluded from the elapsed time: > > ------------------------------------------------------------------ > SELECT_COUNT: 1,000,000 > THREAD_COUNT: 2 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 91.0 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 66.3 s > > > ------------------------------------------------------------------ > SELECT_COUNT: 133,333 > THREAD_COUNT: 15 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.6 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 51.6 s > > > ------------------------------------------------------------------ > SELECT_COUNT: 20,000 > THREAD_COUNT: 100 > Testing with one connections (ReadWrite) and filled table... > Elapsed: 11.5 s > Testing with one connections (ReadWrite) and filled table... > Elapsed: 55.9 s > > > On Thursday, 20. September 2012 at 16:22, Teg wrote: > > > Hello Sebastian, > > > > Is this total time or time just of the DB access? I'm wondering how > > much of this is just "opening the connection" overhead time versus > > query time. Assuming the overhead of creating 100 threads is the same. > > > > I'm be interested in knowing how long it takes assuming you don't > > start timing it till after all 100 threads have opened the connections > > to the file. > > > > Wonder if running this same test 100 times in a row for each mode, > > leaving the connections open in between, would show the timing's > > converging? Basically reducing the affect of the startup overhead. > > > > > > C > > > > Thursday, September 20, 2012, 9:46:07 AM, you wrote: > > > > SK> I tested with a database containing one table with 50,000 entries. > > > > SK> I then ran "SELECT *" on this table from 100 concurrent threads > > SK> where each thread randomly selected 20,000 table entries. > > > > SK> The results are: > > > > SK> * using a single connection for all threads: 11 seconds > > SK> * using one connection per thread: 59,3 seconds > > > > > > SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote: > > > > > > You don't say how much speed difference you see.... > > > > > > > > But a separate connection will have separate caches. So you could just > > > > be seeing a difference in caching behavior. > > > > > > > > One connection uses one cache so will be in L1/L2/L3 cache more often > > > > than multiple threads thrashing the cache. > > > > > > > > http://www.tomshardware.com/forum/266376-28-intel-cache > > > > > > > > > > > > Michael D. Black > > > > Senior Scientist > > > > Advanced Analytics Directorate > > > > Advanced GEOINT Solutions Operating Unit > > > > Northrop Grumman Information Systems > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thursday, 20. September 2012 at 15:23, Richard Hipp wrote: > > > > > > > > > > > > > On Thu, Sep 20, 2012 at 9:12 AM, Sebastian Krysmanski < > > > > > > > sql...@lists.manski.net (mailto:sql...@lists.manski.net)> wrote: > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > > > I'm trying to use SQLite in a multi-threaded application. I've > > > > > > > > done > > > > > > some > > > > > > > > tests and it seems that using the same connection on multiple > > > > > > > > threads > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > is > > > > > > > > faster than having one connection per thread. > > > > > > > > > > > > > > > > However, http://www.sqlite.org/c3ref/errcode.html states: > > > > > > > > > > > > > > > > "When the serialized threading mode ( > > > > > > http://www.sqlite.org/threadsafe.html) > > > > > > > > is in use, it might be the case that a second error occurs on a > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > separate > > > > > > > > thread in between the time of the first error and the call to > > > > > > > > these > > > > > > > > interfaces. When that happens, the second error will be > > > > > > > > reported since > > > > > > > > these interfaces always report the most recent result." > > > > > > > > > > > > > > > > So, this is a problem in my application (and I definitely need > > > > > > > > multi > > > > > > > > threading). > > > > > > > > > > > > > > > > Obtaining an exclusive lock for the database connection, as > > > > > > > > suggested > > > > > > in > > > > > > > > the documentation, is not an option for me because even read > > > > > > > > only > > > > > > > > statements (SELECT) can potentially return an error. And > > > > > > > > obtaining an > > > > > > > > exclusive lock for a read statement eliminates all concurrency > > > > > > > > there > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > is in > > > > > > > > SQLite. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Every operation on an SQLite database connection operates under an > > > > > > > exclusive mutex on that database connection, so you don't have any > > > > > > > concurrency anyhow. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > So the only solution I can come up with is to make > > > > > > > > "sqlite3_errmsg()" > > > > > > (and > > > > > > > > related functions) use thread local memory. > > > > > > > > > > > > > > > > Is there (or has there ever been made) any attempt on storing > > > > > > > > the error > > > > > > > > message in thread local memory? (I'm a C# and Java developer, > > > > > > > > so I'm > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > not > > > > > > > > sure whether thread local memory even exists in C. It does in > > > > > > > > C# and > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Java > > > > > > > > though.) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Thread local storage has been available to C code since long > > > > > > > before Java > > > > > > > and C# were even invented. But it is accessed through library > > > > > > > routines > > > > > > > that are not cross-platform, so we are not interested in using it > > > > > > > in > > > > > > > SQLite. Furthermore, making such a change would break backwards > > > > > > > compatibility, which is a huge no-no with SQLite. > > > > > > > > > > > > > > Best regards > > > > > > > > Sebastian > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > > > sqlite-users mailing list > > > > > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > D. Richard Hipp > > > > > > > d...@sqlite.org (mailto:d...@sqlite.org) > > > > > > > _______________________________________________ > > > > > > > sqlite-users mailing list > > > > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > > > sqlite-users mailing list > > > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > D. Richard Hipp > > > > > d...@sqlite.org (mailto:d...@sqlite.org) > > > > > _______________________________________________ > > > > > sqlite-users mailing list > > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > > > > sqlite-users mailing list > > > > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > > > > > > > SK> _______________________________________________ > > SK> sqlite-users mailing list > > SK> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > > SK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > -- > > Best regards, > > Teg mailto:t...@djii.com > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org) > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org (mailto: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
