RE: Changing default value of wal_sync_method to open_datasync onLinux - Mailing list pgsql-hackers

From Tsunakawa, Takayuki
Subject RE: Changing default value of wal_sync_method to open_datasync onLinux
Date
Msg-id 0A3221C70F24FB45833433255569204D1F8CE01E@G01JPEXMBYT05
Whole thread Raw
In response to Re: Changing default value of wal_sync_method to open_datasync onLinux  (Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Responses Re: Changing default value of wal_sync_method to open_datasync onLinux
List pgsql-hackers
From: Mark Kirkwood [mailto:mark.kirkwood@catalyst.net.nz]
> I think the use of 'nobarrier' is probably disabling most/all reliable
> writing to the devices. What do the numbers look like if use remove this
> option?

Disabling the filesystem barrier is a valid tuning method as the PG manual says:

https://www.postgresql.org/docs/devel/static/wal-reliability.html

[Excerpt]
Recent SATA drives (those following ATAPI-6 or later) offer a drive cache flush command (FLUSH CACHE EXT), while SCSI
driveshave long supported a similar command SYNCHRONIZE CACHE. These commands are not directly accessible to
PostgreSQL,but some file systems (e.g., ZFS, ext4) can use them to flush data to the platters on write-back-enabled
drives.Unfortunately, such file systems behave suboptimally when combined with battery-backup unit (BBU) disk
controllers.In such setups, the synchronize command forces all data from the controller cache to the disks, eliminating
muchof the benefit of the BBU. You can run the pg_test_fsync program to see if you are affected. If you are affected,
theperformance benefits of the BBU can be regained by turning off write barriers in the file system or reconfiguring
thedisk controller, if that is an option. If write barriers are turned off, make sure the battery remains functional; a
faultybattery can potentially lead to data loss. Hopefully file system and disk controller designers will eventually
addressthis suboptimal behavior. 


I removed nobarrier mount option on a VM with HDD.  pgbench throughput decreased about 30% to 3550 tps, but the
relativedifference between fdatasync and open_datasync is similar.  I cannot disable nowritebarrier on the bare metal
withSSD for now, as other developers and test teams are using it. 



Regards
Takayuki Tsunakawa






pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Typo in procarray.c
Next
From: Alvaro Herrera
Date:
Subject: Re: Server crash in pg_replication_slot_advance function