Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XkhEY-0004Of-FF for pgsql-performance@arkaria.postgresql.org; Sat, 01 Nov 2014 22:33:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XkhEX-0002h4-Lk for pgsql-performance@arkaria.postgresql.org; Sat, 01 Nov 2014 22:33:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XkhEV-0002gv-E4 for pgsql-performance@postgresql.org; Sat, 01 Nov 2014 22:33:19 +0000 Received: from mail-pa0-x22a.google.com ([2607:f8b0:400e:c03::22a]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XkhEM-0007FL-KA for pgsql-performance@postgresql.org; Sat, 01 Nov 2014 22:33:18 +0000 Received: by mail-pa0-f42.google.com with SMTP id bj1so9930378pad.29 for ; Sat, 01 Nov 2014 15:33:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:content-type:subject:message-id:date:to:mime-version; bh=3Q4riMS1hWkkbPhM7a88+KusSv6/x33tobMjkBsVo0k=; b=lBm9vuehkeEwDemOPyjQpywcGFzHBL7KfdDYbFDu4kg6z80/wkAcpbHT/ZJ0xMNe63 UorRvu80A9QRXW3VvOCFhv8uU21GiVNdWe23G6UQEJnz3/P6vqebboJyvettD8QW/rRB ln1D6AvygGPM7SzvSD4HbkDCaad0b9Uk+umTD80CEJ6tx03mezHAUh2oBqa18o1gXyNV rdH+JsLZPNhUig09L5rdEUmi/I7FJ8Dd3rpjP/TOox+17BiH+46vB2n9uGJr0nBwzhhE PN2RLAHm34y1rrqPTvbiw+U+PShPbe/E6FZ0Ee1qJKl3mt8nsXPJlDfqZOLGmToO0ZTP 9D5A== X-Received: by 10.70.42.175 with SMTP id p15mr33427505pdl.53.1414881186978; Sat, 01 Nov 2014 15:33:06 -0700 (PDT) Received: from [10.0.1.16] (cpe-104-174-121-31.socal.res.rr.com. [104.174.121.31]) by mx.google.com with ESMTPSA id pd9sm13315258pbb.7.2014.11.01.15.33.05 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sat, 01 Nov 2014 15:33:06 -0700 (PDT) From: Mike Wilson Content-Type: multipart/alternative; boundary="Apple-Mail=_565180F0-FED9-404C-AB70-D1BDC6DC26AF" Subject: Replication Lag Causes Message-Id: Date: Sat, 1 Nov 2014 15:33:04 -0700 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 8.0 \(1990.1\)) X-Mailer: Apple Mail (2.1990.1) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_565180F0-FED9-404C-AB70-D1BDC6DC26AF Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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? =20 * 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. =20 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. =20 =20 Mike Wilson Predicate Logic Consulting --Apple-Mail=_565180F0-FED9-404C-AB70-D1BDC6DC26AF Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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



= --Apple-Mail=_565180F0-FED9-404C-AB70-D1BDC6DC26AF--