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

On Thu, May 12, 2016 at 11:14 AM, Viswanath <M(dot)Viswanath16(at)gmail(dot)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