On Thu, Jul 05, 2012 at 01:29:18PM +0100, Simon Slavin scratched on the wall: > > On 5 Jul 2012, at 9:34am, _ph_ <hauptma...@yahoo.com> wrote: > > > I already read your previous replies, but to revisit my scenaro: > > > > - My OS is "sensitive to fragmentation" > > - We are running with auto-vacuum enabled, so the freelist_count is usually > > small (not a good indicator) > > Ah. If you're always running auto-vacuum, then I don't think > explicitly issuing VACUUM is going to be useful at all. Don't bother.
Yes, bother. Auto-vacuum only deals with free pages. It does not do all the other things a normal vacuum does. Even if you run auto-vacuum, it is still a good idea to vacuum a very dynamic database from time to time. Auto vacuum tends to *increase* the amount of fragmentation within the file, since it moves pages around to keep the free list short. This means that pages for a given object (table, index, etc.) may be spread out across the SQLite file, which can cause extra seeks during table/index scans. OS level (filesystem) defragmentation won't help with this. http://www.sqlite.org/pragma.html#pragma_auto_vacuum Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. On the other hand, if the internal structure of the SQLite file is badly fragmented, having the file be fragmented in the filesystem isn't such a big deal. You only take the hit once. > > but fragmentation supposedly gets worse > > Fragmentation of the database file on disk is something that SQLite > can't control, so you are down to the various defragmentation > facilities (including the one built into Windows) to solve that. You can also get fragmentation inside the SQLite file, in the way that the pages are used. VACUUM is the only way to fix this, since the defrag process has to do with moving SQLite pages around inside the SQLite file. > > -We use sqlite as application data format, a typical user has dozens > > of files. > > This makes "During a support call" is not an option Yes and no. If there is some hidden menu feature to force a VACUUM, that might come in handy if you have a customer with a particularly large (or slow) file. It is easy to put in "just in case", and doesn't change the customer experience if it isn't in their face. If you're using databases as application files, I'm assuming they're not all that huge, however. If the files are moderately small (a few dozen megs or less) you might just vacuum the file every time you open it (if file updates tend to be very dynamic) or every 20th time or something. A file that's only a few megs only takes a few seconds to VACUUM. You can put up a dialog that says "Optimizing file structure...". On the other hand, a file that's only a few megs is not likely to see much of a performance boost from a VACUUM. I'd be more concerned about filesystem fragmentation than I would be about SQLite fragmentation. > You could use the shell tool to turn the database file into SQL commands, > and then back into a new database file on disk. This will both > defragment the file, and make sure it's not using unneeded space. For all intents and purposes, this is what VACUUM does. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
