Content-Type: multipart/mixed; boundary="------------Gjp3mspxLuJqRhNxJX0y7ZuF"
Message-ID: <39706e78-cc03-01b1-ee17-4592e40b7312@enterprisedb.com>
Date: Sat, 2 Dec 2023 01:10:57 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.15.1
Subject: Re: logical decoding and replication of sequences, take 2
Content-Language: en-US
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>,
 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Masahiko Sawada <sawada.mshk@gmail.com>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Dilip Kumar <dilipbalaut@gmail.com>
References: <c5e671bc-3400-c2f4-14d3-1ae06c462dad@enterprisedb.com>
 <CAA4eK1LTaGxk54oAu=tPfe8XB3-3JvbJqCAFw0px1mROpZTw6A@mail.gmail.com>
 <CAA4eK1Kzh-u9PFCMTXf4N=P5cmDnxDbs5mnr0-9K7HmsRXrvtQ@mail.gmail.com>
 <652f6e81-f71b-b760-b1bb-ab078b8dbe53@enterprisedb.com>
 <CAA4eK1+fEHLn2sYS+6eZV-S8boejmo1sAUYWL4G5KeFbvmy5kA@mail.gmail.com>
 <TY3PR01MB9889DF7B06484285501A563FF5BDA@TY3PR01MB9889.jpnprd01.prod.outlook.com>
 <a101c08e-1fc8-1238-34da-bb984c9806ef@enterprisedb.com>
 <36f3a200-f185-887c-da08-fdc866436e6c@enterprisedb.com>
 <CAA4eK1LkZ+99rKhp1Nxhg9iGBppf1FgLEFmC5SntNFtc1xJFTg@mail.gmail.com>
 <195be236-7580-72c6-97c7-59f06c4e093c@enterprisedb.com>
 <4954a4f8-ba9d-d352-571b-7b94372ea5ae@enterprisedb.com>
 <CAA4eK1+dsdB_g=Y9UnBf8GQk1VnHnugbPWp3Ebt1JXtETcQUgQ@mail.gmail.com>
 <418ff5ac-1664-9038-8ac6-0dd308b5df07@enterprisedb.com>
 <e66ff0de-6f2d-baf9-aee9-ba412868df75@enterprisedb.com>
 <CAA4eK1KZmXuDdm+gNeE8-9wEm27X+9-pb=ym9=tJ5jG6DT-qig@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <CAA4eK1KZmXuDdm+gNeE8-9wEm27X+9-pb=ym9=tJ5jG6DT-qig@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/39706e78-cc03-01b1-ee17-4592e40b7312%40enterprisedb.com>
Precedence: bulk

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


On 11/30/23 12:56, Amit Kapila wrote:
> On Thu, Nov 30, 2023 at 5:28 AM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> 3) "bad case" - small transactions that generate a lot of relfilenodes
>>
>>   select alter_sequence();
>>
>> where the function is defined like this (I did create 1000 sequences
>> before the test):
>>
>>   CREATE OR REPLACE FUNCTION alter_sequence() RETURNS void AS $$
>>   DECLARE
>>       v INT;
>>   BEGIN
>>       v := 1 + (random() * 999)::int;
>>       execute format('alter sequence s%s restart with 1000', v);
>>       perform nextval('s');
>>   END;
>>   $$ LANGUAGE plpgsql;
>>
>> This performs terribly, but it's entirely unrelated to sequences.
>> Current master has exactly the same problem, if transactions do DDL.
>> Like this, for example:
>>
>>   CREATE OR REPLACE FUNCTION create_table() RETURNS void AS $$
>>   DECLARE
>>       v INT;
>>   BEGIN
>>       v := 1 + (random() * 999)::int;
>>       execute format('create table t%s (a int)', v);
>>       execute format('drop table t%s', v);
>>       insert into t values (1);
>>   END;
>>   $$ LANGUAGE plpgsql;
>>
>> This has the same impact on master. The perf report shows this:
>>
>>   --98.06%--pg_logical_slot_get_changes_guts
>>        |
>>         --97.88%--LogicalDecodingProcessRecord
>>              |
>>              --97.56%--xact_decode
>>                   |
>>                    --97.51%--DecodeCommit
>>                         |
>>                         |--91.92%--SnapBuildCommitTxn
>>                         |     |
>>                         |      --91.65%--SnapBuildBuildSnapshot
>>                         |           |
>>                         |           --91.14%--pg_qsort
>>
>> The sequence decoding is maybe ~1%. The reason why SnapBuildSnapshot
>> takes so long is because:
>>
>> -----------------
>>   Breakpoint 1, SnapBuildBuildSnapshot (builder=0x21f60f8)
>>                                       at snapbuild.c:498
>>   498        + sizeof(TransactionId) *   builder->committed.xcnt
>>   (gdb) p builder->committed.xcnt
>>   $4 = 11532
>> -----------------
>>
>> And with each iteration it grows by 1.
>>
> 
> Can we somehow avoid this either by keeping DDL-related xacts open or
> aborting them?
I
I'm not sure why the snapshot builder does this, i.e. why we end up
accumulating that many xids, and I didn't have time to look closer. So I
don't know if this would be a solution or not.

> Also, will it make any difference to use setval as
> do_setval() seems to be logging each time?
> 

I think that's pretty much what case (2) does, as it calls nextval()
enough time for each transaction do generate WAL. But I don't think this
is a very sensible benchmark - it's an extreme case, but practical cases
are far closer to case (1) because sequences are intermixed with other
activity. No one really does just nextval() calls.

> If possible, can you share the scripts? Kuroda-San has access to the
> performance machine, he may be able to try it as well.
> 

Sure, attached. But it's a very primitive script, nothing fancy.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--------------Gjp3mspxLuJqRhNxJX0y7ZuF
Content-Type: application/x-compressed-tar; name="scripts.tgz"
Content-Disposition: attachment; filename="scripts.tgz"
Content-Transfer-Encoding: base64

H4sIAAAAAAAAA+1YbW/aSBDO1+6vmEu5BnJxsHlVQhKJEieKRMmJEN19OAmMvZhVje1612mk
qv/9Zv1CTULgLgqkUT0fwHjnffaZnSUI3UM+3dkoqUiNWk1+a826mv2WVK3is1arVyu1RrNZ
b+6omtpsaDugbtatmEIujAAAv2mwmm/1+hul97+VQx6Ux8wtjw0+JeQ9uOFsTAPwJiACw+WG
KZjnchAeBKELngvUMKdgOoy6gvzd7gxuTjUsI+l0r/Qe/mjUyPnHXvuTfiooF4RMQjdSIcWH
3AyYL+AbIe/ix9OChs8+/+JAIRYDxYRdgcymISiIXTg7A4uOQ/vQ8WyonH3QlvBz6lBTgG8P
rcDzhwH1HYbyaHbIHU8U96Qv0eNe6f9qNAOKngyRF1U6K3UfQPzDoqZnMdfeK7WWWUNz9pi6
mEbFlcYKSe5AmUAhSZEioBBld+7VGSRSSuFbzFQ+RK/LUu/3uWIZB8oq7YzcMv6TE/36gvwj
2Az9JDd6V+8MYB8u+tefZNBptDKsoU3F0Jwark35YrS92243/dxjrumEFlXumcVlJlSMnkgj
5N17uX38wJswh0LI0SL4NJgcgOWBmDIOzOWCGtYxskruJVGcQOKBjAFjiutTmycVPiSyXw0m
YOIFMDbMz9S1pGkJciEXuUOpD1pqBn2AAGsVWKDYoPgw8m2sr6xDnJFRhs33AvEomfHr7xhj
nMnTkTCYI+uqLUv7CBmpOfXmZS4kBUANcudaY1AUNlHoPeOCp8GTeAviYvqCLOzXE0hBFifn
8YYLZjKmfflGYhyxzkwQxhjLUcZUfglxX1ESebY7pjZzW5E7CQubQOKQSN6nIpkl3krcTIQE
FA2sqyjN38+FuEQFiJkv3ZX+pCscNwe1UAp3BQXDEdiITG82M1yLE1lTJtdGyI7plV1n1MIt
lKR090nPCmyJD/gywmbqhuW5lCQpkDaZWFyXaQuZY0WucXNKZ8bDKiSsywqAMbKZ72QccOm9
uDMckmmLyat5VpZLHKAfAmoq4HNIo84sAmbbmKu/2l0FbdpyRz3Wq9TUuepwvNDdLU9icqF+
WQXxghItrFYR1yx1+SA1LQHOLJrVGXEqKefztHqhWK1WqcSKX/uc/Vkp2zc2ZWPN/KdVK81o
/tOaiOiqhvNfRaup+fy3Der09fZAh+s+9PU/u+2ODhe3vc7g6roXA2mYAqlYQo7Bbb93A3ce
s6CNc0mBnOudbruvE0C6g6veoEU+6pdXveTF8Sm26T+giIC2vBmq2Iejo6PS8TEeC62Ih95T
M8SWg719ZuAotQh04L9zPHij0xu+MjGNej7OFnelWFyezCiZ9oPiHo9mjt55ixQK0G33Lm/b
lzr4jm/jBm8RQv5zwJWfL+JnxpXMr1Hz3lJQi4MAhpSMApnSPRTJjBsokGHEk4NiMlAcz7n0
zCtqK/Px2rB6M5SZODZmY03/xwNgfv+vVyry/l9R1Ure/7dByaz/NMaSa/Big32aPRmccwC+
EVq8GWzGxlr8N6o/8N+M5r9KNZ//tkLPwT9MAm8GNnVpIOcKlGSUF7WDmpq3hrdGDy/2m7Cx
Dv9qcv+L8V+V+G/Umzn+t0EJvBevCDlGfxl6/Cfcy9tYi/+qlsG/nP/xsZrjfxuU4P/hfz15
B/hFaNm/5S9tYy3+6z/O/6qqSvxXm/Uc/9ugpfivFJ+6+L+2uznllFNOOb0Q/QuSeeaLACgA
AA==

--------------Gjp3mspxLuJqRhNxJX0y7ZuF--