On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the
wall:
> Hi,
>
> I have a SQLite database with one large table, and I'd like to shrink
> the size of that table to free up space in the filesystem. My problem
> is that the database is (for example) 100 MB, and I have 80 MB of free
> filesystem space. I figured that I could DELETE, say, half of the
> records from the table, then VACUUM, and the VACUUM would
> [temporarily] need ~50 MB of free space for the journal (since that's
> how much real data there is).
>
> Instead, I'm finding that it needs a full 100 MB for the journal, even
> though once the VACUUM succeeds the resulting DB is only 50 MB. As a
> result, I'm stuck unable to shrink the database, since VACUUM fails
> with a disk I/O error (out of space), seemingly no matter many entries
> I remove ahead of time. I know the space is being freed, since
> "PRAGMA freelist_count" shows the expected numbers. So presumably
> this is just an artifact of the way VACUUM is implemented internally.
Are you sure it is the journal file that is growing too large?
VACUUM works by making a logical copy of the database from the
original database into a temp database. This restructures the
database and recovers space. The temp database is then copied back
to the original database using low-level page copy. This low-level
copy then truncates the original database file, recovering
filesystem space.
This also means the total space required to VACUUM a database is:
[old database size] + [new database size] + [journal file]
While I have not tested this, I was under the impression that the
journal file is very very small, as no modifications are made to the
database, other than the final low-level copy (which is not a
journaled operation).
Now, if I'm following you correctly, the numbers you gave seem to
indicate that this should work... If the old database is 100MB and
the new database is 50MB and I'm saying the journal file is small,
then 80MB free before you start should be enough.
Except... all that disk space isn't taken from the same spot. The
temp database is opened as '' (e.g. a zero-length string), which
creates it in the temporary space. Where, exactly, this is depends
on the host OS. Unix likes /var/tmp, /usr/tmp/ or /tmp, for example.
If /tmp is a different filesystem (as it often is), and if it isn't
big enough to hold the new database, you're going to get a space
error.
So... Just to verify if this really is or isn't the journal file,
you can try to turn the journal file off:
PRAGMA journal_mode = OFF;
I would do this on a test system. If the problem really is the
journal file, this should allow things to work. If you still get a
space error, we're dealing with something else.
If that doesn't work, move the temporary files to some place that has
enough space to hold the new database file. It should be OK to set
this to the same directory as the original database
PRAGMA temp_store_directory = '/path/to/database/directory';
If that filesystem has enough space to hold both the old database and
the new database, you should be able to vacuum it correctly.
Let us know how it works out.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
