Re: Deleted WAL files held open by backends in Linux

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Deleted WAL files held open by backends in Linux
Date: 2009-12-01 01:18:24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There has to be something causing those sessions to touch WAL, and
>> the dirty-buffer scenario doesn't seem reliable enough.

> This is seeming fairly likely to be the cause, though. It may be a
> combination of the nightly VACUUM FREEZE ANALYZE we typically do on
> every database and the monitoring software, which would directly run
> a couple queries each five seconds or so, and would also exercise
> the software publishers on a similar schedule. My "smoking gun" is
> that the monitoring software was restarted last night after the time
> that the nightly vacuums would have completed, and its connections
> are not showing as having a deleted WAL file open today.

Hmm. If the read-only sessions are long-lived then you could expect
that the probability of having flushed a dirty block (and hence had to
write some WAL) increases over time and eventually approaches 1.
How old were the sessions you were looking at?

If we think this is worth doing something about (I'm not convinced yet)
then the answer would be to forcibly close a backend's open WAL file
in some reasonably seldom-used code path. One possibility that comes
to mind is to do it in ProcessCatchupEvent(), which will be invoked in
approximately the right circumstances: a backend that is sitting idle
for a long time within an otherwise busy system. That wouldn't be a
100% solution, because if the backend is handling a steady stream of
queries it will likely never run ProcessCatchupEvent(). But the places
that would be 100% (like transaction commit) would probably entail too
much of a performance hit from repeatedly opening and closing WAL files.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-12-01 01:33:05 Re: ProcessUtility_hook
Previous Message Bruce Momjian 2009-12-01 01:11:35 Re: ProcessUtility_hook