Re: Vacuum full - disk space eaten by WAL logfiles - Mailing list pgsql-admin

From Tom Lane
Subject Re: Vacuum full - disk space eaten by WAL logfiles
Date
Msg-id 1756.1105466681@sss.pgh.pa.us
Whole thread Raw
In response to Re: Vacuum full - disk space eaten by WAL logfiles  ("Lee Wu" <Lwu@mxlogic.com>)
List pgsql-admin
"Lee Wu" <Lwu@mxlogic.com> writes:
> On the other hand, should not PG use fix number/amount
> of LOG files like Oracle even though there may be other activities at
> the same time?

No, it certainly should not.  Anyone who's admin'd an Oracle
installation will tell you what a PITA it is that Oracle keels over
and dies when you exceed the fixed log space allocation.

The real question here is why the log space isn't getting recycled in
a timely fashion.

> Postgres.log:
> Jan  8 20:15:52 mybox postgres[8037]: [73] LOG:  recycled transaction
> log file 00001AB100000060
> all other recycling transaction log ...
> Jan  8 20:15:52 mybox postgres[8037]: [74] LOG:  removing transaction
> log file 00001AB100000061
> all other removing transaction log ...
> Jan  8 20:17:27 mybox postgres[8213]: [13] LOG:  recycled transaction
> log file 00001AB2000000A3
> all other recycling transaction log ...
> Jan  8 20:17:42 mybox postgres[8213]: [74] LOG:  removing transaction
> log file 00001AB200000077
> all other removing transaction log ...
> Jan  8 20:25:33 mybox postgres[1602]: [13] PANIC:  ZeroFill failed to
> write /my/pg_xlog/xlogtemp.1602: No space left on device
> Jan  8 20:25:35 mybox postgres[8213]: [163] LOG:  removing transaction
> log file 00001AB2000000EC
> Jan  8 20:25:35 mybox postgres[1602]: [14-1] LOG:  statement: COPY
> table1 (domain, domain_id, customer_id, action_unspecified,
> action_unknown,
> Jan  8 20:25:35 mybox postgres[8213]: [164] LOG:  removing transaction
> log file 00001AB2000000ED
> Jan  8 20:25:35 mybox postgres[8213]: [165] LOG:  removing transaction
> log file 00001AB2000000EE
> Jan  8 20:25:35 mybox postgres[1602]: [14-2]  action_none, action_deny,
> action_fail, action_strip, action_tag, action_quarantine, action_clean,
> action_copy, action_allow,

Hmm.  You seem to have removed all the evidence about the interesting
question, which is what process 8213 (which was evidently doing a
checkpoint) was doing between 20:17:42 and 20:25:35.

Also, what postgresql.conf parameters are you using?  The mix
of "removing" and "recycling" operations seems a bit odd.

            regards, tom lane

pgsql-admin by date:

Previous
From: "Lee Wu"
Date:
Subject: Re: Vacuum full - disk space eaten by WAL logfiles
Next
From: Christian Fowler
Date:
Subject: Assimilation of these "versus" and hardware threads