为什么SQL数据库使用写头日志而不是命令日志?[英] Why do SQL databases use a write-ahead log over a command log?

本文是小编为大家收集整理的关于为什么SQL数据库使用写头日志而不是命令日志?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我阅读了有关voltdb的命令日志.命令日志记录事务调用,而不是像在书面日志中一样更改每行.通过仅记录调用,将命令日志保持在最低限度,从而限制了磁盘I/O对性能的影响.

任何人都可以解释为什么VoltDB使用命令日志背后的数据库理论,以及为什么标准SQL数据库(例如Postgres,MySQL,SQLServer,Oracle,Oracle)使用Write-Ahead日志?

推荐答案

我认为更好的是:

为什么新的分布式voltdb使用命令日志上的写入日志?

让我们做一个实验,想象您将编写自己的存储/数据库实现.毫无疑问,您足够先进,可以抽象文件系统并使用块存储以及一些其他优化.

一些基本术语:

  • 状态:在给定时间点存储信息
  • 命令:存储的指令更改其状态

因此,您的数据库看起来如下:

在此处输入图像说明

下一步是执行一些命令:

在此处输入图像说明

请注意几个重要方面:

  1. 命令可能会影响许多存储的实体,因此许多块会变得肮脏
  2. 下一个状态是当前状态和命令的函数

可以跳过某些中间状态,因为它足以拥有一系列命令.

在此处输入图像说明

最后,您需要保证数据完整性.

  • 写入记录 - 中心概念是状态更改应在任何重大更新以进行永久存储之前记录.按照我们的想法,我们可以记录每个块的增量更改.
  • 命令记录 - 中心概念是仅记录命令,用于生产状态.

在此处输入图像说明

两种方法都有利弊.写入日志包含所有更改的数据,命令日志将需要添加处理,但是快速且轻巧.

voltdb:命令记录和恢复

命令记录的关键是它记录了调用,而不是 后果,交易.通过仅记录调用, 命令日志保持在最低限度,限制了磁盘I/O的影响 具有性能.

其他注释

sqlite:write-ahead loggging

传统回滚期刊通过编写一份副本 原始的未改变数据库内容成一个单独的回滚日记 文件然后写入直接更改为数据库文件.

当特殊记录表明提交附加时,会发生提交 到WAL.因此,可以在不写信给 原始数据库,允许读者继续从 最初的不变数据库,而更改同时进行 致力于WAL.

PostgreSql:write-ahead logging(wal) /p>

使用WAL结果大大减少了磁盘写入, 因为只有日志文件才需要冲洗到磁盘以保证 提交交易,而不是每个数据文件更改 通过交易.

日志文件是顺序编写的,因此 同步日志的成本远低于冲洗成本 数据页.对于处理许多小型的服务器尤其如此 触摸数据存储不同部分的交易.此外, 当服务器正在处理许多小型交易时,一个 日志文件的fsync可能足以进行许多交易.

结论

命令记录:

  1. 更快
  2. 足迹较低
  3. 具有更重的"重播"过程
  4. 需要频繁的快照

提前记录是一种提供原子性的技术.更好的命令记录性能也应改善交易处理. 1英尺的数据库

确认

voltdb博客:voltdb命令的简介

命令记录的一个优点是白羊座样式记录 可以在执行之前记录事务而不是执行 交易和等待日志数据潮汐磁盘.其他 优势是命令日志所需的IO吞吐量为 由用于中继命令的网络和 gig-e,廉价的商品磁盘可以满足此吞吐量.

记住voltdb是由其性质分发的.因此,交易在处理方面有些棘手,表现影响很明显.

voltdb博客:voltdb的新命令登录功能

voltdb中的命令日志由存储过程调用和 它们的参数.在每个节点上创建一个日志,每个日志是 复制是因为所有工作都复制到多个节点.这个 结果可以在重播时进行重复的命令日志 时间.由于voltdb交易是强烈订购的,所以该命令 日志也包含订购信息.因此可以发生重播 按确切顺序,原始交易运行,完整 交易隔离VoltDB提供.由于召唤本身 通常小于修改后的数据,可以在 他们承诺,这种方法对 表现.这意味着voltdb用户可以实现相同的类型 平流层性能数字,还有额外的耐用性 保证.

其他推荐答案

从Postgres的描述中 http:/http://www.postgresql.org/docs/9.1/static/wal-intro.html 和voltdb的命令日志(您引用过的),我根本看不到太大的区别.它似乎是具有不同名称的相同概念.

两者都仅将日志文件同步到磁盘,而不是数据同步数据,以便可以通过重播日志文件来恢复数据.

voltdb的第10.4节解释说,他们的社区版本没有命令日志,因此不会通过酸测试.即使在企业版中,我也看不到它们交易隔离的详细信息(例如 http://www.postgresql.org/docs/9.1/static/transaction-iso.html )需要使我感到舒适,因为voltdb和Postges一样严重.

其他推荐答案

与WAL一起,读者从未填充的日志中读到页面.没有对主数据库进行修改.使用命令日志记录,您无法从命令日志中读取.

命令记录因此大不相同. voltdb使用命令记录来创建恢复点并确保持久性,但是它正在实时写入主DB商店(RAM) - 所有服务员锁定问题等.

本文地址:https://www.itbaoku.cn/post/597411.html

问题描述

I read about Voltdb's command log. The command log records the transaction invocations instead of each row change as in a write-ahead log. By recording only the invocation, the command logs are kept to a bare minimum, limiting the impact the disk I/O will have on performance.

Can anyone explain the database theory behind why Voltdb uses a command log and why the standard SQL databases such as Postgres, MySQL, SQLServer, Oracle use a write-ahead log?

推荐答案

I think it is better to rephrase:

Why does new distributed VoltDB use a command log over write-ahead log?

Let's do an experiment and imagine you are going to write your own storage/database implementation. Undoubtedly you are advanced enough to abstract a file system and use block storage along with some additional optimizations.

Some basic terminology:

  • State : stored information at a given point of time
  • Command : directive to the storage to change its state

So your database may look like the following:

enter image description here

Next step is to execute some command:

enter image description here

Please note several important aspects:

  1. A command may affect many stored entities, so many blocks will get dirty
  2. Next state is a function of the current state and the command

Some intermediate states can be skipped, because it is enough to have a chain of commands instead.

enter image description here

Finally, you need to guarantee data integrity.

  • Write-Ahead Logging - central concept is that State changes should be logged before any heavy update to permanent storage. Following our idea we can log incremental changes for each block.
  • Command Logging - central concept is to log only Command, which is used to produce the state.

enter image description here

There are Pros and Cons for both approaches. Write-Ahead log contains all changed data, Command log will require addition processing, but fast and lightweight.

VoltDB: Command Logging and Recovery

The key to command logging is that it logs the invocations, not the consequences, of the transactions. By recording only the invocation, the command logs are kept to a bare minimum, limiting the impact the disk I/O will have on performance.

Additional notes

SQLite: Write-Ahead Logging

The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file.

A COMMIT occurs when a special record indicating a commit is appended to the WAL. Thus a COMMIT can happen without ever writing to the original database, which allows readers to continue operating from the original unaltered database while changes are simultaneously being committed into the WAL.

PostgreSQL: Write-Ahead Logging (WAL)

Using WAL results in a significantly reduced number of disk writes, because only the log file needs to be flushed to disk to guarantee that a transaction is committed, rather than every data file changed by the transaction.

The log file is written sequentially, and so the cost of syncing the log is much less than the cost of flushing the data pages. This is especially true for servers handling many small transactions touching different parts of the data store. Furthermore, when the server is processing many small concurrent transactions, one fsync of the log file may suffice to commit many transactions.

Conclusion

Command Logging:

  1. is faster
  2. has lower footprint
  3. has heavier "Replay" procedure
  4. requires frequent snapshot

Write Ahead Logging is a technique to provide atomicity. Better Command Logging performance should also improve transaction processing. Databases on 1 Foot

enter image description here

Confirmation

VoltDB Blog: Intro to VoltDB Command Logging

One advantage of command logging over ARIES style logging is that a transaction can be logged before execution begins instead of executing the transaction and waiting for the log data to flush to disk. Another advantage is that the IO throughput necessary for a command log is bounded by the network used to relay commands and, in the case of Gig-E, this throughput can be satisfied by cheap commodity disks.

It is important to remember VoltDB is distributed by its nature. So transactions are a little bit tricky to handle and performance impact is noticeable.

VoltDB Blog: VoltDB’s New Command Logging Feature

The command log in VoltDB consists of stored procedure invocations and their parameters. A log is created at each node, and each log is replicated because all work is replicated to multiple nodes. This results in a replicated command log that can be de-duped at replay time. Because VoltDB transactions are strongly ordered, the command log contains ordering information as well. Thus the replay can occur in the exact order the original transactions ran in, with the full transaction isolation VoltDB offers. Since the invocations themselves are often smaller than the modified data, and can be logged before they are committed, this approach has a very modest effect on performance. This means VoltDB users can achieve the same kind of stratospheric performance numbers, with additional durability assurances.

其他推荐答案

From the description of Postgres' write ahead http://www.postgresql.org/docs/9.1/static/wal-intro.html and VoltDB's command log (which you referenced), I can't see much difference at all. It appears to be the identical concept with a different name.

Both sync only the log file to the disk but not the data so that the data could be recovered by replaying the log file.

Section 10.4 of VoltDB explains that their community version does not have command log so it would not pass the ACID test. Even in the enterprise edition, I don't see the details of their transaction isolation (e.g. http://www.postgresql.org/docs/9.1/static/transaction-iso.html) needed to make me comfortable that VoltDB is as serious as Postges.

其他推荐答案

With WAL, readers read from pages from unflushed logs. No modification is made to the main DB. With command logging, you have no ability to read from the command log.

Command logging is therefore vastly different. VoltDB uses command logging to create recovery points and ensure durability, sure - but it is writing to the main db store (RAM) in real time - with all the attendant locking issues, etc.