pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow queries in hot standby
3+ messages / 2 participants
[nested] [flat]
* Slow queries in hot standby
@ 2015-11-04 15:04 Thomas SIMON <tsimon@neteven.com>
2015-11-04 16:13 ` Re: Slow queries in hot standby Keith Fiske <keith@omniti.com>
0 siblings, 1 reply; 3+ messages in thread
From: Thomas SIMON @ 2015-11-04 15:04 UTC (permalink / raw)
To: pgsql-admin
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)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow queries in hot standby
2015-11-04 15:04 Slow queries in hot standby Thomas SIMON <tsimon@neteven.com>
@ 2015-11-04 16:13 ` Keith Fiske <keith@omniti.com>
2015-11-16 13:20 ` Re: Slow queries in hot standby Thomas SIMON <tsimon@neteven.com>
0 siblings, 1 reply; 3+ messages in thread
From: Keith Fiske @ 2015-11-04 16:13 UTC (permalink / raw)
To: Thomas SIMON <tsimon@neteven.com>; +Cc: pgsql-admin
On Wed, Nov 4, 2015 at 10:04 AM, Thomas SIMON <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)
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow queries in hot standby
2015-11-04 15:04 Slow queries in hot standby Thomas SIMON <tsimon@neteven.com>
2015-11-04 16:13 ` Re: Slow queries in hot standby Keith Fiske <keith@omniti.com>
@ 2015-11-16 13:20 ` Thomas SIMON <tsimon@neteven.com>
0 siblings, 0 replies; 3+ messages in thread
From: Thomas SIMON @ 2015-11-16 13:20 UTC (permalink / raw)
To: Keith Fiske <keith@omniti.com>; +Cc: pgsql-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/;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-11-16 13:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-04 15:04 Slow queries in hot standby Thomas SIMON <tsimon@neteven.com>
2015-11-04 16:13 ` Keith Fiske <keith@omniti.com>
2015-11-16 13:20 ` Thomas SIMON <tsimon@neteven.com>
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