On Thu, Dec 19, 2024 at 11:11 PM Nisha Moond <nisha.moond...@gmail.com> wrote: > > Here is further performance test analysis with v16 patch-set. > > > In the test scenarios already shared on -hackers [1], where pgbench was run > only on the publisher node in a pub-sub setup, no performance degradation was > observed on either node. > > > > In contrast, when pgbench was run only on the subscriber side with > detect_update_deleted=on [2], the TPS performance was reduced due to dead > tuple accumulation. This performance drop depended on the > wal_receiver_status_interval—larger intervals resulted in more dead tuple > accumulation on the subscriber node. However, after the improvement in patch > v16-0002, which dynamically tunes the status request, the default TPS > reduction was limited to only 1%. > > > > We performed more benchmarks with the v16-patches where pgbench was run on > both the publisher and subscriber, focusing on TPS performance. To summarize > the key observations: > > - No performance impact on the publisher as dead tuple accumulation does not > occur on the publisher.
Nice. It means that frequently getting in-commit-phase transactions by the subscriber didn't have a negative impact on the publisher's performance. > > - The performance is reduced on the subscriber side (TPS reduction (~50%) > [3] ) due to dead tuple retention for the conflict detection when > detect_update_deleted=on. > > - Performance reduction happens only on the subscriber side, as workload on > the publisher is pretty high and the apply workers must wait for the amount > of transactions with earlier timestamps to be applied and flushed before > advancing the non-removable XID to remove dead tuples. Assuming that the performance dip happened due to dead tuple retention for the conflict detection, would TPS on other databases also be affected? > > > [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? > Test Run: > > - Ran pgbench(read-write) on both the publisher and the subscriber with 30 > clients for a duration of 120 seconds, collecting data over 5 runs. > > - Note that pgbench was running for different tables on pub and sub. > > (The scripts used for test "case1-2_measure.sh" and case1-2_setup.sh" are > attached). > > > > Results: > > > > Run# pub TPS sub TPS > > 1 32209 13704 > > 2 32378 13684 > > 3 32720 13680 > > 4 31483 13681 > > 5 31773 13813 > > median 32209 13684 > > regression 7% -53% What was the TPS on the subscriber when detect_update_deleted = false? And how much were the tables bloated compared to when detect_update_deleted = false? Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
