Re: Sync Rep v19

On 2011-03-05 18:25, Yeb Havinga wrote:
> On Sat, Mar 5, 2011 at 5:53 PM, Simon Riggs <simon@2ndquadrant.com 
> <mailto: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)