Re: XID wraparound with huge pg_largeobject

On 12/02/2015 09:36 AM, David Kensiski wrote:
> On Tue, Dec 1, 2015 at 1:48 PM, Roxanne Reid-Bennett <rox(at)tara-lu(dot)com
> <mailto:rox(at)tara-lu(dot)com>> wrote:
>
> On 11/30/2015 9:58 AM, David Kensiski wrote:
>> I am working with a client who has a 9.1 database rapidly
>> approaching XID wraparound. 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. (Or more accurately, never been allowed to succeed
>> because we needed to get the replica back on-line.)
>> ...
>> Any other ideas about how we can do this?
>>
> David,
>
> My gut reaction was maybe dump/restore ... but it's
> pg_largeobject. I have read the list for years and my memory
> tells me that it is a problem child in that arena. (e.g. as you
> found out w Slony...) and at 4000Gb, not something that can
> sandbox very well.
>
>
> I attempted to dump the qa system and pipe it to restore on a test
> server and it crashed qa. Doesn't a dump run as a single transaction,
> so a long running dump on a busy database accrues more and more
> resources that don't get released until it completes? Or am I missing
> something?
>
> Either way, it means several days of downtime given the size of the
> database. Not really practical.
>
>
> Because it's v9.1... and you hadn't gotten any responses (until
> Jeff)... and I had the time... I did some digging in the archives...
>
> The most promising alternate idea... Last February Adam Hooper was
> migrating to SSD, Bill Moran suggesting trying to CLUSTER
> pg_largeobject instead of VACUUM FULL. (full topic: on 2/3/2015
> entitled "VACUUM FULL pg_largeobject without (much) downtime?")
>
> CLUSTER has been referenced in the list other times to collapse
> unused space.... (sometime in 2010-2011):
>
> As a last resort this week, I'm going to get 500+GB of extra file store
> added, add a tablespace and move pg_largeobjects to this area. Then use
> CLUSTER to rebuild pg_largeobjects back in the default tablespace. This
> should fix things I hope, and if needed I'll use Cluster regularly.
>
> This is intriguing and is worth testing to see if it would work.
> Getting the space to do it on production might be tough since all
> drive slots are full and we're already using 3 TB drives. But worth
> trying in qa and if it works, then I can get creative for prod.
>
> It's "an" other idea... I've no idea whether it will work any
> better than biting the bullet and just running VACUUM FULL.
>
> other bits and pieces...
>
> In 2010, Tom suggested REINDEX then VACUUM on pg_largeobject for
> an 8.? system. That peaked my interest because we found with 9.1
> that weekly reindexing helped with performance. However the
> person who used it didn't find any performance improvement with
> his VACUUM. I think reindexing was added to VACUUM FULL in the
> 9.0 release (but would have to search the release notes to find it).
>
> I remember reading somewhere during this (but can't find the
> reference <sigh>) that an interrupted VACUUM FREEZE does capture
> "some" data, so multiples of those actually incrementally improves
> the speed of the next - but again I can't find the reference, so
> I've no idea who, when, version, and whether my memory is faulty
> or misapplied.
>
>
> I've seen this as well, and Jeff alluded to it in his post.
> Subsequent vacuums will run faster after a partial vacuum since some
> of the data has already been processed.
>
>
> There are miscellaneous improvements in the actual running of
> VACUUM FULL (and more often autovacuum) suggested through tweaking
> the vacuum parameters "vacuum_cost_delay" being a high priority
> target. Jeff's questions all point an identifying any limitations
> that are costing you time due to configuration.
>
>
> Definitely be tweaking the parameters to get the best performance if
> we have to go the vacuum route.
>
>
> Totally not part of this specific problem... You have run or know
> of vacuumlo for deleting orphaned LOs...? Might be worth running
> it before you collect your free space. [just in case you didn't
> or hadn't... twice the bang, half the pain - but only if you do it
> before collecting your free space]
>
>
> I don't think anyone has tried to vacuumlo, but another one I can test
> in qa. If it improves vacuum full performance it might be worth doing
> it in two phases.
>
> Thanks to both of you for your input! (I forgot to thank Jeff in my
> reply to him.)
>
> --Dave
>
>

Not sure if it applies in your case (large objects via bytea vs pg built
in large objects). However, maybe you can partition the table as follows:

1) create a new master table
2) create the needed partitions
3) begin migrating in the background, as you migrate each partition, via
a select * from current table, you will not be copying any dead rows
4) once all existing data is copied perform the following:
a) declare outage
b) copy remaining data
c) rename or drop the old table
d) rename the new master table to the old table name
e) end outage