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
