Tom Lane <tgl@sss.pgh.pa.us> wrote:
> How old were the sessions you were looking at?
Days to months old.
> If we think this is worth doing something about
> (I'm not convinced yet)
Once one knows about the issue, it's only a minor annoyance, and
that infrequently, so it's not worth anything that would take
significant effort or would cause any measurable performance hit.
> 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.
In our case there would tend to be bursts of activity on some of
these connections, and they would usually have a dribble of activity
from the monitoring system. This dribble would consist of a couple
sub-millisecond SELECT statements once every five or ten seconds.
(I have no clue whether that's frequent enough to suppress the
ProcessCatchupEvent() call.)
Is there a reasonably cheap way to check whether the backend has a
WAL file open and whether that one is the current append target? As
you point out, the need to actually close such a file would be
infrequent, and it seems as though when the need exists, it wouldn't
be a matter of *whether* it would need to be closed, just *when*.
Currently it sounds like we would often wind up doing it the next
time we try to run a query which has to flush dirty buffers, or when
the connection is being closed. It seems like those might not be
the ideal time to add the overhead of the close, so we would just
need to find someplace which isn't worse.
-Kevin
