On Sat, May 23, 2015 at 6:46 AM, Nils Goroll <slink@schokola.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.
