> With SQLITE_THREADSAFE=2 > with SQLITE_THREADSAFE=1 With 2 threads and THREADSAFE=2 I get 167 seconds. With 2 threads and THREADSAFE=1, I get 177 seconds. With 1 thread and THREADSAFE=1, I get 53 seconds. With 1 thread and THREADSAFE=2, I get 52 seconds.
One thing I'm worried about is I have two differing definitions of SQLITE_THREADSAFE when I compile via the command line with "-DSQLITE_THREADSAFE=2", so I'm worried that one is clobbering the other. Here is a clip from the compile log, you can see *both* THREADSAFE=2 and THREADSAFE=1: gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" -DPACKAGE_VERSION=\"3.7.7.1\" "-DPACKAGE_STRING=\"sqlite 3.7.7.1\"" -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\"; -DPACKAGE_URL=\"\" -DPACKAGE=\"sqlite\" -DVERSION=\"3.7.7.1\" -DSTDC_HEADERS=1 -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -DHAVE_READLINE=1 -I.@am__isrc@ -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -O2 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_ENABLE_RTREE -DSQLITE_THREADSAFE=2 -DSQLITE_OPEN_NOMUTEX -DSQLITE_ENABLE_LOCKING_STYLE -DSQLITE_DEFAULT_TEMP_CACHE_SIZE=5000 -DSQLITE_DEFAULT_CACHE_SIZE=20000 -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c -fno-common -DPIC -o .libs/sqlite3.o <command-line>: warning: "SQLITE_THREADSAFE" redefined <command-line>: warning: this is the location of the previous definition > The way you're splitting your work is bad. You can see that your row counts > for 1 thread are 30000 and for 2 threads 45,000 so you're not gaining a lot. > 3 threads is 90,000 so you've tripled your workload totally defeating > multi-threading. When I run with 1 thread, it queries all 99k examples. When I use two threads, each thread query 45k of the examples. Three threads query 33k of the examples each. The total should always be 99k examples, which is what I'm seeing on my machine. Thanks, Seth On Aug 4, 2011, at 6:36 AM, Black, Michael (IS) wrote: > Also.. > > . > > Your elapsed time is using clock() which tells you processor time. > > > > With SQLITE_THREADSAFE=2 > > 59.855 - 1 thread > > 49.535 - 2 threads > > 92.789 - 3 threads > > > > with SQLITE_THREADSAFE=1 > > 61.146 - 1 thread > > 49.568 - 2 threads > > 64.932 - 3 threads > > > > The way you're splitting your work is bad. You can see that your row counts > for 1 thread are 30000 and for 2 threads 45,000 so you're not gaining a lot. > 3 threads is 90,000 so you've tripled your workload totally defeating > multi-threading. > > > > > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > ________________________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Seth Price [s...@pricepages.org] > Sent: Wednesday, August 03, 2011 9:07 PM > To: sqlite-users@sqlite.org > Subject: EXT :[sqlite] Threading makes SQLite 3x slower?? > > Hey all, I have a small DB bound application that I'm working on. I expect > that it will take 10-20 days to run when I'm done with it. So I'm trying to > make it multithreaded. But after spending all afternoon getting threading > going, it runs on the order of 3x slower per query when I go from one to two > threads. > > Is this expected? > > The full DB has around 17 million rows in it, and for each row I am trying to > count all rows with similar characteristics, divided into different > classifications. I was already able to improve speed 10x by using the R*tree > extension to narrow my search. My queries look like this: > > SELECT class, COUNT(*) AS count FROM data_r JOIN data USING (rowNum) WHERE 57 > < col0min AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min > AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min AND > col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 < 56 AND 66 > < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 < col4 AND col4 < > 137 AND 81 < col5 AND col5 < 85 GROUP BY class; > > They take around 0.04 seconds each (times 17 million). I thought that I > should be able to access the DB from each thread without penalty because they > are read-only queries. I also tried making copies of the DB file to access a > different file with each thread (also slower). Oddly, there are no frequent > disk access while I run the program, so there must be caching somewhere. > > How can I make threading work? Download the example set and code here: > http://seth.bluezone.usu.edu/sqlite/ > > There is info on how I compile and run the program in the header. Use > NUM_THREADS to change the number. The stats on which thread is taking how > much time is printed under "Pruning Conflicting Examples..." and the > threading code is under "pruneEx(ExPtr ex)". I'm on a Mac Pro running 10.6.8. > > Thoughts? Comments? Ideas? > > Thanks, > Seth > _______________________________________________ > 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
