X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8310F3A5A3F for ; Tue, 11 Jan 2005 18:05:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48076-01 for ; Tue, 11 Jan 2005 18:04:59 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8B27A3A56FD for ; Tue, 11 Jan 2005 18:04:59 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0BI4f6f001757; Tue, 11 Jan 2005 13:04:41 -0500 (EST) To: "Lee Wu" Cc: pgsql-admin@postgresql.org Subject: Re: Vacuum full - disk space eaten by WAL logfiles In-reply-to: References: Comments: In-reply-to "Lee Wu" message dated "Tue, 11 Jan 2005 08:53:08 -0700" Date: Tue, 11 Jan 2005 13:04:41 -0500 Message-ID: <1756.1105466681@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/63 X-Sequence-Number: 15922 "Lee Wu" 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