pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FULL needed sometimes to prevent transaction ID wraparound?
6+ messages / 6 participants
[nested] [flat]
* VACUUM FULL needed sometimes to prevent transaction ID wraparound?
@ 2006-08-22 18:10 Marinos Yannikos <mjy@geizhals.at>
2006-08-22 19:19 ` Re: VACUUM FULL needed sometimes to prevent transaction ID wraparound? Alvaro Herrera <alvherre@commandprompt.com>
2006-08-22 19:28 ` Re: VACUUM FULL needed sometimes to prevent transaction ID wraparound? Dave Dutcher <dave@tridecap.com>
2006-08-22 22:00 ` Re: VACUUM FULL needed sometimes to prevent transaction Jeff Davis <pgsql@j-davis.com>
0 siblings, 3 replies; 6+ messages in thread
From: Marinos Yannikos @ 2006-08-22 18:10 UTC (permalink / raw)
To: pgsql-performance
Hello,
we're looking into the reason why we are getting warnings about
transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is
claiming that VACUUM without FULL cannot reassign XIDs properly when
max_fsm_pages was set too low (it says so here too, but this is rather
old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is
this true, or do we have a different issue here? We're using 8.1.3 with
a database generated on 8.1.3 (i.e. not migrated from 7.x or anything
like that).
Thanks,
Marinos
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM FULL needed sometimes to prevent transaction ID wraparound?
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
@ 2006-08-22 19:19 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2006-08-22 19:19 UTC (permalink / raw)
To: Marinos Yannikos <mjy@geizhals.at>; +Cc: pgsql-performance
Marinos Yannikos wrote:
> Hello,
>
> we're looking into the reason why we are getting warnings about
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is
> claiming that VACUUM without FULL cannot reassign XIDs properly when
> max_fsm_pages was set too low (it says so here too, but this is rather
> old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is
> this true, or do we have a different issue here? We're using 8.1.3 with
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything
> like that).
It's not true. Having shortage of FSM entries will make you lose space,
but it will be able to recycle Xids anyway.
I guess your problem is that you're not vacuuming all databases for some
reason. I'd advise to lose the -q and make sure you're not redirecting
to somewhere you can't read the log; the read the log and make sure
everything is going fine.
What's the warning anyway? Does it say that wraparound point is
nearing, or does it merely say that it is on Xid <some number here> and
you don't know how far that number actually is?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM FULL needed sometimes to prevent transaction ID wraparound?
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
@ 2006-08-22 19:28 ` Dave Dutcher <dave@tridecap.com>
2 siblings, 0 replies; 6+ messages in thread
From: Dave Dutcher @ 2006-08-22 19:28 UTC (permalink / raw)
To: 'Marinos Yannikos' <mjy@geizhals.at>; pgsql-performance
I would guess that you are not running vacuumdb as a user with permission to
vacuum the postgres or template1 databases. Try telling vacuumdb to log in
as postgres or whatever your superuser account is called.
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Marinos Yannikos
> Sent: Tuesday, August 22, 2006 1:11 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] VACUUM FULL needed sometimes to prevent
> transaction ID wraparound?
>
>
> Hello,
>
> we're looking into the reason why we are getting warnings about
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is
> claiming that VACUUM without FULL cannot reassign XIDs properly when
> max_fsm_pages was set too low (it says so here too, but this
> is rather
> old:
> http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is
> this true, or do we have a different issue here? We're using
> 8.1.3 with
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything
> like that).
>
> Thanks,
> Marinos
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM FULL needed sometimes to prevent transaction
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
@ 2006-08-22 22:00 ` Jeff Davis <pgsql@j-davis.com>
2006-08-23 12:50 ` Re: VACUUM FULL needed sometimes to prevent transaction Markus Schaber <schabi@logix-tt.com>
2 siblings, 1 reply; 6+ messages in thread
From: Jeff Davis @ 2006-08-22 22:00 UTC (permalink / raw)
To: Marinos Yannikos <mjy@geizhals.at>; +Cc: pgsql-performance
On Tue, 2006-08-22 at 20:10 +0200, Marinos Yannikos wrote:
> Hello,
>
> we're looking into the reason why we are getting warnings about
> transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is
> claiming that VACUUM without FULL cannot reassign XIDs properly when
> max_fsm_pages was set too low (it says so here too, but this is rather
> old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is
> this true, or do we have a different issue here? We're using 8.1.3 with
> a database generated on 8.1.3 (i.e. not migrated from 7.x or anything
> like that).
Usually this is caused by either:
(1) You're not vacuuming as a superuser, so it's not able to vacuum
everything.
(2) You have a long-running transaction that never completed for some
strange reason.
Hope this helps,
Jeff Davis
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM FULL needed sometimes to prevent transaction
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
2006-08-22 22:00 ` Re: VACUUM FULL needed sometimes to prevent transaction Jeff Davis <pgsql@j-davis.com>
@ 2006-08-23 12:50 ` Markus Schaber <schabi@logix-tt.com>
2006-08-23 13:24 ` Re: VACUUM FULL needed sometimes to prevent transaction Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2006-08-23 12:50 UTC (permalink / raw)
To: pgsql-performance
Hi, Jeff & all,
Jeff Davis wrote:
> (2) You have a long-running transaction that never completed for some
> strange reason.
I just asked myself whether a 2-phase-commit transaction that was
prepared, but never committed, can block vacuuming and TID recycling.
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM FULL needed sometimes to prevent transaction
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
2006-08-22 22:00 ` Re: VACUUM FULL needed sometimes to prevent transaction Jeff Davis <pgsql@j-davis.com>
2006-08-23 12:50 ` Re: VACUUM FULL needed sometimes to prevent transaction Markus Schaber <schabi@logix-tt.com>
@ 2006-08-23 13:24 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2006-08-23 13:24 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Markus Schaber wrote:
> I just asked myself whether a 2-phase-commit transaction that was
> prepared, but never committed, can block vacuuming and TID recycling.
>
Yes.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-08-23 13:24 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-22 18:10 VACUUM FULL needed sometimes to prevent transaction ID wraparound? Marinos Yannikos <mjy@geizhals.at>
2006-08-22 19:19 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-22 19:28 ` Dave Dutcher <dave@tridecap.com>
2006-08-22 22:00 ` Re: VACUUM FULL needed sometimes to prevent transaction Jeff Davis <pgsql@j-davis.com>
2006-08-23 12:50 ` Re: VACUUM FULL needed sometimes to prevent transaction Markus Schaber <schabi@logix-tt.com>
2006-08-23 13:24 ` Re: VACUUM FULL needed sometimes to prevent transaction Heikki Linnakangas <heikki@enterprisedb.com>
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