On Wed, Sep 1, 2010 at 2:41 PM, Taras Glek <tg...@mozilla.com> wrote:
> Hi, > Currently VACUUM takes care of sqlite-level fragmentation. Unfortunately > it does little for fs-level fragmentation since the same file is being > reused. It would be really beneficial for Mozilla performance if we > could get a vacuum/hotcopy mode. > > As I understand it, currently vacuum works by copying out the data to a > new file, then copying it back in and truncating the original db file. > > It would be really nice to instead do something like: > > copy the data to a new file > > swap the underlying filehandles to point at new file > > remove old file > > rename the new file to old name. > > This yields two benefits: > > A less fragmented db > > ~50% vacuum speedup since the data is only copied once > > Currently we can copy the data to a new file, but it is a pretty > invasive change to swap all of the current sqlite connections to the new > file. Things like prepared statements, etc need to be updated for every > single db consumer. Thus it would make sense to have this feature on the > sqlite side. > > Is this reasonable request? > If other connections have the database file open while it is being vacuumed, then on unix the other connections will still be left open on the old unlinked version of the database file and will never see the new content. And on windows, the file swapping and renaming simply is not allowed while other connections have the database files open. The work around is to modify SQLite so that it is constantly closing and reopening the database files. But that adds rather large overheads that seem likely to be much greater than any savings seen through a reduction in disk FS fragmentation. > > Thanks, > Taras > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
