Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a1LQD-0002CR-4c for pgsql-admin@arkaria.postgresql.org; Tue, 24 Nov 2015 21:46:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a1LQC-0002EV-NO for pgsql-admin@arkaria.postgresql.org; Tue, 24 Nov 2015 21:46:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1GSK-0006aF-8E for pgsql-admin@postgresql.org; Tue, 24 Nov 2015 16:28:36 +0000 Received: from mail-bn1on0061.outbound.protection.outlook.com ([157.56.110.61] helo=na01-bn1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a1GSB-0000Yu-VE for pgsql-admin@postgresql.org; Tue, 24 Nov 2015 16:28:35 +0000 Received: from BN3PR0501MB1394.namprd05.prod.outlook.com (10.160.117.140) by BN3PR0501MB1393.namprd05.prod.outlook.com (10.160.117.14) with Microsoft SMTP Server (TLS) id 15.1.331.20; Tue, 24 Nov 2015 16:28:24 +0000 Received: from BN3PR0501MB1394.namprd05.prod.outlook.com ([10.160.117.140]) by BN3PR0501MB1394.namprd05.prod.outlook.com ([10.160.117.140]) with mapi id 15.01.0331.019; Tue, 24 Nov 2015 16:28:24 +0000 From: "Williams, Alex" To: Yaser Raja , Scott Ribe CC: "Williams, Alex" , "pgsql-admin@postgresql.org" Subject: Re: Question on Hot Standby in PostgreSQL Thread-Topic: [ADMIN] Question on Hot Standby in PostgreSQL Thread-Index: AQHRI8y8zBjK47Xr6EiOzEJaz9mcjZ6qEjSAgAAMXQCAAPBIAA== Date: Tue, 24 Nov 2015 16:28:24 +0000 Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: spf=none (sender IP is ) smtp.mailfrom=awilliams@teamdrg.com; x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [209.64.158.208] x-microsoft-exchange-diagnostics: 1; BN3PR0501MB1393; 5:WB+SWFa/Wxura3jiVHMUQnpRlWX9HHM7MXgRVyAYgkUyyOlszQVwtfEsKVCR7Pj+Li/3duPD92hfyiTYR1uw2xwY7LEcO7YK0BLGRiLhQA3vAA9JY+8kkWHVDpfFy+ZHUTuoA++QEPxIoIo4sKZxHw==; 24:/L1xuEJxOSRGtdlcIgMf3ZQNSxV/Uj/cHcM9KMnssIRCRW/BBQaTRHh4E2ftJJgxhvkjJo28ruTghq5LpxGd/V720B0R+8SpHERv+ztA8SI=; 20:nGFtoUEbMCohvwn42t8p+zcy3BHrzFnbleLQrLJn4ZZHJ/PMffVv60sywb0xEGPHQeqXntOmFndI8yuBdjJ6JGPpYaBc/D5iiKkveD50aKe7UmhH9pKxfHNITSh0A1DAFlEOudpduclmjfUYvSt9avh1cZmA2MB2lEIrOwd3M+s= x-microsoft-antispam: UriScan:; BCL:0; PCL:0; RULEID:(42140001); SRVR:BN3PR0501MB1393; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(173375146687380); x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(2401047)(5005006)(520078)(8121501046)(3002001)(10201501046); SRVR:BN3PR0501MB1393; BCL:0; PCL:0; RULEID:; SRVR:BN3PR0501MB1393; x-forefront-prvs: 0770F75EA9 x-forefront-antispam-report: SFV:NSPM; SFS:(10009020)(24454002)(199003)(189002)(377454003)(189998001)(5008740100001)(16236675004)(3846002)(36756003)(101416001)(99286002)(66066001)(81156007)(122556002)(5001770100001)(97736004)(5001960100002)(19617315012)(10400500002)(87936001)(5002640100001)(50986999)(6116002)(86362001)(19580395003)(19580405001)(15975445007)(2950100001)(92566002)(40100003)(5007970100001)(102836003)(5004730100002)(586003)(106116001)(105586002)(77096005)(106356001)(54356999)(11100500001)(2900100001); DIR:OUT; SFP:1101; SCL:1; SRVR:BN3PR0501MB1393; H:BN3PR0501MB1394.namprd05.prod.outlook.com; FPR:; SPF:None; PTR:InfoNoRecords; A:1; MX:1; LANG:en; received-spf: None (protection.outlook.com: teamdrg.com does not designate permitted sender hosts) Content-Type: multipart/alternative; boundary="_000_D278FEB5111A05awilliamsdresourcesgroupcom_" MIME-Version: 1.0 X-OriginatorOrg: teamdrg.com X-MS-Exchange-CrossTenant-originalarrivaltime: 24 Nov 2015 16:28:24.1521 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 5d6495b1-5cd4-4a4f-a6dd-1f5f3bf58831 X-MS-Exchange-Transport-CrossTenantHeadersStamped: BN3PR0501MB1393 X-MS-Exchange-CrossPremises-AuthAs: Internal X-MS-Exchange-CrossPremises-AuthMechanism: 04 X-MS-Exchange-CrossPremises-AuthSource: BN3PR0501MB1394.namprd05.prod.outlook.com X-MS-Exchange-CrossPremises-SCL: 1 X-MS-Exchange-CrossPremises-messagesource: StoreDriver X-MS-Exchange-CrossPremises-BCC: X-MS-Exchange-CrossPremises-originalclientipaddress: 209.64.158.208 X-MS-Exchange-CrossPremises-avstamp-service: 1.0 X-MS-Exchange-CrossPremises-antispam-scancontext: DIR:Originating; SFV:NSPM; SKIP:0; X-MS-Exchange-CrossPremises-journaled-to-recipients: local-dresources365@journalloma.liveoffice.com X-MS-Exchange-CrossPremises-processed-by-journaling: Journal Agent X-OrganizationHeadersPreserved: BN3PR0501MB1393.namprd05.prod.outlook.com X-Pg-Spam-Score: -0.6 (/) 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 --_000_D278FEB5111A05awilliamsdresourcesgroupcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Thank you both, I really appreciate your responses, and hopefully this may = help others who may run into this. I noticed on the link to that ref. it was 9.3, we have 9.2 (which can use t= hat param too,) but I didn't realize I was looking at the 9.0 docs and didn= 't see that param there. Concerning these two params below, when using hot_standby_feedback, what w= ould you set them if you had the situation where you had queries constantly= running on the standby, some quick and some long, practically all day/nigh= t, everyday? max_standby_archive_delay (integer) max_standby_streaming_delay (integer) [http://www.postgresql.org/docs/9.2/static/hot-standby.html] Remedial possibilities exist if the number of standby-query cancellations i= s found to be unacceptable. The first option is to set the parameter hot_st= andby_feedback, which prevents VACUUM from removing recently-dead rows and = so cleanup conflicts do not occur. If you do this, you should note that thi= s will delay cleanup of dead rows on the primary, which may result in undes= irable table bloat. However, the cleanup situation will be no worse than if= the standby queries were running directly on the primary server, and you a= re still getting the benefit of off-loading execution onto the standby. max= _standby_archive_delay must be kept large in this case, because delayed WAL= files might already contain entries that conflict with the desired standby= queries. Should I still keep them as -1 (infinite)? If I do, will the WAL updates st= ill be applied while current queries are running, for example: 1. Query1 starts SELECT (10 minute query) @ 10:00:00 AM (current row versio= n xid's) 2. The WAL update is applied to the standby (2 seconds) starts at 10:00:08 = AM, completes at 10:00:10 AM 3. Query2 starts SELECT @ 10:00:15 AM (starts 5 seconds after the previous = WAL update was applied; will it get the new data -- row versions xids -- ju= st applied from the WAL update from #2? It looks like the xids are preserve= d on the primary when that param is set: hot_standby_feedback, but just wan= t to confirm, that while the primary maybe more bloated now with dead rows,= the new and existing (which could be dead on the primary) are there for t= he standby to see?) And sorry, just one last question (I'll be going over the docs thoroughly t= oday and will be testing these in two VM's before I do anything in producti= on) and that is: for the vacuuming of dead rows on the master, I assume tha= t will now occur once there are no queries running on the standby? We have = a lot of cpu power/space/mem, so that won't be an issue, but I'm just curio= us what will trigger that, I just assume no active queries running would do= that -- pls let me know if I'm wrong. I've seen this, but prefer not to set that because it may still kill long r= unning queries on the standby. "Another option is to increase vacuum_defer_cleanup_age on the primary server, so that dead rows will not be cleaned up a= s quickly as they normally would be. This will allow more time for queries = to execute before they are canceled on the standby, without having to set a= high max_standby_streaming_delay. However it is difficult to guarantee any= specific execution-time window with this approach, since vacuum_defer_clea= nup_age is measured in transactions executed on the primary server." Again, thank you both. Alex From: Yaser Raja > Date: Monday, November 23, 2015 4:08 PM To: Scott Ribe > Cc: "Williams, Alex" >,= "pgsql-admin@postgresql.org" > Subject: Re: [ADMIN] Question on Hot Standby in PostgreSQL On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe > wrote: On Nov 20, 2015, at 12:50 PM, Williams, Alex > wrote: > > However, I thought with the use of MVCC, > an active query on the slave (long running, 30 seconds+) can run reading > from row xid versions/snapshot... The problem is that the master could well make changes that would remove ro= ws in that snapshot because the master does not know anything about the row= s that need preserving for the replica's query. hot_standby_feedback addresses this problem. Here is the detail from the PG docs: "Specifies whether or not a hot standby will send feedback to the primary o= r upstream standby about queries currently executing on the standby. This p= arameter can be used to eliminate query cancels caused by cleanup records, = but can cause database bloat on the primary for some workloads. Feedback me= ssages will not be sent more frequently than once per wal_receiver_status_i= nterval. The default value is off. This parameter can only be set in the po= stgresql.conf file or on the server command line." http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html Note that this will only avoid the conflicts caused by cleanup records (whi= ch normally is the majority of conflicts). That said, IIRC there may be something in 9.4 or 9.5 which provides for the= replica to send that info to the server in order to coordinate things, but= I haven't used that and so cannot offer more. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin --_000_D278FEB5111A05awilliamsdresourcesgroupcom_ Content-Type: text/html; charset="us-ascii" Content-ID: Content-Transfer-Encoding: quoted-printable
Thank you both, I really appreciate your responses, and hopefully this= may help others who may run into this.

I noticed on the link to that ref. it was 9.3, we have 9.2 (which can = use that param too,) but I didn't realize I was looking at the 9.0 docs and= didn't see that param there.

Concerning these two params below, when using hot_standby_feedbac= k,  what would you set them if you had the situation where you had que= ries constantly running on the standby, some quick and some long, prac= tically all day/night, everyday?

max_standby_archive_delay (integer)
max_standby_streaming_delay= (integer)

[http://www.postgresql.org/docs/9.2/static/hot-standby.html]

Remedial possibilities exist if the number of standby-query cancellati= ons is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do no= t occur. If you do this, you should note that this will delay cleanup of de= ad rows on the primary, which may result in undesirable table bloat. Howeve= r, the cleanup situation will be no worse than if the standby queries were running directly on the primary = server, and you are still getting the benefit of off-loading execution onto= the standby. max_standby_archive_delay must be kept large = in this case, because delayed WAL files might already contain entries that = conflict with the desired standby queries.

Should I still keep them as -1 (infinite)? If I do, will the WAL updat= es still be applied while current queries are running, for example:

1. Query1 starts SELECT (10 minute query) @ 10:00:00 AM (current row v= ersion xid's)
2. The WAL update is applied to the standby (2 seconds) star= ts at 10:00:08 AM, completes at 10:00:10 AM
3. Query2 starts SELECT @ 10:00:15 AM (starts 5 seconds after the prev= ious WAL update was applied; will it get the new data -- row versions xids = -- just applied from the WAL update from #2? It looks like the xids are pre= served on the primary when that param is set: hot_standby_feedback, but just want to confirm, that wh= ile the primary maybe more bloated now with dead rows, the new and existing= (which could be dead on the primary)  are there for the standby&= nbsp;to see?)

And sorry, just one last question (I'll be going over the docs thoroug= hly today and will be testing these in two VM's before I do anything in pro= duction) and that is: for the vacuuming of dead rows on the master, I assum= e that will now occur once there are no queries running on the standby? We have a lot of cpu power/spa= ce/mem, so that won't be an issue, but I'm just curious what will trigger t= hat, I just assume no active queries running would do that -- pls let me kn= ow if I'm wrong.

I've seen this, but prefer not to set that because it may still kill l= ong running queries on the standby.
"Another option is to increase vacuum_defer_cleanup_age on the primary server, so that dead rows will = not be cleaned up as quickly as they normally would be. This will allow more time for queries to execute before they are cancele= d on the standby, without having to set a high max_standby_streaming_delay. However it is diffi= cult to guarantee any specific execution-time window with this approach, si= nce vacuum_defer_cleanup_age is measured in transact= ions executed on the primary server."

Again,  thank you both.

Alex

From: Yaser Raja <yrraja@gmail.com>
Date: Monday, November 23, 2015 4:0= 8 PM
To: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: "Williams, Alex" <= awilliams@teamdrg.com>, &qu= ot;pgsql-admin@postgresql.org= " <pgsql-admin@po= stgresql.org>
Subject: Re: [ADMIN] Question on Ho= t Standby in PostgreSQL



On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe <scott_= ribe@elevated-dev.com> wrote:
On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams@teamdrg.com> wrote:
>
> However, I thought with the use of MVCC,
> an active query on the slave (long running, 30 seconds+) can run r= eading
> from row xid versions/snapshot...

The problem is that the master could well make changes that would remove ro= ws in that snapshot because the master does not know anything about the row= s that need preserving for the replica's query.


hot_standby_feedback addresses this problem. 

Here is the detail from the PG docs:

"Spe= cifies whether or not a hot standby will send feedback to the primary or up= stream standby about queries currently executing on the standby. This param= eter can be used to eliminate query cancels caused by cleanup records, but can cause database bloat on the primary for some workloads. Feedback messages will not be sent mo= re frequently than once per wal_receiver_status_interval. The default value is off. This parameter can onl= y be set in the postgresql.conf file or on = the server command line."

http://www.postgresql.org/docs/9.3/static/runtime-= config-replication.html


Note that this will only avoid the conflicts caused by cleanup records= (which normally is the majority of conflicts). 


 

That said, IIRC there may be something in 9.4 or 9.5 which provides for the= replica to send that info to the server in order to coordinate things, but= I haven't used that and so cannot offer more.

--
Scott Ribe
scott_ribe@elevated-dev.com<= /a>
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0= 567 voice







--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

 --_000_D278FEB5111A05awilliamsdresourcesgroupcom_--