Thanks Clemens, comments below: > -----Original Message----- > From: [email protected] [mailto:sqlite-users- > [email protected]] On Behalf Of Clemens Ladisch > Sent: 09 November 2012 12:26 > To: [email protected] > Subject: Re: [sqlite] Anomalously slow performance on updates to early > entries in a DB > > O'Toole, Eamonn wrote: > > We are seeing a performance anomaly on updates to existing object > > records in the SQLite DB. If the container DB is sufficiently large > > (about 10 million objects, 3.3GB) then the time to update records at > > the beginning of the database by order of entry is anomalously high. > > Does VACUUM change anything? [O'Toole, Eamonn] It improves the times, but there is still a very large performance penalty on updates to the beginning of the db. > > > The time is particularly bad for the first approx. 100K records, is > > somewhat better for the next 900K records, and settles down to > > a consistent average from approx 1 million records on. If this > > consistent average time is around 7 seconds for 10,000 updates, then > > we see times of about 170 seconds for 10,000 updates on the first > 100K > > records. We don't see this anomalously high update time if we start > > the updates after the first 1 million records. > > > > Note that table updates are performed by first DELETEing the entry > and > > then INSERTing the changed entry. > > Is it the DELETE or the INSERT that is slow, or both? [O'Toole, Eamonn] Good question - I don't know > > Are you addressing the records by ROWID or by name? [O'Toole, Eamonn] By name > > > Note that Chunky_Ks (IRC) has suggested that B-tree rebalancing could > > be the fundamental issue. > > How big are the records, compared to the page size? > (And what is the output of sqlite3_analyzer?) [O'Toole, Eamonn] I'll post the sqlite_analyzer output in a separate mail. > > What kind of updates are these? If you're changing many values in the > 'deleted' column in the same way, it might be possible that the > ix_object_deleted_name index needs to be rebalanced. [O'Toole, Eamonn] In our tests we change just the "created_at" field in the db entry. However, more commonly the "size" and "etag" (md5sum) fields would also change. > > > CREATE TRIGGER object_update BEFORE UPDATE ON object > > BEGIN > > SELECT RAISE(FAIL, 'UPDATE not allowed; DELETE and > INSERT'); > > END; > > Couldn't you implement this by running the code of both DELETE and > INSERT triggers for the old and new records? (This should avoid the > need to rebalance the object table.) > > [O'Toole, Eamonn] Good point. On the face of it, I think that you're correct. I'll try this and see.
> Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
