Обсуждение: Finding if old transactions are running...

I was thinking about one of the "pathological cases" where Slony-I
behaves badly, and had an optimization thought...

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
long-running transaction...

... 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
stats...)?

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.

[Wishful thinking...]

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
itback to   the client so you can then "THWACK!" them.
 

I could live with less than perfection, as long as I don't get false
positives...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/x.html
"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

In an attempt to throw the authorities off his trail, Christopher Browne <cbbrowne@acm.org> transmitted:
> It sure would be nice to be able to have a way to query the start
> time of the eldest transaction on the system.

I can see this function available in the backend:
  TransactionId GetOldestXmin(bool alldbs);

The cost is based on walking thru each backend process, which I guess
is obvious, as if there are 47 backends, that means 47 xids.
Presumably not _too_ expensive; certainly something that has to be run
every time a vacuum is requested.

Is there a way to expose this?  Without "leaping into grand evil?"
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
If you're not part of the solution, you're part of the precipitate.

I can see this function available in the backend:
  TransactionId GetOldestXmin(bool alldbs);

The cost is based on walking thru each backend process, which I guess
is obvious, as if there are 47 backends, that means 47 xids.
Presumably not _too_ expensive; certainly something that has to be run
every time a vacuum is requested.

Is there a way to expose this?
-- 
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/languages.html
If you're not part of the solution, you're part of the precipitate.