John,
I've tried the following test program (in pseudo code):
for(i = 0; i < 100000; i++){
insert row with new primary key;
rowid[i] = last_row_id();
}
for(;;){
for(i = 0; i < 100000; i++){
r = random(100000);
delete from table where id = rowid[r];
insert row;
rowid[r] = last_row_id();
}
print wall and CPU time, and file size
}
The program also issues a "COMMIT" and BEGIN every thousand iterations
through the loop;
The result is was the following:
update: 341.043948 user: 12.565154 sys: 24.393575 (85942272 bytes)
update: 384.651467 user: 13.385425 sys: 26.384125 (86450176 bytes)
update: 394.730470 user: 13.461950 sys: 26.564895 (86810624 bytes)
update: 402.420568 user: 13.602438 sys: 26.445388 (86810624 bytes)
update: 397.040290 user: 13.890028 sys: 26.192591 (86810624 bytes)
update: 377.765721 user: 13.615615 sys: 26.526867 (86810624 bytes)
update: 388.036631 user: 13.482629 sys: 26.585133 (87326720 bytes)
The number at the end is the file size.
The test program keeps the table size constant, and the CPU time does
not seem to increase like yours does. From the looks of it the program
is only using about 10% of CPU time, so it's likely waiting from disk
I/O, which points to file system cache trashing.
When the table is filled initially all the btree pages are packed as
densely as possible. After a number of updates this will get a little
worse, so you would expect the file to grow a bit further, and the
updates to get a little slower. As you can see though I could not
reproduce your results.
Make sure that your table does not grow indefinitely, because btrees
have O(log N) search and update complexity, and your graph looks
suspiciously like a logarithmic curve.
Gé
John Ruttenberg wrote:
>I have a benchmark program that demonstrates significant performace
>degradation over time. Here is what this benchmark does:
>
> 1. Create a table with a primary integer key and a blob value.
> 2. Populate the table with 1M rows. In all cases the blobs are strings
> with random lengths between 1 and 1k bytes.
> 3. Loop foreer, making passes. A pass does 1M operations each of which
> is randomly selected and can be either:
> a. an insert of an existing row, chosen at random, or
> b. an assert of a new row as in 2.
> c. Operations are grouped with BEGIN and COMMIT into batches of 1k.
>
>Here are plots of time for each pass on a modern linux machine (fc3, >3GHz
>processor, ide drives), and a modern XP machine (similar hardware).
>
> http://rutt.smugmug.com/photos/23341774-O.jpg
>
>Both show an immediate and steep degradation. Fine, it would be great to be
>able to sustain 52 microseconds/operation, but even 10x that is darn good.
>The disturbing thing is that the performance doesn't seem to have stabalized,
>but continues to drif upward slowly.
>
>This is using sqlite-3.1.3. I have attached my benchmark program. If you
>want to run:
>
> 1. Create the table with the sqlite3 command line:
>
> sqlite3
> create table counters (inx integer primary key, value blob);
>
> 2. Run with:
>
> dftest5 1000000
>
>It will run forever, printing times per pass.
>
>
>
--
Ge' Weijers
e-mail: [EMAIL PROTECTED]
tel: (520)623-8542
