Обсуждение: [PERFORM] 99% time spent in WAL wait events
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
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
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" <puravc@gmail.com> 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.66Disk 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 ThanksRegards