SEO

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/
> From each according to his ability. To each according to his ability
> to litigate.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFPihtIB7bNG8LQkwRAh9TAJ0bzeihHBVPEAd0701qBi LzosBCbACfX4ew
CFPVVzSrClNEixoXlB/Tvu0=
=JLu7
-----END PGP SIGNATURE-----