Re: Slow queries in hot standby

On Wed, Nov 4, 2015 at 10:04 AM, Thomas SIMON <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)
> 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