Re: [HACKERS] GUC for cleanup indexes threshold.

On Mon, Jun 18, 2018 at 1:56 PM, Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> Hi!
>
> On Sat, Jun 16, 2018 at 11:23 PM Darafei "Komяpa" Praliaskouski
> <me(at)komzpa(dot)net> wrote:
>> It is cool to see this in Postgres 11. However:
>>
>>>
>>> 4) vacuum_cleanup_index_scale_factor can be set either by GUC or reloption.
>>> Default value is 0.1. So, by default cleanup scan is triggered after increasing of
>>> table size by 10%.
>>
>>
>> vacuum_cleanup_index_scale_factor can be set to the maximum of 100.
>> I imagine that on a large append-only table with IOPS storage system budget it may happen that I would want to never perform a full scan on index. Roughly, with parameter set to 100, if we vacuum the table first time with 1 tuple and 130 byte wide rows, we'll have a full scan at 130 bytes, 12 kbytes, 1.2MB, 123MB, 12 GB, 1.2TB.
>>
>> If we happen to perform the first vacuum when there are 4 tuples in the table, it becomes 52kb, 5MB, 495MB, 48GB - and both 12GB and 48GB will exhaust any storage spike IOPS budget, slowing everything down rather suddenly.
>>
>> Can the upper limit for this GUC be lifted, or have a value for "never"?
>
> I have some further exploration of how statistics obtained by B-tree
> index vacuum cleanup is used.
>
> 1) Collected pages and tuples numbers are not directly used, but used
> for an estimation of tuples density per page, while current number of
> page is estimated using smgr (see btcostestimate()). So, unless
> density of tuples significantly changes, having index statistics
> stalled doesn't affect query plans.
> 2) Our optimization for skipping B-tree index vacuum cleanup works
> only in case when use manually vacuums table in order to update
> visibility map. Autovacuum is not triggered for append-only tables.
> So, if user doesn't have special care about append-only tables,
> they're not vacuumed until "autovacuum to prevent wraparound". Thus,
> index statistics could be very stalled. And I don't think we have
> many occurrences of issues with stalled index statistics.
> 3) We have very safe defaul of vacuum_cleanup_index_scale_factor equal
> to 1.1. But as Darafei claimed, 100 maximum value is probably too low
> for advanced users, who really need benefits of this optimization.

Thank you for explanation.

> So, I'm proposing to raise maximum valus of
> vacuum_cleanup_index_scale_factor to DBL_MAX. Any objections?
>

I agree to expand the maximum value. But if users don't want index
cleanup it would be helpful if we have an option (e.g. setting to -1)
to disable index cleanup while documenting a risk of disabling index
cleanup. It seems to me that setting very high values means the same
purpose.
Also, your patch lacks documentation update.

BTW, I realized that postgresql.conf.sample doesn't have
vacuum_cleanup_index_scale_factor option. Attached patch fixes it.

Regards,

--
Masahiko Sawada