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 [sqlite-users-boun...@sqlite.org] on behalf of Sebastian Krysmanski [sql...@lists.manski.net] Sent: Thursday, September 20, 2012 10:25 AM To: 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 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
