On Thu, Nov 30, 2023 at 5:28 AM Tomas Vondra <tomas.von...@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.