769121 - Preempt wraparound autovacuum on Socorro production

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[[:jberkus] Josh Berkus]

Matt,

So we're getting pretty close to needing to vacuum many of socorro's biggest tables due to XID wraparound.  If we leave this up to autovacuum, then it's liable to kick in in the middle of the day and make socorro unresponsive.  So we need to take two steps to avoid this *within the next week*, preferably within the next few days.

(1) Increase autovacuum_freeze_max_age to 1billion (1000000000).  This is about our maximum safe limit.

(2) write a script which vacuums the oldest tables during the night/weekend to preempt autovacuum.  You can get the oldest tables by running:

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';

anything over 300,000,000 should probably be vacuumed.  You'd do this by:

SET vacuum_freeze_min_age = 10000;
SET vacuum_freeze_table_age = 0;
VACUUM <tablename>.

... for each table over 300,000,000.

We should probably script (2) so that we can do it a couple times a year.

Comment 1

[[:jberkus] Josh Berkus]

Oh, also:

(3) we should have a nagios probe on wraparound.  I'd swear we installed one when we set up PHX; can you check on it?

Comment 2

[Matt Pressman [:mpressman]]

I increased the autovacuum_freeze_max_age on prod during the purge downtime.
So #1 has been completed.

Comment 3

[Matt Pressman [:mpressman]]

I'll be running this tonight after hours ~6:00PST

Comment 4

[Matt Pressman [:mpressman]]

there are currently 176 tables that will be vacuumed.

Comment 5

[Matt Pressman [:mpressman]]

running vacuum now

Comment 6

[Matt Pressman [:mpressman]]

I had the vacuuming cut off at 2am for the batch jobs, I'll check the output to see how far we got and post it shortly

Comment 7

[Matt Pressman [:mpressman]]

so 88 of the 176 were vacuumed, half - I will kick off the rest so it will finish tonight

Comment 8

[Matt Pressman [:mpressman]]

kicking off the next round of vacuuming.

Comment 9

[Matt Pressman [:mpressman]]

vacuuming completed for all 176 rels