4 ms·

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 integ

• jeltz 9y ago
  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.
  • kaamos 9y ago
    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.
    • jeltz 9y ago
      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.
  • jandrewrogers 9y ago
    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.
    • jeltz 9y ago
      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.
• floatboth 9y ago
  > 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.