On Thursday, January 9, 2025 9:48 AM Masahiko Sawada <sawada.mshk@gmail.com>
Hi,
>
> On Wed, Jan 8, 2025 at 3:00 AM Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
> wrote:
> >
> > On Wednesday, January 8, 2025 6:33 PM Masahiko Sawada
> <sawada.mshk@gmail.com> wrote:
> >
> > Hi,
> >
> > > On Wed, Jan 8, 2025 at 1:53 AM Amit Kapila <amit.kapila16@gmail.com>
> > > wrote:
> > > > On Wed, Jan 8, 2025 at 3:02 PM Masahiko Sawada
> > > <sawada.mshk@gmail.com> wrote:
> > > > >
> > > > > On Thu, Dec 19, 2024 at 11:11 PM Nisha Moond
> > > <nisha.moond412@gmail.com> wrote:
> > > > > >
> > > > > >
> > > > > > [3] Test with pgbench run on both publisher and subscriber.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Test setup:
> > > > > >
> > > > > > - Tests performed on pgHead + v16 patches
> > > > > >
> > > > > > - Created a pub-sub replication system.
> > > > > >
> > > > > > - Parameters for both instances were:
> > > > > >
> > > > > >
> > > > > >
> > > > > > share_buffers = 30GB
> > > > > >
> > > > > > min_wal_size = 10GB
> > > > > >
> > > > > > max_wal_size = 20GB
> > > > > >
> > > > > > autovacuum = false
> > > > >
> > > > > Since you disabled autovacuum on the subscriber, dead tuples
> > > > > created by non-hot updates are accumulated anyway regardless of
> > > > > detect_update_deleted setting, is that right?
> > > > >
> > > >
> > > > I think hot-pruning mechanism during the update operation will
> > > > remove dead tuples even when autovacuum is disabled.
> > >
> > > True, but why did it disable autovacuum? It seems that
> > > case1-2_setup.sh doesn't specify fillfactor, which makes hot-updates less
> likely to happen.
> >
> > IIUC, we disable autovacuum as a general practice in read-write tests
> > for stable TPS numbers.
>
...
> In test case 3, we observed a -53% performance dip, which is worse than the
> results of test case 5 with wal_receiver_status_interval = 100s. Given that
> in test case 5 with wal_receiver_status_interval = 100s we cannot remove dead
> tuples for the most of the whole 120s test time, probably we could not remove
> dead tuples for a long time also in test case 3. I expected that the apply
> worker gets remote transaction XIDs and tries to advance slot.xmin more
> frequently, so this performance dip surprised me.
As noted in my previous email[1], the delay primarily occurs during the final
phase (RCI_WAIT_FOR_LOCAL_FLUSH), where we wait for concurrent transactions
from the publisher to be applied and flushed locally (e.g., last_flushpos <
data->remote_lsn). I think that the interval between each transaction ID
advancement is brief, the duration of each advancement itself is significant.
> I would like to know how many times the apply worker gets remote transaction
> XIDs and succeeds in advance slot.xmin during the test.
my colleague will collect and share the data soon.
[1]
https://www.postgresql.org/message-id/OS0PR01MB57164C9A65F29875AE63F0BD94132%40OS0PR01MB5716.jpnprd01.prod.outlook.com
Best Regards,
Hou zj
