Re: High COMMIT times

Today another DBA suggested that perhaps increasing wal_buffers might be an
option. Currently our wal_buffers is 32MB. Total system memory is 128GB,
FYI.

I'm curious if wal_buffers being full and/or undersized would present
itself in higher COMMIT times as we are observing. We're considering trying
this with wal_buffers=64MB as a start. I don't see any actual limit defined
in the documentation, but I'm assuming there's such a thing as "too high"
here. I'm curious what is and what negative side effects would indicate
that as well.

From the documentation:

On systems with high log output, XLogFlush requests might not occur often
enough to prevent XLogInsertRecord from having to do writes. On such
systems one should increase the number of WAL buffers by modifying the
wal_buffers parameter. When full_page_writes is set and the system is very
busy, setting wal_buffers higher will help smooth response times during the
period immediately following each checkpoint.

We do have full_page_writes=on as well, so this sounds like worth exploring.

We also currently have wal_compression=off. We have plenty of CPU headroom
and so are considering enabling that first (since it doesn't require a DB
restart like changing wal_buffers does). I imagine enabling wal_compression
would save some IO.

I'm interested to hear what you folks think about either or both of these
suggestions.

Thanks,
Don.

--
Don Seiler
www.seiler.us