pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: What causes streaming replication delay? [SOLVED, I think] 4+ messages / 3 participants [nested] [flat]
* Re: What causes streaming replication delay? [SOLVED, I think] @ 2016-08-31 20:22 Dianne Skoll <dfs@roaringpenguin.com> 2016-08-31 20:51 ` Re: What causes streaming replication delay? [SOLVED, I think] Scott Whitney <scott@journyx.com> 2016-08-31 21:14 ` Re: What causes streaming replication delay? [SOLVED, I think] Eduardo Morras <emorrasg@yahoo.es> 0 siblings, 2 replies; 4+ messages in thread From: Dianne Skoll @ 2016-08-31 20:22 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What causes streaming replication delay? [SOLVED, I think] 2016-08-31 20:22 Re: What causes streaming replication delay? [SOLVED, I think] Dianne Skoll <dfs@roaringpenguin.com> @ 2016-08-31 20:51 ` Scott Whitney <scott@journyx.com> 1 sibling, 0 replies; 4+ messages in thread From: Scott Whitney @ 2016-08-31 20:51 UTC (permalink / raw) To: Dianne Skoll <dfs@roaringpenguin.com>; pgsql-admin 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 think] 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/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What causes streaming replication delay? [SOLVED, I think] 2016-08-31 20:22 Re: What causes streaming replication delay? [SOLVED, I think] Dianne Skoll <dfs@roaringpenguin.com> @ 2016-08-31 21:14 ` Eduardo Morras <emorrasg@yahoo.es> 1 sibling, 0 replies; 4+ messages in thread From: Eduardo Morras @ 2016-08-31 21:14 UTC (permalink / raw) To: pgsql-admin On Wed, 31 Aug 2016 16:22:22 -0400 Dianne Skoll <dfs@roaringpenguin.com> wrote: > 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. You were bitten by the RFC 1925 points 1, 2 and 9. A solution as is point 3 is "a bad idea". https://tools.ietf.org/html/rfc1925 > 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 --- --- Eduardo Morras <emorrasg@yahoo.es> -- 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] 4+ messages in thread
* Re: What causes streaming replication delay? [SOLVED, I think] @ 2016-08-31 20:49 Scott Whitney <scott@journyx.com> 0 siblings, 0 replies; 4+ messages in thread From: Scott Whitney @ 2016-08-31 20:49 UTC (permalink / raw) To: Dianne Skoll <dfs@roaringpenguin.com>; pgsql-admin I was wondering about that, Dianne. In general, PG (or any other DB in the world...don't paint this 12yr+ user as a hater) suffers from I/O issues and substructures (NAS, SAN, NFS log or WAL) about 95%. Memory and CPU cover the other 4.9%. Then there's network I/O. I have found in my empirical testing that 2Mbps keels me where I need to be for replication, BUT I am only using streaming replication. I don't WAL archive outside my LAN. NO, I DON'T WANT TO TALK ABOUT IT. ;) When you have no appreciable load, and you're running behind on repl? It's time to look at your network. Do you use Nagios or any other network monitoring software? I have written some scripts to alert me to low bandwidth scenarios which I would be happy to share with you (and anyone else). -------- 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 think] 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/ ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-08-31 21:14 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-08-31 20:22 Re: What causes streaming replication delay? [SOLVED, I think] Dianne Skoll <dfs@roaringpenguin.com> 2016-08-31 20:51 ` Scott Whitney <scott@journyx.com> 2016-08-31 21:14 ` Eduardo Morras <emorrasg@yahoo.es> 2016-08-31 20:49 Re: What causes streaming replication delay? [SOLVED, I think] Scott Whitney <scott@journyx.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