Re: Unexplained Major Vacuum Archive Activity During Vacuum

On 1 Nov 2012, at 17:44, Shaun Thomas wrote:

> On 11/01/2012 11:40 AM, Alban Hertroys wrote:
>
>> Instead of attempting to postpone freeze until beyond the life
>> expectancy of our universe, what you probably should have done is
>> vacuum more often so that vacuum has less work to do.
>
> More often than every night, with autovacuum running in the background to get regular stuff that happens during the day? 650M transactions is 3 or 4 days for us. That's hardly the lifetime of the universe. And since I didn't modify vacuum_freeze_table_age, any table vacuumed after 150M transactions is given a vacuum freeze anyway. No harm done.

150M database transactions a day sounds excessive, is there no way to reduce that number?

That aside, 650M transactions in 3 at 4 days is not equal to 150M transactions a day. It can be quite a few more. Since you mentioned that the market halted for 2 days there were probably a lot more transactions waiting than usual; not just piled up work, but lots of attempts at corrections as well. It wouldn't surprise me if you went over 650M transactions that day.

> It's my understanding you *don't* want to freeze excessively. I think once every day is bad enough, honestly.

That's not what I was suggesting. I wasn't talking about vacuum freeze but normal autovacuum with more aggressive parameters.
That should handle transaction wrap-around automatically when it looks like you're getting close to the transaction wrap-around id. As per the docs in 8.2, vacuum freeze was deprecated back then already. Knowing the devs a bit, there was a good reason to do so.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.