Re: xid wrap / optimize frozen tables?

On 5/24/15 6:42 AM, Nils Goroll wrote:
> shared_buffers = 325GB

FWIW, a lot of people report performance loss with shared buffers that
large. At a minimum, if you're going to set them that large then you
want to make sure that the OS has a bare minimum of memory in use for
it's disk cache, since everything in there will be double cached.

> query_start | 2015-05-22 19:33:52.44334+02
> waiting | f
> query | autovacuum: VACUUM public.*redacted*_y2015m04 (to prevent
> wraparound)
> query_start | 2015-05-22 19:34:02.46004+02
> waiting | f
> query | autovacuum: VACUUM ANALYZE public.*redacted*_y2015m05 (to
> prevent wraparound)

By default autovac has a cost delay > 0, which will significantly slow
down those vacuums. Normally that's what you want (so you're not
swamping the system), but in this case it's going to be problematic for you.

Also, if there's other tables that need to be frozen you should
experiment with starting those vacuums now, by hand. That means you'll
have more IO happening than you have spindles, but my experience is that
it can take a lot of extra Postgres backends to actually swamp
sequential reads on an IO system. VACUUM's reads are sequential by
nature, so you should be able to get close to the maximum theoretical
sequential read speed from your drives.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com