Re: canceling statement coming in slave instance - Mailing list pgsql-admin

From Shreeyansh Dba
Subject Re: canceling statement coming in slave instance
Date
Msg-id CAGDYbUNT5++yxrzv_wPtAYPF_RYmUpY+cLSV8nkU8cN0tFaRtA@mail.gmail.com
Whole thread Raw
In response to Re: canceling statement coming in slave instance  (Shreeyansh Dba <shreeyansh2014@gmail.com>)
Responses Re: canceling statement coming in slave instance  (Rohit Arora <arora.leo9@gmail.com>)
List pgsql-admin
Hi Rohit,

In addition to this.

As your application is very high write intensive that results into heavy streaming on the slave causing the slave read queries taking longer time not finishing in the specific time limits causing query cancellation.

You can consider tweaking the parameters max_standby_streaming_delay to the higher value to meet your business requirement completing the said query and also should consider tuning the query to increase its response time.




On Sat, Nov 24, 2018 at 4:53 PM Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Rohit,

It seems you are executing a non-correct SQL statement and when you correct it and try to execute it again you will get this error.

You need to rollback/commit your transaction manually. After rollback/commit try to execute the correct SQL- statement again.

Hope this helps.




On Sat, Nov 24, 2018 at 4:35 PM Rohit Arora <arora.leo9@gmail.com> wrote:
Dear List,

Please note that we are working on PostgreSQL 9.4.19.

Thanks
Rohit Arora


On Sat, Nov 24, 2018 at 4:29 PM Rohit Arora <arora.leo9@gmail.com> wrote:
Dear List,

In few of our Slave PostgreSQL machines.

I occasionally encounter below error.

ERROR:  canceling statement due to conflict with recovery
DETAIL:  User was holding a relation lock for too long

While investigating online i came to know that this issue can be handled by below configuration parameters

"max_standby_archive_delay "
"max_standby_streaming_delay" 

I have increased the value of both the parameters as per below.

Original values:
"max_standby_archive_delay=30s"
"max_standby_streaming_delay=30s"

Current values:
"max_standby_archive_delay=30s" ----> was increased it to 300s but we did not get any benefit so we rollback it to original value
"max_standby_streaming_delay=300s"

But still i occasionally encounter the mention issue.

Please note that on Master node we have heavy write  operations and these Slave nodes are geographically distinct on a WAN connection.

Thanks in Advance
Rohit Arora


pgsql-admin by date:

Previous
From: Achilleas Mantzios
Date:
Subject: Re: could not connect to server, in order to operatepgAdmin/PostgreSQL
Next
From: Rohit Arora
Date:
Subject: Re: canceling statement coming in slave instance