On 03/29/2013 11:57 AM, Rob Turpin wrote:
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?
Technically I think you could just open the database with an lsm
connection and use it for the lsm_work() and lsm_checkpoint()
calls. But no, it can't really be done using the sqlite4 API.
Perhaps we need some super-easy way to delegate all that to a
background thread like Leveldb does.
Dan.
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
