Обсуждение: hot_standby_feedback vs. max_standby_archive_delay/max_standby_streaming_delay?

On 08/21/2014 03:44 PM, Steve Kehlet wrote:

> So we're going to try applying max_standby_archive_delay and
> max_standby_streaming_delay to 1h or so. We're also looking at
> pg_xlog_replay_pause(), although this is less desirable because we
> don't want to have to ask the people running reports to remember to
> pause and resume things.

Looks right to me. Though with these settings, keep in mind that these
are not on a per-query basis. You will have queries canceled that may
have only been running for a few seconds, if other queries have caused a
long cumulative delay.

You can fiddle with these until you stop getting cancellations though.
Especially if, as you say, it's a slave that can fall behind safely.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On 8/21/2014 1:44 PM, Steve Kehlet wrote:
> Our queries on our Standby are getting cancelled and so we're
> investigating how to prevent this. The standby is for running periodic
> reporting queries only, we don't care if it falls behind a little bit,
> we just set this guy up to reduce the load on the Primary.
>
> While researching there appear to be several different parameters that
> can help solve this. It sounds like we don't need hot_standby_feedback
> or vacuum_defer_cleanup_age, we really just want to pause things on
> the standby to let it run its queries. So we're going to try
> applying max_standby_archive_delay and max_standby_streaming_delay to
> 1h or so. We're also looking at pg_xlog_replay_pause(), although this
> is less desirable because we don't want to have to ask the people
> running reports to remember to pause and resume things.
>
> Can anyone confirm we're on the right track or provide further
> guidance? Thanks so much.

you will need sufficient wal archiving and/or wal_keep_segments on the
server to cover the worst case period that the slave will get behind due
to pause or whatever.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Steve Kehlet wrote:
> Our queries on our Standby are getting cancelled and so we're investigating
> how to prevent this. The standby is for running periodic reporting queries
> only, we don't care if it falls behind a little bit, we just set this guy
> up to reduce the load on the Primary.
>
> While researching there appear to be several different parameters that can
> help solve this. It sounds like we don't need hot_standby_feedback or
> vacuum_defer_cleanup_age, we really just want to pause things on the
> standby to let it run its queries. So we're going to try
> applying max_standby_archive_delay and max_standby_streaming_delay to 1h or
> so. We're also looking at pg_xlog_replay_pause(), although this is less
> desirable because we don't want to have to ask the people running reports
> to remember to pause and resume things.

Note that not all standby cancellations are the same.  For instance if
you're grabbing access exclusive locks on the master and the salve is,
say, taking pg_dump for a long time, no amount of standby feedback or
vacuum deferring will save you.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services