On Tue, Jul 06, 2010 at 11:45:18PM +0200, Kristoffer Danielsson scratched on the wall: > > I've been reading the documentation. I've been googling and thinking. > > Still, I can't figure out the best way to determine when to run > the VACUUM-command. Note that I do NOT want to enable "auto vacuum". > > I do remember reading something about calculating empty space, > used pages etc etc. Still, no perfect answer. > > Q: How do I programmatically (through sqlite-APIs?) determine if it's > time to VACUUM a database? In general, what is the best method here?
It depends on your needs and goals. VACUUM does two things. First it recovers space. This usually is not significant unless you've just deleted a huge number of rows you are unlikely to replace with other data. Over time, most databases say roughly the same size, or they continually grow. It is also easy to get into trouble if space is so tight you need to VACUUM, because the VACUUM process can easily require free space that is 2x the database size. In other words, you can't VACUUM a 10GB DB file with only 10GB of free space, and you'll usually need something much closer to 20GB (in essence, you need free space to hold a copy of the old and a copy of the new database in addition to the original database file). The other reason to VACUUM is to defragment the database. This happens at the database page level and the file level. VACUUM will "repack" database pages to get better record storage, and it will also rearrange the file so that all the pages that belong to a database object (table, index, etc.) are in the same place. This can greatly improve scan speeds, as the I/O is better. This is more important for somewhat large databases that have a high flux. Overall, I tend to manually VACUUM files when I delete something very large, or when roughly 40% of the contents have changed. I've never done it in code. There are many applications that use utility databases (prefs, configs, and even document files) that just never VACUUM. -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
