4 ms·

For what it's worth SQL Server doesn't meet his definition of "durable." It writes a transaction log and then writes those transactions to disk on a checkpoint

For what it's worth SQL Server doesn't meet his definition of "durable." It writes a transaction log and then writes those transactions to disk on a checkpoint [1]. Typically, that checkpoint automagically happens, but you can also force it to clear the buffer with a "checkpoint" command. InnoDB also does this, too [2]. I don't know if MemSQL actually has checkpointing or what, but it's just worth noting. [1]: http://msdn.microsoft.com/en-us/library/ms189573.aspx http://msdn.microsoft.com/en-us/library/ms189573.aspx [2]: http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.html http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.ht...

spudlyo 14y ago
I think you may be missing the point. Most databases use transaction/redo logs, it has to do with how often the writes to the transaction log are flushed. With proper durability, the transaction log is flushed with every commit, not when buffers become full or a background thread gets around to it.
__dumbideas 14y ago
MySQL's InnoDB has configurable durability: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.htm...
jstclair 14y ago
This is a little misleading; an internal checkpoint happens upon transaction commit[1]. The comment makes it sound as if one or more transactions can commit before a checkpoint writes them to disk. [1]: http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105)....
- spudlyo 14y ago
  The transaction is actually written to disk twice. Once when it is written to the transaction log, and then again when a checkpoint happens and it's written to the underlying data blocks. Transaction log writes are sequential and fast, writes to the underlying data blocks are random and slow. The whole point of the checkpoint is to convert this sequential i/o to random i/o in an efficient manner. Still, that's not what we're talking about. We're talking about the difference between writing data to the transaction log, and writing data to the transaction log and then flushing it. Once you call fsync() and the data is flushed you know for a fact (provided the hardware isn't lying to you) the bytes are on the damn platter, and not in some OS buffer cache.
  - Nitramp 14y ago
    ... you'd think. However in practice most operating systems and/or file systems and/or disks cheat. fsync() is usually buffered in the hard disk itself. You can disable that and force the hard disk to truly write out on fsync, but that is so prohibitively slow that people rarely do that. If you want absolute durability, you'll have to have hard disks running on some battery buffered power supply, which is a common configuration. On the other hand, in a proper database system, at least the data files won't be corrupted by a missing fsync, so it'll come up. Figuring out whether that one commit did or did not go out in the very rare event of a fatal power failure and a just pending fsync() and the commit making it out of the network stack in time is probably a Heisenberg-esque inquiry into obscure realms of uncertainty.
    - spudlyo 14y ago
      First of all, ext(2|3|4) and XFS filesystems honor fsync. That's what all the noise about Firefox and SQLite was all about. Secondly the HP RAID controllers I'm familiar with disable the drive write cache by default, and throw dire warnings if you try to turn it on, and make you ACK your choice like: Without the proper safety precautions, use of write cache on physical drives could cause data loss in the event of a power failure.... The bottom line is that Database pros insist on and get actual durability, and with a battery backed write cache, it's not painful.
      - Nitramp 14y ago
        The file system does, but does the hard drive? But I agree, getting battery backed write caches is not that hard, it's just not a default config.
sageikosa 14y ago
SQL Server writes to the transaction log immediately, it writes the contents of modified data pages on checkpoints, but only for transactions that have committed. For data pages that haven't been written to (dirty pages) when the system crashes, the recovery process can recreate completed transactional changes from the log (which is rolling forward), or wipes out failed transactions from the log (rolling backward).

spudlyo 14y ago

I think you may be missing the point. Most databases use transaction/redo logs, it has to do with how often the writes to the transaction log are flushed. With proper durability, the transaction log is flushed with every commit, not when buffers become full or a background thread gets around to it.

__dumbideas 14y ago

MySQL's InnoDB has configurable durability: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.htm...

jstclair 14y ago

This is a little misleading; an internal checkpoint happens upon transaction commit[1]. The comment makes it sound as if one or more transactions can commit before a checkpoint writes them to disk. [1]: http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105).aspx http://msdn.microsoft.com/en-us/library/ms186259(v=sql.105)....

spudlyo 14y ago
The transaction is actually written to disk twice. Once when it is written to the transaction log, and then again when a checkpoint happens and it's written to the underlying data blocks. Transaction log writes are sequential and fast, writes to the underlying data blocks are random and slow. The whole point of the checkpoint is to convert this sequential i/o to random i/o in an efficient manner. Still, that's not what we're talking about. We're talking about the difference between writing data to the transaction log, and writing data to the transaction log and then flushing it. Once you call fsync() and the data is flushed you know for a fact (provided the hardware isn't lying to you) the bytes are on the damn platter, and not in some OS buffer cache.
- Nitramp 14y ago
  ... you'd think. However in practice most operating systems and/or file systems and/or disks cheat. fsync() is usually buffered in the hard disk itself. You can disable that and force the hard disk to truly write out on fsync, but that is so prohibitively slow that people rarely do that. If you want absolute durability, you'll have to have hard disks running on some battery buffered power supply, which is a common configuration. On the other hand, in a proper database system, at least the data files won't be corrupted by a missing fsync, so it'll come up. Figuring out whether that one commit did or did not go out in the very rare event of a fatal power failure and a just pending fsync() and the commit making it out of the network stack in time is probably a Heisenberg-esque inquiry into obscure realms of uncertainty.
  - spudlyo 14y ago
    First of all, ext(2|3|4) and XFS filesystems honor fsync. That's what all the noise about Firefox and SQLite was all about. Secondly the HP RAID controllers I'm familiar with disable the drive write cache by default, and throw dire warnings if you try to turn it on, and make you ACK your choice like: Without the proper safety precautions, use of write cache on physical drives could cause data loss in the event of a power failure.... The bottom line is that Database pros insist on and get actual durability, and with a battery backed write cache, it's not painful.
    - Nitramp 14y ago
      The file system does, but does the hard drive? But I agree, getting battery backed write caches is not that hard, it's just not a default config.

sageikosa 14y ago

SQL Server writes to the transaction log immediately, it writes the contents of modified data pages on checkpoints, but only for transactions that have committed. For data pages that haven't been written to (dirty pages) when the system crashes, the recovery process can recreate completed transactional changes from the log (which is rolling forward), or wipes out failed transactions from the log (rolling backward).