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: Thu, 30 Nov 2023 00:58:45 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[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]>
On 11/29/23 15:41, Tomas Vondra wrote:
> ...
>>
>> One thing that worries me about that approach is that it can suck with
>> the workload that has a lot of DDLs that create XLOG_SMGR_CREATE
>> records. We have previously fixed some such workloads in logical
>> decoding where decoding a transaction containing truncation of a table
>> with a lot of partitions (1000 or more) used to take a very long time.
>> Don't we face performance issues in such scenarios?
>>
>
> I don't think we do, really. We will have to decode the SMGR records and
> add the relfilenodes to the hash table(s), but I think that affects the
> lookup performance too much. What I think might be a problem is if we
> have many top-level transactions, especially if those transactions do
> something that creates a relfilenode. Because then we'll have to do a
> hash_search for each of them, and that might be measurable even if each
> lookup is O(1). And we do the lookup for every sequence change ...
>
I did some micro-benchmarking today, trying to identify cases where this
would cause unexpected problems, either due to having to maintain all
the relfilenodes, or due to having to do hash lookups for every sequence
change. But I think it's fine, mostly ...
I did all the following tests with 64 clients. I may try more, but even
with this there should be fair number of concurrent transactions, which
determines the number of top-level transactions in reorderbuffer. I'll
try with more clients tomorrow, but I don't think it'll change stuff.
The test is fairly simple - run a particular number of transactions
(might be 1000 * 64, or more). And then measure how long it takes to
decode the changes using test_decoding.
Now, the various workloads I tried:
1) "good case" - small OLTP transactions, a couple nextval('s') calls
begin;
insert into t (1);
select nextval('s');
insert into t (1);
commit;
This is pretty fine, the sequence part of reorderbuffer is really not
measurable, it's like 1% of the total CPU time. Which is expected,
because we only wal-log every 32-nd increment or so.
2) "good case" - same as (1) but more nextval calls to always do wal
begin;
insert into t (1);
select nextval('s') from generate_series(1,40);
insert into t (1);
commit;
Here sequences are more measurable, it's like 15% of CPU time, but most
of that comes to AbortCurrentTransaction() in the non-transactional
branch of ReorderBufferQueueSequence. I don't think there's a way around
that, and it's entirely unrelated to relfilenodes. The function checking
if the change is transactional (ReorderBufferSequenceIsTransactional) is
less than 1% of the profile - and this is the version that always walks
all top-level transactions.
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. That looks quite weird, possibly
a bug worth fixing, but unrelated to this patch. I can't investigate
this more at the moment, not sure when/if I'll get to that.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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