Re: [sqlite] sqlite3 and sqlite4 disk I/O
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 <danielk1...@gmail.com> 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 <danielk1...@gmail.com> >> 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: > > real0m16.190s > user0m0.620s > sys 0m0.552s > > And with sqlite4: > > real0m1.966s > user0m1.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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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
Re: [sqlite] sqlite3 and sqlite4 disk I/O
A table with two columns, both integers, one column primary key. I iterate and insert 100,000 values. Then do 100,000 random updates. On Tue, Mar 26, 2013 at 4:30 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/26/2013 06:34 AM, Rob Turpin wrote: > >> Dan, >> >> I haven't heard anything more about this. I was wondering if you also >> think the CPU performance for sqlite4 should be better? Maybe you could >> help me with where I could focus my attention in looking into this. I >> know >> that's probably a big question to a potentially complex issue, but might >> as >> well ask. >> > > I'm interested to know too. I think I'll try to recreate the test > when I get the chance. How large an initial database are you using? > > Dan. > > > > > > >> Thanks, >> Rob >> >> On Mon, Mar 18, 2013 at 12:20 PM, Rob Turpin <flax3...@gmail.com> wrote: >> >> I understand it's still under development, but was curious about the >>> difference. I have commented out all of the debug defines and enabled >>> -DNDEBUG=1 for the sqlite4 build. >>> >>> >>> On Mon, Mar 18, 2013 at 12:00 PM, Simon Slavin <slav...@bigfraud.org >>> >wrote: >>> >>> >>>> On 18 Mar 2013, at 5:28pm, Rob Turpin <flax3...@gmail.com> wrote: >>>> >>>> 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? >>>>> >>>> >>>> You know that SQLite4 is Not Ready for Primetime, right ? Might the >>>> distribution you're using have a bunch of debugging stuff in ? >>>> >>>> Simon. >>>> __**_ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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<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<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
Re: [sqlite] sqlite3 and sqlite4 disk I/O
Dan, I haven't heard anything more about this. I was wondering if you also think the CPU performance for sqlite4 should be better? Maybe you could help me with where I could focus my attention in looking into this. I know that's probably a big question to a potentially complex issue, but might as well ask. Thanks, Rob On Mon, Mar 18, 2013 at 12:20 PM, Rob Turpin <flax3...@gmail.com> wrote: > I understand it's still under development, but was curious about the > difference. I have commented out all of the debug defines and enabled > -DNDEBUG=1 for the sqlite4 build. > > > On Mon, Mar 18, 2013 at 12:00 PM, Simon Slavin <slav...@bigfraud.org>wrote: > >> >> On 18 Mar 2013, at 5:28pm, Rob Turpin <flax3...@gmail.com> wrote: >> >> > 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? >> >> You know that SQLite4 is Not Ready for Primetime, right ? Might the >> distribution you're using have a bunch of debugging stuff in ? >> >> Simon. >> ___ >> 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
Re: [sqlite] sqlite3 and sqlite4 disk I/O
I understand it's still under development, but was curious about the difference. I have commented out all of the debug defines and enabled -DNDEBUG=1 for the sqlite4 build. On Mon, Mar 18, 2013 at 12:00 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 18 Mar 2013, at 5:28pm, Rob Turpin <flax3...@gmail.com> wrote: > > > 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? > > You know that SQLite4 is Not Ready for Primetime, right ? Might the > distribution you're using have a bunch of debugging stuff in ? > > Simon. > ___ > 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
Re: [sqlite] sqlite3 and sqlite4 disk I/O
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 <danielk1...@gmail.com> 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? >> > > How large are the transactions? > > __**_ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] sqlite3 and sqlite4 disk I/O
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? For both tests pragmas synchronous=NORMAL, and journal_mode=WAL are set. Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] backup api for sqlite4?
So I'm assuming saving in memory databases isn't an option with sqlite4. On Sat, Mar 2, 2013 at 5:58 AM, Richard Hipp <d...@sqlite.org> wrote: > On Sat, Mar 2, 2013 at 5:52 AM, Rob Turpin <flax3...@gmail.com> wrote: > > > The backup API for sqlite3 > > > > sqlite3_backup_init > > sqlite3_backup_step > > sqlite3_backup_finish > > > > I can't find anything similar in sqlite4. Is there any, or plans for it? > > > > There are no pressing plans. It isn't on the to-do list. > > The SQLite3 backup mechanism operates deep down at the page level of the > b-tree storage engine. But SQLite4 does not use a b-tree storage engine, > it uses LSM, so the SQLite3 backup mechanism isn't directly applicable. > Some entirely need implementation method would need to be devised. > > > > > > Thanks, > > Rob > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] backup api for sqlite4?
The backup API for sqlite3 sqlite3_backup_init sqlite3_backup_step sqlite3_backup_finish I can't find anything similar in sqlite4. Is there any, or plans for it? Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite4 UPDATE performance
Richard, I commented out SQLITE4_DEBUG and SQLITE4_MEMDEBUG and added -DNDEBUG=1. That did the trick! More like 224,000 updates per second. Thanks for your help Rob On Wed, Feb 27, 2013 at 5:47 AM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Feb 27, 2013 at 3:24 AM, Rob Turpin <flax3...@gmail.com> wrote: > > > I wrote up a test case to do some performance tests for the update > > statement, and I'd thought I'd ask before probing around the code first. > > > > For SQLite3: 280,000 updates per second > > For SQLite4: 290 updates per second > > > > So why the abysmal drop for the in memory SQLite4? > > > > I ran this in the profiler and saw that the SQLite4 is spending 97.53% of > its time (literally) in a single debugging check inside of the in-memory KV > storage engine: assertUpPointer(). This routine is entirely option, of > course, and is there merely to verify the integrity of the binary tree used > for storage. If you disable that one routine, the in-memory database is > very fast, it seems. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] SQLite4 UPDATE performance
I wrote up a test case to do some performance tests for the update statement, and I'd thought I'd ask before probing around the code first. I did some comparisons with SQLite3. The update statement is like this: update pk_sk set sk=? where pk=? pk being the primary key. Using the command line interpreter and running an explain query plan I get: SQLite3: 0|0|0|SEARCH TABLE pk_sk USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SQLite4: 0|0|0|SEARCH TABLE pk_sk USING PRIMARY KEY (pk=?) (~1 rows) Don't know if that provides anything, thought I'd send it. So a general synopsis of the tests. I'm doing random updates when there are 100,000 rows in a table. For SQLite3 when using a DB on disk I'm getting around 41,000 updates per second For SQLite4 when using a DB on disk I'm getting around 9,200 updates per second For the moment I'm not too worried about this, but it's really weird when I run tests with an in memory DB For SQLite3: 280,000 updates per second For SQLite4: 290 updates per second So why the abysmal drop for the in memory SQLite4? I'm assuming maybe some tracer/debug code I know nothing about. Thought I'd ask about this before I started getting lost in rooting around code and debugging my two versions of tests looking for something. If someone can give a little info. to give me a better focus on where to look, that would be great. Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite4 lsm storage engine
Dan, I'm getting an LSM_OK on lsm_close. I attached the writer and reader test case. If you comment out the lsm_config call that turns off logging, all the writes get in. Thanks, Rob On Wed, Feb 20, 2013 at 9:43 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 02/20/2013 11:37 PM, Rob Turpin wrote: > >> Yes. >> > > Is it succeeding? Returning LSM_OK? > > Dan. > > > > >> On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy <danielk1...@gmail.com> >> wrote: >> >> On 02/20/2013 05:07 PM, Rob Turpin wrote: >>> >>> I'm running some performance tests on the lsm storage engine, and an >>>> issue >>>> has cropped up for me. I retrieved the sqlite4 code from the repository >>>> about a week ago. >>>> >>>> I'm doing a simple single threaded test to see what kind of performance >>>> I >>>> can get on write transactions. After completing the write test, I run >>>> another program to read the DB, and all the writes are not there. >>>> >>>> Here's are the config options I set before calling lsm_open. >>>> >>>> int iVal = 0; >>>> lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, ); >>>> lsm_config(db, LSM_CONFIG_USE_LOG, ); >>>> iVal = 4096; >>>> lsm_config(db, LSM_CONFIG_AUTOFLUSH, ); >>>> iVal = 8192; >>>> lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, ); >>>> >>>> >>>> If logging is on, there is no issue, all writes are in the DB. But >>>> since >>>> I'm trying to increase performance I turn this off. >>>> >>>> From reading the documentation I was assuming the >>>> LSM_CONFIG_AUTOCHECKPOINT >>>> setting should sync the writes in memory to disk at 8MB (per my setting, >>>> default is 2 MB). I'm performing no explicit commits or checkpoints. >>>> I'm >>>> simply calling lsm_insert to write data to the DB. Perhaps this is >>>> where >>>> I'm confused about the expected behavior. >>>> >>>> Could anyone correct me where I'm wrong on my assumptions, or could >>>> there >>>> be an issue with this? >>>> >>>> >>> Are you calling lsm_close() at the end of the write test? >>> >>> Dan. >>> >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users> >>> <http://sqlite.org:8080/**cgi-bin/mailman/listinfo/**sqlite-users<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<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<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
Re: [sqlite] sqlite4 lsm storage engine
Yes. On Wed, Feb 20, 2013 at 3:14 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 02/20/2013 05:07 PM, Rob Turpin wrote: > >> I'm running some performance tests on the lsm storage engine, and an issue >> has cropped up for me. I retrieved the sqlite4 code from the repository >> about a week ago. >> >> I'm doing a simple single threaded test to see what kind of performance I >> can get on write transactions. After completing the write test, I run >> another program to read the DB, and all the writes are not there. >> >> Here's are the config options I set before calling lsm_open. >> >> int iVal = 0; >> lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, ); >> lsm_config(db, LSM_CONFIG_USE_LOG, ); >> iVal = 4096; >> lsm_config(db, LSM_CONFIG_AUTOFLUSH, ); >> iVal = 8192; >> lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, ); >> >> >> If logging is on, there is no issue, all writes are in the DB. But since >> I'm trying to increase performance I turn this off. >> >> From reading the documentation I was assuming the >> LSM_CONFIG_AUTOCHECKPOINT >> setting should sync the writes in memory to disk at 8MB (per my setting, >> default is 2 MB). I'm performing no explicit commits or checkpoints. I'm >> simply calling lsm_insert to write data to the DB. Perhaps this is where >> I'm confused about the expected behavior. >> >> Could anyone correct me where I'm wrong on my assumptions, or could there >> be an issue with this? >> > > Are you calling lsm_close() at the end of the write test? > > Dan. > > > > > __**_ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<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] sqlite4 lsm storage engine
I'm running some performance tests on the lsm storage engine, and an issue has cropped up for me. I retrieved the sqlite4 code from the repository about a week ago. I'm doing a simple single threaded test to see what kind of performance I can get on write transactions. After completing the write test, I run another program to read the DB, and all the writes are not there. Here's are the config options I set before calling lsm_open. int iVal = 0; lsm_config(db, LSM_CONFIG_MULTIPLE_PROCESSES, ); lsm_config(db, LSM_CONFIG_USE_LOG, ); iVal = 4096; lsm_config(db, LSM_CONFIG_AUTOFLUSH, ); iVal = 8192; lsm_config(db, LSM_CONFIG_AUTOCHECKPOINT, ); If logging is on, there is no issue, all writes are in the DB. But since I'm trying to increase performance I turn this off. >From reading the documentation I was assuming the LSM_CONFIG_AUTOCHECKPOINT setting should sync the writes in memory to disk at 8MB (per my setting, default is 2 MB). I'm performing no explicit commits or checkpoints. I'm simply calling lsm_insert to write data to the DB. Perhaps this is where I'm confused about the expected behavior. Could anyone correct me where I'm wrong on my assumptions, or could there be an issue with this? Thanks, Rob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
