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(at)2ndquadrant(dot)com
> <mailto:simon(at)2ndquadrant(dot)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)