Re: VACUUM's ancillary tasks

On Fri, Oct 7, 2016 at 9:40 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Oct 6, 2016 at 2:46 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>>
>> > Also, I think that doing more counts which get amalgamated into the same
>> > threshold, rather than introducing another parameter, is a bad thing. I
>> > have insert-mostly, most of the time, tables which are never going to
>> > benefit from index-only-scans, and I don't want to pay the cost of them
>> > getting vacuumed just because of some iOn Thu, Oct 6, 2016 at 3:56 PM,
>> > Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> >> Sure, I could handle each case separately, but the goal of this patch
>>
>> nserts, when I will never get a
>> > benefit out of it. I could turn autovacuum off for those tables, but
>> > then I
>> > would have to remember to manually intervene on the rare occasion they
>> > do
>> > get updates or deletes. I want to be able to manually pick which tables
>> > I
>> > sign up for this feature (and then forget it), not manually pick which
>> > ones
>> > to exempt from it and have to constantly review that.
>>
>> Of course, if you do that, then what will happen is eventually it will
>> be time to advance relfrozenxid for that relation, and you'll get a
>> giant soul-crushing VACUUM of doom, rather than a bunch of small,
>> hopefully-innocuous VACUUMs.
>
>
> I think I will get the soul-crushing vacuum of doom anyway, if the database
> lasts that long. For one reason, in my case while updates and deletes are
> rare, they are common enough that the frozen vm bits from early vacuums will
> be mostly cleared before the vacuum of doom comes around.
>
> For a second reason, I don't think the frozen bit in the vm actually gets
> set by much of anything other than a autovacuum-for-wraparound or a manual
> VACUUM FREEZE.

Yeah, the freeze map would be effective especially for static table.
Since we can skip to vacuum frozen page and the freezing tuples is not
big pain usually, we might want to change autovacuum more aggressive
to freeze the page by reducing default value of vacuum_freeze_min_age.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center