Re: [sqlite] SQLite4 Performance

[Michael Black]

Thanks...progress...now we get a SIGBUS later on...

Program received signal SIGBUS, Bus error.
0x0046ce4c in treeShmalloc (pDb=0x6c9728, bAlign=0, nByte=25,
pRc=0x7fffd784) at src/lsm_tree.c:668
668 pNext->iNext = 0;

pRc looks suspiciously like it's ready to overflow

#include 
#include 
#include 
#include 
#include "sqlite4/sqlite4.h"

#define NRECORDS 5000

time_t base_seconds;
suseconds_t base_useconds;

double tic() {
  struct timeval tv;
  double f;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
  f= base_seconds + base_useconds/1.0e6;
  return f;
}

// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,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;
  double startTime,stopTime;
  char *sql,*errmsg=NULL;
  char *databaseName="data.db";
  sqlite4 *db;
  sqlite4_env *env=NULL;
  sqlite4_stmt *stmt1,*stmt2;
  remove(databaseName);
  rc =
sqlite4_open(env,"data.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))";
  sql = "create table if not exists t_foo (key binary(16) , value
binary(16))";
  rc=sqlite4_prepare(db,sql,-1,,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,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA cache_size=8000",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA page_size=4096",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  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,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  startTime=tic();
  for(i=0; i<=NRECORDS; ++i) {
char key[16],value[16];
long number = random();
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,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);
#if 0
if  (i>0&&(i % 100)==0) {
  sql="COMMIT";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
if (i>0 && (i % 10) == 0) {
  //printf("%ld,%g \n",i,10/toc());
  printf("%g \n",10/toc());
  fflush(stdout);
  tic();
}
  }
  rc=sqlite4_finalize(stmt2);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_close(db);
  checkrc(db,SQLITE4_OK,rc,1,"Error closing database'%s': ",databaseName);
  stopTime=tic();
  printf("avg %.0f inserts/sec\n",NRECORDS/(stopTime-startTime));
  return 0;
}

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 Performance

[Dan Kennedy]

On 01/31/2013 11:13 PM, Michael Black wrote:

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


Thanks for this. Fixed here:

  http://www.sqlite.org/src4/info/bc0f61fc3b

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 Performance

[Dan Kennedy]

On 12/31/2012 11:03 AM, Cory Isaacson 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.

Are there configuration or build options that could be slowing it down?


I hope so. This is what I get on Linux:

  http://www.sqlite.org/src4/doc/trunk/www/lsmperf.wiki

Can you share the benchmark code you were using?

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 Performance

[Michael Black]

econds_t base_useconds;

void tic() {
  struct timeval tv;
  gettimeofday(,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(,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,,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,NU
LL);
  rc =
sqlite3_open_v2("data.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,,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,);
  checkrc(db,SQLITE_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite3_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite3_exec(db, "PRAGMA cache_size=8000",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite3_exec(db, "PRAGMA page_size=4096",NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  sql="BEGIN";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  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,,NULL);
  checkrc(db,SQLITE_OK,rc,1,"Error preparing statement '%s': ",sql);
  tic();
  for(i=0; i<5000; ++i) {
char key[16],value[16];
long number = random();
if (i>0 && (i % 10) == 0) {
  //printf("%ld,%g \n",i,10/toc());
  printf("%g \n",10/toc());
  fflush(stdout);
  tic();
}
#if 1
if  (i>0&&(i % 100)==0) {
  sql="COMMIT";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite3_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,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

Re: [sqlite] SQLite4 Performance

[Simon Slavin]

On 31 Dec 2012, at 4:03am, Cory Isaacson  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] SQLite4 Performance

[Cory Isaacson]

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.

Are there configuration or build options that could be slowing it down?

Also on OS X 10.8, for some reason the sqlite4 command line utility breaks
saying that it cannot open the database. Running from C code works fine
however.

Thanks,

Cory
--



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users