Re: XID wraparound with huge pg_largeobject

[CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx>]

On 12/02/2015 09:36 AM, David Kensiski wrote:

On Tue, Dec 1, 2015 at 1:48 PM, Roxanne Reid-Bennett <rox@xxxxxxxxxxx> 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