Re: XID wraparound with huge pg_largeobject

On 12/2/15 11:18 AM, Jeff Janes wrote:
> Is there a lot of free space in pg_largeobjects table (i.e. recently
> ran vacuumlo)? I wonder if it weren't doing a very slow backwards
> scan over the table in order to truncate away unused space. The
> problem is that the backwards scan might not trigger the kernels
> read-ahead code, so every page is read in as a random IO, rather than
> sequential IO. This can look a lot like doing nothing, depending on
> what monitoring tools you use. the disk is always busy, but just in a
> horribly inefficient way.
>
> Assuming you are on minor release 9.1.10 or later, if this is the case
> you should be able to just have another session do a `lock TABLE
> pg_largeobject in access share mode;` and hold the lock for a while.
> This will cause the vacuum to abandon the truncation scan and finish
> up the accounting for the freezing. You can then worry about
> finishing up the truncation once the wrap-around danger is over.

Another option would be to manually read in the heap files at the OS
level so they're at least in the kernel cache. Looking at the "max()" of
the ctid column in the table would give you an idea of what's going on
here. (I put max() in quotes because there aren't real operators on
ctid. I suspect the easiest way to simulate this would be to create a
compound type (block int, lp smallint), cast ctid to that and then do
max(block*2^16+lp).)

I would *definitely* at least start a vacuum freeze on the table. You
really don't want to get backed into a corner on this.

Something else to keep in mind is that a full-scan vacuum (which is what
this needs to be) will block on a block if any other process has a pin
on it (and by doing so, block anyone else from getting a pin on that
block until it's done). If you have other processes hitting this table
heavily that could cause a problem. Unfortunately, I don't think this
locking would show up in pg_locks, so it's hard to diagnose. (BTW, I
think the logic to optionally skip this lock was added in 9.2, in which
case *any* vacuum you run could run into this problem).

You might want to watch the talk I gave at pgCon about the details of
how vacuum works. Note that's all based on 9.4 though, so YMMV.
http://www.pgcon.org/2015/schedule/events/829.en.html
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com