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.
--
