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
Dan.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
