cancel
Showing results for 
Search instead for 
Did you mean: 

Will Read-Only query on salve affect primary ?

Highlighted
Level 2 Adventurer

Will Read-Only query on salve affect primary ?

Hi All,

 

We have a scenario, where the application wants to run some read-only query on standby server. We have configured a master-slave streaming replication. The application team is requesting for a read-only access on slave. My concern is that primary receives a lot of transaction and is busy for 24x7, so will fetching data from standby might cause a conflict on primary.

If so, is there any alternate solution to avoid conflict on primary as well as query termination on secondary!

 

Please suggest some open source tools / method to over come with it.

 

Thanks !

3 REPLIES 3
EDB Team Member

Re: Will Read-Only query on salve affect primary ?

Hi Ram,

 

If your streaming replication is asynchronous. Then, in that case, your application read-only queries on the slave cluster won't hamper any performance on the master server.

 

Hope this answers your query.

 

Regards,

Dhananjay 

Regards,
Dhananjay
Level 2 Adventurer

Re: Will Read-Only query on salve affect primary ?

Thank you, Dhananjay 

 

Sorry for the delay, One quick question - will the standdby queries get hamper due to any conflict with respect to master?

 

Thanks in advance

 

EDB Team Member

Re: Will Read-Only query on salve affect primary ?

Hi Ram,

 

Yes, There could be a conflict scenario.

 

In that case, you can get error messages as below indicates that the queries on standby server are canceled as they were conflicting with the recovery.

OperationalError: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.

 

These are dependent on the two parameters max_standby_archive_delay and max_standby_streaming_delay.

 

Queries which are conflicting for the set value will get canceled.

 

So, You can set maximum time a query takes for the execution on replica to avoid these issue, it requires only reload of the database.