Обсуждение: Finding if old transactions are running...
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
> 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: You can get that from pg_stat_activity, if you have the relevant stats turned on. Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> 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:
> You can get that from pg_stat_activity, if you have the relevant stats
> turned on.
pg_stat_activity will tell you about the oldest active query, but not
about oldest open transaction.
regards, tom lane
On Thu, Feb 24, 2005 at 11:14:07AM -0500, Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > >> 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: > > > You can get that from pg_stat_activity, if you have the relevant stats > > turned on. > > pg_stat_activity will tell you about the oldest active query, but not > about oldest open transaction. You can get list of currently runing transactions from pg_locks table, but no start time... But if you can remember oldest transaction_id from the last vacuum then you got what you need: if oldest transaction still here - you not need to vacuum
Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > >> 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: > > > You can get that from pg_stat_activity, if you have the relevant stats > > turned on. > > pg_stat_activity will tell you about the oldest active query, but not > about oldest open transaction. And pg_stat_activity can lose information when the network is under heavy load too. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: >> > You can get that from pg_stat_activity, if you have the relevant stats >> > turned on. >> >> pg_stat_activity will tell you about the oldest active query, but not >> about oldest open transaction. > > And pg_stat_activity can lose information when the network is under > heavy load too. On a side note, a similar issue came up with libpqxx, in the part that deals with connections being lost while committing a transaction. The library tries to reconnect and figure out whether the commit completed or not, but it was pointed out that the commit might actually still be in progress by that time. Tom, I believe you said at the time that I should check pg_stat_activity. My current code polls it for the old backend pid. But if that is neither 100% reliable nor unconditionally available, wouldn't it be better if I just queried pg_locks for the transaction's ID? Would that work for all backend versions I can expect to see? Jeroen
jtv@xs4all.nl writes:
> Tom, I believe you said at the time that I should check pg_stat_activity.
> My current code polls it for the old backend pid. But if that is neither
> 100% reliable nor unconditionally available, wouldn't it be better if I
> just queried pg_locks for the transaction's ID? Would that work for all
> backend versions I can expect to see?
pg_locks certainly seems like a better solution. Perhaps it didn't
exist when you went with pg_stat_activity? Can't recall offhand.
Note that you still want to look for your old backend's PID; it seems
impractically expensive to keep track of the current transaction's XID.
(At a minimum that would cost another query per xact...)
regards, tom lane
> jtv@xs4all.nl writes: > pg_locks certainly seems like a better solution. Perhaps it didn't > exist when you went with pg_stat_activity? Can't recall offhand. Neither do I... But I do need something that will work with at least any recent backend version--say, 7.2 or since. The more the better, really. Any idea how old pg_locks is? > Note that you still want to look for your old backend's PID; it seems > impractically expensive to keep track of the current transaction's XID. > (At a minimum that would cost another query per xact...) Yes, I see now--I thought I had the transaction ID handy already anyway, but I didn't. Thanks. Jeroen