Subject: Re: MultiXact\SLRU buffers configuration
To: "Andrey M. Borodin" <x4mmm@yandex-team.ru>
Cc: Daniel Gustafsson <daniel@yesql.se>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <3B099683-ECCD-43CD-A3D6-F08C3745002A@yandex-team.ru>
 <20200514.151659.1224056315528271804.horikyota.ntt@gmail.com>
 <102F673B-C9C5-4823-A92F-96799FC61ED0@yandex-team.ru>
 <20200515.090333.24867479329066911.horikyota.ntt@gmail.com>
 <BA079D14-8D11-4A73-B6EC-FE393C712439@yesql.se>
 <2ECE132B-C042-4489-930E-DBC5D0DAB84A@yandex-team.ru>
 <a7f1c4e1-1015-92a4-2bd4-6736bd13d03e@postgrespro.ru>
 <2087E87D-44CA-4443-8E6A-5087F07443F4@yandex-team.ru>
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Message-ID: <74104522-6537-d19d-3109-edbf3dea6289@postgrespro.ru>
Date: Wed, 7 Oct 2020 17:23:28 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: <2087E87D-44CA-4443-8E6A-5087F07443F4@yandex-team.ru>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 28.09.2020 17:41, Andrey M. Borodin wrote:
> Hi, Anastasia!
>
>> 28 авг. 2020 г., в 23:08, Anastasia Lubennikova <a.lubennikova@postgrespro.ru> написал(а):
>>
>> 1) The first patch is sensible and harmless, so I think it is ready for committer. I haven't tested the performance impact, though.
>>
>> 2) I like the initial proposal to make various SLRU buffers configurable, however, I doubt if it really solves the problem, or just moves it to another place?
>>
>> The previous patch you sent was based on some version that contained changes for other slru buffers numbers: 'multixact_offsets_slru_buffers' and 'multixact_members_slru_buffers'. Have you just forgot to attach them? The patch message "[PATCH v2 2/4]" hints that you had 4 patches)
>> Meanwhile, I attach the rebased patch to calm down the CFbot. The changes are trivial.
>>
>> 2.1) I think that both min and max values for this parameter are too extreme. Have you tested them?
>>
>> +               &multixact_local_cache_entries,
>> +               256, 2, INT_MAX / 2,
>>
>> 2.2) MAX_CACHE_ENTRIES is not used anymore, so it can be deleted.
>>
>> 3) No changes for third patch. I just renamed it for consistency.
> Thank you for your review.
>
> Indeed, I had 4th patch with tests, but these tests didn't work well: I still did not manage to stress SLRUs to reproduce problem from production...
>
> You are absolutely correct in point 2: I did only tests with sane values. And observed extreme performance degradation with values ~ 64 megabytes. I do not know which highest values should we pick? 1Gb? Or highest possible functioning value?

I would go with the values that we consider adequate for this setting. 
As I see, there is no strict rule about it in guc.c and many variables 
have large border values. Anyway, we need to explain it at least in the 
documentation and code comments.

It seems that the default was conservative enough, so it can be also a 
minimal value too. As for maximum, can you provide any benchmark 
results? If we have a peak and a noticeable performance degradation 
after that, we can use it to calculate the preferable maxvalue.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company