> ToDo > 1. currently this patch supports synchronous transfer. so we can't set > different synchronous transfer mode to each server. > we need to improve the patch for support following cases. > - SYNC standby and make separate ASYNC failback safe standby > - ASYNC standby and make separate ASYNC failback safe standby > > 2. we have not measure performance yet. we need to measure perfomance.
Here are the tests results, showing the performance overhead of the patch ( failback_safe_standby_v4.patch ): Tests are carried out in two different scenarios: 1. Tests with Fast transaction workloads. 2. Test with large loads. Test Type-1: Tests with pgbench (*Tests with fast transaction workloads*) Notes: 1. These test are for testing the performance overhead caused by the patch for fast transaction workloads. 2. Tests are performed with the pgbench benchmark and performance measurement factor is the TPS value. 3. Values represents the TPS for 4 runs,and the last value represents the average of all the runs. Settings for tests: transaction type: TPC-B (sort of) scaling factor: 300 query mode: simple number of clients:150 number of threads:1 duration:1800 s Analysis of results: 1) Synchronous Replication :(753.06, 748.81, 748.38, 747.21, Avg- 747.2) 2) Synchronous Replication + Failsafe standby (commit) : (729.13,724.33 , 713.59 , 710.79, Avg- 719.46) 3) Synchronous Replication + Failsafe standby (all) : (692.08, 688.08, 711.23,711.62, Avg- 700.75) 4) Asynchronous Replication :(1008.42, 993.39, 986.80 ,1028.46 , Avg-1004.26 ) 5) Asynchronous Replication + Failsafe standby (commit) : (974.49, 978.60 ,969.11, 957.18 , Avg- 969.84) 6) Asynchronous Replication + Failsafe standby (data_flush) : (1011.79, 992.05, 1030.20,940.50 , Avg- 993.63) In above test results the performance numbers are very close to each other, also because of noise they show variation. hence following is approximate conclusion about the overhead of patch. 1. Streaming replication + synchronous_transfer (all , data_flush):: a) On an average, synchronous replication combined with synchronous_transfer (all ) causes 6.21 % performance overhead. b) On an average, asynchronous streaming replication combined synchronous_transfer (data_flush ) causes averagely 1.05 % performance overhead. 2. Streaming replication + synchronous_transfer (commit): a) On an average, synchronous replication combined with synchronous_transfer (commit ) causes 3.71 % performance overhead. b) On an average, asynchronous streaming replication combined with synchronous_transfer (commit) causes averagely 3.42 % performance overhead. Test Type-2: Tests with pgbench -i (*Tests with large loads:*) Notes: 1. These test are for testing the performance overhead caused by the patch for large loads and index builds. 2. Tests are performed with the pgbench -i (initialization of test data i.e the time taken for creating tables of pgbench, inserting tuples and building primary keys.) 3. The performance measurement factor is the wall clock time for pgbench -i (measured with time command). 4. Values represents the Wall clock time for 4 runs and the last value represents the average of all the runs. pgbench settings: Scale factor: 300 ( Database size - 4.3873 GB) Test results: 1) Synchronous Replication : (126.98, 133.83, 127.77, 129.70, Avg-129.57) (second) 2) Synchronous Replication + synchronous_transfer (commit) : (132.87, 125.85, 133.91, 134.61, Avg-131.81) (second) 3) Synchronous Replication + synchronous_transfer (all) : (133.59, 132.82, 134.20, 135.22, Avg-133.95) (second) 4) Asynchronous Replication : ( 126.75 , 136.95, 130.42, 127.77, 130.47) (second) 5) Asynchronous Replication + synchronous_transfer (commit) : (128.13, 133.06, 127.62, 130.70, Avg-129.87) (second) 6) Asynchronous Replication + synchronous_transfer (data_flush) : (134.55 , 139.90, 144.47, 143.85, Avg-140.69) (second) In above test results the performance numbers are very close to each other, also because of noise they show variation. hence following is approximate conclusion about the overhead of patch. 1. Streaming replication + synchronous_transfer (all , data_flush):: a) On an average, synchronous replication combined with synchronous_transfer (all ) causes 3.38 % performance overhead. b) On an average, asynchronous streaming replication combined synchronous_transfer (data_flush ) causes averagely 7.83 % performance overhead. 2. Streaming replication + synchronous_transfer (commit): a) On an average, synchronous replication combined with synchronous_transfer (commit ) causes 1.72 % performance overhead. b) On an average, asynchronous streaming replication combined with synchronous_transfer (commit) causes averagely (-0.45) % performance overhead. The test results for both the cases (large loads and fast transactions) shows variation because of noise, But we can observe that approximately patch causes 3-4% performance overhead. Regards, Samrat Revgade -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
