I think I may now understand the problem we're seeing. I left out a very important piece of information: On the dbs we're seeing the increase in insert time, the average row size is large - really large. Basically we can see averages as high as 45k. This was causing overflow chains to be as high in some dbs as 14 pages long. I think it is pretty obvious why performance starts out good but then goes south. When the overflow chains are contiguous in general, all is good. But over time, with chain lengths that long, repeated updates of rows causes them to get spread all over the db. So, in other words, "no duh!"
For now, moving the page size to 32k for databases like this seems to be doing the trick. We're watching to see what happens, but performance is much better and for now seems to be holding steady. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alexey Pechnikov Sent: Wednesday, October 07, 2009 3:33 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Insert speed greatly decreasing over time Hello! Because update == delete + insert you must have fragmentation. The degree of the fragmentation is proporsional to count of replaces. So you may have problems after inserting a lot of dublicates. Are you really need to insert or update? 1. I'm using few hundread MB databases like CREATE TABLE telephony_log ( ... unique (nas_name,port,duration,origin,date_start) on conflict ignore ); Performance of "ignore" conflict resolution is more better. 2. May be unique index can be better than text PK 3. May be "insert into t select * from temp_t" can be helpful 4. You can mark records as deleted without deleting it immediate. And delete old records periodically whith vacuum after this operation. ================================== PRAGMA auto_vacuum=0; pragma default_cache_size=200000; CREATE TABLE t (k varchar(50) not null, d text not null, e datetime,is_new int default 1); create index t_k_idx on t(k); begin; CREATE TEMP TABLE temp_t (k varchar(50) primary key not null, d text not null, e datetime,is_new int default 1); insert into temp_t ... update t set is_new=0 where k in (select k from temp_t); insert into t select * from temp_t; commit; By cron: delete from t where is_new=0; vacuum; Best regards, Alexey Pechnikov. http://pechnikov.tel/ _______________________________________________ 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
