Thread: Should autovacuum do a database wide vacuum near transaction limit?
Hi, I'm running 8.3.9 on ubuntu with autovacuum enabled and seemingly working properly., however I've started getting messages saying that I'm near the transaction limit and I need to do a database wide vacuum, which I've started.
From reading the docs, though I thought that autovacuum would do this on my behalf, is this true? I would have expected it to run before I started getting warnings, or does it wait until the warnings start? If not, is there any config setting or other reason why autovacuum isn't doing a database wide vacuum as expected or do I always need to do a manual one?
Thanks
John
From reading the docs, though I thought that autovacuum would do this on my behalf, is this true? I would have expected it to run before I started getting warnings, or does it wait until the warnings start? If not, is there any config setting or other reason why autovacuum isn't doing a database wide vacuum as expected or do I always need to do a manual one?
Thanks
John
--
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
"John Lister" <john.lister-ps@kickstone.com> writes:
> Hi, I'm running 8.3.9 on ubuntu with autovacuum enabled and seemingly working properly., however I've started
gettingmessages saying that I'm near the transaction limit and I need to do a database wide vacuum, which I've started.
> From reading the docs, though I thought that autovacuum would do this on my behalf, is this true?
It's supposed to. Did you look around to see if AV was trying to do
anything?
It's possible you got bit by this recently-fixed bug, which caused any
large-scale autovacuum operation to take unreasonably long to complete:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master [b58c25055] 2010-11-19 22:28:20 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500
Fix leakage of cost_limit when multiple autovacuum workers are active.
When using default autovacuum_vac_cost_limit, autovac_balance_cost relied
on VacuumCostLimit to contain the correct global value ... but after the
first time through in a particular worker process, it didn't, because we'd
trashed it in previous iterations. Depending on the state of other autovac
workers, this could result in a steady reduction of the effective
cost_limit setting as a particular worker processed more and more tables,
causing it to go slower and slower. Spotted by Simon Poole (bug #5759).
Fix by saving and restoring the GUC variables in the loop in do_autovacuum.
In passing, improve a few comments.
Back-patch to 8.3 ... the cost rebalancing code has been buggy since it was
put in.
regards, tom lane
>> Hi, I'm running 8.3.9 on ubuntu with autovacuum enabled and seemingly >> working properly., however I've started getting messages saying that I'm >> near the transaction limit and I need to do a database wide vacuum, which >> I've started. >> From reading the docs, though I thought that autovacuum would do this on >> my behalf, is this true? > > It's supposed to. Did you look around to see if AV was trying to do > anything? It still seems to be running occasionally vacuuming a table, according to pg_stat_all_tables, it ran 1min ago > It's possible you got bit by this recently-fixed bug, which caused any > large-scale autovacuum operation to take unreasonably long to complete: I'll have to pull in the latest versions, which unfortunately means stopping the db, which I was trying to avoid. On another bizarre note, A database wide vacuum has just finished, but I'm still getting the warnings: GMT WARNING: database "backend" must be vacuumed within 10205310 transactions GMT HINT: To avoid a database shutdown, execute a full-database VACUUM in "backend I did vacuum verbose analyze which doesn't seem to have made a difference... doing SELECT datname, age(datfrozenxid) FROM pg_database; reports that backend has a transaction count near 2Billion but also so does template0 am I doing something wrong? Cheers John
"John Lister" <john.lister-ps@kickstone.com> writes:
> On another bizarre note, A database wide vacuum has just finished, but I'm
> still getting the warnings:
> GMT WARNING: database "backend" must be vacuumed within 10205310
> transactions
Did you do that vacuum as a superuser?
regards, tom lane
> "John Lister" <john.lister-ps@kickstone.com> writes: >> On another bizarre note, A database wide vacuum has just finished, but >> I'm >> still getting the warnings: > >> GMT WARNING: database "backend" must be vacuumed within 10205310 >> transactions > > Did you do that vacuum as a superuser? yes John
On 21/01/2011 23:40, Tom Lane wrote: > "John Lister"<john.lister-ps@kickstone.com> writes: >> On another bizarre note, A database wide vacuum has just finished, but I'm >> still getting the warnings: >> GMT WARNING: database "backend" must be vacuumed within 10205310 >> transactions > Did you do that vacuum as a superuser? Thanks for your help, but I managed to work it out using an answer you gave in another thread. I looked at which tables had a frozen xid equal to the database value and found that there were 7 temporary tables with numbers equal or very close to it. I couldn't find a way to determine which process created those tables ( - is this possible?) and therefore see how long it had been running, etc Instead I tried to vacuum them, but this didn't make any difference (or indeed do anything), so in the end I deleted the tables manually instead, which instantly reset the transaction count back to the 1billion mark. I now need to find out which process probably died due to its temp tables disappearing, again they appeared odd - single alphabetical names - which I wasn't expecting... Was this expected behaviour with temporary tables? Cheers John
> "John Lister"<john.lister-ps@kickstone.com> writes: >> On another bizarre note, A database wide vacuum has just finished, but >> I'm >> still getting the warnings: >> GMT WARNING: database "backend" must be vacuumed within 10205310 >> transactions > Did you do that vacuum as a superuser? Thanks for your help, but I managed to work it out using an answer you gave in another thread. I looked at which tables had a frozen xid equal to the database value and found that there were 7 temporary tables with numbers equal or very close to it. I couldn't find a way to determine which process created those tables ( - is this possible?) and therefore see how long it had been running, etc Instead I tried to vacuum them, but this didn't make any difference (or indeed do anything), so in the end I deleted the tables manually instead, which instantly reset the transaction count back to the 1billion mark. I now need to find out which process probably died due to its temp tables disappearing, again they appeared odd - single alphabetical names - which I wasn't expecting... Was this expected behaviour with temporary tables? Cheers John
On Sat, Jan 22, 2011 at 2:27 AM, John Lister <john.lister@kickstone.com> wrote: > Instead I tried to vacuum them, but this didn't make any difference (or > indeed do anything), so in the end I deleted the tables manually instead, > which instantly reset the transaction count back to the 1billion mark. I > now need to find out which process probably died due to its temp tables > disappearing, again they appeared odd - single alphabetical names - which I > wasn't expecting... > > Was this expected behaviour with temporary tables? It's more expected behavior when you have long running transactions. I haven't seen it caused by temp tables. Was the parent process in a really long transaction or just open a long time without one?
Apologies resending to the list as used wrong account... >> Was this expected behaviour with temporary tables? >It's more expected behavior when you have long running transactions. >I haven't seen it caused by temp tables. Was the parent process in a >really long transaction or just open a long time without one? The first thing I checked was for open transactions, but alas there were none. I suspect the process had been open a long time without creating any transactions, but don't know which process it was at this point, the connection was owned by my colleague so need to check with him or look for dead applications... John
"John Lister" <john.lister-ps@kickstone.com> writes:
>>> Was this expected behaviour with temporary tables?
>> It's more expected behavior when you have long running transactions.
>> I haven't seen it caused by temp tables. Was the parent process in a
>> really long transaction or just open a long time without one?
> The first thing I checked was for open transactions, but alas there were
> none. I suspect the process had been open a long time without creating any
> transactions, but don't know which process it was at this point, the
> connection was owned by my colleague so need to check with him or look for
> dead applications...
It's possible for temp tables to remain behind after a backend crash.
Not clear if that's your situation or not, but it does happen.
Such tables will get cleaned out whenever the owning pg_temp_nnn schema
is next used to hold temp tables --- but if it's a high-numbered schema
that might not happen for a long time. Also, in 8.4 and up, autovacuum
will forcibly drop orphaned temp tables once they get old enough to
start creating xid-wraparound issues.
regards, tom lane
Re: Should autovacuum do a database wide vacuum near transaction limit?
From
"PriceGoblin Accounts"
Date:
>> Was this expected behaviour with temporary tables? >It's more expected behavior when you have long running transactions. >I haven't seen it caused by temp tables. Was the parent process in a >really long transaction or just open a long time without one? The first thing I checked was for open transactions, but alas there were none. I suspect the process had been open a long time without creating any transactions, but don't know which process it was at this point, the connection was owned by my colleague so need to check with him or look for dead applications... John