Re: Slow queries in hot standby

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(at)neteven(dot)com
> <mailto:tsimon(at)neteven(dot)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(at)postgresql(dot)org
> <mailto:pgsql-admin(at)postgresql(dot)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/>