Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Yes, I've been looking into using the prepare/bind/step functions instead.

I'm at a complete loss as to what might have caused the behavior I initially 
saw, since every test I run now runs in constant time.

Thanks for the doc links Dennis, and thanks to everyone for their help and 
suggestions.

Cheers,
JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dennis Cote
Sent: 19. júní 2009 04:43
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

Jens Páll Hafsteinsson wrote:
> Here's the code I'm using to run the test (it includes the schema). I've been 
> running this code for the last few days and am a bit baffled about my recent 
> results, since I'm not seeing the gradual slowing anymore. This happened 
> after I changed to use version 3.6.15 of sqlite and even if I change back to 
> 3.6.14 it still behaves very consistently, that is, doing the insert and 
> delete in constant time.
>
>   
Jens,

I get similar constant time results using equivalent SQL scripts and the 
command line SQLite program.

I noticed tat you are using the sqlite3_exec() API in your tests. If you 
are concerned about performance, as you seem to be based on the testing 
you are doing, you should look at switching to the preferred 
prepare/bind/step API functions (see http://www.sqlite.org/cintro.html 
and http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for info and samples).

Your test code is probably spending as much time compiling the same 
insert statement over and over again as it is on doing the actual 
inserts into the database. Using the alternate API you would prepare the 
statement once, then bind the values to be used for each insert, execute 
the insert, then reset the statement to be run again for the next insert.

HTH
Dennis Cote
___
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] Database inserts gradually slowing down

[Dennis Cote]

Jens Páll Hafsteinsson wrote:
> Here's the code I'm using to run the test (it includes the schema). I've been 
> running this code for the last few days and am a bit baffled about my recent 
> results, since I'm not seeing the gradual slowing anymore. This happened 
> after I changed to use version 3.6.15 of sqlite and even if I change back to 
> 3.6.14 it still behaves very consistently, that is, doing the insert and 
> delete in constant time.
>
>   
Jens,

I get similar constant time results using equivalent SQL scripts and the 
command line SQLite program.

I noticed tat you are using the sqlite3_exec() API in your tests. If you 
are concerned about performance, as you seem to be based on the testing 
you are doing, you should look at switching to the preferred 
prepare/bind/step API functions (see http://www.sqlite.org/cintro.html 
and http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for info and samples).

Your test code is probably spending as much time compiling the same 
insert statement over and over again as it is on doing the actual 
inserts into the database. Using the alternate API you would prepare the 
statement once, then bind the values to be used for each insert, execute 
the insert, then reset the statement to be run again for the next insert.

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

Re: [sqlite] Database inserts gradually slowing down

[John Stanton]

Jay A. Kreibich wrote:
> On Wed, Jun 17, 2009 at 11:52:45AM +1000, John Machin scratched on the wall:
>   
>> On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote:
>>
>> 
>>> One other note, if you have a primary key whose value is continually
>>> increasing your pk index can become imbalanced and therefore
>>> inefficient.
>>>   
>> A B-tree becomes imbalanced? How so?
>>
>> http://www.sqlite.org/fileformat.html#btree_structures says: "The tree 
>> is always of uniform height, meaning the number of intermediate levels 
>> between each leaf node page and the root page is the same."
>>
>> Do you have any evidence to the contrary?
>> 
>
>   It won't become imbalanced, but if you're inserting rows with an
>   explicit INTEGER PRIMARY KEY value in a non-increasing order, the
>   tree will require sorting and re-balancing.  That takes time and
>   requires additional disk writes (and, as others have pointed out,
>   disk writes are VERY expensive due to their transactional nature).
>
>   Also, depending on just how mixed up the pattern is, you can get into
>   situations where a very large index will over-flow the default 1500
>   page cache-size.  It is well known that if you want to build an index
>   on a large table, increasing the cache size will help make that
>   process faster.  It might be true here as well.  Try setting the page
>   cache to something nice and huge, like 10x or 100x the default, and
>   see if that helps.
>
>-j
>
>   
You are correct that a split in a B-Tree is expensive, more so if there 
are many levels.  Also the B-Tree algorithm keeps the index balanced but 
it does not prevent fragmentation.  An addition to the B-Tree logic to 
perform node merges where possible will limit fragmentation and limit 
the creation of unnecessary levels.

B-Trees which are expected to grow substantially can achieve a speed 
increase by partially filling the nodes so that  insertions can occur 
without forcing a split.

My guess is if you have a large number of Sqlite insertions you might 
find that to drop the index and re-raise it when the insertions are 
complete will be faster.

Note that the Sqlite rowids are organized as a B-Tree, but because they 
are consecutive numbers they make a well organized index.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Here's the code I'm using to run the test (it includes the schema). I've been 
running this code for the last few days and am a bit baffled about my recent 
results, since I'm not seeing the gradual slowing anymore. This happened after 
I changed to use version 3.6.15 of sqlite and even if I change back to 3.6.14 
it still behaves very consistently, that is, doing the insert and delete in 
constant time.

I don't think I changed anything in my original 3.6.14 install but I can't be 
sure since after going into 'silly mode' I copied the 3.6.15 sqlite code over 
my origianl 3.6.14 sqlite code and I now have no way of checking if I made some 
changes there. At most, I may have changed some defines but if the sqlite 
documentation does not suggest such a thing I doubt that I have done so on my 
own accord. I have always compiled the code defining SQLITE_THREADSAFE=1.

The only thing I can think of that's different from what I did when I saw the 
gradual slowing was that I am now always creating a new database when running 
the test (I delete the file) and used 3.6.15 for a while. I had been hammering 
away on the same database using the test code below for a while before I 
noticed the increase in execution time. Could that be a factor? Could it also 
be that 3.6.15 did some cleanup somewhere which could have been confusing my 
previous 3.6.14, since now when I use 3.6.14 it seems quite stable?

Anyway, I will continue to try and reproduce the slowing (by not creating a new 
database and re-use the table) but I'm getting a bit pessimistic on being able 
to do so, which is a good thing if this was a fluke, but also a bad thing since 
it leaves behind a nagging feeling that something might still be wrong. Maybe 
it should be a best practice to vacuum the database regularly to keep it in 
shape?


// Code begins

#include 
#include 
#include 
#include 

int
callback(void* arg, int argc, char** argv, char** column_names)
{
/*
for (int i = 0; i < argc; i++)
{
std::cout << argv[i] << " ";
}
std::cout << std::endl;
*/

return 0;
}

int
main()
{
sqlite3* db;
int res;
char* error_msg = 0;

/*
std::cout << "Opening time file...";
std::fstream file("time.txt", std::ios::app);
if (!file.is_open())
{
std::cerr << "failed\n";
exit(1);
}
std::cout << "done\n";
*/

std::cout << "Opening database...";
res = sqlite3_open("my1.db", );
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << sqlite3_errmsg(db) << std::endl;
sqlite3_close(db);
exit(1);
}
std::cout << "done\n";

std::string sql;

/**/
std::cout << "Dropping table t1...";
sql = "drop table t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
//exit(1);
}
else
{
std::cout << "done" << std::endl;
}

std::cout << "Creating table t1...";
sql = "create table t1(a integer, b integer, c varchar(100))";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;

std::cout << "Creating index i1 on t1...";
sql = "create index i1 on t1(a)";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;
/**/

while (true)
{
//std::cout << "Deleting from table t1...";
sql = "delete from t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;

SYSTEMTIME start;
SYSTEMTIME end;

double start_seconds;
double end_seconds;
int record_count = 1000;
int batches = 100;

//std::cout << "Inserting and selecting " << record_count * batches << 
" records in batches of " << record_count << std::endl;

GetSystemTime();

char sql_c[1024];
for (int b = 0; b < batches; b++)
{
int row;

//std::cout << "Beginning transaction...";
sql = "begin transaction";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;

for (int i = 0; i < record_count; i++)
{
row = b * record_count + i;
 

Re: [sqlite] Database inserts gradually slowing down

[John Machin]

On 17/06/2009 11:52 AM, Dennis Cote wrote:
> Jens Páll Hafsteinsson wrote:
>> Closing and opening again did not speed up steps 1-4, it actually slowed 
>> things down even more. The curve from the beginning is a bit similar to a 
>> slightly flattened log curve. When I closed the database and started the 
>> test again, a similar curve appeared again, but now starting from where the 
>> first run left off.
>>
>> I've been running the same 3.6.15 since this afternoon (the previous test 
>> was using 3.6.14) and it seems to flatten out fairly quickly but it is 
>> significantly slower (2.3 to 1.3 times slower, depending on where you 
>> measure it using the data I have). I'm not that worried about that for the 
>> time being; I'm just hoping it will stay flat.
>>
>> JP
>> On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:
>>> 1.   start a transaction
>>> 2.   insert 1000 records
>>> 3.   commit
>>> 4.   repeat steps 1-3 100 times
>>> 5.   delete everything from the table
>>> 6.   Start again at step 1
> 
> I suspect that you may be using a autoincrement id field and then 
> running into the extra work (both CPU load and increased disk space) 
> needed to handle the variable sized integer storage method used by 
> SQLite. This would lead to the type of logarithmic growth you are 
> seeing. The first few iterations used short single byte integer values, 
> the next bunch use 2 byte integer values, etc. The autoincrement field 
> would cause SQLite to continue at the same speed after restarting the 
> application as you have described, since the next field values used 
> would continue from where it left off at the end of the previous run.

Even after "delete everything from the table"; see below.

> 
> I would have expected the time to stabilize on 3 byte values fairly 
> qucikly, and then only change again when switching to values that 
> required 4 bytes.
> 
> This may be a part of the answer even if it is not the complete answer.

 From Jens's description, he is writing only 1000 * (1 + 100) = 101,000 
records before "delete everything from the table". A 3-byte variable 
integer will hold a number 16K to 2M approx. 4-byte: 2M to 268M approx.

On the surface, should be OK. UNDER the surface:

sqlite> create table t (p integer primary key autoincrement, x text);
sqlite> select * from sqlite_master;
table|t|t|2|CREATE TABLE t (p integer primary key autoincrement, x text)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE 
sqlite_sequence(name,seq)
sqlite> insert into t(x) values('blah');
sqlite> insert into t(x) values('yadda');
sqlite> select * from sqlite_sequence;
t|2
sqlite> delete from t;
sqlite> select * from sqlite_sequence;
t|2 == whoops

Documented (and good behaviour) -- never re-issue a key while the table 
exists.

Possible solution (apart from DROP TABLE):

sqlite> delete from sqlite_sequence where name = 't';
sqlite> select * from sqlite_sequence;
sqlite>


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

Re: [sqlite] Database inserts gradually slowing down

[Jay A. Kreibich]

On Wed, Jun 17, 2009 at 11:52:45AM +1000, John Machin scratched on the wall:
> On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote:
> 
> > One other note, if you have a primary key whose value is continually
> > increasing your pk index can become imbalanced and therefore
> > inefficient.
> 
> A B-tree becomes imbalanced? How so?
> 
> http://www.sqlite.org/fileformat.html#btree_structures says: "The tree 
> is always of uniform height, meaning the number of intermediate levels 
> between each leaf node page and the root page is the same."
> 
> Do you have any evidence to the contrary?

  It won't become imbalanced, but if you're inserting rows with an
  explicit INTEGER PRIMARY KEY value in a non-increasing order, the
  tree will require sorting and re-balancing.  That takes time and
  requires additional disk writes (and, as others have pointed out,
  disk writes are VERY expensive due to their transactional nature).

  Also, depending on just how mixed up the pattern is, you can get into
  situations where a very large index will over-flow the default 1500
  page cache-size.  It is well known that if you want to build an index
  on a large table, increasing the cache size will help make that
  process faster.  It might be true here as well.  Try setting the page
  cache to something nice and huge, like 10x or 100x the default, and
  see if that helps.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Dennis Cote]

Jens Páll Hafsteinsson wrote:
> Closing and opening again did not speed up steps 1-4, it actually slowed 
> things down even more. The curve from the beginning is a bit similar to a 
> slightly flattened log curve. When I closed the database and started the test 
> again, a similar curve appeared again, but now starting from where the first 
> run left off.
>
> I've been running the same 3.6.15 since this afternoon (the previous test was 
> using 3.6.14) and it seems to flatten out fairly quickly but it is 
> significantly slower (2.3 to 1.3 times slower, depending on where you measure 
> it using the data I have). I'm not that worried about that for the time 
> being; I'm just hoping it will stay flat.
>
> JP
>
>
>
> On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:
>
>   
>> 1.   start a transaction
>>
>> 2.   insert 1000 records
>>
>> 3.   commit
>>
>> 4.   repeat steps 1-3 100 times
>>
>> 5.   delete everything from the table
>>
>> 6.   Start again at step 1
>> 
>
>   
Jens,

Can you post the schema for your table and the index (i.e. the actual 
schema you are using for the test), and also the code that shows how you 
are assigning values to the columns in your table when you do the inserts?

I suspect that you may be using a autoincrement id field and then 
running into the extra work (both CPU load and increased disk space) 
needed to handle the variable sized integer storage method used by 
SQLite. This would lead to the type of logarithmic growth you are 
seeing. The first few iterations used short single byte integer values, 
the next bunch use 2 byte integer values, etc. The autoincrement field 
would cause SQLite to continue at the same speed after restarting the 
application as you have described, since the next field values used 
would continue from where it left off at the end of the previous run.

I would have expected the time to stabilize on 3 byte values fairly 
qucikly, and then only change again when switching to values that 
required 4 bytes.

This may be a part of the answer even if it is not the complete answer.

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

Re: [sqlite] Database inserts gradually slowing down

[John Machin]

On 17/06/2009 6:17 AM, Hoover, Jeffrey wrote:

> One other note, if you have a primary key whose value is continually
> increasing your pk index can become imbalanced and therefore
> inefficient.

A B-tree becomes imbalanced? How so?

http://www.sqlite.org/fileformat.html#btree_structures says: "The tree 
is always of uniform height, meaning the number of intermediate levels 
between each leaf node page and the root page is the same."

Do you have any evidence to the contrary?

> You may be able to get around this by using INTEGER
> PRIMARY KEY (or INTEGER PRIMARY KEY AUTOINCREMENT).

So with autoincrement SQLite3 generates the "continually increasing" pk 
values instead of the app doing it, and that solves the alleged problem?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Douglas E. Fajardo]

This may be a red herring, but how do you generate the 'random' characters for 
the test? Could entropy exhaustion affecting the results?

Just a thought...
*** Doug Fajardo

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Tuesday, June 16, 2009 12:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

On Tue, Jun 16, 2009 at 1:59 PM, Jens Páll
Hafsteinsson<j...@lsretail.com> wrote:
> Ok, but you might have to run this a bit longer (or more iterations).

I ran the code a bit longer, and yes, I do notice an increase. Here
are my results after 610 iterations... I have provided the numbers for
10 iterations every hundred (my numbers are lower this time than the
earlier posted 8 seconds or so per iteration because of a different
way of generating random strings).

1.  3 wallclock secs ( 0.87 usr +  0.24 sys =  1.11 CPU)
2.  2 wallclock secs ( 0.88 usr +  0.26 sys =  1.14 CPU)
3.  3 wallclock secs ( 0.89 usr +  0.26 sys =  1.15 CPU)
4.  2 wallclock secs ( 0.88 usr +  0.27 sys =  1.15 CPU)
5.  3 wallclock secs ( 0.88 usr +  0.26 sys =  1.14 CPU)
6.  2 wallclock secs ( 0.88 usr +  0.27 sys =  1.15 CPU)
7.  3 wallclock secs ( 0.89 usr +  0.27 sys =  1.16 CPU)
8.  2 wallclock secs ( 0.88 usr +  0.26 sys =  1.14 CPU)
9.  3 wallclock secs ( 0.89 usr +  0.27 sys =  1.16 CPU)
10.  2 wallclock secs ( 0.88 usr +  0.27 sys =  1.15 CPU)
..
200.  4 wallclock secs ( 0.89 usr +  0.30 sys =  1.19 CPU)
201.  4 wallclock secs ( 0.89 usr +  0.29 sys =  1.18 CPU)
202.  4 wallclock secs ( 0.89 usr +  0.30 sys =  1.19 CPU)
203.  3 wallclock secs ( 0.88 usr +  0.31 sys =  1.19 CPU)
204.  4 wallclock secs ( 0.89 usr +  0.30 sys =  1.19 CPU)
205.  4 wallclock secs ( 0.88 usr +  0.31 sys =  1.19 CPU)
206.  4 wallclock secs ( 0.90 usr +  0.30 sys =  1.20 CPU)
207.  4 wallclock secs ( 0.89 usr +  0.29 sys =  1.18 CPU)
208.  4 wallclock secs ( 0.88 usr +  0.30 sys =  1.18 CPU)
209.  4 wallclock secs ( 0.89 usr +  0.30 sys =  1.19 CPU)
210.  3 wallclock secs ( 0.89 usr +  0.30 sys =  1.19 CPU)
..
300.  5 wallclock secs ( 0.89 usr +  0.32 sys =  1.21 CPU)
301.  4 wallclock secs ( 0.90 usr +  0.33 sys =  1.23 CPU)
302.  5 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
303.  6 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
304.  5 wallclock secs ( 0.89 usr +  0.32 sys =  1.21 CPU)
305.  6 wallclock secs ( 0.90 usr +  0.33 sys =  1.23 CPU)
306.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
307.  5 wallclock secs ( 0.89 usr +  0.31 sys =  1.20 CPU)
308.  8 wallclock secs ( 0.89 usr +  0.32 sys =  1.21 CPU)
309. 13 wallclock secs ( 0.90 usr +  0.35 sys =  1.25 CPU)
310.  6 wallclock secs ( 0.90 usr +  0.33 sys =  1.23 CPU)
..
400.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
401.  5 wallclock secs ( 0.89 usr +  0.32 sys =  1.21 CPU)
402.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
403.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
404.  5 wallclock secs ( 0.89 usr +  0.32 sys =  1.21 CPU)
405.  6 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
406.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
407.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
408.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
409.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
410.  5 wallclock secs ( 0.89 usr +  0.33 sys =  1.22 CPU)
..
500.  5 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
501.  6 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
502.  6 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
503.  6 wallclock secs ( 0.90 usr +  0.36 sys =  1.26 CPU)
504.  6 wallclock secs ( 0.88 usr +  0.37 sys =  1.25 CPU)
505. 11 wallclock secs ( 0.89 usr +  0.36 sys =  1.25 CPU)
506.  9 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
507.  5 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
508.  6 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
509.  6 wallclock secs ( 0.89 usr +  0.34 sys =  1.23 CPU)
510.  6 wallclock secs ( 0.88 usr +  0.34 sys =  1.22 CPU)
..
600.  6 wallclock secs ( 0.89 usr +  0.36 sys =  1.25 CPU)
601.  7 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
602.  6 wallclock secs ( 0.88 usr +  0.35 sys =  1.23 CPU)
603.  7 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
604.  6 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
605.  6 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
606.  7 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
607.  6 wallclock secs ( 0.88 usr +  0.35 sys =  1.23 CPU)
608.  7 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)
609.  6 wallclock secs ( 0.89 usr +  0.36 sys =  1.25 CPU)
610.  7 wallclock secs ( 0.89 usr +  0.35 sys =  1.24 CPU)


> My first 10 runs give the following results (in seconds):
>
> 1       15,681
> 2       16,010
> 3       16,093
> 4       16,168
> 5       16,057
> 6       16,585
> 7       16,114
> 8       16,596
> 9       

Re: [sqlite] Database inserts gradually slowing down

[Hoover, Jeffrey]

I vacuum frequently, particular after large updates or deletes.

Two other potential optimizations of inserts (you may already be doing
this):
- use bulk insert
- encapsulate the inserts within a transaction

One other note, if you have a primary key whose value is continually
increasing your pk index can become imbalanced and therefore
inefficient.  You may be able to get around this by using INTEGER
PRIMARY KEY (or INTEGER PRIMARY KEY AUTOINCREMENT).  As a side benefit
this kind of column queries very efficiently since the column value is
row's address (the ROWID).

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wes Freeman
Sent: Tuesday, June 16, 2009 4:03 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

VACUUM cleaned up the file in my current test, after 1200 iterations,
making it run at ~4.6seconds again, rather than ~5.1. It seemed to get
it almost back to the performance of a clean file.

Didn't know about the vacuum command--Cool. By the way, the vacuum
operation takes ~1.6 seconds for my test database. Seems like a
reasonable thing to run before a backup or something, if your
application would be prone to this sort of fragmentation.

Wes

On Tue, Jun 16, 2009 at 1:44 PM, Hoover, Jeffrey<jhoo...@jcvi.org>
wrote:
> Wouldn't a period VACUUMing of the database alleviate fragmentation?
>
> - Jeff
>
___
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] Database inserts gradually slowing down

[Wes Freeman]

VACUUM cleaned up the file in my current test, after 1200 iterations,
making it run at ~4.6seconds again, rather than ~5.1. It seemed to get
it almost back to the performance of a clean file.

Didn't know about the vacuum command--Cool. By the way, the vacuum
operation takes ~1.6 seconds for my test database. Seems like a
reasonable thing to run before a backup or something, if your
application would be prone to this sort of fragmentation.

Wes

On Tue, Jun 16, 2009 at 1:44 PM, Hoover, Jeffrey wrote:
> Wouldn't a period VACUUMing of the database alleviate fragmentation?
>
> - Jeff
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Wes Freeman]

On Tue, Jun 16, 2009 at 2:51 PM, Jens Páll
Hafsteinsson wrote:
> Closing and opening again did not speed up steps 1-4, it actually slowed 
> things down even more. The curve from the beginning is a bit similar to a 
> slightly flattened log curve. When I closed the database and started the test 
> again, a similar curve appeared again, but now starting from where the first 
> run left off.

I noticed the same thing: if I start the test over with the same file,
the time is roughly where it left off.

I'm to 800 iterations now, and it's up to ~4.9s. I'm not really
alarmed by this increase, yet.

On Tue, Jun 16, 2009 at 3:11 PM, Jens Páll
Hafsteinsson wrote:
> Wes, I gather you are using version 3.6.14 in this test (?)

It looks like the Sqlite version included with Qt 4.5 is 3.5.9. I may
have to compile the latest. I think it's strange that you're seeing
such a big difference between minor version numbers.

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

Re: [sqlite] Database inserts gradually slowing down

[P Kishor]

> 2       21,753
> 3       23,5
> 4       26,591
> 5       27,925
> 6       29,682
> 7       29,807
> 8       31,944
> 9       32,422
> 10      34,144
>
> Increasing like crazy (but seems to level off later at 40 seconds).
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
> Sent: 16. júní 2009 16:55
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll
> Hafsteinsson<j...@lsretail.com> wrote:
>> The key factor here is not the total time taken to perform these operations 
>> but the fact that the time is *increasing* for each run. I am looking for 
>> consistency in that I need to be able to let the application perform these 
>> steps in constant time over a long period of time (months).
>>
>
>
> Using SQLite 3.6.13, Perl 5.8.8, on a Mac OS X Leopard Macbook with 4
> GB RAM and 320 GB 7200 RPM Seagate drive, I get
>
> [11:27 AM] ~/Projects/sqlite_test$perl db_slows.pl
> 1. the code took: 8 wallclock secs ( 6.83 usr +  0.30 sys =  7.13 CPU)
> 2. the code took: 9 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
> 3. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
> 4. the code took: 8 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
> 5. the code took: 8 wallclock secs ( 6.78 usr +  0.33 sys =  7.11 CPU)
> 6. the code took: 8 wallclock secs ( 6.81 usr +  0.33 sys =  7.14 CPU)
> 7. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
> 8. the code took: 9 wallclock secs ( 6.80 usr +  0.35 sys =  7.15 CPU)
> 9. the code took: 8 wallclock secs ( 6.79 usr +  0.34 sys =  7.13 CPU)
> 10. the code took: 8 wallclock secs ( 6.90 usr +  0.34 sys =  7.24 CPU)
>
> Pretty linear performance. As stated in the OP, I
> 1. Insert 1000 records (an integer and a 100 random char string;
> 2. Commit;
> 3. Repeat 1 one hundred times;
> 4. DELETE all records;
> 5. Repeat 1-4 x 10.
>
> The table is
>
> CREATE TABLE foo (id INTEGER PRIMARY KEY, num INTEGER, desc TEXT);
>
> There are no indexes.
>
>
> man/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Wes, I gather you are using version 3.6.14 in this test (?)

Please also try version 3.6.15 since I'm seeing drastically different behavior 
using that version (starting similarly fast as .14 but quickly slowing down 
after about 15 iterations and then leveling off after 22 iterations).

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Wes Freeman
Sent: 16. júní 2009 18:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

I also wrote my own test in a small Qt/C++ app.

First I tried on my 10k rpm U160 SCSI drive, and it stayed roughly
between ~23 seconds per set (one set is 100 of: start
transaction->insert 1000->commit, as described). I let it run for
about 10 minutes before stopping it. It's hard drive bound on this
drive, taking only about 20% of one of the CPU cores, and creating a
horrible-sounding disk churning noise.

Second, I tried on my two raid 0 (striped for performance) 7200rpm
drives, and it was much faster--~4.5 seconds per set. It does seem to
be increasing, but I'm only up to 200 iterations, so it's difficult to
tell if it's significant. It seems to be CPU bound for this test, as
it's maxing one of my cores at 95-100% (Intel Core 2 Quad @2.66GHz w/
12MB cache).

I'm disappointed with the performance of my "faster" drive, but I
guess this is a good showing for the benefits of raid 0. I'm wondering
if my SCSI drive has some issues at this point.

I'll continue testing over longer periods to see how much worse it
gets on my system. Konrad's comment on fragmentation sounds like a
reasonable explanation. I'm curious about a non-transactional test, to
see if this occurs at a single-insert level. This may be something to
keep in mind when writing an application that will use a database file
transactionally for months at a time.

Wes
___
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] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Thanks for the info Konrad. This is very similar to what I'm doing so that 
might be the case. Just for curiosity's sake, I might try to drop the table 
in-between the runs to see what happens.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Konrad J Hambrick
Sent: 16. júní 2009 17:43
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down


Jens --

I have seen Index and Record Fragmentation cause
the kind of slowdowns you are describing.

Especially when there are a lot deletes followed
by a 'largish' (100,000-record) batch of inserts.

I have found on occasion that the speed is more
consistent if I can find a way to drop and create
a 'fresh' table from time to time.

The fresh table eliminates the fragmentation.

Sounds like that won't work without a lot of effort
since you have records remaining in the table.

-- kjh


On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
> 
> Dropping the table is not an option since I always will have some data in the 
> table (even though I delete everything in my test). Maybe the culprit is that 
> I delete everything from the table?
> 
> JP
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
> Sent: 16. júní 2009 15:51
> To: kon...@payplus.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
> 
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote:
>> Hmmm ...
>>
>> Is there a way to replace step 5 (delete everything from the table)
>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>
> 
> This shouldn't be necessary unless the table has triggers.  From
> http://sqlite.org/lang_delete.html :
> 
> "When the WHERE is omitted from a DELETE statement and the table being
> deleted has no triggers, SQLite uses an optimization to erase the
> entire table content without having to visit each row of the table
> individual. This "truncate" optimization makes the delete run much
> faster."
> 
___
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] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Ok, but you might have to run this a bit longer (or more iterations). My first 
10 runs give the following results (in seconds):

1   15,681
2   16,010
3   16,093
4   16,168
5   16,057
6   16,585
7   16,114
8   16,596
9   16,115
10  16,270

Jumping around a bit but it seems linear (but it's not). I'm not saying that 
3.6.13 behaves the stame as 3.6.14 but this only becomes really apparent when 
run over an extended period of time. 3.6.15 looks promising though in this 
respect but looking at the first 10 runs of that version does definitely not 
say the whole story (at least in my case):

1   17,874
2   21,753
3   23,5
4   26,591
5   27,925
6   29,682
7   29,807
8   31,944
9   32,422
10  34,144

Increasing like crazy (but seems to level off later at 40 seconds).

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: 16. júní 2009 16:55
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll
Hafsteinsson<j...@lsretail.com> wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
>


Using SQLite 3.6.13, Perl 5.8.8, on a Mac OS X Leopard Macbook with 4
GB RAM and 320 GB 7200 RPM Seagate drive, I get

[11:27 AM] ~/Projects/sqlite_test$perl db_slows.pl
1. the code took: 8 wallclock secs ( 6.83 usr +  0.30 sys =  7.13 CPU)
2. the code took: 9 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
3. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
4. the code took: 8 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
5. the code took: 8 wallclock secs ( 6.78 usr +  0.33 sys =  7.11 CPU)
6. the code took: 8 wallclock secs ( 6.81 usr +  0.33 sys =  7.14 CPU)
7. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
8. the code took: 9 wallclock secs ( 6.80 usr +  0.35 sys =  7.15 CPU)
9. the code took: 8 wallclock secs ( 6.79 usr +  0.34 sys =  7.13 CPU)
10. the code took: 8 wallclock secs ( 6.90 usr +  0.34 sys =  7.24 CPU)

Pretty linear performance. As stated in the OP, I
1. Insert 1000 records (an integer and a 100 random char string;
2. Commit;
3. Repeat 1 one hundred times;
4. DELETE all records;
5. Repeat 1-4 x 10.

The table is

CREATE TABLE foo (id INTEGER PRIMARY KEY, num INTEGER, desc TEXT);

There are no indexes.


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

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Closing and opening again did not speed up steps 1-4, it actually slowed things 
down even more. The curve from the beginning is a bit similar to a slightly 
flattened log curve. When I closed the database and started the test again, a 
similar curve appeared again, but now starting from where the first run left 
off.

I've been running the same 3.6.15 since this afternoon (the previous test was 
using 3.6.14) and it seems to flatten out fairly quickly but it is 
significantly slower (2.3 to 1.3 times slower, depending on where you measure 
it using the data I have). I'm not that worried about that for the time being; 
I'm just hoping it will stay flat.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: 16. júní 2009 13:30
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down


On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:

> 1.   start a transaction
>
> 2.   insert 1000 records
>
> 3.   commit
>
> 4.   repeat steps 1-3 100 times
>
> 5.   delete everything from the table
>
> 6.   Start again at step 1

Okay, so do that until it's slow, then close the database and open it  
again, and do steps 1-4 once more.  Is it still slow, or did closing  
and opening speed it up ?

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] Database inserts gradually slowing down

[Konrad J Hambrick]

Jeffrey --

Maybe so, but I should have looked further in Jen's
reply at the built-in optimizations for DELETE on a
Table without any filters and not having TRIGGERS ...

sorry :(

-- kjh


On 06/16/2009 12:44 PM, Hoover, Jeffrey wrote:
> Wouldn't a period VACUUMing of the database alleviate fragmentation?
> 
> - Jeff
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Konrad J Hambrick
> Sent: Tuesday, June 16, 2009 1:43 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
> 
> 
> Jens --
> 
> I have seen Index and Record Fragmentation cause
> the kind of slowdowns you are describing.
> 
> Especially when there are a lot deletes followed
> by a 'largish' (100,000-record) batch of inserts.
> 
> I have found on occasion that the speed is more
> consistent if I can find a way to drop and create
> a 'fresh' table from time to time.
> 
> The fresh table eliminates the fragmentation.
> 
> Sounds like that won't work without a lot of effort
> since you have records remaining in the table.
> 
> -- kjh
> 
> 
> On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote:
>> The key factor here is not the total time taken to perform these operations 
>> but the fact that the time is *increasing* for each run. I am looking for 
>> consistency in that I need to be able to let the application perform these 
>> steps in constant time over a long period of time (months).
>>
>> Dropping the table is not an option since I always will have some data in 
>> the table (even though I delete everything in my test). Maybe the culprit is 
>> that I delete everything from the table?
>>
>> JP
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
>> Sent: 16. júní 2009 15:51
>> To: kon...@payplus.com; General Discussion of SQLite Database
>> Subject: Re: [sqlite] Database inserts gradually slowing down
>>
>> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> 
>> wrote:
>>> Hmmm ...
>>>
>>> Is there a way to replace step 5 (delete everything from the table)
>>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>>
>> This shouldn't be necessary unless the table has triggers.  From
>> http://sqlite.org/lang_delete.html :
>>
>> "When the WHERE is omitted from a DELETE statement and the table being
>> deleted has no triggers, SQLite uses an optimization to erase the
>> entire table content without having to visit each row of the table
>> individual. This "truncate" optimization makes the delete run much
>> faster."
>>
> ___
> 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] Database inserts gradually slowing down

[Hoover, Jeffrey]

Wouldn't a period VACUUMing of the database alleviate fragmentation?

- Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Konrad J Hambrick
Sent: Tuesday, June 16, 2009 1:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down


Jens --

I have seen Index and Record Fragmentation cause
the kind of slowdowns you are describing.

Especially when there are a lot deletes followed
by a 'largish' (100,000-record) batch of inserts.

I have found on occasion that the speed is more
consistent if I can find a way to drop and create
a 'fresh' table from time to time.

The fresh table eliminates the fragmentation.

Sounds like that won't work without a lot of effort
since you have records remaining in the table.

-- kjh


On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
> 
> Dropping the table is not an option since I always will have some data in the 
> table (even though I delete everything in my test). Maybe the culprit is that 
> I delete everything from the table?
> 
> JP
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
> Sent: 16. júní 2009 15:51
> To: kon...@payplus.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
> 
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote:
>> Hmmm ...
>>
>> Is there a way to replace step 5 (delete everything from the table)
>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>
> 
> This shouldn't be necessary unless the table has triggers.  From
> http://sqlite.org/lang_delete.html :
> 
> "When the WHERE is omitted from a DELETE statement and the table being
> deleted has no triggers, SQLite uses an optimization to erase the
> entire table content without having to visit each row of the table
> individual. This "truncate" optimization makes the delete run much
> faster."
> 
___
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] Database inserts gradually slowing down

[Konrad J Hambrick]

Jens --

I have seen Index and Record Fragmentation cause
the kind of slowdowns you are describing.

Especially when there are a lot deletes followed
by a 'largish' (100,000-record) batch of inserts.

I have found on occasion that the speed is more
consistent if I can find a way to drop and create
a 'fresh' table from time to time.

The fresh table eliminates the fragmentation.

Sounds like that won't work without a lot of effort
since you have records remaining in the table.

-- kjh


On 06/16/2009 10:57 AM, Jens Páll Hafsteinsson wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
> 
> Dropping the table is not an option since I always will have some data in the 
> table (even though I delete everything in my test). Maybe the culprit is that 
> I delete everything from the table?
> 
> JP
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
> Sent: 16. júní 2009 15:51
> To: kon...@payplus.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
> 
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote:
>> Hmmm ...
>>
>> Is there a way to replace step 5 (delete everything from the table)
>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>
> 
> This shouldn't be necessary unless the table has triggers.  From
> http://sqlite.org/lang_delete.html :
> 
> "When the WHERE is omitted from a DELETE statement and the table being
> deleted has no triggers, SQLite uses an optimization to erase the
> entire table content without having to visit each row of the table
> individual. This "truncate" optimization makes the delete run much
> faster."
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Simon Slavin]

On 16 Jun 2009, at 4:31pm, Konrad J Hambrick wrote:

> Is there a way to replace step 5 (delete everything from the table)
> with a System-Level rm / unlink and a sqlite CREATE TABLE ?

JP can totally do that, but his original question was why the system  
gradually gets slower and slower.  and that does indicate a bug  
somewhere, which should probably be identified.


On 16 Jun 2009, at 2:52pm, Jens Páll Hafsteinsson wrote:

> does sqlite use temporary files beside the database file?



Might be interesting to have a look at those, with the application in  
a 'paused' state while it's taking a long time to do things, and see  
if any of them have got to be particularly big.

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

Re: [sqlite] Database inserts gradually slowing down

[P Kishor]

On Tue, Jun 16, 2009 at 10:57 AM, Jens Páll
Hafsteinsson<j...@lsretail.com> wrote:
> The key factor here is not the total time taken to perform these operations 
> but the fact that the time is *increasing* for each run. I am looking for 
> consistency in that I need to be able to let the application perform these 
> steps in constant time over a long period of time (months).
>


Using SQLite 3.6.13, Perl 5.8.8, on a Mac OS X Leopard Macbook with 4
GB RAM and 320 GB 7200 RPM Seagate drive, I get

[11:27 AM] ~/Projects/sqlite_test$perl db_slows.pl
1. the code took: 8 wallclock secs ( 6.83 usr +  0.30 sys =  7.13 CPU)
2. the code took: 9 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
3. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
4. the code took: 8 wallclock secs ( 6.78 usr +  0.34 sys =  7.12 CPU)
5. the code took: 8 wallclock secs ( 6.78 usr +  0.33 sys =  7.11 CPU)
6. the code took: 8 wallclock secs ( 6.81 usr +  0.33 sys =  7.14 CPU)
7. the code took: 8 wallclock secs ( 6.80 usr +  0.34 sys =  7.14 CPU)
8. the code took: 9 wallclock secs ( 6.80 usr +  0.35 sys =  7.15 CPU)
9. the code took: 8 wallclock secs ( 6.79 usr +  0.34 sys =  7.13 CPU)
10. the code took: 8 wallclock secs ( 6.90 usr +  0.34 sys =  7.24 CPU)

Pretty linear performance. As stated in the OP, I
1. Insert 1000 records (an integer and a 100 random char string;
2. Commit;
3. Repeat 1 one hundred times;
4. DELETE all records;
5. Repeat 1-4 x 10.

The table is

CREATE TABLE foo (id INTEGER PRIMARY KEY, num INTEGER, desc TEXT);

There are no indexes.


> Dropping the table is not an option since I always will have some data in the 
> table (even though I delete everything in my test). Maybe the culprit is that 
> I delete everything from the table?
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Matthew L. Creech
> Sent: 16. júní 2009 15:51
> To: kon...@payplus.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote:
>>
>> Hmmm ...
>>
>> Is there a way to replace step 5 (delete everything from the table)
>> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>>
>
> This shouldn't be necessary unless the table has triggers.  From
> http://sqlite.org/lang_delete.html :
>
> "When the WHERE is omitted from a DELETE statement and the table being
> deleted has no triggers, SQLite uses an optimization to erase the
> entire table content without having to visit each row of the table
> individual. This "truncate" optimization makes the delete run much
> faster."
>
> --
> Matthew L. Creech
> ___
> 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
>



-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Simon Slavin]

On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote:

> 1.   start a transaction
>
> 2.   insert 1000 records
>
> 3.   commit
>
> 4.   repeat steps 1-3 100 times
>
> 5.   delete everything from the table
>
> 6.   Start again at step 1

Okay, so do that until it's slow, then close the database and open it  
again, and do steps 1-4 once more.  Is it still slow, or did closing  
and opening speed it up ?

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

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

The key factor here is not the total time taken to perform these operations but 
the fact that the time is *increasing* for each run. I am looking for 
consistency in that I need to be able to let the application perform these 
steps in constant time over a long period of time (months).

Dropping the table is not an option since I always will have some data in the 
table (even though I delete everything in my test). Maybe the culprit is that I 
delete everything from the table?

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Matthew L. Creech
Sent: 16. júní 2009 15:51
To: kon...@payplus.com; General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

On Tue, Jun 16, 2009 at 11:31 AM, Konrad J Hambrick<kon...@payplus.com> wrote:
>
> Hmmm ...
>
> Is there a way to replace step 5 (delete everything from the table)
> with a System-Level rm / unlink and a sqlite CREATE TABLE ?
>

This shouldn't be necessary unless the table has triggers.  From
http://sqlite.org/lang_delete.html :

"When the WHERE is omitted from a DELETE statement and the table being
deleted has no triggers, SQLite uses an optimization to erase the
entire table content without having to visit each row of the table
individual. This "truncate" optimization makes the delete run much
faster."

-- 
Matthew L. Creech
___
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] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Thanks for the pointers Kees. I'll keep those in mind when I go into trying to 
optimize the database.

I was clearly way off track when I said I was perfectly aware of the issues 
concerning the database. I completely forgot about taking the spin factor into 
account as you and Jim have pointed out to me. Thanks again.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kees Nuyt
Sent: 16. júní 2009 15:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Database inserts gradually slowing down

On Tue, 16 Jun 2009 14:23:47 +, Jens Páll Hafsteinsson
<j...@lsretail.com> wrote:

> Yes, I'm perfectly aware of this and hence I
> would expect the disk to be sweating like hell
> running this test while the CPU is relatively
> relaxed (given that sqlite is disk bound in
> this case and not CPU bound).
>
> But this is not happening; neither the disk nor
> the CPU are practically doing anything, which
> is a bit strange. It's as if both the disk and
> the CPU are waiting for each other or that
> sqlite is playing 'nice' behind my back and
> giving up the CPU when it shouldn't.

Apart from seeks, the disk has to spin until the correct
start sector is under the head. Then it can write a database
page, perhaps a few database pages.

There are a few parameters you can use to optimize this:
- PRAGMA page_size
- PRAGMA [default_]cache_size
- the number of INSERTs per transaction
- The schema: INDEX PRIMARY KEY on the first column
  instead of a non-unique index
  (if the application allows it)
- load the database in order of index(es)

Especially a non-unique index with low cardinality has a lot
of overhead.

>JP
-- 
  (  Kees Nuyt
  )
c[_]
___
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] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Ah, of course. Thanks for the explanation Jim.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jim Wilcoxson
Sent: 16. júní 2009 15:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

You are doing transactions here, which is a very different thing from
normal disk I/O.  Your CPU is idle because it is waiting for the disk.
 Your disk is idle because it is waiting for the platters to rotate
around again.  The best case you can achieve on a 7200RPM disk is 120
transactions (commits) per second.

In practice, you will see much lower TPS rates, and they will
typically be 50% fractions.  For 1 sync per transaction, the max rate
is 120 TPS.  For 2 syncs per transaction, the max rate is 60 TPS.  For
3 syncs per transaction, the max rate is 30 TPS.  SQLite always does
at least 2 syncs per transaction, and sometimes 3, depending on the
options you use, so a transaction rate of 30 TPS is reasonable.

The only way you can speed this up is to get a disk that rotates
faster, ie, a 10K or 15K rpm drive will do faster transactions, but
even so, you are still limited to 250 TPS with a 15K drive.  Or, get a
battery-backup caching controller that will lie to your OS and tell it
that the data is on the media, when really it is only stored in the
controller's memory.  This allows the controller to combine write
requests to increase the transaction rate.

Jim

On 6/16/09, Jens Páll Hafsteinsson <j...@lsretail.com> wrote:
> Yes, I'm perfectly aware of this and hence I would expect the disk to be
> sweating like hell running this test while the CPU is relatively relaxed
> (given that sqlite is disk bound in this case and not CPU bound).
>
> But this is not happening; neither the disk nor the CPU are practically
> doing anything, which is a bit strange. It's as if both the disk and the CPU
> are waiting for each other or that sqlite is playing 'nice' behind my back
> and giving up the CPU when it shouldn't.
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
> Sent: 16. júní 2009 14:06
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> Remember the implications of Moore's law and how much time has passed.
>
> CPU speed is much faster than memory speed.
> Memory speed is much faster than disk access.
>
> This is why hardware folks play all sorts of tricks with pipelines, caches,
> interleaving, and parallelism.
>
> For a single process that interacts with the HDD, the HDD will be the bottle
> neck and the CPU will spend lots of time waiting for the rest of the machine
> to catch up.  Even if you have a RAID system, the CPU is still much faster
> than the bus the hard drives are on.
>
>
> On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
> <j...@lsretail.com>wrote:
>
>> In step 5 I execute "delete from t1" without any where clause.
>>
>> I haven't monitored the disk space used (does sqlite use temporary files
>> beside the database file?) but the database file itself has been fixed in
>> size at around 12MB (12.461.056 bytes) the whole time.
>>
>> The load on the disk is minimal (sustained 1MB/s) and the CPU load is
>> about
>> 6%, which is a bit surprising since I thought I would be putting a huge
>> load
>> on the computer running a loop like this. I'm not at all happy to see
>> these
>> low load numbers given how the test is programmed (it should practically
>> own
>> the machine). The database should utilize the computer much better than
>> this.
>>
>> I've been running the test now for about 10 minutes using 3.6.15 and it
>> 'seems' to be behaving as before, slowly increasing in execution time. I
>> want to run this a bit longer to be absolutely sure and will let you know.
>>
>> JP
>>
>> -----Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:
>> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
>> Sent: 16. júní 2009 12:15
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Database inserts gradually slowing down
>>
>> How do you do step 5? Like "delete from table" or "delete from table
>> where ..."? Do you see any degrade in disk space used by database
>> along with slowness?
>>
>> Pavel
>>
>> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
>> Hafsteinsson<j...@lsretail.com> wrote:
>> > Hi
>> >
>> > I've been running some tests against sqlite and have found that inserts
>> are gradually 

Re: [sqlite] Database inserts gradually slowing down

[Konrad J Hambrick]

Hmmm ...

Is there a way to replace step 5 (delete everything from the table)
with a System-Level rm / unlink and a sqlite CREATE TABLE ?

-- kjh


On 06/16/2009 10:06 AM, Jim Wilcoxson wrote:
> You are doing transactions here, which is a very different thing from
> normal disk I/O.  Your CPU is idle because it is waiting for the disk.
>  Your disk is idle because it is waiting for the platters to rotate
> around again.  The best case you can achieve on a 7200RPM disk is 120
> transactions (commits) per second.
> 
> In practice, you will see much lower TPS rates, and they will
> typically be 50% fractions.  For 1 sync per transaction, the max rate
> is 120 TPS.  For 2 syncs per transaction, the max rate is 60 TPS.  For
> 3 syncs per transaction, the max rate is 30 TPS.  SQLite always does
> at least 2 syncs per transaction, and sometimes 3, depending on the
> options you use, so a transaction rate of 30 TPS is reasonable.
> 
> The only way you can speed this up is to get a disk that rotates
> faster, ie, a 10K or 15K rpm drive will do faster transactions, but
> even so, you are still limited to 250 TPS with a 15K drive.  Or, get a
> battery-backup caching controller that will lie to your OS and tell it
> that the data is on the media, when really it is only stored in the
> controller's memory.  This allows the controller to combine write
> requests to increase the transaction rate.
> 
> Jim
> 
> On 6/16/09, Jens Páll Hafsteinsson <j...@lsretail.com> wrote:
>> Yes, I'm perfectly aware of this and hence I would expect the disk to be
>> sweating like hell running this test while the CPU is relatively relaxed
>> (given that sqlite is disk bound in this case and not CPU bound).
>>
>> But this is not happening; neither the disk nor the CPU are practically
>> doing anything, which is a bit strange. It's as if both the disk and the CPU
>> are waiting for each other or that sqlite is playing 'nice' behind my back
>> and giving up the CPU when it shouldn't.
>>
>> JP
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
>> Sent: 16. júní 2009 14:06
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Database inserts gradually slowing down
>>
>> Remember the implications of Moore's law and how much time has passed.
>>
>> CPU speed is much faster than memory speed.
>> Memory speed is much faster than disk access.
>>
>> This is why hardware folks play all sorts of tricks with pipelines, caches,
>> interleaving, and parallelism.
>>
>> For a single process that interacts with the HDD, the HDD will be the bottle
>> neck and the CPU will spend lots of time waiting for the rest of the machine
>> to catch up.  Even if you have a RAID system, the CPU is still much faster
>> than the bus the hard drives are on.
>>
>>
>> On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
>> <j...@lsretail.com>wrote:
>>
>>> In step 5 I execute "delete from t1" without any where clause.
>>>
>>> I haven't monitored the disk space used (does sqlite use temporary files
>>> beside the database file?) but the database file itself has been fixed in
>>> size at around 12MB (12.461.056 bytes) the whole time.
>>>
>>> The load on the disk is minimal (sustained 1MB/s) and the CPU load is
>>> about
>>> 6%, which is a bit surprising since I thought I would be putting a huge
>>> load
>>> on the computer running a loop like this. I'm not at all happy to see
>>> these
>>> low load numbers given how the test is programmed (it should practically
>>> own
>>> the machine). The database should utilize the computer much better than
>>> this.
>>>
>>> I've been running the test now for about 10 minutes using 3.6.15 and it
>>> 'seems' to be behaving as before, slowly increasing in execution time. I
>>> want to run this a bit longer to be absolutely sure and will let you know.
>>>
>>> JP
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org [mailto:
>>> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
>>> Sent: 16. júní 2009 12:15
>>> To: General Discussion of SQLite Database
>>> Subject: Re: [sqlite] Database inserts gradually slowing down
>>>
>>> How do you do step 5? Like "delete from table" or "delete from table
>>> where ..."? Do you see any degrade in disk space used by database
>>> along with slowness?
>>&

Re: [sqlite] Database inserts gradually slowing down

[Kees Nuyt]

On Tue, 16 Jun 2009 14:23:47 +, Jens Páll Hafsteinsson
 wrote:

> Yes, I'm perfectly aware of this and hence I
> would expect the disk to be sweating like hell
> running this test while the CPU is relatively
> relaxed (given that sqlite is disk bound in
> this case and not CPU bound).
>
> But this is not happening; neither the disk nor
> the CPU are practically doing anything, which
> is a bit strange. It's as if both the disk and
> the CPU are waiting for each other or that
> sqlite is playing 'nice' behind my back and
> giving up the CPU when it shouldn't.

Apart from seeks, the disk has to spin until the correct
start sector is under the head. Then it can write a database
page, perhaps a few database pages.

There are a few parameters you can use to optimize this:
- PRAGMA page_size
- PRAGMA [default_]cache_size
- the number of INSERTs per transaction
- The schema: INDEX PRIMARY KEY on the first column
  instead of a non-unique index
  (if the application allows it)
- load the database in order of index(es)

Especially a non-unique index with low cardinality has a lot
of overhead.

>JP
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Jim Wilcoxson]

You are doing transactions here, which is a very different thing from
normal disk I/O.  Your CPU is idle because it is waiting for the disk.
 Your disk is idle because it is waiting for the platters to rotate
around again.  The best case you can achieve on a 7200RPM disk is 120
transactions (commits) per second.

In practice, you will see much lower TPS rates, and they will
typically be 50% fractions.  For 1 sync per transaction, the max rate
is 120 TPS.  For 2 syncs per transaction, the max rate is 60 TPS.  For
3 syncs per transaction, the max rate is 30 TPS.  SQLite always does
at least 2 syncs per transaction, and sometimes 3, depending on the
options you use, so a transaction rate of 30 TPS is reasonable.

The only way you can speed this up is to get a disk that rotates
faster, ie, a 10K or 15K rpm drive will do faster transactions, but
even so, you are still limited to 250 TPS with a 15K drive.  Or, get a
battery-backup caching controller that will lie to your OS and tell it
that the data is on the media, when really it is only stored in the
controller's memory.  This allows the controller to combine write
requests to increase the transaction rate.

Jim

On 6/16/09, Jens Páll Hafsteinsson <j...@lsretail.com> wrote:
> Yes, I'm perfectly aware of this and hence I would expect the disk to be
> sweating like hell running this test while the CPU is relatively relaxed
> (given that sqlite is disk bound in this case and not CPU bound).
>
> But this is not happening; neither the disk nor the CPU are practically
> doing anything, which is a bit strange. It's as if both the disk and the CPU
> are waiting for each other or that sqlite is playing 'nice' behind my back
> and giving up the CPU when it shouldn't.
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
> Sent: 16. júní 2009 14:06
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> Remember the implications of Moore's law and how much time has passed.
>
> CPU speed is much faster than memory speed.
> Memory speed is much faster than disk access.
>
> This is why hardware folks play all sorts of tricks with pipelines, caches,
> interleaving, and parallelism.
>
> For a single process that interacts with the HDD, the HDD will be the bottle
> neck and the CPU will spend lots of time waiting for the rest of the machine
> to catch up.  Even if you have a RAID system, the CPU is still much faster
> than the bus the hard drives are on.
>
>
> On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
> <j...@lsretail.com>wrote:
>
>> In step 5 I execute "delete from t1" without any where clause.
>>
>> I haven't monitored the disk space used (does sqlite use temporary files
>> beside the database file?) but the database file itself has been fixed in
>> size at around 12MB (12.461.056 bytes) the whole time.
>>
>> The load on the disk is minimal (sustained 1MB/s) and the CPU load is
>> about
>> 6%, which is a bit surprising since I thought I would be putting a huge
>> load
>> on the computer running a loop like this. I'm not at all happy to see
>> these
>> low load numbers given how the test is programmed (it should practically
>> own
>> the machine). The database should utilize the computer much better than
>> this.
>>
>> I've been running the test now for about 10 minutes using 3.6.15 and it
>> 'seems' to be behaving as before, slowly increasing in execution time. I
>> want to run this a bit longer to be absolutely sure and will let you know.
>>
>> JP
>>
>> -----Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:
>> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
>> Sent: 16. júní 2009 12:15
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Database inserts gradually slowing down
>>
>> How do you do step 5? Like "delete from table" or "delete from table
>> where ..."? Do you see any degrade in disk space used by database
>> along with slowness?
>>
>> Pavel
>>
>> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
>> Hafsteinsson<j...@lsretail.com> wrote:
>> > Hi
>> >
>> > I've been running some tests against sqlite and have found that inserts
>> are gradually slowing down. Since I'm new to sqlite I might be doing
>> something wrong and thought I'd ask here if anyone has seen this before or
>> know what might be causing this.
>> >
>> > The test I'm running creates a database containing a single table (int,
>> int, varchar(100)) alo

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Yes, I'm perfectly aware of this and hence I would expect the disk to be 
sweating like hell running this test while the CPU is relatively relaxed (given 
that sqlite is disk bound in this case and not CPU bound).

But this is not happening; neither the disk nor the CPU are practically doing 
anything, which is a bit strange. It's as if both the disk and the CPU are 
waiting for each other or that sqlite is playing 'nice' behind my back and 
giving up the CPU when it shouldn't.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Adam DeVita
Sent: 16. júní 2009 14:06
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

Remember the implications of Moore's law and how much time has passed.

CPU speed is much faster than memory speed.
Memory speed is much faster than disk access.

This is why hardware folks play all sorts of tricks with pipelines, caches,
interleaving, and parallelism.

For a single process that interacts with the HDD, the HDD will be the bottle
neck and the CPU will spend lots of time waiting for the rest of the machine
to catch up.  Even if you have a RAID system, the CPU is still much faster
than the bus the hard drives are on.


On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
<j...@lsretail.com>wrote:

> In step 5 I execute "delete from t1" without any where clause.
>
> I haven't monitored the disk space used (does sqlite use temporary files
> beside the database file?) but the database file itself has been fixed in
> size at around 12MB (12.461.056 bytes) the whole time.
>
> The load on the disk is minimal (sustained 1MB/s) and the CPU load is about
> 6%, which is a bit surprising since I thought I would be putting a huge load
> on the computer running a loop like this. I'm not at all happy to see these
> low load numbers given how the test is programmed (it should practically own
> the machine). The database should utilize the computer much better than
> this.
>
> I've been running the test now for about 10 minutes using 3.6.15 and it
> 'seems' to be behaving as before, slowly increasing in execution time. I
> want to run this a bit longer to be absolutely sure and will let you know.
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: 16. júní 2009 12:15
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> How do you do step 5? Like "delete from table" or "delete from table
> where ..."? Do you see any degrade in disk space used by database
> along with slowness?
>
> Pavel
>
> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
> Hafsteinsson<j...@lsretail.com> wrote:
> > Hi
> >
> > I've been running some tests against sqlite and have found that inserts
> are gradually slowing down. Since I'm new to sqlite I might be doing
> something wrong and thought I'd ask here if anyone has seen this before or
> know what might be causing this.
> >
> > The test I'm running creates a database containing a single table (int,
> int, varchar(100)) along with an index (not unique) on the first field. I
> then perform the following operations (all records have unique data in the
> first field):
> >
> >
> > 1.   start a transaction
> >
> > 2.   insert 1000 records
> >
> > 3.   commit
> >
> > 4.   repeat steps 1-3 100 times
> >
> > 5.   delete everything from the table
> >
> > 6.   Start again at step 1
> >
> > The time taken to execute steps 1-4 increases gradually from taking
> around 16 seconds the first time to just over 28 seconds after about 2400
> iterations. To me, this is alarming since this time increase seems not to be
> asymptotic (not sure though, there is a slight curve on the graph and I
> haven't done any curve fitting) and I fear that I will end up with an
> unusable slow database after some time of use. Even if I shut down the test
> application and start again (on the same database and without re-creating
> the table), it just continues running as before it was stopped, that is,
> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
> >
> > At first I ran this for shorter periods without an index and think I saw
> the same behavior, but I have to verify that to be sure.
> >
> > I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual
> Studio 2008.
> >
> > If anyone is interested I can e-mail the collected data and the test
> program I'm using.
> >
> > Any pointers app

Re: [sqlite] Database inserts gradually slowing down

[Adam DeVita]

Remember the implications of Moore's law and how much time has passed.

CPU speed is much faster than memory speed.
Memory speed is much faster than disk access.

This is why hardware folks play all sorts of tricks with pipelines, caches,
interleaving, and parallelism.

For a single process that interacts with the HDD, the HDD will be the bottle
neck and the CPU will spend lots of time waiting for the rest of the machine
to catch up.  Even if you have a RAID system, the CPU is still much faster
than the bus the hard drives are on.


On Tue, Jun 16, 2009 at 9:52 AM, Jens Páll Hafsteinsson
<j...@lsretail.com>wrote:

> In step 5 I execute "delete from t1" without any where clause.
>
> I haven't monitored the disk space used (does sqlite use temporary files
> beside the database file?) but the database file itself has been fixed in
> size at around 12MB (12.461.056 bytes) the whole time.
>
> The load on the disk is minimal (sustained 1MB/s) and the CPU load is about
> 6%, which is a bit surprising since I thought I would be putting a huge load
> on the computer running a loop like this. I'm not at all happy to see these
> low load numbers given how the test is programmed (it should practically own
> the machine). The database should utilize the computer much better than
> this.
>
> I've been running the test now for about 10 minutes using 3.6.15 and it
> 'seems' to be behaving as before, slowly increasing in execution time. I
> want to run this a bit longer to be absolutely sure and will let you know.
>
> JP
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: 16. júní 2009 12:15
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Database inserts gradually slowing down
>
> How do you do step 5? Like "delete from table" or "delete from table
> where ..."? Do you see any degrade in disk space used by database
> along with slowness?
>
> Pavel
>
> On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
> Hafsteinsson<j...@lsretail.com> wrote:
> > Hi
> >
> > I've been running some tests against sqlite and have found that inserts
> are gradually slowing down. Since I'm new to sqlite I might be doing
> something wrong and thought I'd ask here if anyone has seen this before or
> know what might be causing this.
> >
> > The test I'm running creates a database containing a single table (int,
> int, varchar(100)) along with an index (not unique) on the first field. I
> then perform the following operations (all records have unique data in the
> first field):
> >
> >
> > 1.   start a transaction
> >
> > 2.   insert 1000 records
> >
> > 3.   commit
> >
> > 4.   repeat steps 1-3 100 times
> >
> > 5.   delete everything from the table
> >
> > 6.   Start again at step 1
> >
> > The time taken to execute steps 1-4 increases gradually from taking
> around 16 seconds the first time to just over 28 seconds after about 2400
> iterations. To me, this is alarming since this time increase seems not to be
> asymptotic (not sure though, there is a slight curve on the graph and I
> haven't done any curve fitting) and I fear that I will end up with an
> unusable slow database after some time of use. Even if I shut down the test
> application and start again (on the same database and without re-creating
> the table), it just continues running as before it was stopped, that is,
> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
> >
> > At first I ran this for shorter periods without an index and think I saw
> the same behavior, but I have to verify that to be sure.
> >
> > I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual
> Studio 2008.
> >
> > If anyone is interested I can e-mail the collected data and the test
> program I'm using.
> >
> > Any pointers appreciated.
> >
> > Thanks,
> > JP
> >
> > -
> > "What you take away is more important than what you add." Chuck Moore
> >
> > ___
> > 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
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

In step 5 I execute "delete from t1" without any where clause.

I haven't monitored the disk space used (does sqlite use temporary files beside 
the database file?) but the database file itself has been fixed in size at 
around 12MB (12.461.056 bytes) the whole time.

The load on the disk is minimal (sustained 1MB/s) and the CPU load is about 6%, 
which is a bit surprising since I thought I would be putting a huge load on the 
computer running a loop like this. I'm not at all happy to see these low load 
numbers given how the test is programmed (it should practically own the 
machine). The database should utilize the computer much better than this.

I've been running the test now for about 10 minutes using 3.6.15 and it 'seems' 
to be behaving as before, slowly increasing in execution time. I want to run 
this a bit longer to be absolutely sure and will let you know.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: 16. júní 2009 12:15
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

How do you do step 5? Like "delete from table" or "delete from table
where ..."? Do you see any degrade in disk space used by database
along with slowness?

Pavel

On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
Hafsteinsson<j...@lsretail.com> wrote:
> Hi
>
> I've been running some tests against sqlite and have found that inserts are 
> gradually slowing down. Since I'm new to sqlite I might be doing something 
> wrong and thought I'd ask here if anyone has seen this before or know what 
> might be causing this.
>
> The test I'm running creates a database containing a single table (int, int, 
> varchar(100)) along with an index (not unique) on the first field. I then 
> perform the following operations (all records have unique data in the first 
> field):
>
>
> 1.       start a transaction
>
> 2.       insert 1000 records
>
> 3.       commit
>
> 4.       repeat steps 1-3 100 times
>
> 5.       delete everything from the table
>
> 6.       Start again at step 1
>
> The time taken to execute steps 1-4 increases gradually from taking around 16 
> seconds the first time to just over 28 seconds after about 2400 iterations. 
> To me, this is alarming since this time increase seems not to be asymptotic 
> (not sure though, there is a slight curve on the graph and I haven't done any 
> curve fitting) and I fear that I will end up with an unusable slow database 
> after some time of use. Even if I shut down the test application and start 
> again (on the same database and without re-creating the table), it just 
> continues running as before it was stopped, that is, taking 28 seconds (and 
> increasing) if I stop it after 2400 iterations.
>
> At first I ran this for shorter periods without an index and think I saw the 
> same behavior, but I have to verify that to be sure.
>
> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual Studio 
> 2008.
>
> If anyone is interested I can e-mail the collected data and the test program 
> I'm using.
>
> Any pointers appreciated.
>
> Thanks,
> JP
>
> -
> "What you take away is more important than what you add." Chuck Moore
>
> ___
> 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

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

No, but I downloaded 3.6.15 and am running it through the test now. Will let 
you know the results.

JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of hiral
Sent: 16. júní 2009 12:10
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

Sorry I got it.
Did you tried with 3.6.14.2.

Thank you.
-Hiral

On Tue, Jun 16, 2009 at 5:39 PM, hiral <hiralsmaill...@gmail.com> wrote:

> Hi,
>
> Can you please let me know which version of sqlite, are you using?
>
> Thank you.
> -Hiral
>
>   On Tue, Jun 16, 2009 at 5:36 PM, Jens Páll Hafsteinsson <
> j...@lsretail.com> wrote:
>
>> Hi
>>
>> I've been running some tests against sqlite and have found that inserts
>> are gradually slowing down. Since I'm new to sqlite I might be doing
>> something wrong and thought I'd ask here if anyone has seen this before or
>> know what might be causing this.
>>
>> The test I'm running creates a database containing a single table (int,
>> int, varchar(100)) along with an index (not unique) on the first field. I
>> then perform the following operations (all records have unique data in the
>> first field):
>>
>>
>> 1.   start a transaction
>>
>> 2.   insert 1000 records
>>
>> 3.   commit
>>
>> 4.   repeat steps 1-3 100 times
>>
>> 5.   delete everything from the table
>>
>> 6.   Start again at step 1
>>
>> The time taken to execute steps 1-4 increases gradually from taking around
>> 16 seconds the first time to just over 28 seconds after about 2400
>> iterations. To me, this is alarming since this time increase seems not to be
>> asymptotic (not sure though, there is a slight curve on the graph and I
>> haven't done any curve fitting) and I fear that I will end up with an
>> unusable slow database after some time of use. Even if I shut down the test
>> application and start again (on the same database and without re-creating
>> the table), it just continues running as before it was stopped, that is,
>> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
>>
>> At first I ran this for shorter periods without an index and think I saw
>> the same behavior, but I have to verify that to be sure.
>>
>> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual
>> Studio 2008.
>>
>> If anyone is interested I can e-mail the collected data and the test
>> program I'm using.
>>
>> Any pointers appreciated.
>>
>> Thanks,
>> JP
>>
>> -
>> "What you take away is more important than what you add." Chuck Moore
>>
>> ___
>> 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

Re: [sqlite] Database inserts gradually slowing down

[Pavel Ivanov]

How do you do step 5? Like "delete from table" or "delete from table
where ..."? Do you see any degrade in disk space used by database
along with slowness?

Pavel

On Tue, Jun 16, 2009 at 8:06 AM, Jens Páll
Hafsteinsson wrote:
> Hi
>
> I've been running some tests against sqlite and have found that inserts are 
> gradually slowing down. Since I'm new to sqlite I might be doing something 
> wrong and thought I'd ask here if anyone has seen this before or know what 
> might be causing this.
>
> The test I'm running creates a database containing a single table (int, int, 
> varchar(100)) along with an index (not unique) on the first field. I then 
> perform the following operations (all records have unique data in the first 
> field):
>
>
> 1.       start a transaction
>
> 2.       insert 1000 records
>
> 3.       commit
>
> 4.       repeat steps 1-3 100 times
>
> 5.       delete everything from the table
>
> 6.       Start again at step 1
>
> The time taken to execute steps 1-4 increases gradually from taking around 16 
> seconds the first time to just over 28 seconds after about 2400 iterations. 
> To me, this is alarming since this time increase seems not to be asymptotic 
> (not sure though, there is a slight curve on the graph and I haven't done any 
> curve fitting) and I fear that I will end up with an unusable slow database 
> after some time of use. Even if I shut down the test application and start 
> again (on the same database and without re-creating the table), it just 
> continues running as before it was stopped, that is, taking 28 seconds (and 
> increasing) if I stop it after 2400 iterations.
>
> At first I ran this for shorter periods without an index and think I saw the 
> same behavior, but I have to verify that to be sure.
>
> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual Studio 
> 2008.
>
> If anyone is interested I can e-mail the collected data and the test program 
> I'm using.
>
> Any pointers appreciated.
>
> Thanks,
> JP
>
> -
> "What you take away is more important than what you add." Chuck Moore
>
> ___
> 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] Database inserts gradually slowing down

[hiral]

Sorry I got it.
Did you tried with 3.6.14.2.

Thank you.
-Hiral

On Tue, Jun 16, 2009 at 5:39 PM, hiral  wrote:

> Hi,
>
> Can you please let me know which version of sqlite, are you using?
>
> Thank you.
> -Hiral
>
>   On Tue, Jun 16, 2009 at 5:36 PM, Jens Páll Hafsteinsson <
> j...@lsretail.com> wrote:
>
>> Hi
>>
>> I've been running some tests against sqlite and have found that inserts
>> are gradually slowing down. Since I'm new to sqlite I might be doing
>> something wrong and thought I'd ask here if anyone has seen this before or
>> know what might be causing this.
>>
>> The test I'm running creates a database containing a single table (int,
>> int, varchar(100)) along with an index (not unique) on the first field. I
>> then perform the following operations (all records have unique data in the
>> first field):
>>
>>
>> 1.   start a transaction
>>
>> 2.   insert 1000 records
>>
>> 3.   commit
>>
>> 4.   repeat steps 1-3 100 times
>>
>> 5.   delete everything from the table
>>
>> 6.   Start again at step 1
>>
>> The time taken to execute steps 1-4 increases gradually from taking around
>> 16 seconds the first time to just over 28 seconds after about 2400
>> iterations. To me, this is alarming since this time increase seems not to be
>> asymptotic (not sure though, there is a slight curve on the graph and I
>> haven't done any curve fitting) and I fear that I will end up with an
>> unusable slow database after some time of use. Even if I shut down the test
>> application and start again (on the same database and without re-creating
>> the table), it just continues running as before it was stopped, that is,
>> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
>>
>> At first I ran this for shorter periods without an index and think I saw
>> the same behavior, but I have to verify that to be sure.
>>
>> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual
>> Studio 2008.
>>
>> If anyone is interested I can e-mail the collected data and the test
>> program I'm using.
>>
>> Any pointers appreciated.
>>
>> Thanks,
>> JP
>>
>> -
>> "What you take away is more important than what you add." Chuck Moore
>>
>> ___
>> 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] Database inserts gradually slowing down

[hiral]

Hi,

Can you please let me know which version of sqlite, are you using?

Thank you.
-Hiral

On Tue, Jun 16, 2009 at 5:36 PM, Jens Páll Hafsteinsson
wrote:

> Hi
>
> I've been running some tests against sqlite and have found that inserts are
> gradually slowing down. Since I'm new to sqlite I might be doing something
> wrong and thought I'd ask here if anyone has seen this before or know what
> might be causing this.
>
> The test I'm running creates a database containing a single table (int,
> int, varchar(100)) along with an index (not unique) on the first field. I
> then perform the following operations (all records have unique data in the
> first field):
>
>
> 1.   start a transaction
>
> 2.   insert 1000 records
>
> 3.   commit
>
> 4.   repeat steps 1-3 100 times
>
> 5.   delete everything from the table
>
> 6.   Start again at step 1
>
> The time taken to execute steps 1-4 increases gradually from taking around
> 16 seconds the first time to just over 28 seconds after about 2400
> iterations. To me, this is alarming since this time increase seems not to be
> asymptotic (not sure though, there is a slight curve on the graph and I
> haven't done any curve fitting) and I fear that I will end up with an
> unusable slow database after some time of use. Even if I shut down the test
> application and start again (on the same database and without re-creating
> the table), it just continues running as before it was stopped, that is,
> taking 28 seconds (and increasing) if I stop it after 2400 iterations.
>
> At first I ran this for shorter periods without an index and think I saw
> the same behavior, but I have to verify that to be sure.
>
> I'm using sqlite version 3.6.14 on Windows XP, compiled using Visual Studio
> 2008.
>
> If anyone is interested I can e-mail the collected data and the test
> program I'm using.
>
> Any pointers appreciated.
>
> Thanks,
> JP
>
> -
> "What you take away is more important than what you add." Chuck Moore
>
> ___
> 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