Comment by kaamos | Hacker News Reader

Comment by kaamos | Hacker News Reader

Parent Full thread

Data integrity is of course important, and MySQL indeed has a weak story here, especially in older versions.

Is PostgreSQL flawless when it comes to data integrity? No, that would also be false advertising. Some examples:

- data checksums are disabled by default (silent data corruptions!), and the user manual specifically warns they are not cheap. And the reasons they are expensive is that PostgreSQL does not support O_DIRECT IO;

- removing a file on the filesystem may lead to a silent loss of data in PostgreSQL: https://www.postgresql.org/message-id/c571dfc5-91b0-0df2-4e3...

- some important control structures (which also impact data visibility and thus, integrity) are not even protected by checksums: https://www.postgresql.org/message-id/CAA4eK1L%3DXS3k7X9pfYK...

- there are no tools to verify replication integrity, similar to pt-table-checksum for MySQL; that would require statement-based replication, which is not supported in PostgreSQL, even in the next major release

Why would O_DIRECT make checksums any cheaper? You always update the checksum at the same time as the data so I cannot see how O_DIRECT could matter.

I am pretty sure the real reasons for checksums being off by default are two:

1) That you need to WAL log hint bit changes if for checksums to work. Hint bits are part of PostgreSQL's MVCC implementation and that you do not need to WAL log them is a clever optimization. This extra WAL traffic can at least in theory hurt some workloads.

2) Probably most importantly: nobody has provided the benchmarks necessary to show that the cost is not too high.

Without O_DIRECT, the page may be updated multiple times in the page cache before it hits the disk. The only thing PostgreSQL can do is to update the checksum before expelling the page from its own cache, so it will be doing unnecessary work in write-intensive workloads. But yes, hint bits and WAL is also a good point.

But PostgreSQL does not update the file directly so this is not an issue. Instead PostgreSQL does all updates in its buffer cache, and then has a background writer which flushes modified shared buffers to the file (but without using O_DIRECT), and then finally it fsyncs the modified file. The window between modifying the file and fsyncing is usually not big so almost always when a page is updated multiple times that will happen before a page is written from the buffers to the OS's file cache.

The above is obviously inefficient which slows down writes, but checksums are not affected by this since those are only calculated on flush. The costs of not using O_DIRECT are 1) extra work on flushing to disk, 2) IO spikes due to the unpredictable nature of when and in what order the OS decides to flush to disk, and 3) wasted RAM. The main benefit is that being able to use the file cache on read loads (by tuning PostgreSQL with small buffers) is that PostgreSQL is nicer to run on shared machines, for example my laptop, since the OS can steal back the read cache when PostgreSQL stops using it.

Checksums on modified pages should only be computed immediately prior to transferring pages to disk. DIRECT_IO makes that transfer point explicit so you can compute the checksum exactly once per transfer regardless of the number of page modifications. If you use the kernel cache like PostgreSQL, you must apply the checksum pessimistically because you don't know when the page will _actually_ be written back to disk by the kernel in-between page modifications.

Computing page checksums burns quite a bit of memory bandwidth, and memory bandwidth is a major resource bottleneck in modern databases. In the DIRECT_IO case, the checksum memory bandwidth should be the same as your storage bandwidth; pessimistic checksumming without DIRECT_IO can burn memory bandwidth that significantly exceeds the storage bandwidth.

Particularly in cases where you have modern, high-performance storage devices, like PCIe connected flash arrays, you can't afford to burn memory bandwidth on checksumming beyond the minimum technically required.

As I explained in another reply, PostgreSQL does no write directly to the files/file cache. It has its own buffer cache and only calculates the checksum when flushing dirty pages to disk (where PostgreSQL does a normal, cached write syscall and then fsyncs). The lack of O_DIRECT causes other performance issues, but not this one.

> data checksums are disabled by default (silent data corruptions!)

Use ZFS.

> removing a file on the filesystem may lead to a silent loss of data in PostgreSQL

Don't remove database files. LOL. Looking at the link:

> data file is zeroed after some power loss(it is the most known issue on XFS in the past)

Use ZFS.

> not even protected by checksums

Use ZFS.

Reply on news.ycombinator.com