事务日志驱动器是否需要与数据库驱动器一样快？
我们告诉客户将 SQL Server 数据库文件 (mdf) 放在与事务日志文件 (ldf) 不同的物理驱动器上。科技公司（由我们的客户雇用）希望将事务日志放在比数据库驱动器更慢（例如更便宜）的驱动器上，因为使用事务日志，您只是顺序写入日志文件。
我告诉他们，我认为驱动器（实际上是 RAID 配置）也需要位于快速驱动器上，因为对数据库的每个数据更改调用都需要保存在那里，以及数据库本身。
但说完之后，我意识到我对此并不完全确定。如果带有数据库的驱动器速度很快，事务日志驱动器的速度是否会对性能产生显着差异？
We are telling our client to put a SQL Server database file (mdf), on a different physical drive than the transaction log file (ldf). The tech company (hired by our client) wanted to put the transaction log on a slower (e.g. cheaper) drive than the database drive, because with transaction logs, you are just sequencially writing to the log file.
I told them that I thought that the drive (actually a RAID configuration) needed to be on a fast drive as well, because every data changing call to the database, needs be saved there, as well as to the database itself.
After saying that though, I realized I was not entirely sure about that. Does the speed of the transaction log drive make a significant difference in performance... if the drive with the database is fast?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
日志驱动器的速度是写入密集型数据库的最关键因素。任何更新的发生速度都不能快于日志的写入速度，因此您的驱动器必须支持峰值时的最大更新速率。所有更新都会生成日志。数据库文件 (MDF/NDF) 更新可以承受较慢的写入速率，因为两个因素导致
所以您是对的，日志吞吐量至关重要。
但同时，日志写入具有特定的顺序写入模式：日志始终附加在末尾。所有机械驱动器对于读取和写入以及顺序操作都具有更高的吞吐量，因为它们涉及磁盘头的物理移动较少。您的操作人员所说的也是如此，较慢的驱动器实际上可以提供足够的吞吐量。
但所有这些都伴随着一些重大警告：
The speed of the log drive is the most critical factor for a write intensive database. No updates can occur faster than the log can be written, so your drive must support your maximum update rate experienced at a spike. And all updates generate log. Database file (MDF/NDF) updates can afford slower rates of write because of two factors
So you are right that the log throughput is critical.
But at the same time, log writes have a specific pattern of sequential writes: log is always appended at the end. All mechanical drives have a much higher throughput, for both reads and writes, for sequential operations, since they involve less physical movement of the disk heads. So is also true what your ops guys say that a slower drive can offer in fact sufficient throughput.
But all these come with some big warnings:
简而言之，如果您谈论的是 OLTP 数据库，则吞吐量由写入事务日志的速度决定。一旦达到此性能上限，所有其他相关操作都必须等待提交日志完成。
这是对事务日志内部结构的非常简单的理解，整本书都专门讨论了事务日志，但基本要点仍然存在。
现在，如果您使用的存储系统可以提供同时支持事务日志和数据库数据文件所需的 IOPS，那么共享驱动器/LUN 将足以满足您的需求。
为了向您提供具体的推荐操作方案，我需要更多地了解您的数据库工作负载以及您需要数据库服务器提供的性能。
获取标题SQL Server 2008 Internals 全面了解 SQL Server 事务日志的内部结构，它是最好的 SQL Server 书籍之一它会在几分钟内从您从阅读中获得的价值中收回成本。
In simplistic terms, if you are talking about an OLTP database, your throughput is determined by the speed of your writes to the Transaction Log. Once this performance ceiling is hit, all other dependant actions must wait on the commit to log to complete.
This is a VERY simplistic take on the internals of the Transaction Log, to which entire books are dedicated, but the rudimentary point remains.
Now if the storage system you are working with can provide the IOPS that you require to support both your Transaction Log and Database data files together then a shared drive/LUN would provide adequately for your needs.
To provide you with a specific recommended course of action I would need to know more about your database workload and the performance you require your database server to deliver.
Get your hands on the title SQL Server 2008 Internals to get a thorough look into the internals of the SQL Server transaction log, it's one of the best SQL Server titles out there and it will pay for itself in minutes from the value you gain from reading.
嗯，事务日志是提供ACID的主要结构，可能是性能的一大瓶颈，如果你定期备份，它所需的空间是有上限的，所以我会把它放在一个安全、快速且空间足够的驱动器中+ 一点余量。
Well, the transaction log is the main structure that provides ACID, can be a big bottleneck for performance, and if you do backups regularly its required space has an upper limit, so i would put it in a safe, fast drive with just space enough + a bit of margin.
事务日志应该位于最快的驱动器上，如果它只能完成对日志的写入，那么它可以在内存中完成事务的其余部分，并稍后将其写入磁盘。
The Transaction log should be on the fastest drives, if it just can complete the write to the log it can do the rest of the transaction in memory and let it hit disk later.