public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Hayato Kuroda (Fujitsu) <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: logical decoding and replication of sequences, take 2
Date: Sat, 2 Dec 2023 01:10:57 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAA4eK1KZmXuDdm+gNeE8-9wEm27X+9-pb=ym9=tJ5jG6DT-qig@mail.gmail.com>
References: <[email protected]>
<CAA4eK1LTaGxk54oAu=tPfe8XB3-3JvbJqCAFw0px1mROpZTw6A@mail.gmail.com>
<CAA4eK1Kzh-u9PFCMTXf4N=P5cmDnxDbs5mnr0-9K7HmsRXrvtQ@mail.gmail.com>
<[email protected]>
<CAA4eK1+fEHLn2sYS+6eZV-S8boejmo1sAUYWL4G5KeFbvmy5kA@mail.gmail.com>
<TY3PR01MB9889DF7B06484285501A563FF5BDA@TY3PR01MB9889.jpnprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<CAA4eK1LkZ+99rKhp1Nxhg9iGBppf1FgLEFmC5SntNFtc1xJFTg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAA4eK1+dsdB_g=Y9UnBf8GQk1VnHnugbPWp3Ebt1JXtETcQUgQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAA4eK1KZmXuDdm+gNeE8-9wEm27X+9-pb=ym9=tJ5jG6DT-qig@mail.gmail.com>
On 11/30/23 12:56, Amit Kapila wrote:
> On Thu, Nov 30, 2023 at 5:28 AM Tomas Vondra
> <[email protected]> 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
Attachments:
[application/x-compressed-tar] scripts.tgz (1.3K, ../[email protected]/2-scripts.tgz)
download
view thread (45+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: logical decoding and replication of sequences, take 2
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox