Re: [HACKERS] Finding if old transactions are running...
In an attempt to throw the authorities off his trail, Christopher Browne [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Finding if old transactions are running...
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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Finding if old transactions are running...
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Finding if old transactions are running...
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Finding if old transactions are running...
On Thu, Feb 24, 2005 at 11:14:07AM -0500, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Finding if old transactions are running...
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] 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, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Finding if old transactions are running...
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Finding if old transactions are running...
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Finding if old transactions are running...
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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 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 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 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 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 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
