MIME-Version: 1.0
From: Purav Chovatia <puravc@gmail.com>
Date: Mon, 16 Oct 2017 19:04:36 +0530
Message-ID: 
 <CADrzpjE7RZtPjEWQYitaRo5wjfVTy02LhAbDsgCk43WxPH=acA@mail.gmail.com>
Subject: 99% time spent in WAL wait events
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="94eb2c0d0dde5aa78b055baa1136"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c0d0dde5aa78b055baa1136
Content-Type: text/plain; charset="UTF-8"

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

--94eb2c0d0dde5aa78b055baa1136
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>We are running workload on a EDB=
 Postgres Advanced Server 9.6 and we see that 99% of the time is spent on W=
AL wait events:</div><div><span style=3D"font-size:10pt;font-family:Arial,s=
ans-serif"><i>System Wait
Information<br>
<br>
WAIT NAME COUNT WAIT TIME % WAIT<br>
---------------------------------------------------------------------------=
<br>
wal flush 564552 298.789464 41.67<br>
wal write 521514 211.601124 29.51<br>
wal file sync 521546 205.519643 28.66<br></i>
<br>
</span></div><div><span style=3D"font-size:10pt;font-family:Arial,sans-seri=
f">Disk IO performance is not an issue and WAL is on a dedicated disk.</spa=
n></div><div><span style=3D"font-size:10pt;font-family:Arial,sans-serif"><b=
r></span></div><div><span style=3D"font-size:10pt;font-family:Arial,sans-se=
rif">Can somebody pls suggest if there is any possibility to improve this &=
amp; how?</span></div><div><span style=3D"font-size:10pt;font-family:Arial,=
sans-serif"><br></span></div><div><span style=3D"font-size:10pt;font-family=
:Arial,sans-serif">We already tried wal_buffers=3D96m, wal_sync_method=3Dop=
en_sync/open_datasync, checkpoint_completion_target=3D0.9 but none of those=
 helped.</span></div><div><span style=3D"font-size:10pt;font-family:Arial,s=
ans-serif"><br></span></div><div><p><span style=3D"font-size:10pt;font-fami=
ly:Arial,sans-serif">System has 32GB RAM and shared_buffers=3D8GB. All tran=
sactions are
happening on a single table which has about 1.5m records and the table size=
 is
1.7GB with just one PK index.<span></span></span></p></div><div><span style=
=3D"font-size:10pt;font-family:Arial,sans-serif"><br></span></div><div><spa=
n style=3D"font-size:10pt;font-family:Arial,sans-serif">Many Thanks</span><=
/div><div><span style=3D"font-size:10pt;font-family:Arial,sans-serif"><br><=
/span></div><div><span style=3D"font-size:10pt;font-family:Arial,sans-serif=
">Regards</span></div></div>

--94eb2c0d0dde5aa78b055baa1136--