Canceling a vacuum freeze

From: Natalie Wenz <nataliewenz(at)ebureau(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Canceling a vacuum freeze
Date: 2016-05-23 21:29:39
Message-ID: 365F785B-D974-4210-9F00-51520BE08C07@ebureau.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Question:
When you cancel a vacuum freeze (or auto vacuum that is running because the table's max xid exceeded autovacuum_max_freeze_age) that's running on a large table, how much work is potentially lost?

I ask because I have a vacuum freeze running in single-user mode on a database that is 46 TB, 35 TB of which is one table. I recently migrated it to 9.5.2 (on a new host), but because so much of the data is in one ridiculous table, I dumped the old database, excluding that big table, with pg_dump, restored everything else using pg_restore, and then used copy statements to dump and then load the data from the big table in chunks, so I could dump and reload that table with some parallelism. I got everything migrated, and started logging live data to it. Everything went well until a few weeks later, when the auto vacuum wasn't able to keep up with our transaction load. The database shut itself down and I got it running in single-user mode and started a vacuum freeze. After a while we realized (you probably know where this is going) that the vacuum has to freeze allllllll of the rows in the 35 TB table. I'm wondering if it would be worth it to shut it down again and retune for a more aggressive vacuum. I'm under the impression that some work would probably be lost, but is there a limit to how much?

My follow up question would be what would this most aggressive vacuum tuning look like.

Many thanks,
Natalie

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2016-05-23 22:46:00 Re: Canceling a vacuum freeze
Previous Message Kevin Grittner 2016-05-23 16:49:36 Re: The problem is related to concurrent resquests