Thread: Update or Delete causes canceling of long running slave queries

Hi,
I have the following configurations.

On master:
autovacuum=off
vacuum_defer_cleanup_age=0

On slave:
hot_standby_feedback = off
max_standby_streaming_delay = 30s

Now when I try to run a long query on slave and do some update or delete on
the table on master I am getting the following error.

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

The error code was : 40001

I am not running vacuum manually and autovacuum is 'off'
What could possibly causing the conflict?



--
View this message in context:
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, May 12, 2016 at 6:37 AM, Viswanath <M.Viswanath16@gmail.com> wrote:

> Hi,
> I have the following configurations.
>
> On master:
> autovacuum=off
> vacuum_defer_cleanup_age=0
>
> On slave:
> hot_standby_feedback = off
> max_standby_streaming_delay = 30s
>
> Now when I try to run a long query on slave and do some update or delete on
> the table on master I am getting the following error.
>
> ERROR:  canceling statement due to conflict with recovery
> DETAIL:  User query might have needed to see row versions that must be
> removed.
>
> The error code was : 40001
>
> I am not running vacuum manually and autovacuum is 'off'

I hope you doing that for experimental purposes only.

> What could possibly causing the conflict?

Probably pruning of HOT update chains.

Try turning hot_standby_feedback on, or setting
vacuum_defer_cleanup_age to something more than zero.

Cheers,

Jeff

Hi Jeff,
 Yes I am turning off autovacuum for experimental purpose only. I was
curious to know what is causing the queries to be killed when the autovacuum
is not running. I guess it had to be the pruning of HOT update chains like
you have mentioned.
Also I have already tried changing the parameters hot_standby_feedback and
vacuum_defer_cleanup_age, it works fine, but if HOT update is the problem
then changing these setting will hinder it right? If so then is it safe to
use these parameters? Or will there be any notable problems or performance
issues? This pruning of HOT update could be the case for update only or
delete queries too?



--
View this message in context:
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903344.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,
I am using postgres 9.5
Yes,I restarted the server after changing the autovacuum to off. Also
verified that autovacuum process is not running.

standby db configurations: (mostly default conf only)
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = off
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s



--
View this message in context:
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903334.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, May 12, 2016 at 11:14 AM, Viswanath <M.Viswanath16@gmail.com> wrote:
> Hi Jeff,
>  Yes I am turning off autovacuum for experimental purpose only. I was
> curious to know what is causing the queries to be killed when the autovacuum
> is not running. I guess it had to be the pruning of HOT update chains like
> you have mentioned.
> Also I have already tried changing the parameters hot_standby_feedback and
> vacuum_defer_cleanup_age, it works fine, but if HOT update is the problem
> then changing these setting will hinder it right? If so then is it safe to
> use these parameters? Or will there be any notable problems or performance
> issues?


It will hinder it some, but not by more than it would be hindered if
the long-running query were running directly on the master rather than
on the slave.

It is possible that the amount of hindering it would cause problems,
but it is not very likely.  Do you have things like work-queue tables
which turn over extremely quickly?  And how long-running are the long
running queries?  Did you have serious bloat problems before you split
the workload between the master and the slaves?


> This pruning of HOT update could be the case for update only or
> delete queries too?

The delete query should not create new HOT update chains, but I think
it will still clean up HOT update chains left behind by previous
updates, as it touchs blocks which happen to contain them.

Cheers,

Jeff

Hi,
Thank you for the explanation.
The slave queries are mostly reporting queries,which sometimes would take
30+ running time due to complex joins and criteria. We haven't tried running
these queries on master before splitting, and it is possible that there will
be heavy updates and deletes on master on some tables some times. Now we
have to decide between these two parameters as which one to use. Can you
give any suggestion?



--
View this message in context:
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903415.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, May 12, 2016 at 9:22 PM, Viswanath <M.Viswanath16@gmail.com> wrote:
> Hi,
> Thank you for the explanation.
> The slave queries are mostly reporting queries,which sometimes would take
> 30+ running time due to complex joins and criteria. We haven't tried running
> these queries on master before splitting, and it is possible that there will
> be heavy updates and deletes on master on some tables some times. Now we
> have to decide between these two parameters as which one to use. Can you
> give any suggestion?

I would go with hot_standby_feedback = on, and look for other
solutions only if that is demonstrated to cause a problem (like severe
bloat) under your workload.

Cheers,

Jeff

Hi,
What if we have a chain of slaves (master->slave->sec slave)? And if queries
would run on each of them? or a config like master ->slave1
                                 ->slave2

Is it ok to enable feedback on both slaves ?



--
View this message in context:
http://postgresql.nabble.com/Update-or-Delete-causes-canceling-of-long-running-slave-queries-tp5903250p5903914.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.