On Nov 2, 2014, at 12:33 PM, Greg Spiegelberg <gspiegelberg@gmail.com> wrote:

Hi Mike,

Sounds very familiar. Our master fans out to 16 slaves = (cascading) and we had great success with segregating database queries = to different slaves and some based on network latency. I'd = suggest, if possible, alter the application to use the slave for simple = SELECT's and FUNCTION's performing SELECT-like only work while limiting = those applications and queries that perform DML to the master = (obviously). If the load on the slave increases too much, spin up = another slave. I'd mention from experience that it could be the = load on the slave that is giving the appearance of replication = lag. This is what led us to having (1) slave per = application.

There is also the BDR multi-master available in 9.4beta if = you're wanting to live on the edge.

-Greg

On Sat, = Nov 1, 2014 at 4:33 PM, Mike Wilson <mfwilson@gmail.com> wrote:
I have two 9.3.4 PG instances = that back a large internet website that has very seasonal traffic and = can generate large query loads. My instances are in a master-slave = streaming replication setup and are stable and in general perform = very well. The only issues we have with the boxes is that when the = master is busy the slave may start to lag excessively. I can give = specifics as to what heavily loaded means and additionally the = postgresql.conf for both boxes but my basic questions are:
* What causes streaming replication lag to = increase?
* What parameters can = be tuned to reduce streaming replication lag?
= * Can a loaded slave affect lag adversely?
* Can increasing max_wal_senders help reduce = lag?

The = reason I ask this is that as mentioned above the servers are stable and = are real troopers in general as they back a very popular web site that = puts the master under heavy seasonal load at times. At those times = though we see an almost exponential growth in streaming replication lag = compared to load on the master.

For example, the master is a very beefy = Solaris:
* 4 Recent Intel Zeons (16 = physical cores)
* 256 GB of ECC = RAM
* 12 TB of ZFS (spindle and SSD = internal storage)
* DB on disk size is = 2TB
* ZFS ARC cache of roughly = 250G.
* ZFS ARC2/ZIL configured for = SSD=E2=80=99s (this is awesome by the way)

Basic PG Config:
shared_buffers =3D 2GB
= work_mem =3D 128MB
= max_connections =3D 1700 (supports roughly 100 web = servers)
wal_keep_segments =3D 256 = (roughly enough for 24 hours of operation under heavy load)
wal_sender_timeout =3D 60s
replication_timeout=3D(not set)
wal_receiver_status_interval=3D10s
max_wal_senders=3D6
= * wal archiving is off
* 98% of = the queries on the master complete in under 500ms.
* No hung or very long running queries in = general.

The = master on a normal day maintains a load of about 0.5, during which = replication lag to the slave is in hundreds milliseconds. When the = production db server is heavily hit though the load may go as high as 4 = on the master and the streaming replication lag may increase to more = than 2 hours relatively quickly. Load on the slave is generally = below 1 even when the master is heavily loaded. The traffic to the = master is primarily read with about 10% DML (new users, purchase = records, etc). DML statements increase proportionally when under = load though. The master and slave are connected via dedicated 10G = fiber link and even under heavy load the utilization of the link is = nowhere near close to saturation. BTW, the slave does run some = reported related queries throughout the day that might take up to a = minute to complete.

I have the task of figuring out why this otherwise healthy DB = starts to lag so badly under load and if there is anything that we could = do about it. I=E2=80=99ve been wondering particularly if we should = up the max_wal_senders but from the docs it is unclear if that would = help. In my testing with pg_bench on our dev boxes which were the = previous production hardware for these servers I have determined that it = doesn=E2=80=99t take much DML load on the master to get the slave to = start lagging severely. I was wondering if this was expected = and/or some design consideration? Possibly streaming replication = isn=E2=80=99t meant to be used for heavily hit databases and maintain = small lag times? I would like to believe that the fault is = something we have done though and that there is some parameter we could = tune to reduce this lag.

Any recommendations would be very helpful.

Mike Wilson
Predicate Logic Consulting