On Tue, Dec 1, 2015 at 9:12 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:
> On Mon, Nov 30, 2015 at 9:58 AM, David Kensiski <da...@kensiski.org> > wrote: > > I am working with a client who has a 9.1 database rapidly approaching XID > > wraparound. > > The hard limit at 2 billion, or the soft limit at > autovacuum_freeze_max_age? > We're approaching the hard limit -- we are about to break 1.5 billion. > They also have an exceedingly large pg_largeobject table (4217 > > GB) that has never been vacuumed. An attempt to vacuum this on a replica > > has run for days and never succeeded. > > What was slowing it down? Reading? Writing? CPU? fdatasync? Locks? > Was it run with throttling (e.g. nonzero vacuum_cost_delay) or > without? > I just talked to my colleague who had tested it and it just stopped doing anything. No cpu, no disk i/o, no apparent activity. No bueno. > > What is the throughput available on our RAID? > It's 6 drives in a RAID 10 configuration, so striped across three Seagate Barracuda drives. Theoretically we should be able to get as much as 18 Gb/s, actual mileage may vary. > > Are there creative ways to do such a vacuum with minimal impact on > > production? Even if I let the vacuum complete on the replica, I don't > think > > I can play accrued logs from the master, can I? > > No. And if you could replay the logs, I doubt it would have much of a > different impact than just running the vacuum freeze on the master > directly would. You just need to bite the bullet. > > At some point you need to read the entire table in one session, even > if that means scheduling some downtime (or degraded performance time) > in order to do it. It will also need to rewrite the entire table, but > if there are "vacuum freeze" attempted but which don't run to > completion, their partial work will lessen the amount of writing (but > not reading) the ultimately successful vacuum will need to do. So > start vacuum freeze now, and if you end up needing to cancel it at > least part of its work will not go wasted. > Unpleasant, but if that's what we have to do, we have to do it. :-( --Dave
