Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZyJjy-0006iK-Fp for pgsql-admin@arkaria.postgresql.org; Mon, 16 Nov 2015 13:22:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZyJjx-0000Hp-N1 for pgsql-admin@arkaria.postgresql.org; Mon, 16 Nov 2015 13:22:37 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZyJiH-0006g0-Qq for pgsql-admin@postgresql.org; Mon, 16 Nov 2015 13:20:53 +0000 Received: from mail4.neteven.com ([5.135.111.119]) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1ZyJiE-0001Rs-4M for pgsql-admin@postgresql.org; Mon, 16 Nov 2015 13:20:52 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mail4.neteven.com (Postfix) with ESMTP id 33FA21E1B5E; Mon, 16 Nov 2015 14:20:48 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mail4.neteven.com Received: from mail4.neteven.com ([127.0.0.1]) by localhost (mail4.neteven.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id inAFAe9L9E3q; Mon, 16 Nov 2015 14:20:48 +0100 (CET) Message-ID: <5649D82F.806@neteven.com> Date: Mon, 16 Nov 2015 14:20:47 +0100 From: Thomas SIMON User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: Keith Fiske CC: "pgsql-admin@postgresql.org" Subject: Re: Slow queries in hot standby References: <563A1E83.8010208@neteven.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010608000103030202040905" X-Pg-Spam-Score: -3.4 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------010608000103030202040905 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 --------------010608000103030202040905 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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> 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

--------------010608000103030202040905--