Re: Slow queries in hot standby

[Thomas SIMON <tsimon@xxxxxxxxxxx>]

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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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