Prev: failover vs. read only queries
Next: walwriter not closing old files
From: Magnus Hagander on 9 Jun 2010 06:54
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/
 | 
Pages: 1
Prev: failover vs. read only queries
Next: walwriter not closing old files