pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedShould autovacuum do a database wide vacuum near transaction limit?
11+ messages / 5 participants
[nested] [flat]
* Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-21 21:48 John Lister <john.lister-ps@kickstone.com>
0 siblings, 1 reply; 11+ messages in thread
From: John Lister @ 2011-01-21 21:48 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-21 23:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-01-21 23:04 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
"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 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'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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-21 23:33 John Lister <john.lister-ps@kickstone.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: John Lister @ 2011-01-21 23:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
>> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-21 23:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2011-01-21 23:40 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-21 23:42 John Lister <john.lister@kickstone.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: John Lister @ 2011-01-21 23:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> "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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 09:27 John Lister <john.lister@kickstone.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 11+ messages in thread
From: John Lister @ 2011-01-22 09:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 09:29 John Lister <john.lister-ps@kickstone.com>
parent: John Lister <john.lister@kickstone.com>
1 sibling, 0 replies; 11+ messages in thread
From: John Lister @ 2011-01-22 09:29 UTC (permalink / raw)
To: pgsql-admin
> "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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 09:36 Scott Marlowe <scott.marlowe@gmail.com>
parent: John Lister <john.lister@kickstone.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2011-01-22 09:36 UTC (permalink / raw)
To: John Lister <john.lister@kickstone.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; John Lister <john.lister-ps@kickstone.com>; pgsql-admin
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 09:46 PriceGoblin Accounts <accounts@pricegoblin.co.uk>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: PriceGoblin Accounts @ 2011-01-22 09:46 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
>> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 12:13 John Lister <john.lister-ps@kickstone.com>
0 siblings, 1 reply; 11+ messages in thread
From: John Lister @ 2011-01-22 12:13 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Should autovacuum do a database wide vacuum near transaction limit?
@ 2011-01-22 19:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Lister <john.lister-ps@kickstone.com>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2011-01-22 19:34 UTC (permalink / raw)
To: John Lister <john.lister-ps@kickstone.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-01-22 19:34 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-21 21:48 Should autovacuum do a database wide vacuum near transaction limit? John Lister <john.lister-ps@kickstone.com>
2011-01-21 23:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-21 23:33 ` John Lister <john.lister-ps@kickstone.com>
2011-01-21 23:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-21 23:42 ` John Lister <john.lister@kickstone.com>
2011-01-22 09:27 ` John Lister <john.lister@kickstone.com>
2011-01-22 09:29 ` John Lister <john.lister-ps@kickstone.com>
2011-01-22 09:36 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-22 09:46 ` PriceGoblin Accounts <accounts@pricegoblin.co.uk>
2011-01-22 12:13 Re: Should autovacuum do a database wide vacuum near transaction limit? John Lister <john.lister-ps@kickstone.com>
2011-01-22 19:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox