I'd also be interested in a VACUUM TO feature, more for performance aspect than the fragmentation, although that's a plus too. The backup program I'm working on packs many files into archives, which are SQLite databases. I have run some vacuum tests here; the filesystem cache was purged before each test:
cp 1GB archive: 44 seconds (for baseline comparison) Vacuum 1GB archive w/sqlite3: real 2m15.421s user 0m8.776s sys 0m34.205s Dump and reload 1GB archive: $ time sqlite3 arc.0.0.rm ".dump"|sqlite3 arc.0.0.new real 0m52.174s user 0m23.750s sys 0m9.086s Creating a new archive is more than twice as fast as doing a vacuum on an existing archive, and nearly as fast as a straight cp. While an extra minute and a half for SQLite vacuum may not seem like a big deal, a backup retention operation could affect many archives. So 30 archives would require an extra 45 minutes to vacuum. I've had to add code to the backup program to avoid doing vacuums whenever possible because they're slow. I would suggest the VACUUM TO feature takes a read lock on the database and creates a new, vacuumed database, but leaves it up to the application whether to replace the original or not. If the application decides to do a rename over the original database, then yes, this could goof up other connections, but that could happen anyway if an app decided to delete a database: the other connections would keep on using the database, even though it is unlinked from the filesystem. For my single-user application, VACUUM TO would be very useful. In multi-connection cases, the app would have to provide some kind of mechanism outside SQLite to coordinate things, or just stick to the regular vacuum operation. Jim -- HashBackup: easy onsite and offsite Unix backup http://sites.google.com/site/hashbackup On Thu, Sep 9, 2010 at 7:19 PM, Taras Glek <tg...@mozilla.com> wrote: > On 09/01/2010 11:41 AM, Taras Glek 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? > > > > Thanks, > > Taras > I sent this last week, wanted to address some issues that were raised > about my proposal. > > Sorry for the weird(and late!) reply, I'm subscribed via a digest(would > be so nice to have a gmane mirror), so I can't reply directly. > > Richard Hipp: > > 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. > > > > > I agree with both points. A copying VACUUM should specify that it does > not support the multi-connection usecase. It fail abort if it detects > another db connection( or have this mentioned in documentation if this > detection isn't possible). > The wins from avoiding disk fragmentation+copying less data are > significant. Punting seems like a reasonable alternative to forcing > sqlite to constantly close/open the db. > > > Jay A. Kreibich: > > > You're also breaking transactional integrity. You need the option of > > backing-out of the operation right up until the moment it works, and > > this procedure can't do that. For example, if you lose power right > > after "remove old file", your database is no longer there. > > > You are right my original sequence of events was flawed, it should be: > > copy the data to a new file > > swap the underlying filehandles to point at new file > > rename the new file to old name(this also removes old file). > > This takes care of the "remove old file" problem. > > > You'd have to do that anyways, for all connections other than the one > > that issued the VACUUM command. Coordinating FD swaps across multiple > > connections in the same process would be confusing enough-- there > > would be no possible way to do it across database connections in > > multiple processes. > As I said above, I think restricting this to single-connection usecases > is reasonable. > > > Maybe there would be some way to pre-populate the rollback journal > > with the full contents of the original database. Then the file could > > be truncated before the copy-back procedure. That would make it > > clear to the OS that it is free to allocate whatever file blocks it > > wants, hopefully in better patterns. The copy back could also be > > done in very large chunks. > That sounds like a reasonable alternative to me. It does more IO than > copy+rename, but I'm mostly interested in avoiding fragmentation here > and this solve that issue. > > > On a personal level, I don't think it is worth it. In the end, you're > > still hoping the OS and filesystem will make smart choices about block > > allocations. An application shouldn't need to be spending a lot > > of time worrying about this level of filesystem performance. No > > matter what, you're just hinting and setting up conditions that > > should allow the filesystem driver to do something smart and fast. > > It may, or it may not, actually do so. > I appreciate your feeling on the matter. But there are APIs(ie > fallocate) that are specifically designed to minimize fragmentation. The > underlying filesystem can not be expected to guess every possible > workload correctly. > > Scott Hess: > > I agree with Jay - while it is tempting to have SQLite bite off > > optimizing this kind of thing, it's pretty far out of scope. Next > > we'll be talking about running SQLite on raw partitions! The recent > > addition of SQLITE_FCNTL_CHUNK_SIZE is probably about all the hinting > > you can productively manage. > > > > [In the end, VACUUM itself is kind of a hack, I sometimes suspect that > > more than half of the time when it is being used it is probably as a > > result of a micro-benchmark finding, and in the long term may actually > > be causing more harm than good.] > I'm not suggesting raw partitions, though they would not have these icky > fragmentation issues :) > I strongly disagree about VACUUM is causing more harm then good. A > heavily fragmented db (either on sqlite level or fs level) causes > noticeably/measurably more sluggish Firefox performance. > SQLITE_FCNTL_CHUNK_SIZE solves a different problem. > > SQLITE_FCNTL_CHUNK_SIZE: grow the database in a way that minimizes > fragmentation. > > Copying VACUUM: Take an existing db and minimize fragmentation. > > The two features nicely complement each other. > > To Ben Danper: Indeed. > > > To summarize: This wont work for all scenarios, but it would be > immensely useful for the single-connection responsive-io-addicted > use-case that is Firefox. > > Thanks, > Taras > _______________________________________________ > 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
