On Mon, Mar 15, 2010 at 2:11 AM, Matthew L. Creech <mlcre...@gmail.com> wrote: > > I'll give this a try tomorrow on a real device with journaling off, > and see how much space it uses in /tmp with journaling turned off. >
I ran some tests on a real device with a real database, and got the following results: ======== Test 1: 250,000 entries, normal VACUUM Before the test: - 27.9 MB database - No journal - 37.5 MB free disk space By the time the journal file starts growing: - 27.9 MB database - Small journal - ~15 MB free disk space Toward the end of the operation (before failure): - 27.9 MB database - 14.6 MB journal - < 2 MB free disk space The VACUUM operation fails with a disk I/O error, presumably due to running out of space. ======== Test 2: 250,000 entries, journal_mode = OFF, VACUUM Before the test: - 27.9 MB database - 37.5 MB free disk space Toward the end of the operation: - 28.1 MB database - 10.6 MB free disk space The VACUUM operation succeeds. ======== Test 3: 250,000 entries trimmed to 100,000 entries, normal VACUUM Before the test: - 27.9 MB database - No journal - 37.5 MB free disk space By the time the journal file starts growing: - 27.9 MB database - Small journal - ~33 MB free disk space Toward the end of the operation: - 27.9 MB database - ~28 MB journal - ~5 MB free disk space Afterward: - 11.2 MB database - 54.3 MB free disk space The VACUUM operation succeeds. ======== Test 4: 250,000 entries trimmed to 100,000 entries, journal_mode = OFF, VACUUM Before the test: - 27.9 MB database - 37.5 MB free disk space Toward the end of the operation: - 28.1 MB database - 33.3 MB free disk space The VACUUM operation succeeds. ======== I never did see any temporary files, but space was obviously being taken up for a temp database, so I assume that SQLite opens a file then unlink()s it or something like that. It looks like in the normal (journaled) case, the journal file consistently grows about as large as the original database, but the extra disk space used up by the temporary table is dependent on the new database size. So Jay's estimate of disk usage: [old database size] + [new database size] + [journal file] is correct, but for the normal VACUUM case, [journal file] is basically equivalent to [old database size]. So it's really just: (2 * [old database size]) + [new database size] This means that to VACUUM a SQLite database of size X, you need at least 2X of _additional_ free disk space available. That seems rather wasteful, just looking at it as a SQLite user. Although programmatically there may be reasons for it that I'm not aware of. At any rate, I guess my immediate problem will have to be solved with a one-off patch that disables journaling and does a VACUUM, and for long-term usage I'll need to shrink the database capacity even more to account for the 3x disk space usage if I need to VACUUM again in the future. Thanks -- Matthew L. Creech _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
