Update or Delete causes canceling of long running slave queries

On Thu, 12 May 2016 21:56 Viswanath, <M.Viswanath16@gmail.com> wrote:

Hi,
I have the following configurations.

On master:
autovacuum=off

Have you changed it recently or it has always been off? In case you changed
it recently, a restart would be needed for this setting to take effect.

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?

Please share mode details like version and standby DB conf etc.

--
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general