Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1e47Mh-0006QI-7z for pgsql-performance@arkaria.postgresql.org; Mon, 16 Oct 2017 15:31:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1e47Mg-0003n3-Qn for pgsql-performance@arkaria.postgresql.org; Mon, 16 Oct 2017 15:31:38 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1e47Mf-0003fp-Kj for pgsql-performance@postgresql.org; Mon, 16 Oct 2017 15:31:37 +0000 Received: from mail-wm0-x235.google.com ([2a00:1450:400c:c09::235]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1e47Mc-0007hB-Tb for pgsql-performance@postgresql.org; Mon, 16 Oct 2017 15:31:36 +0000 Received: by mail-wm0-x235.google.com with SMTP id q132so4590670wmd.2 for ; Mon, 16 Oct 2017 08:31:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=cuJQnGfou2TTfKBk4F/sZ9rqQedJwTCaJtSE7QdoHw4=; b=KTkwANY8eKJowRvz3XdbAgxz5wPP5eOATDp2yCOMX9wyvZCs6Wm0BSOKK9B9hVWKA7 Utk4qy+BijztBTb2gs68uvP1SV37vge+/LwsWsf+yR7sy6wd/k1TQf0tgT2E12JJyjXl 70Tod1ZJrr9sq2dq9mVr0ZIoU7Sa4ZdjHyREJAN4C6lhRAqW7ift9GxTuimvEg8KmoE+ lBudIuq7fUGI3963MmUht0Y43qeCjunV3DHddMBCbrQbkuf4drt1ii3cyKAxU1OCPt5G FF3NKOJ5TWFRh/ERtDpu/AjIbLi5iGzt6oqfnSxk9j1Zk7RYGQxhBxVMZE+OXHipmeN7 nMGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=cuJQnGfou2TTfKBk4F/sZ9rqQedJwTCaJtSE7QdoHw4=; b=gCGYCotM2DvvO8vGbC2dX7MKCf4wP2TOOh9jBzHkkM04XirQoW8QkEjctyyn5H1uLU onOM7qZBX95sh0yLODbUSYHp7OLx7ywg7XOs5zsF54Xc9HMGVVgcQSiAvmhwoQq65dbY aY/HjcW4U4QkLA342ksV7QAntJ6VJXffR/XwEkrrnjmMFeJpqz84eV753mUNYGTCysWo w0yxLRcf2JlfUj1oK3+58VT0rI8tpVsbszfMne93QaOsHK9MuvEiVKyGnrS6STpZjsOH kG7/oB8qknQ0xFGeCl8m6C3EV2RbweHtQqP/Ysvqj7qa88tJF1KeC4WajdixN7+/dkCJ N4Qw== X-Gm-Message-State: AMCzsaX/m9VEKZyyN+xfh0CcCtav0bEvwXK//e/9cwr8a530bqn1/h3M 07nS7m/gI1+XOWbQDIChZI29m/YsLAONviNzoDg= X-Google-Smtp-Source: ABhQp+QxZU4AX5OU2D9uUaITYNERampbMZJpkC0ylv3q48l2BqgJ3Y32CKx/NON+og5FZBbTkNsVO78VNGkNLfQJVbs= X-Received: by 10.223.132.163 with SMTP id 32mr891709wrg.218.1508167893055; Mon, 16 Oct 2017 08:31:33 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.197.78 with HTTP; Mon, 16 Oct 2017 08:31:32 -0700 (PDT) Received: by 10.223.197.78 with HTTP; Mon, 16 Oct 2017 08:31:32 -0700 (PDT) In-Reply-To: References: From: Purav Chovatia Date: Mon, 16 Oct 2017 21:01:32 +0530 Message-ID: Subject: Re: 99% time spent in WAL wait events To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="94eb2c0d0dde86fd9d055babb327" 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 --94eb2c0d0dde86fd9d055babb327 Content-Type: text/plain; charset="UTF-8" Kindly ignore this post. It was an oversight - the wait times are in millisec and hence even if we manage to reduce these waits to 0, we will gain only 1000 msec of savings during a workload of 40min. Regards On 16 Oct 2017 7:04 pm, "Purav Chovatia" wrote: Hello, We are running workload on a EDB Postgres Advanced Server 9.6 and we see that 99% of the time is spent on WAL wait events: *System Wait Information WAIT NAME COUNT WAIT TIME % WAIT --------------------------------------------------------------------------- wal flush 564552 298.789464 41.67 wal write 521514 211.601124 29.51 wal file sync 521546 205.519643 28.66* Disk IO performance is not an issue and WAL is on a dedicated disk. Can somebody pls suggest if there is any possibility to improve this & how? We already tried wal_buffers=96m, wal_sync_method=open_sync/open_datasync, checkpoint_completion_target=0.9 but none of those helped. System has 32GB RAM and shared_buffers=8GB. All transactions are happening on a single table which has about 1.5m records and the table size is 1.7GB with just one PK index. Many Thanks Regards --94eb2c0d0dde86fd9d055babb327 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Kindly ignore this post. It was an oversight - the wait t= imes are in millisec and hence even if we manage to reduce these waits to 0= , we will gain only 1000 msec of savings during a workload of 40min.=C2=A0<= div dir=3D"auto">
Regards

On 16 Oct 2017 7:04 pm, &qu= ot;Purav Chovatia" <puravc@gmai= l.com> wrote:
Hello,

We are running workload on a EDB Post= gres Advanced Server 9.6 and we see that 99% of the time is spent on WAL wa= it events:
System Wait Information

WAIT NAME COUNT WAIT TIME % WAIT
-----------------------------------------------------------------= ----------
wal flush 564552 298.789464 41.67
wal write 521514 211.601124 29.51
wal file sync 521546 205.519643 28.66
Disk IO performance is not an issue and WAL is on a dedicated disk.
Can somebody pls suggest if there is any possibility to improve this &= amp; how?

We already tried wal_buffers=3D96m, wal_sync_method=3Dop= en_sync/open_datasync, checkpoint_completion_target=3D0.9 but non= e of those helped.

System has 32GB RAM and shared_buffers=3D8GB= . All transactions are happening on a single table which has about 1.5m records and the table size= is 1.7GB with just one PK index.


Many Thanks<= /div>

<= /span>
Regards

--94eb2c0d0dde86fd9d055babb327--