recovery from xid wraparound

On Tue, Oct 24, 2006 at 07:43:15AM +0100, Shane Wright wrote:

Anyway - not noticed any data loss yet and was hoping it would be such
that if all tables had been vacuumed recently (including system catalog
tables), that there would be no remaining rows that would appear to
have a future xid and so the database should be ok?

Running vacuum is the right solution, but I think you have to let it
finish. In particular, in that version a database-wide vacuum has to
complete before it will update the datfrozenxid (it's not tracked per
table).

a) is my assumption about the database being ok correct - assuming all
tables have been vacuumed recently, including catalog tables?

Should be ok, but apparently you missed one, or didn't do a database
wide vacuum.

b) is it possible to safely abort my whole table vacuum now so I can
run it at the weekend when there's less traffic?

Aborting vacuum is safe, but you have to do a database-wide vacuum at
some point.

c) if I have experienced data loss, on the assumption all the table
structure remains (looks like it does), and I have a working backup
from before the xid wraparound (I do), can I just reinsert any
detected-missing data at the application level without needing a
dump/reload?

A VACUUM will recover any data that slipped beyond the horizon less
than 1 billion transactions ago, which I think covers you completely.
The only issue is that unique indexes may be confused because new
conflicting data may have been inserted while the old data was
invisible. Only you can say if that's going to be an issue.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martin,

Thanks :)

Running vacuum is the right solution, but I think you have to let it
finish. In particular, in that version a database-wide vacuum has to
complete before it will update the datfrozenxid (it's not tracked per
table).

a) is my assumption about the database being ok correct - assuming

all

tables have been vacuumed recently, including catalog tables?

Should be ok, but apparently you missed one, or didn't do a database

wide

vacuum.

Yes, probably missed this 220 million row beast that's still running
now..

If I was to abort this vacuum, given that all other tables are vacuumed
(including system catalog tables), what's the worst case scenario? -
given that more transactions are happening on the database

If I understand correctly, it would be that some rows could disappear
from this large unvacuumed table if their xid was too old - but no other
consequence?

(fully aware that a db-wide vacuum is needed, but if it can [safely]
wait for the weekend that would be preferable)

Many thanks,

S

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 24 October 2006 10:24
To: Shane Wright
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] recovery from xid wraparound

On Tue, Oct 24, 2006 at 07:43:15AM +0100, Shane Wright wrote:

Anyway - not noticed any data loss yet and was hoping it would be such

that if all tables had been vacuumed recently (including system
catalog tables), that there would be no remaining rows that would
appear to have a future xid and so the database should be ok?

Running vacuum is the right solution, but I think you have to let it
finish. In particular, in that version a database-wide vacuum has to
complete before it will update the datfrozenxid (it's not tracked per
table).

a) is my assumption about the database being ok correct - assuming all

tables have been vacuumed recently, including catalog tables?

Should be ok, but apparently you missed one, or didn't do a database
wide vacuum.

b) is it possible to safely abort my whole table vacuum now so I can
run it at the weekend when there's less traffic?

Aborting vacuum is safe, but you have to do a database-wide vacuum at
some point.

c) if I have experienced data loss, on the assumption all the table
structure remains (looks like it does), and I have a working backup
from before the xid wraparound (I do), can I just reinsert any
detected-missing data at the application level without needing a
dump/reload?

A VACUUM will recover any data that slipped beyond the horizon less than
1 billion transactions ago, which I think covers you completely. The
only issue is that unique indexes may be confused because new
conflicting data may have been inserted while the old data was
invisible. Only you can say if that's going to be an issue.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Tue, Oct 24, 2006 at 11:06:01AM +0100, Shane Wright wrote:

If I was to abort this vacuum, given that all other tables are vacuumed
(including system catalog tables), what's the worst case scenario? -
given that more transactions are happening on the database

Only tables that havn't been vacuumed in the last billion transactions
are at risk. It's possible that if you've vacuumed that large table
recently by itself that all the data is actually safe, just the system
doesn't realise it.

Just make sure you've really covered *all* the system tables. If they
go you get really wierd results.

If I understand correctly, it would be that some rows could disappear
from this large unvacuumed table if their xid was too old - but no other
consequence?

The VACUUM would make them reappear. To truly disappear they would
have to be 3 billion transactions old. That leaves the unique index
issue I mentioned.

(fully aware that a db-wide vacuum is needed, but if it can [safely]
wait for the weekend that would be preferable)

That's risk-management. For example, all the really old tuples are
possibly near the beginning of the table, thus this current vacuum will
have fixed them already. But to get a handle on that you need to
analyse your tuple turnover and usage ratio.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn,

Thanks,

Just make sure you've really covered *all* the system tables. If they

go you

get really weird results.

I've been under the impression system tables get done first, then
user(me)-created tables after - which means my previous [aborted]
attempts at vacuuming them would have covered it, unless I'm missing
something?

(db was created by initdb, then pg_restore to load data into it,
database was then vacuumed before production work began)

I've looked at the list of catalog tables from 7.4's docs (URL below),
and all 28 have been processed in this vacuum, so presumably same order
for previous attempts:
http://www.postgresql.org/docs/7.4/static/catalogs.html

Checked with 'grep vacuuming vacuum.log | grep pg_ | grep toast -v |
sort | uniq'

Does this sound like a fair assumption?

(it is on the first database in the cluster, these aren't coming up from
other databases)

Many thanks for your help!

S

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 24 October 2006 11:50
To: Shane Wright
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] recovery from xid wraparound

On Tue, Oct 24, 2006 at 11:06:01AM +0100, Shane Wright wrote:

If I was to abort this vacuum, given that all other tables are
vacuumed (including system catalog tables), what's the worst case
scenario? - given that more transactions are happening on the database

Only tables that havn't been vacuumed in the last billion transactions
are at risk. It's possible that if you've vacuumed that large table
recently by itself that all the data is actually safe, just the system
doesn't realise it.

Just make sure you've really covered *all* the system tables. If they go
you get really wierd results.

If I understand correctly, it would be that some rows could disappear
from this large unvacuumed table if their xid was too old - but no
other consequence?

The VACUUM would make them reappear. To truly disappear they would have
to be 3 billion transactions old. That leaves the unique index issue I
mentioned.

(fully aware that a db-wide vacuum is needed, but if it can [safely]
wait for the weekend that would be preferable)

That's risk-management. For example, all the really old tuples are
possibly near the beginning of the table, thus this current vacuum will
have fixed them already. But to get a handle on that you need to analyse
your tuple turnover and usage ratio.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout <kleptog@svana.org> writes:

A VACUUM will recover any data that slipped beyond the horizon less
than 1 billion transactions ago, which I think covers you completely.
The only issue is that unique indexes may be confused because new
conflicting data may have been inserted while the old data was
invisible. Only you can say if that's going to be an issue.

I don't think there's a risk there. Uniqueness checks use SnapshotDirty
so they aren't time-sensitive.

regards, tom lane

"Shane Wright" <shane.wright@edigitalresearch.com> writes:

Just make sure you've really covered *all* the system tables.

I've been under the impression system tables get done first, then
user(me)-created tables after -

No, there's no such guarantee. A database-wide vacuum just does the
tables in the order it finds 'em in pg_class.

regards, tom lane

Tom,

Thanks

But are there just 28 (the 28 that have been vacuumed), or are there more (in 7.4).

Happy there's no guarantee, but would help to know any possible damager in my current situation,

Thanks

S

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 24 October 2006 15:23
To: Shane Wright
Cc: Martijn van Oosterhout; pgsql-general@postgresql.org
Subject: Re: [GENERAL] recovery from xid wraparound

"Shane Wright" <shane.wright@edigitalresearch.com> writes:

Just make sure you've really covered *all* the system tables.

I've been under the impression system tables get done first, then
user(me)-created tables after -

No, there's no such guarantee. A database-wide vacuum just does the tables in the order it finds 'em in pg_class.

regards, tom lane

Incidentally, how many passes of a table can vacuum make! Its currently
on its third trip through the 20Gb of indices, meaning another 7 hours
till completion [of this table]!.

Assume it only does three passes? (it chooses based on the table
continuing to be updated while vacuum is running)

S

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 24 October 2006 10:24
To: Shane Wright
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] recovery from xid wraparound

On Tue, Oct 24, 2006 at 07:43:15AM +0100, Shane Wright wrote:

Anyway - not noticed any data loss yet and was hoping it would be such

that if all tables had been vacuumed recently (including system
catalog tables), that there would be no remaining rows that would
appear to have a future xid and so the database should be ok?

Running vacuum is the right solution, but I think you have to let it
finish. In particular, in that version a database-wide vacuum has to
complete before it will update the datfrozenxid (it's not tracked per
table).

a) is my assumption about the database being ok correct - assuming all

tables have been vacuumed recently, including catalog tables?

Should be ok, but apparently you missed one, or didn't do a database
wide vacuum.

b) is it possible to safely abort my whole table vacuum now so I can
run it at the weekend when there's less traffic?

Aborting vacuum is safe, but you have to do a database-wide vacuum at
some point.

c) if I have experienced data loss, on the assumption all the table
structure remains (looks like it does), and I have a working backup
from before the xid wraparound (I do), can I just reinsert any
detected-missing data at the application level without needing a
dump/reload?

A VACUUM will recover any data that slipped beyond the horizon less than
1 billion transactions ago, which I think covers you completely. The
only issue is that unique indexes may be confused because new
conflicting data may have been inserted while the old data was
invisible. Only you can say if that's going to be an issue.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Shane Wright wrote:

Incidentally, how many passes of a table can vacuum make! Its currently
on its third trip through the 20Gb of indices, meaning another 7 hours
till completion [of this table]!.

Assume it only does three passes? (it chooses based on the table
continuing to be updated while vacuum is running)

As many passes at it needs to. It is limited by maintenance_work_mem
(in 7.4 I think it was sort_mem).

It needs to collect an array of tuple pointers, and it keeps them in
memory. When the array grows to maintenance_work_mem, it stops scanning
the table and scans the indexes, removing everything that points to
those tuple pointers. Then it goes back to scanning the table.

So the problem is that it scans the whole indexes many times. If you
increase maintenance_work_mem way up for this vacuum task, it will need
to wholly scan the indexes less times (hopefully only one), making the
process a lot faster.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Tue, Oct 24, 2006 at 03:47:52PM +0100, Shane Wright wrote:

Incidentally, how many passes of a table can vacuum make! Its currently
on its third trip through the 20Gb of indices, meaning another 7 hours
till completion [of this table]!.

Assume it only does three passes? (it chooses based on the table
continuing to be updated while vacuum is running)

It depends on how many tuples it needs to process and how much memory
you gave it (the maintainence_work_mem settings). The more memory you
give it, the less passes it needs to do...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

"Shane Wright" <shane.wright@edigitalresearch.com> writes:

Incidentally, how many passes of a table can vacuum make!

Lots, especially if the table hasn't been vacuumed in a long time...
Perhaps you should be using a higher maintenance_work_mem?
(Um, in 7.4 make that vacuum_mem.) Larger work memory translates
directly to fewer passes over the indexes.

regards, tom lane

Aw :(

Its at the default of 8Mb. The table contains 220 million rows and 6 indices. It has a few deleted rows...

If I change vacuum_mem I'll need to at least 'pg_ctl reload' - will it apply straightaway with the next vacuum query or does it need a full restart?

Does vacuum_mem need shared memory? (i.e. is it subject to the OS's limit) - have looked in the docs and googled but can't see detail on this

If I have managed to vacuum all the catalog tables, and my script has ensured all user tables other than this one have been vacuumed, then... will the first pass of vacuum on this have set the xid to FrozenXID for all rows - i.e. is the table safe?

What's the relative safety of restarting this vacuum with a bigger vacuum_mem, say at the end of the week when traffic is quieter?

Basically if its just datfrozenxid that's not updated I can live with delaying the vacuum a few days. But if things are more serious then obviously I can't wait.

Is it safe to say that if the catalog tables are ok and an individual tables has been vacuumed then its data is safe?

S

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 24 October 2006 15:52
To: Shane Wright
Cc: pgsql-general@postgresql.org; Martijn van Oosterhout
Subject: Re: [GENERAL] recovery from xid wraparound

"Shane Wright" <shane.wright@edigitalresearch.com> writes:

Incidentally, how many passes of a table can vacuum make!

Lots, especially if the table hasn't been vacuumed in a long time... Perhaps you should be using a higher maintenance_work_mem? (Um, in 7.4 make that vacuum_mem.) Larger work memory translates directly to fewer passes over the indexes.

regards, tom lane

On Tue, Oct 24, 2006 at 04:18:09PM +0100, Shane Wright wrote:

If I change vacuum_mem I'll need to at least 'pg_ctl reload' - will
it apply straightaway with the next vacuum query or does it need a
full restart?

You can control it per session I think. So you can start psql and type:

# set vacuum_mem=<huge>;
SET
# VACUUM VERBOSE;
<blah>

You don't have to change the main config, unless you want it to apply
forever. Although, 8MB is small in general so you might want to up it
anyway. But for this one-off vacuum of this large table you could give
a much larger amount of memory.

Does vacuum_mem need shared memory? (i.e. is it subject to the OS's
limit) - have looked in the docs and googled but can't see detail on
this

It's just ordinary memory. If you have a few gig to spare, you can give
it all to the vacuum.

If I have managed to vacuum all the catalog tables, and my script has
ensured all user tables other than this one have been vacuumed,
then... will the first pass of vacuum on this have set the xid to
FrozenXID for all rows - i.e. is the table safe?

Pass.

Although I think the point is that it hasn't scanned to whole table yet
because it ran out of memory...

Is it safe to say that if the catalog tables are ok and an individual
tables has been vacuumed then its data is safe?

Yes...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

"Shane Wright" <shane.wright@edigitalresearch.com> writes:

If I change vacuum_mem I'll need to at least 'pg_ctl reload' - will it apply straightaway with the next vacuum query or does it need a full restart?

reload is enough.

Basically if its just datfrozenxid that's not updated I can live with delaying the vacuum a few days. But if things are more serious then obviously I can't wait.

The question is how close to the wraparound horizon is any of your data.
We don't really know that --- the datfrozenxid provides a lower bound
but we don't know where things are in reality. Also, are you prepared
to tolerate wrong answers (missing rows) for awhile? As Martijn
mentioned, the vacuum will retrieve rows that have slid past the wrap
horizon, but they'll have been invisible to your queries meanwhile.

regards, tom lane