Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bfCVm-0005AZ-EG for pgsql-admin@arkaria.postgresql.org; Wed, 31 Aug 2016 20:53:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bfCVm-0004nl-1C for pgsql-admin@arkaria.postgresql.org; Wed, 31 Aug 2016 20:53:30 +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_2) (envelope-from ) id 1bfCU3-0001TJ-PE for pgsql-admin@postgresql.org; Wed, 31 Aug 2016 20:51:43 +0000 Received: from mail-bl2nam02on0078.outbound.protection.outlook.com ([104.47.38.78] helo=NAM02-BL2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bfCTy-00069X-QF for pgsql-admin@postgresql.org; Wed, 31 Aug 2016 20:51:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=journyx.onmicrosoft.com; s=selector1-journyx-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=PUutAEahZA1ilqI2dDZPOrSqQYp+EulgQrFnMl5DzxI=; b=HHikc9Yfnwj4GpjIoor4xS3D82X5HOvnxKJTHqEQrY3FsONh9rg9oFkPzrRWqQeQTWtP9QhZk4DX1HU8zkyRhIEV6c0bPJpSdMXxMUZ+gJealZz0XaTIcRVW1W13jF2yKylYgWRAPXK+TDt3vNRGRaN8NTszgCiIpfENqrJJ6LU= Received: from BY1PR11MB0390.namprd11.prod.outlook.com (10.162.146.14) by BY1PR11MB0389.namprd11.prod.outlook.com (10.162.146.139) with Microsoft SMTP Server (version=TLS1_0, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384) id 15.1.587.9; Wed, 31 Aug 2016 20:51:34 +0000 Received: from BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) by BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) with mapi id 15.01.0599.010; Wed, 31 Aug 2016 20:51:33 +0000 From: Scott Whitney To: Dianne Skoll , pgsql-admin Subject: Re: What causes streaming replication delay? [SOLVED, I think] Thread-Topic: [ADMIN] What causes streaming replication delay? [SOLVED, I think] Thread-Index: AQHSA8WdvxdK6W9xsEyumeSjRQMOSqBji1+B Date: Wed, 31 Aug 2016 20:51:33 +0000 Message-ID: References: <20160831114417.7fc1040d@hydrogen.roaringpenguin.com> <20160831131604.0f305404@hydrogen.roaringpenguin.com>,<20160831162222.21288e76@hydrogen.roaringpenguin.com> In-Reply-To: <20160831162222.21288e76@hydrogen.roaringpenguin.com> 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=scott@journyx.com; x-originating-ip: [70.112.92.197] x-ms-office365-filtering-correlation-id: 3d17ff42-d237-49bb-d103-08d3d1e097d5 x-microsoft-exchange-diagnostics: 1;BY1PR11MB0389;6:a3aVCf6jAWmGX7PYFlxU/3ouGMX5ac7YCNX7kDuSpyrM6pRmsqJA7RD7c/awpgyhOO+YZH3/dpwgLLC6UkD4h/SmtJ6SG30bgQ7nRwTAJ2d0ofL0NvMyZAIWiR9akckO8hR0MwYWDyj3+VZEzTxgRKKAu6/3uRXICCT9nKqFv31U8Lf7tLgwoHdcAI5DZI7RzQ3yVJ9kLokb4vZXJPCeuTTu13gBS/JOwWm9Ugt0RPo8BFi2OMBwrbhaeXWuRd9ADwG/yvULc8ZlawLAG8wqbwkSulZgvjQTlU+n3oM1j7fr4shVOcSDIl1jCw2pG+Fv;5:NKQDQ/synDb+3/nSNjjz5rudT8BS9Ru3yfJIu1BhYl8eh2eS14GY1TK5VVlKJ54hc1PbkI9PAsz6QK/d2ghUMpB6W493ciBq7DaQ8U21/SSM+h9AsswYeiVvY8GXRNeIfYwONyU471xI3ALr94Aydg==;24:j1V2oLNVkLC+v9IojqdsWUP67EQdu7SlY8rZRPTUgY2UGiP0bRA+tRqQzLJsF+bpFzvqTqTqV7IdFmsqThSm8rxjz7hCQDT7MhTxvhkyzck=;7:X1gaUJ8B7D9cPQVQHWFbYFMLYAteFo3dpFhWA/ZTFY8DHllBGzRFBZMAnX44QQNB+jHOo1YqA0sVYscmA82mpESL2sLqyTzFXig8fgbQnbhzdWhWJ6fwFUsijY+It8NnJlOeCk1M9mg8ktFJuBZ4LMeffpax/nbOnKkuqHNSpgz/YWhujKbtgunO6hQGG1TPW+uyZWVa7+E1TrYOInFsvjGVT0pquP34Ocbg5CPnhgUQ7c+y829zx2cHlx0r8ixB x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0389; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:(158342451672863)(101568727142576); x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(6040176)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6042046)(6043046);SRVR:BY1PR11MB0389;BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0389; x-forefront-prvs: 00514A2FE6 x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(7916002)(199003)(189002)(377454003)(15974865002)(10400500002)(9686002)(106356001)(105586002)(106116001)(68736007)(3660700001)(92566002)(16236675004)(3280700002)(5660300001)(63666004)(19617315012)(122556002)(99286002)(93886004)(33646002)(5002640100001)(7846002)(87936001)(77096005)(107886002)(97736004)(5001770100001)(7906003)(19580395003)(189998001)(15975445007)(81156014)(3846002)(102836003)(81166006)(66066001)(19580405001)(7736002)(86362001)(2906002)(95246002)(101416001)(586003)(2900100001)(6116002)(8676002)(76176999)(50986999)(54356999)(19625215002)(2950100001)(8936002)(51650200001);DIR:OUT;SFP:1101;SCL:1;SRVR:BY1PR11MB0389;H:BY1PR11MB0390.namprd11.prod.outlook.com;FPR:;SPF:None;PTR:InfoNoRecords;A:1;MX:1;LANG:en; received-spf: None (protection.outlook.com: journyx.com does not designate permitted sender hosts) spamdiagnosticoutput: 1:99 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_cmqukrrfd3nlbxm892kvjs641472676585091emailandroidcom_" MIME-Version: 1.0 X-OriginatorOrg: journyx.com X-MS-Exchange-CrossTenant-originalarrivaltime: 31 Aug 2016 20:51:33.6971 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 83db917a-cb55-44ba-82ef-25aa3e32343f X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0389 X-Pg-Spam-Score: -1.9 (-) 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_cmqukrrfd3nlbxm892kvjs641472676585091emailandroidcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Also, for the stupid-system-impaired-metric-didn't-put-us-on-the-moon, 200k= m is like 120 miles. Or, for the LOCAL network administrator something like 650k feet. So, we're not talking a direct cable... ;) -------- Original message -------- From: Dianne Skoll Date: 08/31/2016 3:24 PM (GMT-06:00) To: pgsql-admin Subject: Re: [ADMIN] What causes streaming replication delay? [SOLVED, I th= ink] Hi, I think I solved it. This is so ridiculous! The primary and standby databases are 200km apart and the path between them has a bandwidth of only 100 Mb/s. We had bursts of writes that created huge amounts of replication traffic. In addition to streaming replication, we're still copying WAL files over for safety (and also because this is 9.1...) We were hitting our bandwidth limit. By adding the --bwlimit parameter to the rsync command that copies the WAL files, we can limit the bandwidth usage during the bursts while still keeping it high enough for the WAL-file archiving to keep up on average. Keeping an eye on it for 24h before I completely declare it solved. Regards, Dianne. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin Journyx, Inc. 7600 Burnet Road #300 Austin, TX 78757 www.journyx.com p 512.834.8888 f 512-834-8858 Do you receive our promotional emails? You can subscribe or unsubscribe to = those emails at http://go.journyx.com/emailPreference/e/4932/714/ --_000_cmqukrrfd3nlbxm892kvjs641472676585091emailandroidcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Also, for the stupid-system-impaired-metric-didn't-put-us-on-the-moon,= 200km is like 120 miles.

Or, for the LOCAL network administrator something like 650k feet.

So, we're not talking a direct cable...

;)


-------- Original message --------
From: Dianne Skoll <dfs@roaringpenguin.com>
Date: 08/31/2016 3:24 PM (GMT-06:00)
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] What causes streaming replication delay? [SOLVED, I th= ink]

Hi,

I think I solved it.  This is so ridiculous!

The primary and standby databases are 200km apart and the path between them=
has a bandwidth of only 100 Mb/s.  We had bursts of writes that create= d
huge amounts of replication traffic.  In addition to streaming
replication, we're still copying WAL files over for safety (and also
because this is 9.1...)

We were hitting our bandwidth limit.

By adding the --bwlimit parameter to the rsync command that copies the WAL<= br> files, we can limit the bandwidth usage during the bursts while still
keeping it high enough for the WAL-file archiving to keep up on average.
Keeping an eye on it for 24h before I completely declare it solved.

Regards,

Dianne.


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


Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscrib= e to those emails at http://go.journyx.com/emailPreference/e/4932/714/ = ;
--_000_cmqukrrfd3nlbxm892kvjs641472676585091emailandroidcom_--