SATA Write Cache PostgreSQL ZFS

D

dkovacevic

I have a PostgreSQL database running with ZFS, using consumer grade SATA drives. The write cache is enabled for these drives. They also support Native Command Queueing.

I am performing backups of the database every evening. However, it would be nice not to have to be concerned with losing a full day's worth of data due to a sudden power failure.

ZFS solves the issue of the write cache by demanding that drives flush changes before continuing to the next transaction. My understanding is that this only applies to certain drives.

How can I tell whether the disks in this server ARE supported by the ZFS cache flush mechanism?
 
In theory ZFS instructs the SATA drive to flush the write caches. In reality SATA drives tend to lie about this.
 
I don't doubt that ZFS issues flush statements, and that some drives lie to improve throughput.

I want to know how to tell whether or not the drives are lying, and if they are lying, what can be done about it.
 
As far as I know, there is no reliable way to tell. The details of the cache flush other than the drive telling "I have flushed the cache" are not visible trough the standard ATA protocol.
 
You know the disks are listening if you set:
# zfs set sync=always pool/fs
And think to yourself "Gosh, look how slow it got all of a sudden.":)

To speed up performance on synchronized writes you have to buy the fastest SSD on the market to maybe reach 100MB/s.

/Sebulon
 
Postgres on ZFS is very likely to keep the transaction states in good shape. You may lose the last (or a few) db transaction(s) when a power outage occurs, depending on vfs.zfs.txg_timeouts and memory buffers. The content of RAM cache is lost on power outages regardless of filesystem beneath. I run my Postgres instances with fsync=off (in postgresql.conf) . I use ZFS snaphshots every 15 minutes for one day. I trust ZFS to do it's magic. But I do not have mission-critical databases ;-)
HTH
 
dkovacevic said:
I have a PostgreSQL database running with ZFS, using consumer grade SATA drives. The write cache is enabled for these drives. They also support Native Command Queueing.

I am performing backups of the database every evening. However, it would be nice not to have to be concerned with losing a full day's worth of data due to a sudden power failure.
Click to expand...

Since 7 years, I am living in a country with frequent sudden power failures. For a too long time, I tried to come away without a UPS, and in total this killed me 3 drives. Last year, I bought a APC-UPS 600ES, which supplies power enough to my server for it shuting down smoothly, i.e. regularly terminating all the services, synching all the drives, etc., and it restarts automatically once the power is back. All this works perfectly with FreeBSD thanks to sysutil/apcupsd.

Out of initially bad, and now very good experience I can only suggest to buy a UPS. Said model is less expensive than a good hard drive.
 
You must log in or register to reply here.
Back
Top