pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcanceling statement coming in slave instance
7+ messages / 3 participants
[nested] [flat]
* canceling statement coming in slave instance
@ 2018-11-24 10:59 Rohit Arora <arora.leo9@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rohit Arora @ 2018-11-24 10:59 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-24 11:04 Rohit Arora <arora.leo9@gmail.com>
parent: Rohit Arora <arora.leo9@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Rohit Arora @ 2018-11-24 11:04 UTC (permalink / raw)
To: pgsql-admin
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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-24 11:23 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Rohit Arora <arora.leo9@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shreeyansh Dba @ 2018-11-24 11:23 UTC (permalink / raw)
To: arora.leo9@gmail.com; +Cc: pgsql-admin
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.
<http://www.shreeyansh.com;
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
>>
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-24 19:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rohit Arora <arora.leo9@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2018-11-24 19:05 UTC (permalink / raw)
To: Rohit Arora <arora.leo9@gmail.com>; pgsql-admin
Rohit Arora wrote:
> 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.
>
> 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.
You'd have to set max_standby_streaming_delay to -1 to avoid the
error completely. But bear in mind that it can delay replication
arbitrarily long.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-26 10:46 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shreeyansh Dba @ 2018-11-26 10:46 UTC (permalink / raw)
To: Rohit Arora <arora.leo9@gmail.com>; +Cc: 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.
<http://www.shreeyansh.com;
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.
>
> <http://www.shreeyansh.com;
>
>
> 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
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-27 04:50 Rohit Arora <arora.leo9@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rohit Arora @ 2018-11-27 04:50 UTC (permalink / raw)
To: shreeyansh2014@gmail.com; +Cc: pgsql-admin
Dear Shreeyansh DBA and Laurenz Albe,
Thanks for your feedback.
Please find my reply below.
*Shreeyansh DBA Said: *
You need to rollback/commit your transaction manually. After
rollback/commit try to execute the correct SQL- statement again.
*My Reply:*
Initially we were actually facing 2 errors during this incidence.
1st error i mentioned in this mail
and when we encounter the 1st error we encounter below error.
ERROR: current transaction is aborted, commands ignored until end of
transaction block
as per my findings due to 1st error connection got corrupted and after
that all queries on that corrupted connection faced the 2nd error and
solution to this 2nd error is to execute rollback.
I already implemented that and after the implementation of rollback now we
are not facing this 2nd issue.
But still i am searching the solution for my original concern raised in
this mail i.e.
*ERROR: canceling statement due to conflict with recovery*
*DETAIL: User was holding a relation lock for too long*
*Shreeyansh DBA Said: *
You can consider tweaking the parameters max_standby_streaming_delay to the
higher value
*My Reply:*
As per my finding increasing the value of this parameters just delay the
occurrence of error and it might cause the bloating.
*Shreeyansh DBA Said: *
and also should consider tuning the query to increase its response time.
*My Reply*
We regularly optimize the queries but and we generally find relief from
this activity but due to frequent changing requirement we occasionally
end up in this situation.
Apart from this if you could help us to understand the actual reason behind
this and can we do anything permanently to resolve this issue then that
would be a great help.
*Laurenz Albe Said:*
You'd have to set max_standby_streaming_delay to -1
* My Reply:*
As per my understanding setting the value of this parameter to -1 might
cause the bloating
with regards
Rohit Arora
On Mon, Nov 26, 2018 at 4:16 PM Shreeyansh Dba <shreeyansh2014@gmail.com>
wrote:
> 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.
>
> <http://www.shreeyansh.com;
>
>
> 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.
>>
>> <http://www.shreeyansh.com;
>>
>>
>> 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
>>>>
>>>>
>>>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: canceling statement coming in slave instance
@ 2018-11-27 05:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rohit Arora <arora.leo9@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2018-11-27 05:12 UTC (permalink / raw)
To: Rohit Arora <arora.leo9@gmail.com>; shreeyansh2014@gmail.com; +Cc: pgsql-admin
On Tue, 2018-11-27 at 10:20 +0530, Rohit Arora wrote:
[is fighting canceled queries dur to replication conflicts, but fears primary bloat]
In that case, I would set
max_standby_streaming_delay = -1
max_standby_archive_delay = -1
hot_standby_feedback = off
Then no queries should get canceled on the standby, and long queries
on the standby won't cause bloat on the primary.
The price you are paying is delayed replication.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-11-27 05:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-24 10:59 canceling statement coming in slave instance Rohit Arora <arora.leo9@gmail.com>
2018-11-24 11:04 ` Rohit Arora <arora.leo9@gmail.com>
2018-11-24 11:23 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-11-26 10:46 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-11-27 04:50 ` Rohit Arora <arora.leo9@gmail.com>
2018-11-27 05:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-11-24 19:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox