How much log space does VACUUM need?
Hello all,
I'm running 7.1RC2 and have a question/problem:
I have a table which is 28150 pages in size. It has two indices
of 8001 and 9750 pages.
The filesystem on which pg_xlog resides has ~750MB free.
No other PostgreSQL work is running.
Yet, when running VACUUM ANALYZE on this table, I run out of
space.
Here's the snippet where it breaks:
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: XLogWrite: new log file created - consider increasing WAL_FILES
DEBUG: Rel booking: Pages: 28150 --> 22814; Tuple(s) moved: 146717. CPU 168.87s/229.66u sec.
FATAL 2: ZeroFill(/data00/pgdata/pg_xlog/xlogtemp.29381) failed: No space left on device
Server process (pid 29381) exited with status 512 at Thu Apr 5 23:06:52 2001
Terminating any active server processes...
Server processes were terminated at Thu Apr 5 23:06:52 2001
Reinitializing shared memory and semaphores
DEBUG: database system was interrupted at 2001-04-05 23:05:59 EDT
DEBUG: CheckPoint record at (0, 3111073188)
DEBUG: Redo record at (0, 3103812936); Undo record at (0, 2332033096); Shutdown FALSE
DEBUG: NextTransactionId: 846; NextOid: 846116
DEBUG: database system was not properly shut down; automatic recovery in progress...
DEBUG: redo starts at (0, 3103812936)
DEBUG: open(logfile 0 seg 188) failed: No such file or directory
DEBUG: redo done at (0, 3154111604)
FATAL 2: ZeroFill(/data00/pgdata/pg_xlog/xlogtemp.29468) failed: No space left on device
/opt/postgresql/bin/postmaster: Startup proc 29468 exited with status 512 - abort
It appears the VACUUM ANALYZE requires >3x the table size in
order to run?
I don't recall seeing this behaviour on my other box, but that
my just be because it has an obscene amount of free space on the
$PGDATA filesystem.
Thanks,
Gordon.
--
It doesn't get any easier, you just go faster.
-- Greg LeMond
"Gordon A. Runkle" <gar@integrated-dynamics.com> writes:
Yet, when running VACUUM ANALYZE on this table, I run out of
space.
Not sure if that should be construed as a bug or not; Vadim will have to
answer that. But here's a thought: try increasing CHECKPOINT_TIMEOUT
and/or CHECKPOINT_SEGMENTS so that you don't get intermediate
checkpoints while the vacuum proceeds. Should reduce the thing's
appetite for log space ...
regards, tom lane
In article <7843.986599087@sss.pgh.pa.us>, "Tom Lane" <tgl@sss.pgh.pa.us>
wrote:
Not sure if that should be construed as a bug or not; Vadim will have to
answer that. But here's a thought: try increasing CHECKPOINT_TIMEOUT
and/or CHECKPOINT_SEGMENTS so that you don't get intermediate
checkpoints while the vacuum proceeds. Should reduce the thing's
appetite for log space ...
Thanks Tom.� I didn't get to do anything with this until
today -- the power supply on that box fried (at least
the 12V side did), during a COPY.� I'm running ext2 as
the filesystem and even so, things came right up after
I fixed the box.� So, I'm happy with PostgreSQL's recovery
ability!� It's WAL worth the effort you put into it.
Sorry, it just came to me and I typed before I thought...
Gordon.
--
It doesn't get any easier, you just go faster.
-- Greg LeMond