From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: hot_standby_feedback parameter doesn't work
Date: Tue, 13 Nov 2018 10:04:48 +0000
Message-ID: <87muqduxgt.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CAJw4d1Vnw+BFVuSnSne8APg+e1cD0p4RVOtdqpf3pEVaSgb3Cw@mail.gmail.com>
References: <CAJw4d1Vnw+BFVuSnSne8APg+e1cD0p4RVOtdqpf3pEVaSgb3Cw@mail.gmail.com>
>>>>> "Andrey" == Andrey Zhidenkov <andrey.zhidenkov@gmail.com> writes:
Andrey> We have a few database clusters (1 master, 2 hot standbys) with
Andrey> hot_standby_feedback setting on. But I am constantly seeing
Andrey> query conflicts on standbys because dead rows on the masters
Andrey> are deleted by VACUUM. Usually I notice it shortly after VACUUM
Andrey> ends, because the replication lag on replicas is starting to
Andrey> increase.
Feedback can only try and avoid one of the approximately five possible
causes of conflicts (albeit the most common one).
Next most likely is a pin conflict, especially if you have any tables
involved which are both small and with a relatively high update
frequency.
(Unfortunately with a max delay of 0 the standby queries won't be getting
cancelled which in turn means that there are no statistics about the
causes of conflicts.)
The most likely scenario for a pin conflict is if you have queries which
are (a) long-running, and (b) contain a sequential scan of a small (one
or a few blocks) table that is _not_ underneath a Hash node or similar;
for example, if the Seq Scan appears as the outer path of a Nestloop
join at or near the top of the plan. In this case, vacuum of the small
table may block because the query is holding pin for an extended period
of time on the block that vacuum wants to clean up. (It can happen with
index scans too, but is less likely.)
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: andrew@tao11.riddles.org.uk, andrey.zhidenkov@gmail.com
Subject: Re: hot_standby_feedback parameter doesn't work
In-Reply-To: <87muqduxgt.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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