Dan, Thanks for looking into this. So I get the notion of explicit scheduling with lsm_work, but as I currently understand the sqlite4 API, explicit scheduling can't be done with a sqlite4 database object. Is that correct?
Rob On Thu, Mar 28, 2013 at 2:02 PM, Dan Kennedy <[email protected]> wrote: > On 03/19/2013 01:18 AM, Rob Turpin wrote: > >> It's a simple table, primary key is an integer, with another column which >> is integer. Although I do begin a transaction, do 1000 updates and then >> commit. >> >> On Mon, Mar 18, 2013 at 11:36 AM, Dan Kennedy <[email protected]> >> wrote: >> >> On 03/19/2013 12:28 AM, Rob Turpin wrote: >>> >>> I was running some performance tests for both sqlite3 and sqlite4 and >>>> have >>>> a question about a difference in my numbers. >>>> >>>> I'm running these on a Linux machine, so I enabled fdatasync for the >>>> sqlite3 build. >>>> >>>> I'm measuring the number of updates I can perform (updates per second), >>>> here are the numbers. >>>> >>>> sqlite3: >>>> Updates (CPU): 156250 >>>> Updates (Clock): 27733.6 >>>> >>>> sqlite4: >>>> Updates (CPU): 46729 >>>> Updates (Clock): 33132.8 >>>> >>>> >>>> With sqlite3 there's a large difference between the CPU time and wall >>>> clock >>>> time. No big deal, that's the I/O to disk. But then I'm wondering why >>>> the >>>> difference with sqlite4 is so small? >>>> >>> > So I tried a test using db schema: > > CREATE TABLE t1(k INTEGER PRIMARY KEY, v INTEGER); > > Initially the db is populated with 100,000 rows. Keys are > contiguous integers starting with 1 and values are pseudo-random > positive integers smaller than 2^32. > > Then run 100,000 statements of the form: > > UPDATE t1 SET v = randomval() WHERE k = ? > > with a BEGIN/COMMIT block around each 1000 UPDATEs. > > With sqlite3, I get: > > real 0m16.190s > user 0m0.620s > sys 0m0.552s > > And with sqlite4: > > real 0m1.966s > user 0m1.424s > sys 0m0.000s > > On a 64-bit Linux host with synchronous=NORMAL and journal_mode=WAL. > sqlite3 built with -Dfdatasync=fdatasync. > > So sqlite4 is using more CPU. But in this particular case is faster > overall because the way the database file is written is more efficient > (i.e. more sequential). It's probably writing a bit less too. > > One way to reduce the (perceived) CPU usage with sqlite4 is to move > segment merges (database work) and database syncs (database checkpoints) > to a background thread or process. Maybe we should have some way to > make doing that easy for applications. More detail in the following, > especially 6.1 and 6.3: > > http://www.sqlite.org/src4/**doc/trunk/www/lsmusr.wiki#** > performance_tuning<http://www.sqlite.org/src4/doc/trunk/www/lsmusr.wiki#performance_tuning> > > Dan. > > > > > ______________________________**_________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
