Re: xid wrap / optimize frozen tables?

On Sat, May 23, 2015 at 6:46 AM, Nils Goroll <slink(at)schokola(dot)de> wrote:

> Hi,
>
> as many before, I ran into the issue of a postgresql database (8.4.1)
> - committing many transactions
> - to huge volume tables (3-figure GB in size)
> - running the xid wrap vacuum (to freeze tuples)
>
> where the additional read IO load has negative impact to the extent of the
> system becoming unusable.
>

(9.4.1 noted)

Are you sure it is the read IO that causes the problem? It should be
pretty light, as it would mostly be satisfied by read-ahead (unless you
have GIN indexes) and for pages that aren't automatically prefetched, it
just waits patiently for the requested data to arrive. (As opposed to
writing, in which it runs around dirtying things that other processes need,
clogging up their IO rather than just its own).

What monitoring techniques do you use to determine the source of the
slowdown?

> Besides considering the fact that this can be worked around by exchanging
> printed sheets of paper or plastic (hello to .au) for hardware, I'd very
> much
> appreciate answers to these questions:
>
> * have I missed any more recent improvements regarding this problem? My
> understanding is that the full scan for unfrozen tuples can be made less
> likely
> (by reducing the number of transactions and tuning the autovac), but that
> it is
> still required. Is this correct?
>
> * A pretty obvious idea seems to be to add special casing for "fully frozen
> tables": If we could register the fact that a table is fully frozen (has
> no new
> tuples after the complete-freeze xid), a vacuum would get reduced to just
> increasing that "last frozen" xid.
>
> It seems like Alvaro Herrera had implemented a patch along the lines of
> this
> idea but I fail to find any other references to it:
>
> http://grokbase.com/t/postgresql/pgsql-hackers/0666gann3t/how-to-avoid-transaction-id-wrap#200606113hlzxtcuzrcsfwc4pxjimyvwgu
>
> Does anyone have pointers what happened to the patch?
>

I don't know happened to that, but there is another patch waiting for
review and testing:

https://commitfest.postgresql.org/5/221/

Cheers,

Jeff