Re: [HACKERS] GUC for cleanup indexes threshold.

On Wed, Feb 28, 2018 at 1:45 AM, Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Wed, Nov 29, 2017 at 6:06 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> wrote:
>>
>> On Wed, Nov 29, 2017 at 11:05 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>
>> wrote:
>> > On 25 September 2017 at 22:34, Kyotaro HORIGUCHI
>> > <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> >
>> >>> > Here is a small patch that skips scanning btree index if no pending
>> >>> > deleted pages exists.
>> >>> > It detects this situation by comparing pages_deleted with
>> >>> > pages_free.
>> >>
>> >> It seems to work to prevent needless cleanup scans.
>> >
>> > So this leaves us in the situation that
>> >
>> > 1. Masahiko's patch has unresolved problems
>> > 2. Yura's patch works and is useful
>> >
>> > Unless there is disagreement on the above, it seems we should apply
>> > Yura's patch (an edited version, perhaps).
>> >
>>
>> IIRC the patches that makes the cleanup scan skip has a problem
>> pointed by Peter[1], that is that we stash an XID when a btree page is
>> deleted, which is used to determine when it's finally safe to recycle
>> the page. Yura's patch doesn't have that problem?
>>
>> [1]
>> https://www.postgresql.org/message-id/CAH2-Wz%3D1%3Dt5fcGGfarQGcAWBqaCh%2BdLMjpYCYHpEyzK8Qg6OrQ%40mail.gmail.com
>
>
> Yes, I think Yura's patch doesn't have that problem, because it skips
> cleanup only when there are no recyclable pages. And that means there
> is no btpo.xact stored, so no XIDs to be wraparounded.

I've looked at the patch again. And you're right, Yura's patch doesn't
have that problem.

>
> BTW, I've rebased Yura's patch. I think this patch has following issues
> for now:
>
> 1) Adding BTP_NEED_NO_CLEANUP as per-page flag doesn't look nice.

Yeah, the alternative ideas are to store the flag it into pd_rune_xid
of meta page or to use 1bit of btm_version so that we don't break disk
format compatibility.

> 2) In the append-only case, index statistics can lag indefinitely.

The original proposal proposed a new GUC that specifies a fraction of
the modified pages to trigger a cleanup indexes. Combining with Yura's
patch and telling requirement of cleanup to indexes from lazyvacuum
code we can deal with it.

Regards,

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