Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to improve the performance of postgresql replication ?

if you want to perform synchronous_commit parameter before that you have to set "synchronous_standby_names" parameter

synchronous_standby_names:

  • synchronous_standby_names means "wait for acknowledgement until my standby replies".
  • should be specify application_name on standby recovery.conf file in primary_conninfo lines ,default application name is walreceiver
  • If any of the current synchronous standbys disconnects for whatever reason, it will be replaced immediately with the next-highest-priority standby. Specifying more than one standby name can allow very high availability.
  • If synchronous_standby_names specified as empty synchronous_commit will behave as local even though you set synchronous_commit to on, remote_write or remote_apply anything.
setting synchronous_standby_names = '*' #defalt standby name
setting synchronous_standby_names = '2 (*)' #will make synchronous commit wait for reply from any 2 standby servers.
The synchronous commit will wait for reply from one of the standbys listed in the order of priority.
For Example:
synchronous_standby_names = '2(app_name1,app_name2)'

How to switch postgreSQL Asynchronous replication to Synchronous Replication

Basic replication concept:

when user trying insert/update/delete the records then issue the commit means first the data changes will go to the primary wall buffer then it will go to the wal sender then wal sender send the data to wal receiver then wal receiver send the transaction to slave's xlog then executor execute this transaction on slave database then standby data was safe
so  when will  primary database user received the "commit complete" (acknowledgement) means its depend on synchronous_commit parameter options
 
synchronous_commit:
synchronous_commit parameter useful for you can adjust the standby reply(acknowledgement) using following 5 options 


1.synchronous_commit=off 
master(primary) no need wait for acknowledgement consider data did not write to master(primary) wal_log as well as slave xlog, if you recieved "commit complete"(acknowledgement ) means data is written only primary disk not a xlog.

2.synchronous_commit=local 

master(primary) wait for acknowledgement until the transaction write to local master(primary) wal_log(xlog)

synchronous_commit = on (default) 

master(primary) wait for acknowledgement until the transaction write to remote slave(standby) wal_log(xlog),once user transaction is written on standby wal file primary server user will get commit complete acknowledgement

synchronous_commit = remote_write  

master(primary) no need wait for acknowledgement until  the transaction write to remote slave(standby) wal_log, if once the the transaction reached the slave(standby) operating system (wall receiver) that is enough,then user will get commit complete
acknowledgement 

 synchronous_commit = remote_apply  
master(primary) wait for acknowledgement until record was applied to the slave(standby) database.it will take more time to user get acknowledgement,user need wait for untill
executor execute the transaction on slave database
 

Note:in my knowledge local is enough,if set other means you will get performance issues so user want to wait every for commit complete.


 

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger