Re: logical decoding and replication of sequences, take 2

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? Also, will it make any difference to use setval as
do_setval() seems to be logging each time?

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

--
With Regards,
Amit Kapila.