Do we still get to report bugs? I checked out the fossil repository fossil clone http://www.sqlite.org/src4 sqlite4.fossil
I tried my insert test and ran into a problem. I'm running Redhat 5.7 gcc 4.4.4 This program dies (showing inserts/sec) 123536 113110 110154 107018 105489 100335 100165 100382 100086 99336.9 insert4: src/lsm_shared.c:996: lsmReadlock: Assertion `(((u32)iShmMax-(u32)iShmMin) < (1<<30))' failed. Aborted It's the COMMIT that causes it. If I take the commit out it keeps on running but eventually gets a "Bus error". Shm file is about 2G at that point. And sqlite4 does appear faster than sqlite3...same program but in sqlite3.. 135052 113865 104801 77650 64325.1 56964.1 54297.1 50751.4 49402.3 47852.7 so version 4 is >2X faster at least this far. Version 4 was running at 76K inserts/sec at 14.9M records when it died so it looks very promising on speed. Is shared mem really limited to 32 bits? Both 3 and 4 versions below... =======SQLITE4=========== #include <stdlib.h> #include <string.h> #include <sys/time.h> #include "sqlite4/sqlite4.h" time_t base_seconds; suseconds_t base_useconds; void tic() { struct timeval tv; gettimeofday(&tv,NULL); base_seconds=tv.tv_sec; base_useconds=tv.tv_usec; } // returns time in seconds since tic() was called double toc() { struct timeval tv; gettimeofday(&tv,NULL); double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6; return mark; } void checkrc(sqlite4 *db,int rc,int checkrc,int flag,char *msg,char *str) { if (rc != checkrc) { fprintf(stderr,msg,str); fprintf(stderr,"%s\n",sqlite4_errmsg(db)); if (flag) { // then fatal exit(1); } } } int main(int argc, char *argv[]) { int rc; long i; char *sql,*errmsg=NULL; char *databaseName="data.db"; sqlite4 *db; sqlite4_env *env=NULL; sqlite4_stmt *stmt1,*stmt2; remove(databaseName); //rc = sqlite4_open_v2(databaseName,&db,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE, NULL); rc = sqlite4_open(env,"data.db",&db,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE,NU LL); checkrc(db,SQLITE4_OK,rc,1,"Error opening database '%s': ",databaseName); sql = "create table if not exists t_foo (key binary(16) primary key, value binary(16))"; rc=sqlite4_prepare(db,sql,-1,&stmt1,NULL); checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql); rc=sqlite4_step(stmt1); checkrc(db,SQLITE4_DONE,rc,1,"Error executing statement '%s': ",sql); rc=sqlite4_finalize(stmt1); checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql); rc=sqlite4_exec(db, "PRAGMA journal_mode=WAL",NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error on WAL mode statement '%s': ",sql); rc=sqlite4_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); rc=sqlite4_exec(db, "PRAGMA cache_size=8000",NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); rc=sqlite4_exec(db, "PRAGMA page_size=4096",NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); sql="BEGIN"; rc=sqlite4_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql); sql = "insert or replace into t_foo(key,value) values(?,?)"; rc=sqlite4_prepare(db,sql,-1,&stmt2,NULL); checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql); tic(); for(i=0; i<50000000; ++i) { char key[16],value[16]; long number = random(); if (i>0 && (i % 100000) == 0) { //printf("%ld,%g \n",i,100000/toc()); printf("%g \n",100000/toc()); fflush(stdout); tic(); } #if 1 // undef to get bus error if (i>0&&(i % 1000000)==0) { sql="COMMIT"; rc=sqlite4_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg); sql="BEGIN"; rc=sqlite4_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg); } #endif memcpy(key,&number,8); memcpy(&key[8],&number,8); memcpy(value,&i,8); rc=sqlite4_bind_blob(stmt2,1,key,16,SQLITE4_STATIC); checkrc(db,SQLITE4_OK,rc,1,"Error bind1 statement '%s': ",sql); rc=sqlite4_bind_blob(stmt2,2,value,16,SQLITE4_STATIC); checkrc(db,SQLITE4_OK,rc,1,"Error bind2 statement '%s': ",sql); rc=sqlite4_step(stmt2); checkrc(db,SQLITE4_DONE,rc,1,"Error finalizing statement '%s': ",sql); rc=sqlite4_reset(stmt2); checkrc(db,SQLITE4_OK,rc,1,"Error resetting statement '%s': ",sql); } return 0; } ======SQLITE3====== #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sys/time.h> #include "sqlite3.h" time_t base_seconds; suseconds_t base_useconds; void tic() { struct timeval tv; gettimeofday(&tv,NULL); base_seconds=tv.tv_sec; base_useconds=tv.tv_usec; } // returns time in seconds since tic() was called double toc() { struct timeval tv; gettimeofday(&tv,NULL); double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6; return mark; } void checkrc(sqlite3 *db,int rc,int checkrc,int flag,char *msg,char *str) { if (rc != checkrc) { fprintf(stderr,msg,str); fprintf(stderr,"%s\n",sqlite3_errmsg(db)); if (flag) { // then fatal exit(1); } } } int main(int argc, char *argv[]) { int rc; long i; char *sql,*errmsg=NULL; char *databaseName="data.db"; sqlite3 *db; sqlite3_stmt *stmt1,*stmt2; remove(databaseName); //rc = sqlite3_open_v2(databaseName,&db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NU LL); rc = sqlite3_open_v2("data.db",&db,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NULL) ; checkrc(db,SQLITE_OK,rc,1,"Error opening database '%s': ",databaseName); sql = "create table if not exists t_foo (key binary(16) primary key, value binary(16))"; rc=sqlite3_prepare_v2(db,sql,-1,&stmt1,NULL); checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql); rc=sqlite3_step(stmt1); checkrc(db,SQLITE_DONE,rc,1,"Error executing statement '%s': ",sql); rc=sqlite3_finalize(stmt1); checkrc(db,SQLITE_OK,rc,1,"Error finalizing statement '%s': ",sql); rc=sqlite3_exec(db, "PRAGMA journal_mode=WAL",NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error on WAL mode statement '%s': ",sql); rc=sqlite3_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); rc=sqlite3_exec(db, "PRAGMA cache_size=8000",NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); rc=sqlite3_exec(db, "PRAGMA page_size=4096",NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s': ",sql); sql="BEGIN"; rc=sqlite3_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql); sql = "insert or replace into t_foo(key,value) values(?,?)"; rc=sqlite3_prepare_v2(db,sql,-1,&stmt2,NULL); checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql); tic(); for(i=0; i<50000000; ++i) { char key[16],value[16]; long number = random(); if (i>0 && (i % 100000) == 0) { //printf("%ld,%g \n",i,100000/toc()); printf("%g \n",100000/toc()); fflush(stdout); tic(); } #if 1 if (i>0&&(i % 1000000)==0) { sql="COMMIT"; rc=sqlite3_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg); sql="BEGIN"; rc=sqlite3_exec(db,sql,NULL,NULL,&errmsg); checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg); } #endif memcpy(key,&number,8); memcpy(&key[8],&number,8); memcpy(value,&i,8); rc=sqlite3_bind_blob(stmt2,1,key,16,SQLITE_STATIC); checkrc(db,SQLITE_OK,rc,1,"Error bind1 statement '%s': ",sql); rc=sqlite3_bind_blob(stmt2,2,value,16,SQLITE_STATIC); checkrc(db,SQLITE_OK,rc,1,"Error bind2 statement '%s': ",sql); rc=sqlite3_step(stmt2); checkrc(db,SQLITE_DONE,rc,1,"Error finalizing statement '%s': ",sql); rc=sqlite3_reset(stmt2); checkrc(db,SQLITE_OK,rc,1,"Error resetting statement '%s': ",sql); } return 0; } -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, December 31, 2012 7:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite4 Performance On 31 Dec 2012, at 4:03am, Cory Isaacson <cory.isaac...@compuflex.com> wrote: > We are doing some basic tests of SQLite4, and the performance seems slow. We > also tested LSM directly and got about the same results. The test is using > an INSERT of two integers, or 2 strings in LSM. It starts at around 9000 > INSERTs/second, then degrades to around 5000 INSERTS/second. > > Both tests were also tested with bigger transaction windows (100, 1000 rows > per transaction), with the same performance. Can you try the same code on the same platform with SQLite3 and let us compare the timings between the two engines ? By the way, I don't know how you downloaded SQLite4 but in case it didn't make it clear, SQLite4 is still under development and not yet considered a finished product. 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