> I fixed a couple of bugs in my program...I had converted from clock() to
> the more appropriate gettimeofday and forgot to remove the CLOCKS_PER_SEC
> factor (what's a few order of magnitude between friends :-). Plus I added
> a 3rd argument so you can in-memory, index, and WAL mode too (or combine
> them).
>
> I should've realized it wasn't running this fast but the small 5000 record
> size got me.
> Test it yourself.
> I do have a 7200RPM drive. My 261.4 numer is still 2+X your theoretical.
I don't want to be a smart-arse, but I still think your 261.4 is to fast.
On a 7200 RPM drive one will have 125 chances/sec to see a sector to be
written. Since sqlite, under normal journal mode, will need 3 syncs
per commit as far as I can recall, the maximum number drops further
down to 41 commit/sec. This is theoretical, in reality one will see
maybe 20 commits/sec. Not sure if a disc write-cache will interfere
with that caluclation, though.
Am I wrong ? :-)
Your loop omits the sqlite_reset call between the bind statements:
This is allowed since some sqlite versions but I'm not sure if
you are really resetting the implicit transaction. Just an idea..
Your new code still uses the clocks scaling:
printf("%.1f\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
That looks strange to me as your are probably scaling seconds
down to milliseconds, or something.
Marcus
>
> batch 5000000 5000000 0
> 274207.4
> batch 5000000 5000000 1 -- memory
> 540540.5
> batch 5000000 5000000 2 -- w/index
> 160481.4
> batch 5000000 5000000 3 -- memory+index
> 220689.7
> batch 5000000 5000000 4 -- WAL mode
> 441989.0
> batch 5000000 5000000 5 -- WAL mode+memory
> 541455.2
> batch 5000000 5000000 6 -- WAL mode+index
> 188902.0
> batch 5000000 5000000 7 -- WAL mode+index+memory
> 219478.7
>
> And doing the 5000 record example and testing commit intervals
> batch 5000 5000 0
> 320000.0
> batch 5000 2500 0
> 320000.0
> batch 5000 1200 0
> 160000.0
> batch 5000 500 0
> 160000.0
> batch 5000 200 0
> 80000.0
> batch 5000 100 0
> 35555.6
> batch 5000 500 0
> 160000.0
> batch 5000 50 0
> 20000.0
> batch 5000 25 0
> 11034.5
> batch 5000 12 0
> 5333.3
> batch 5000 6 0
> 2461.5
> batch 5000 3 0
> 682.3
> batch 5000 2 0
> 509.6
> batch 5000 1 0
> 261.4
>
> #include <stdio.h>
> #include <windows.h>
> #include <winbase.h>
> #include <time.h>
> #include "sqlite3.h"
> double elapsed()
> {
> double t1;
> struct timeval tv;
> static long base_seconds;
> gettimeofday(&tv,NULL);
> if (base_seconds==0) base_seconds=tv.tv_sec;
> return (tv.tv_sec-base_seconds)+tv.tv_usec/(double)1000000;
> }
> int main(int argc, char *argv[])
> {
> sqlite3 *db;
> sqlite3_stmt *stmt=NULL;
> int rc;
> int n=0;
> int nrec=0;
> int interval=0;
> int flags=0;
> double t1;
> if (argc != 4) {
> fprintf(stderr,"Usage: %s nrecords commit_interval flags\n",argv[0]);
> fprintf(stderr,"Flag 0 = simple table\n");
> fprintf(stderr,"Flag 1 = in-memory database\n");
> fprintf(stderr,"Flag 2 = add index\n");
> fprintf(stderr,"Flag 4 = WAL mode\n");
> fprintf(stderr,"Add flags to combine features\n");
> exit(-1);
> }
> nrec=atoi(argv[1]);
> interval=atoi(argv[2]);
> flags=atoi(argv[3]);
> if (flags & 1) {
> rc=sqlite3_open(":memory:",&db);
> }
> else {
> remove("batch.db");
> rc=sqlite3_open("batch.db",&db);
> }
> if (rc != SQLITE_OK) {
> puts(sqlite3_errmsg(db));
> }
> sqlite3_exec(db,"create table t (i integer)",NULL,NULL,NULL);
> if (flags & 2) {
> sqlite3_exec(db,"create index tx on t (i)",NULL,NULL,NULL);
> }
> if (flags & 4) {
> sqlite3_exec(db,"pragma journal_mode=WAL",NULL,NULL,NULL);
> }
> sqlite3_prepare_v2(db,"insert into t values(?)",-1,&stmt,NULL);
> t1=elapsed();
> if (interval != 1) rc=sqlite3_exec(db,"begin",NULL,NULL,NULL);
> while(n < nrec) {
> ++n;
> if (interval != 1 && (n% interval)==0) {
> sqlite3_exec(db,"commit",NULL,NULL,NULL);
> sqlite3_exec(db,"begin",NULL,NULL,NULL);
> }
> sqlite3_bind_int(stmt,1,n);
> rc = sqlite3_step(stmt);
> if (rc != SQLITE_DONE) {
> puts(sqlite3_errmsg(db));
> }
> sqlite3_reset(stmt);
> }
> sqlite3_exec(db,"commit",NULL,NULL,NULL);
> //printf("%.1f inserts per sec\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
> printf("%.1f\n",nrec/((elapsed()-t1)/CLOCKS_PER_SEC));
> sqlite3_close(db);
> }
>
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
> ________________________________________
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Jim Wilcoxson [pri...@gmail.com]
> Sent: Saturday, February 12, 2011 10:11 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected
>
> On Sat, Feb 12, 2011 at 9:10 AM, Black, Michael (IS)
> <michael.bla...@ngc.com
>> wrote:
>
>> Here's a little benchmark program I wrote to test a super-simple
>> one-integer insert to test sql speed and commit interval behavior.
>>
>> Running it on my machine (Windows XP64 8-core 3Ghz gcc (tdm64-1) 4.5.1)
>> I
>> go from 320M inserts per second to 361K inserts per second when no
>> begin/commit occurs. With WAL mode turned on it only drops to 5.9M
>> inserts
>> per second.
>>
>> D:\SQLite>batch 5000 5000
>> 320000000.0 inserts per sec
>> D:\SQLite>batch 5000 2500
>> 320000000.0 inserts per sec
>> D:\SQLite>batch 5000 1200
>> 160000000.0 inserts per sec
>> D:\SQLite>batch 5000 600
>> 160000000.0 inserts per sec
>> D:\SQLite>batch 5000 300
>> 106666666.7 inserts per sec
>> D:\SQLite>batch 5000 150
>> 53333333.3 inserts per sec
>> D:\SQLite>batch 5000 75
>> 32000000.0 inserts per sec
>> D:\SQLite>batch 5000 40
>> 17777777.8 inserts per sec
>> D:\SQLite>batch 5000 20
>> 10000000.0 inserts per sec
>> D:\SQLite>batch 5000 10
>> 3333333.3 inserts per sec
>> D:\SQLite>batch 5000 5
>> 2406015.0 inserts per sec
>> D:\SQLite>batch 5000 2
>> 526315.8 inserts per sec
>> D:\SQLite>batch 5000 1
>> 360766.6 inserts per sec
>>
>
> Unless I'm missing something, SQLite has to update the first page of the
> database on every commit, to update the change counter. Assuming you are
> using rotating media, that record can only be updated 120 times per
> second,
> maximum, on a 7200RPM drive.
>
> I don't understand how you can do 360K commits per second if your system
> is
> actually doing "to the platter" writes on every commit. Can someone clue
> me
> in?
>
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> _______________________________________________
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
