Re: Sync Rep v19 - Mailing list pgsql-hackers

On 2011-03-05 18:25, Yeb Havinga wrote:

On Sat, Mar 5, 2011 at 5:53 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

On a positive note this is one less parameter and will improve
performance as well.

All above changes made.

Ready to commit, barring concrete objections to important behaviour.

I will do one final check tomorrow evening then commit.

Will retest with new version this evening. Also curious to performance improvement, since v17 seems to be topscorer in that department.

Summary of preliminary testing:
1) it is confusing to show messages/ contents of stat_replication that hints at syncrep, when synchronous_replication is on.
2) should guc settings for synchronous_replication be changed so it can only be set in the config file, and hence only change with reload_conf()?
3) speed is comparable to v17 :-)

regards,
Yeb Havinga


So the biggest change is perhaps that you cannot start 'working' immediately after a initdb with synchronous_replication=on, without a connected standby; I needed to create a role for the repuser to make a backup, but the master halted. The initial bootstrapping has to be done with synchronous_replication = off. So I did, made backup, started standby's while still in not synchronous mode. What followed was confusing:

LOG:  00000: standby "standby2" is now the synchronous standby with priority 2

postgres=# show synchronous_replication ; show synchronous_standby_names; select application_name,state,sync_state from pg_stat_replication;
 synchronous_replication
-------------------------
 off
(1 row)
 synchronous_standby_names 
----------------------------
 standby1,standby2,standby3
(1 row)
 application_name |   state   | sync_state
------------------+-----------+------------
 standby2         | STREAMING | SYNC
 asyncone         | STREAMING | ASYNC
(2 rows)

Is it really sync?
pgbench test got 1464 tps.. seems a bit high.

psql postgres, set synchronous_replication = on;
- no errors, and show after disconnect showed this parameter was still on. My guess: we have syncrep! A restart or reload config was not necessary.
pgbench test got 1460 tps.

pg_reload_conf(); with syncrep = on in postgresql.conf
pgbench test got 819 tps

So now this is synchronous.
Disabled the asynchronous standby
pgbench test got 920 tps.

I also got a first first > 1000 tps score :-) (yeah you have to believe me there really was a sync standby server)

$ pgbench -c 10 -M prepared -T 30 test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 50
query mode: prepared
number of clients: 10
number of threads: 1
duration: 30 s
number of transactions actually processed: 30863
tps = 1027.493807 (including connections establishing)
tps = 1028.183618 (excluding connections establishing)