Re: logical decoding and replication of sequences, take 2

On 11/30/23 12:56, Amit Kapila wrote:
> On Thu, Nov 30, 2023 at 5:28 AM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)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