Thread: Should autovacuum do a database wide vacuum near transaction limit?

Should autovacuum do a database wide vacuum near transaction limit?

From
"John Lister"
Date:
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
--
 
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

Re: Should autovacuum do a database wide vacuum near transaction limit?

From
Tom Lane
Date:
"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

Re: Should autovacuum do a database wide vacuum near transaction limit?

From
"John Lister"
Date:
>> 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



Re: Should autovacuum do a database wide vacuum near transaction limit?

From
Tom Lane
Date:
"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

Re: Should autovacuum do a database wide vacuum near transaction limit?

From
"John Lister"
Date:
> "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


Re: Should autovacuum do a database wide vacuum near transaction limit?

From
John Lister
Date:
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

Re: Should autovacuum do a database wide vacuum near transaction limit?

From
"John Lister"
Date:
> "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


Re: Should autovacuum do a database wide vacuum near transaction limit?

From
Scott Marlowe
Date:
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?

Re: Should autovacuum do a database wide vacuum near transaction limit?

From
"John Lister"
Date:
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


Re: Should autovacuum do a database wide vacuum near transaction limit?

From
Tom Lane
Date:
"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