Обсуждение: Growing pg_xlog
I seem to have some obscure problem with the WAL logs (AFAIK that's what is stored in the pg_xlog directory): 63G /var/lib/postgres/data/pg_xlog This is a bit too much wasted space for my taste. 8-/ egrep -i 'wal|checkpoint' /etc/postgresql/postgresql.conf | grep -v ^# returns: wal_buffers = 64 # min 4, typically 8KB each checkpoint_segments = 8 # in logfile segments, min 1, 16MB each The machine is running version 7.3.2r1-5 of the Debian package. Any suggestions?
Florian Weimer <fw@deneb.enyo.de> writes:
> I seem to have some obscure problem with the WAL logs (AFAIK that's what
> is stored in the pg_xlog directory):
> 63G /var/lib/postgres/data/pg_xlog
> This is a bit too much wasted space for my taste. 8-/
The only theory that comes to mind is that the automatically issued
checkpoint operations are failing before they get to the stage of
truncating the WAL. Is there anything suspicious looking in the
postmaster log?
You could also try a manual CHECKPOINT and see if it fails.
regards, tom lane
On Mon, Jul 14, 2003 at 12:55:19PM -0400, Tom Lane wrote: > Florian Weimer <fw@deneb.enyo.de> writes: > > I seem to have some obscure problem with the WAL logs (AFAIK that's what > > is stored in the pg_xlog directory): > > > 63G /var/lib/postgres/data/pg_xlog > > > This is a bit too much wasted space for my taste. 8-/ > > The only theory that comes to mind is that the automatically issued > checkpoint operations are failing before they get to the stage of > truncating the WAL. Is there anything suspicious looking in the > postmaster log? No, I'm afraid. However, the checkpoint subprocess was stuck in the "T" state (meaning that it had received SIGSTOP). 8-/ > You could also try a manual CHECKPOINT and see if it fails. Sending SIGCONT, restarting the database and issuing a CHECKPOINT command seems to have fixed it (CHECKPOINT removed the gigabytes at an amazing rate 8-). Thanks for pushing me into the right direction.
Florian Weimer wrote: >On Mon, Jul 14, 2003 at 12:55:19PM -0400, Tom Lane wrote: > > > >>Florian Weimer <fw@deneb.enyo.de> writes: >> >> >>>I seem to have some obscure problem with the WAL logs (AFAIK that's what >>>is stored in the pg_xlog directory): >>> >>> >>>63G /var/lib/postgres/data/pg_xlog >>> >>> >>>This is a bit too much wasted space for my taste. 8-/ >>> >>> >>The only theory that comes to mind is that the automatically issued >>checkpoint operations are failing before they get to the stage of >>truncating the WAL. Is there anything suspicious looking in the >>postmaster log? >> >> > >No, I'm afraid. However, the checkpoint subprocess was stuck in the "T" >state (meaning that it had received SIGSTOP). 8-/ > > > Ah, running on linux 2.4 I suppose. There is a known bug if someone 'strace's a process and the kills the strace process the traced process is stuck in the T state and needs kill -SIGCONT to continue Lefty