There is a cleanup loop that (commonly) runs every 10 minutes or so
and vacuums the tables that are used by the replication application.
If there is some long-running transaction kicking around, this will
keep that from actually cleaning things out.
Consider the scenario where the system is pretty busy because of that
.. Add in that plenty of updates are going in ...
And so you have a very busy system.
Now add insult to injury in view that the VACUUM adds to the load but
doesn't actually accomplish anything useful because the lingering old
transaction keeps any tuples from being vacuumed out.
The obvious question: Why bother with the VACUUM? Why don't we just
skip it (or do an ANALYZE instead; cheaper, and at least improves the
Alas and alack, the only place I can think of offhand where I can
determine any "global" information on the age of transactions on the
system is to look at pg_stat_activity, and that provides only pretty
limited information, and that only if query monitoring is turned on.
It sure would be nice to be able to have a way to query the start time
of the eldest transaction on the system. If that could be done at a
not-too-high cost, it would be eminently helpful for various sorts of
maintenance processes so that you could assortedly:
a) Be able to know that I should do an ANALYZE rather than wasting
system resources on a futile VACUUM;
b) Find a PID that is misbehaving by running transactions that run
9 hours contrary to production policy, and trace it back to
the client so you can then "THWACK!" them.
I could live with less than perfection, as long as I don't get false
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
"A army's effectiveness depends on its size, training, experience and
morale, and morale is worth more than all the other factors combined."
-- Napoleon Bonaparte
pgsql-hackers by date
|Next:||From: Michael Fuhr||Date: 2005-02-24 03:39:40|
|Subject: Re: problem with function loading|
|Previous:||From: Tom Lane||Date: 2005-02-24 01:36:07|
|Subject: Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c |