public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hayato Kuroda (Fujitsu) <[email protected]>
To: Hayato Kuroda (Fujitsu) <[email protected]>
To: Zhijie Hou (Fujitsu) <[email protected]>
To: Amit Kapila <[email protected]>
To: Ajin Cherian <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Ashutosh Sharma <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: RE: [PATCH] Support automatic sequence replication
Date: Thu, 5 Mar 2026 12:57:03 +0000
Message-ID: <OS9PR01MB12149E526A7FE1202683C5529F57DA@OS9PR01MB12149.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <OS9PR01MB12149FF8CA4CFDBB117E4447DF57CA@OS9PR01MB12149.jpnprd01.prod.outlook.com>
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
<CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC=T0ZFLA@mail.gmail.com>
<CAFPTHDZg1JrunGgOj332hr+gUuH_Jm7skqPpYSvd-QE3yEdRDQ@mail.gmail.com>
<CAJpy0uBz7MCSUkvFJD9ij65vBahNmY+bfCgdGKRqXovYs+K_TA@mail.gmail.com>
<CAJpy0uDsuNqjWd-TmGBxqSS1rnVCJ3B8=SYrtxQ=Vs8kb71QFA@mail.gmail.com>
<CAJpy0uAMWg3KcXtVBS7B0rnchLNrCCVYBByJCzAp=u5LERgtfA@mail.gmail.com>
<CAFPTHDZwEhxhDAeqcPi0GuYN6xBs8gFXHOMUnbg3u2Xigcz4Zg@mail.gmail.com>
<CAE9k0PmTyCU1A9YEf+MRgfeZ8yK1bAYJu=o0bH8DNUTzXejQyQ@mail.gmail.com>
<CAA4eK1L6czEzG4mLNZSyjYC5nX0FMSjjk3csKuxPD3Ph5-7Yvw@mail.gmail.com>
<CAJpy0uAhGQJ=msVsn2GsqWXr+YESJK6x9NBvrUtKvtvp1OVuKQ@mail.gmail.com>
<CAJpy0uAOuu-M6wobH2wHOdTymm-cX9+MqwPyRNoOt=sPKBdCew@mail.gmail.com>
<CAFPTHDZiWYXoKoo4VcBYNH9a=gxDZhfkcBeXt5w6cLw4_ysyKw@mail.gmail.com>
<OS9PR01MB12149D9054CC7F2DC3F0D26A1F577A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
<CAA4eK1KYxQALt46k5uWOO6SUtNjvjOaXwfNjH0AU656YrcGZEw@mail.gmail.com>
<CAFPTHDZYonM+SXG19VVjgWduXQJSuDhcOUWq0NCiiuQubCSt6g@mail.gmail.com>
<CAFPTHDYud1zr0VyizhyhEQXfHMgXVcHrPzE56WUKGCFNskQq2A@mail.gmail.com>
<CAA4eK1JTau3fV7br6xwAV+LXXwM65RuGCuM2J3PQpCONtL1KXA@mail.gmail.com>
<OS9PR01MB1691377CDB1468CDC9820BBEB9470A@OS9PR01MB16913.jpnprd01.prod.outlook.com>
<TY4PR01MB1690715895CDE6FEFA13C2A2C947EA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
<OS9PR01MB12149FF8CA4CFDBB117E4447DF57CA@OS9PR01MB12149.jpnprd01.prod.outlook.com>
Dear Hackers,
> 05.
>
> LogicalRepSyncSequences() starts the transaction and read sequences every
> time.
> Can we cache the seqinfos to reuse in the next iteration? My idea is to introduce
> a syscache callback for the pg_subscription_relto invalidate the cached list.
>
> How about measuring performance once and considering it's a good
> improvement?
I profiled the sequencesync worker when sequences were less actively updated on
the publisher side. In the actively updated system, copying sequences used most
of the CPU time; thus, we could not observe the effect.
Abstract
--------------
Sequencesync worker spent 20-25% of the working time scanning pg_subscription_rel
in the workload. It's not so large compared with the total CPU time; the worker
can work once per 2 seconds or longer. We may able to consider the optimization
if there are easy ways.
Source
-----------
ea47447 + v8 patch set + attached fix patch.
To simplify the analysis, I extracted the scan part into the function
scan_subscription_relations. No configure options are set at build.
Workload
---------------
Two workloads were tested.
A - profile with no sequence updates
0. Defined 100 sequences on both nodes
1. Built a pub-sub replication system.
2. Attached the sequencesync worker as early as creating the subscription.
3. Waited 10 minutes.
B - profile with 10% sequences updates
0. Defined 100 sequences on both nodes
1. Built a pub-sub replication system.
2. Waited till the initial sync was done. On my env 100s was enough
3. Attached the sequencesync worker
4. Updated 10 sequences per second.
5. Repeat step 4 for 10 minutes.
Result
----------
The attached profiles show the detailed results: noupdate.out corresponds to
workload A, while 10percent_update.out is for workload B.
In both cases, scan_subscription_relations spends more than 20% of their working
time. Notable points are to open sequence relations with the AccessShareLock,
committing the transaction, starting the catalog scan, etc.
workload A:
```
| --20.83%--scan_subscription_relations
| |
| |--10.83%--try_table_open
| | try_relation_open
```
workload B:
```
| --24.01%--scan_subscription_relations
| |
| |--12.52%--try_table_open
| | try_relation_open
```
Consideration
--------------
Based on that, we may be able to cache seqinfos to avoid starting the
transaction and opening the sequence. But we need to introduce a relcache
callback to invalidate the specific entry of the list, not sure it's beneficial
more than the complexity.
Configuration
----------------------
Each node had shared_buffer=1GB, and others had the default GUC values.
Environment
--------------------
CPU: Intel(R) Xeon(R) Platinum 8358P CPU @ 2.60GHz, 4 cores, 1 thread per core
Memory: 15GiB
OS: AlmaLinux 9.7
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] 10percent_update.out (817.2K, 2-10percent_update.out)
download
[application/octet-stream] noupdate.out (717.0K, 3-noupdate.out)
download
view thread (58+ 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]
Subject: RE: [PATCH] Support automatic sequence replication
In-Reply-To: <OS9PR01MB12149E526A7FE1202683C5529F57DA@OS9PR01MB12149.jpnprd01.prod.outlook.com>
* 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