Hi Jeff,
Thank you for replying.
wal_sync_method=fsync
Why this change?
Actually, I re-checked and noticed that this config section was left to it's default values, which is the following. Since the commented line said `wal_sync_method = fsync`, I _assumed_ that's the default value. But it seems that Linux uses fdatasync, by default, and the output of pg_test_fsync also shows that it is /probably/ the fastest method on my hardware.
# wal_sync_method = fsync
# the default is the first option supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
PGOPTIONS="-c synchronous_commit=off" pgbench -T 3600 -P 10 ....
I am currently running all my benchmarks with synchronous_commit=off and will get back with my findings.
You could also try pg_test_fsync to get low-level information, to supplement the high level you get from pgbench.
Thanks for pointing me to this tool. never knew pg_test_fsync existed! I've run `pg_test_fsync -s 60` two times and this is the output - https://gist.github.com/saurabhnanda/b60e8cf69032b570c5b554eb50df64f8 I'm not sure what to make of it? Shall I tweak the setting of `wal_sync_method` to something other than the default value?
The effects of max_wal_size are going to depend on how you have IO configured, for example does pg_wal shared the same devices and controllers as the base data? It is mostly about controlling disk usage and crash-recovery performance, neither of which is of primary importance to pgbench performance.
The WAL and the data-directory reside on the same SSD disk -- is this a bad idea? I was under the impression that smaller values for max_wal_size cause pg-server to do "maintenance work" related to wal rotation, etc. more frequently and would lead to lower pgbench performance.
Not all SSD are created equal, so the details here matter, both for the underlying drives and the raid controller.
Here's the relevant output from lshw -- https://gist.github.com/saurabhnanda/d7107d4ab1bb48e94e0a5e3ef96e7260 It seems I have Micron SSDs. I tried finding more information on RAID but couldn't get anything in the lshw or lspci output except the following -- `SATA controller: Intel Corporation Sunrise Point-H SATA controller [AHCI mode] (rev 31)`. Moreover, the devices are showing up as /dev/md1, /dev/md2, etc. So, if my understanding is correct, I don't think I'm on hardware RAID, but software RAID, right?
These machines are from the EX-line of dedicated servers provided by Hetzner, btw.
PS: Cc-ing the list back again because I assume you didn't intend for your reply to be private, right?
-- Saurabh.
