Horton

Re: logical decoding and replication of sequences, take 2

Amit Kapila <amit.kapila16@gmail.com>

View thread
From: Amit Kapila <amit.kapila16@gmail.com>
To: Tomas Vondra <tomas.vondra@enterprisedb.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>
Date: 2023-11-30T11:56:22Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Migrate logical slots to the new node during an upgrade.

  2. Make test_decoding ddl.out shorter

  3. Fix snapshot handling in logicalmsg_decode

  4. doc: Adjust a few more references to "postmaster"

  5. Revert "Logical decoding of sequences"

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? 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.