Subject: Re: MultiXact\SLRU buffers configuration
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Tomas Vondra <tomas.vondra@2ndquadrant.com>,
 Alexander Korotkov <aekorotkov@gmail.com>,
 Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 Daniel Gustafsson <daniel@yesql.se>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <a7f1c4e1-1015-92a4-2bd4-6736bd13d03e@postgrespro.ru>
 <20201028013651.de5cj2xadgmba5nf@development>
 <13D8FD63-559A-4737-B7FD-05288D1CEF8B@yandex-team.ru>
 <20201028233243.ygm6yqlynkqpzekr@development>
 <43F3DE92-F236-4EA5-B4D6-39BEF6BD849D@yandex-team.ru>
 <20201029134933.xd4mh2cofuf6tdfz@development>
 <65C1B4BA-D16F-4939-978B-AC8F370F5A5E@yandex-team.ru>
 <9b4d17df-b811-8323-16be-3cab913216d1@enterprisedb.com>
 <A64B924A-B08E-417D-BD3D-D396D722053B@yandex-team.ru>
 <35862787-8b4d-a290-789e-6e12dc6527e8@enterprisedb.com>
 <13F86913-C01B-4983-AE2E-493F5A028280@yandex-team.ru>
 <6ba7eae2-8b0c-0690-11a5-e921e6586180@darold.net>
 <3F5E23A3-6250-486E-9CCA-E2A5B6A28B4F@yandex-team.ru>
 <636f694a-4d69-d6d9-78d1-c88714f3e2e0@darold.net>
 <3319917a-679e-b07d-b194-473552b72082@darold.net>
 <d6f17699-0c18-1a47-8dfb-f52b36fb7c4f@darold.net>
 <D2CC4D64-CA89-411B-96D7-F0A4BF4BB895@yandex-team.ru>
 <b4911e88-9969-aaba-f6be-ed57bd5fec36@darold.net>
 <1E3DD5BF-A54E-4203-86C5-94C9EFA0E095@yandex-team.ru>
From: Gilles Darold <gilles@darold.net>
Message-ID: <04ae150b-6442-148a-bb23-77082f6fca1d@darold.net>
Date: Thu, 11 Mar 2021 16:50:48 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.7.1
MIME-Version: 1.0
In-Reply-To: <1E3DD5BF-A54E-4203-86C5-94C9EFA0E095@yandex-team.ru>
Content-Type: multipart/alternative;
 boundary="------------F0D284B22CBFCB9C5EAE7717"
Content-Language: fr
Archived-At: 
 <https://www.postgresql.org/message-id/04ae150b-6442-148a-bb23-77082f6fca1d%40darold.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------F0D284B22CBFCB9C5EAE7717
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

Le 15/02/2021 à 18:17, Andrey Borodin a écrit :
>
>> 23 дек. 2020 г., в 21:31, Gilles Darold <gilles@darold.net> написал(а):
>>
>> Sorry for the response delay, we have run several others tests trying to figure out the performances gain per patch but unfortunately we have very heratic results. With the same parameters and patches the test doesn't returns the same results following the day or the hour of the day. This is very frustrating and I suppose that this is related to the Azure architecture. The only thing that I am sure is that we had the best performances results with all patches and
>>
>> multixact_offsets_slru_buffers = 256
>> multixact_members_slru_buffers = 512
>> multixact_local_cache_entries = 4096
>>
>>
>> but I can not say if all or part of the patches are improving the performances. My feeling is that performances gain related to patches 1 (shared lock) and 3 (conditional variable) do not have much to do with the performances gain compared to just tuning the multixact buffers. This is when the multixact contention is observed but perhaps they are delaying the contention. It's all the more frustrating that we had a test case to reproduce the contention but not the architecture apparently.
> Hi! Thanks for the input.
> I think we have a consensus here that configuring SLRU size is beneficial for MultiXacts.
> There is proposal in nearby thread [0] on changing default value of commit_ts SLRU buffers.
> In my experience from time to time there can be problems with subtransactions cured by extending subtrans SLRU.
>
> Let's make all SLRUs configurable?
> PFA patch with draft of these changes.
>
> Best regards, Andrey Borodin.
>
>
> [0] https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql
>

The patch doesn't apply anymore in master cause of error: patch failed: 
src/backend/utils/init/globals.c:150


An other remark about this patch is that it should be mentionned in the 
documentation (doc/src/sgml/config.sgml) that the new configuration 
variables need a server restart, for example by adding "This parameter 
can only be set at server start." like for shared_buffers. Patch on 
postgresql.conf mention it.

And some typo to be fixed:


    s/Tipically/Typically/

    s/asincronous/asyncronous/

    s/confugured/configured/

    s/substrnsactions/substransactions/


-- 
Gilles Darold
LzLabs GmbH


--------------F0D284B22CBFCB9C5EAE7717
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Le 15/02/2021 à 18:17, Andrey Borodin a
      écrit :<br>
    </div>
    <blockquote type="cite"
      cite="mid:1E3DD5BF-A54E-4203-86C5-94C9EFA0E095@yandex-team.ru">
      <pre class="moz-quote-pre" wrap="">

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">23 дек. 2020 г., в 21:31, Gilles Darold <a class="moz-txt-link-rfc2396E" href="mailto:gilles@darold.net">&lt;gilles@darold.net&gt;</a> написал(а):

Sorry for the response delay, we have run several others tests trying to figure out the performances gain per patch but unfortunately we have very heratic results. With the same parameters and patches the test doesn't returns the same results following the day or the hour of the day. This is very frustrating and I suppose that this is related to the Azure architecture. The only thing that I am sure is that we had the best performances results with all patches and

multixact_offsets_slru_buffers = 256
multixact_members_slru_buffers = 512
multixact_local_cache_entries = 4096


but I can not say if all or part of the patches are improving the performances. My feeling is that performances gain related to patches 1 (shared lock) and 3 (conditional variable) do not have much to do with the performances gain compared to just tuning the multixact buffers. This is when the multixact contention is observed but perhaps they are delaying the contention. It's all the more frustrating that we had a test case to reproduce the contention but not the architecture apparently.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hi! Thanks for the input.
I think we have a consensus here that configuring SLRU size is beneficial for MultiXacts.
There is proposal in nearby thread [0] on changing default value of commit_ts SLRU buffers.
In my experience from time to time there can be problems with subtransactions cured by extending subtrans SLRU.

Let's make all SLRUs configurable?
PFA patch with draft of these changes.

Best regards, Andrey Borodin.


[0] <a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql">https://www.postgresql.org/message-id/flat/20210115220744.GA24457%40alvherre.pgsql</a>

</pre>
    </blockquote>
    <p><br>
    </p>
    <p>The patch doesn't apply anymore in master cause of error: patch
      failed: src/backend/utils/init/globals.c:150<br>
    </p>
    <p><br>
    </p>
    <p>An other remark about this patch is that it should be mentionned
      in the documentation (doc/src/sgml/config.sgml) that the new
      configuration variables need a server restart, for example by
      adding "This parameter can only be set at server start." like for
      shared_buffers. Patch on postgresql.conf mention it.<br>
    </p>
    <p>And some typo to be fixed:</p>
    <p><br>
    </p>
    <blockquote>
      <p>s/Tipically/Typically/<br>
      </p>
      <p>s/asincronous/asyncronous/</p>
      <p>s/confugured/configured/</p>
      <p>s/substrnsactions/substransactions/<br>
      </p>
    </blockquote>
    <p><br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Gilles Darold
LzLabs GmbH</pre>
  </body>
</html>

--------------F0D284B22CBFCB9C5EAE7717--