如何刷新二进制日志 [英] How to flush binary logs
问题描述
我阅读了另一篇文章的评论，InnoDB可以通过不刷新二进制日志每次提交的速度进行优化。
这是我第一次听到这个，所以什么是这些二进制日志和什么是刷新所有？为什么我应该这样做（以及何时不这样做）和如何做？
二进制日志用于崩溃恢复，复制。假设它们已启用，关于刷新的两个主要适用设置是：
- innodb_flush_log_at_trx_commit = 2
- sync_binlog = 0
这些设置的优势在于保证可靠性，因为它们允许操作系统延迟将缓冲区刷新到磁盘。 / p>
如果你运行在一个廉价的磁盘控制器（大多数人都是），每次提交刷新日志非常慢。在一个做了很多写的网站上，我看到页面执行时间下降10x-100x！
很清楚，如果你想要最大的恢复效益，你必须将这两个值都设置为1 。如上所述，在系统崩溃的情况下，您可能会丢失几秒钟的数据。对于一些网站，这是不能接受的。对于博客和论坛，这可能是一个值得的折衷。
您可以在此处详细了解设置：
http://dev.mysql.com/doc/ refman / 5.0 / en / innodb-parameters.html
还有很多内存相关的选项，如innodb_buffer_pool_size。搜索MySQL InnoDB调优应该是有帮助的。
I read a comment on another post that InnoDB can be optimized for speed by not flushing binary logs on every commit.
This is the first time I hear about this, so what are these binary logs and what's this flushing all about? Why should I do it (and when not to do it) and how?
The binary logs are used in crash recovery and replication. Assuming they are enabled, the two main applicable settings regarding flushing are:
- innodb_flush_log_at_trx_commit=2
- sync_binlog=0
Those settings favor speed over "guaranteed" reliability because they allow the OS to delay in flushing the buffers to disk.
If you are running on a cheap disk controller (most people are), flushing the logs out on every commit is very slow. On a site that does a lot of writes, I've seen page execution time drop 10x-100x! It's easy to run your own benchmarks and see how it affects you.
To be very clear, if you want maximum recovery benefits, you must set both of those values to 1. As configured above, you may lose a few seconds of data in the case of a system crash. For some sites, that is not acceptable. For blogs and forums, it probably is a worthwhile trade-off.
You can read more about the settings here:
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
There are many memory related options like 'innodb_buffer_pool_size' to configure as well. A search on MySQL InnoDB tuning should help out.
这篇关于如何刷新二进制日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
