Обсуждение: walwriter not closing old files

I've just applied the attached file to the walwriter, to solve a case
when it keeps handles around to old xlog segments, preventing them
from actually being removed, and as such also causing alerts in some
monitoring systems. The way to provoke the problem is:

1. Do something that makes the walwriter active. For example, open a
transaction, do something, wait a while, do something, commit.
2. Now feed the system a steady state of *small*, short-running
transactions. It's easier to provoke if you just do a simple insert
and then pg_switch_xlog(), but it can be done with a regular stream of
inserts. The important thing is that the updates must be short-lived
enough that walwriter *doesn't* trigger to flush anything out. If
you're unlucky (or lucky) you'll hit a walwriter run anyway, in which
case you just repeat the test.

This will leave walwriter holding the old segment open. If your system
*only* does these small and fairly quick transactions, it'll keep the
file open "forever". This is obviously only likely to happen on
lightly loaded systems, but it does keep the file from being properly
removed.

The attached patch will close the open xlog file if it's no longer in
use. The check runs only if there was nothing for walwriter to do - if
it had something to do, the regular xlog routines will switch the file
for us.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander  wrote:
> I've just applied the attached file to the walwriter, to solve a
> case when it keeps handles around to old xlog segments, preventing
> them from actually being removed, and as such also causing alerts
> in some monitoring systems.
Thanks!  I wasted some time on these a while back; I'm sure this will
save others that kind of bother.
> The way to provoke the problem is:
The way I ran into it was to have a web application which only ran
read-only transactions.  Sooner or later it would need to write a
page from the buffer to make space to read a new page, and then it
would forever be holding a WAL file open, even after it was deleted.
Previous thread on the topic starts here:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php
continuing here:
http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php
Resulting in a TODO listed with this description:
Close deleted WAL files held open in *nix by long-lived read-only
backends
-Kevin

On Wed, Jun 9, 2010 at 14:04, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Magnus Hagander  wrote:
>
>> I've just applied the attached file to the walwriter, to solve a
>> case when it keeps handles around to old xlog segments, preventing
>> them from actually being removed, and as such also causing alerts
>> in some monitoring systems.
>
> Thanks!  I wasted some time on these a while back; I'm sure this will
> save others that kind of bother.
>
>> The way to provoke the problem is:
>
> The way I ran into it was to have a web application which only ran
> read-only transactions.  Sooner or later it would need to write a
> page from the buffer to make space to read a new page, and then it
> would forever be holding a WAL file open, even after it was deleted.
>
> Previous thread on the topic starts here:
>
> http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php
>
> continuing here:
>
> http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php
>
> Resulting in a TODO listed with this description:
>
> Close deleted WAL files held open in *nix by long-lived read-only
> backends

That seems to be about the same, but not actually fixed by this one.
This fix *only* fixes it when this happens in the walwriter. Not in
regular backends. OTOH, this happened in non-readonly mode :) My
understanding is that the issue you're talking about happens with the
regular backends, not walwriter, right?

-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

On 09/06/10 15:04, Kevin Grittner wrote:
> Magnus Hagander  wrote:
>> The way to provoke the problem is:
>
> The way I ran into it was to have a web application which only ran
> read-only transactions.  Sooner or later it would need to write a
> page from the buffer to make space to read a new page, and then it
> would forever be holding a WAL file open, even after it was deleted.
>
> Previous thread on the topic starts here:
>
> http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php
>
> continuing here:
>
> http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php
>
> Resulting in a TODO listed with this description:
>
> Close deleted WAL files held open in *nix by long-lived read-only
> backends

This patch only helps with walwriter, though, not backends. Your 
scenario is probably even more common, but will need a different fix.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com