From: Thomas SIMON <tsimon@neteven.com>
To: Keith Fiske <keith@omniti.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Slow queries in hot standby
Date: Mon, 16 Nov 2015 14:20:47 +0100
Message-ID: <5649D82F.806@neteven.com> (raw)
In-Reply-To: <CAG1_KcAv6CjAoJqTcJczCzN+ZrTJArBNv_pjBFOyOWgfenqPGg@mail.gmail.com>
References: <563A1E83.8010208@neteven.com>
<CAG1_KcAv6CjAoJqTcJczCzN+ZrTJArBNv_pjBFOyOWgfenqPGg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi Keith,
thanks for this well explained reply.
I'll use the solution you mentionned above, with one slave with no
delay, ready for failover (what I already have), and another new one
with large delay parameters, who will handle long requests (where
up-to-date is not the most important thing).
It seems to bebeter solution for this kind of problem.
Thomas
Le 04/11/2015 17:13, Keith Fiske a écrit :
>
>
>
>
> On Wed, Nov 4, 2015 at 10:04 AM, Thomas SIMON <tsimon@neteven.com
> <mailto:tsimon@neteven.com>> wrote:
>
> Hi there,
>
> I have very long queries (exports ; ~2h) that I need to play in my
> production cluster.
> I have set up master/slave replication in hot standby.
>
> I would like to plan this queries on the sIave, so I'm looking for
> the best way to execute this (play with max_standby_archive_delay
> and max_standby_streaming_delay ?)
> What are the possible side effects with this kind of very long
> queries on a slave ?
>
> Thanks,
>
> --
>
> Thomas
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
> <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
> The main side affect is what those settings you mentioned control. If
> the query you're running would cause a conflict with replaying
> replication from the master, replication will be put on hold until the
> query completes or the time given for the delay settings is reached.
> Once that time is reached, the query will be forcefully cancelled and
> replication will continue. If replication is held up, that also means
> all other queries on your slave will not be seeing any new data
> either, so you could cause consistency issues depending if your app
> needs current data on the slave.
>
> How much of an affect on your slave this will have also depends how
> much write traffic you're getting. If it's a very write heavy cluster,
> you could run into the slave taking a long time to catch up again.
> Also, this causes WAL files to be held up on the master as well since
> they have to be kept around for the slave to replay them. So disk
> usage on your master will go up while the query is running. Also if
> you have hot_standby_feedback turned on, you can cause excessive bloat
> on the master as well (see
> http://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK).
>
> The other thing to consider when you use the delay settings on a slave
> is that you should not consider that slave as a legitimate failover
> target. If your master suddenly goes away and replication is held up,
> your slave just lost all that data that hadn't been replicated yet.
> You can mitigate this by also using the archive_command on the master
> or pg_receivexlogs to keep an additional backup of your WAL files
> somewhere else. But you still potentially lose that last WAL file's
> worth of data that hadn't completed yet (I believe 9.5 adds support
> for partial WAL files for pg_recievexlogs, but that's not released
> yet). So if you have a slave being used for read-only queries and you
> need the delay, it's recommended to have a separate slave for failover
> without the delay (if you need failover that is).
>
> So, several things to consider when you run a slave for read-only queries.
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com <http://www.keithf4.com/;
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: tsimon@neteven.com, keith@omniti.com
Subject: Re: Slow queries in hot standby
In-Reply-To: <5649D82F.806@neteven.com>
* 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