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/