Manual anti-wraparound vacuums
In another thread, Alvaro quoted from the manual:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:
WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".
(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.)
It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there? Perhaps manual
VACUUMs should not have the page skip behavior at all?
regards, tom lane
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In another thread, Alvaro quoted from the manual:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.)It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?
I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of vie nov 11 12:29:42 -0300 2011:
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In another thread, Alvaro quoted from the manual:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.)It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.
Hmm, so should the manual suggest using VACUUM FREEZE?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In another thread, Alvaro quoted from the manual:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.)It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?
I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.
That doesn't address my concern. (1) The manual does not say you must
use VACUUM FREEZE for this, nor do the HINT messages. (2) You probably
wouldn't want to use VACUUM FREEZE, as that could force a great deal
more I/O than might be necessary to fix the problem. (3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years. And there's also (4) if someone is doing a
manual VACUUM, they might well wish the table to be completely vacuumed,
not just sort of.
I think we'd be better advised to restrict the page-skipping behavior
to autovacuums, period, and remove the connection to FREEZE.
regards, tom lane
On Nov 11, 2011, at 10:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
In another thread, Alvaro quoted from the manual:
If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's oldest
XIDs reach ten million transactions from the wraparound point:WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".(A manual VACUUM should fix the problem, as suggested by the hint; but
note that the VACUUM must be performed by a superuser, else it will fail to
process system catalogs and thus not be able to advance the database's
datfrozenxid.)It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.That doesn't address my concern. (1) The manual does not say you must
use VACUUM FREEZE for this, nor do the HINT messages. (2) You probably
wouldn't want to use VACUUM FREEZE, as that could force a great deal
more I/O than might be necessary to fix the problem. (3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years. And there's also (4) if someone is doing a
manual VACUUM, they might well wish the table to be completely vacuumed,
not just sort of.
If relfrozenxid needs advancing, that also prevents pages from being skipped. So I think there's no problem here. We are just making a very minor extension of a behavior that has existed since 8.4.
...Robert
On 11.11.2011 17:47, Tom Lane wrote:
Robert Haas<robertmhaas@gmail.com> writes:
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.That doesn't address my concern. (1) The manual does not say you must
use VACUUM FREEZE for this, nor do the HINT messages. (2) You probably
wouldn't want to use VACUUM FREEZE, as that could force a great deal
more I/O than might be necessary to fix the problem.
set vacuum_freeze_table_age=0; VACUUM;
will do the trick.
(3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years.
True.
And there's also (4) if someone is doing a
manual VACUUM, they might well wish the table to be completely vacuumed,
not just sort of.I think we'd be better advised to restrict the page-skipping behavior
to autovacuums, period, and remove the connection to FREEZE.
Yeah, I think you're right. We can accomplish that by setting the
default vacuum_freeze_age to 0. That way it's still possible to get the
page-skipping behavior in manual VACUUMs by setting it to non-zero, but
you get a full scan by default.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On 11.11.2011 19:15, Heikki Linnakangas wrote:
On 11.11.2011 17:47, Tom Lane wrote:
(3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years.True.
On second thought, if XID wraparound is close enough that the DBA has to
log in to do manual vacuums to avoid it, relfrozenxid of the
trouble-making tables are surely older than default
vacuum_freeze_table_age, so plain VACUUM is enough to scan the whole table.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
On second thought, if XID wraparound is close enough that the DBA has to
log in to do manual vacuums to avoid it, relfrozenxid of the
trouble-making tables are surely older than default
vacuum_freeze_table_age, so plain VACUUM is enough to scan the whole table.
OK, good. I think we're creating real hazards if anything but the
plainest form of VACUUM is required in this scenario. It seems like
we're safe at the moment, but maybe these considerations should be
documented in the code somewhere, so we don't break the case
accidentally in future.
regards, tom lane
Heikki Linnakangas wrote:
On 11.11.2011 17:47, Tom Lane wrote:
Robert Haas<robertmhaas@gmail.com> writes:
On Fri, Nov 11, 2011 at 9:59 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
It occurs to me to wonder how this scenario will interact with the
recent changes to let VACUUM skip pages. AFAIR there is not a way for a
manual VACUUM to set the anti-wraparound mode, is there?I tweaked Simon's original patch to address exactly this scenario;
VACUUM FREEZE prevents page-skipping behavior.That doesn't address my concern. (1) The manual does not say you must
use VACUUM FREEZE for this, nor do the HINT messages. (2) You probably
wouldn't want to use VACUUM FREEZE, as that could force a great deal
more I/O than might be necessary to fix the problem.set vacuum_freeze_table_age=0; VACUUM;
will do the trick.
(3) In disaster
recovery scenarios, the last thing we want is to be imposing extra
conditions on what an already-stressed DBA has to do to fix things;
especially extra conditions that are different from the way it's worked
for the last ten years.True.
And there's also (4) if someone is doing a
manual VACUUM, they might well wish the table to be completely vacuumed,
not just sort of.I think we'd be better advised to restrict the page-skipping behavior
to autovacuums, period, and remove the connection to FREEZE.Yeah, I think you're right. We can accomplish that by setting the
default vacuum_freeze_age to 0. That way it's still possible to get the
page-skipping behavior in manual VACUUMs by setting it to non-zero, but
you get a full scan by default.
FYI, pg_upgrade needs to use vacuum freeze to remove all references to
pg_clog because the files are coming from the old cluster. If we change
that, pg_upgrade needs to be adjusted.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +