mysql :了解无害数据库日志操作

Question

当涉及到在innodb存储引擎中写入二进制日志的更改时，我正在尝试理解这三个变量。

联木

提交

超时

在阅读了几遍文档之后，我对这些变量的理解如下：

sync_binlog

0  -> Operating system decides when to write log to disk(whenever it has free time)
1  -> after every commit changes written to disk(safest)
N  -> after N commits changes written to disk

innodb_flush_log_at_trx_commit

0 -> written and flushed to disk once per second
1 -> written and flushed after every commit(safest)
2 -> written after every commit and flushed to disk once per second

innodb_flush_log_at_timeout

after N seconds of delay logs are written to file

所以，作为一个实验，这就是我想要的

1)在完成5个事务之前，不应将任何事务写入日志。

(2)在完成了5个事务之后，我希望innodb在这些事务被写入日志之前等待10秒

因此，我将变量设置为

SET @@GLOBAL.sync_binlog=5;                          #Don't write anything until 5 commits have been done?
SET @@GLOBAL.innodb_flush_log_at_trx_commit=0;       #Maybe? because logs are written every second regardless if they are commited or not?
SET @@GLOBAL.innodb_flush_log_at_timeout=10;         #After 5 commit's wait for 10 seconds before writing the transaction's to disk?

如你所见，我到处都有问号，因为我不知道自己在做什么。而这些设置不起作用。每次提交后，我立即在日志中看到更新的更改。

基本上我有个测试表

CREATE TABLE Test(TestCol INT);

作为5 insert语句，我将发出5事务的自动提交

INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(4);
INSERT INTO Test VALUES(5);

我想要的是，如果我插入值(假设只有1到4)，那么我不想看到日志文件中的任何更改，但是一旦我插入了5，这总共产生了5次超越，那么我想让innodb等待10秒，然后写那些事务来记录，这样我就可以有足够的时间打开我的文件资源管理器并运行

mysqlbinlog --verbose --base64-output=DECODE-ROWS binlog.000001

查看日志文件中的更改

但是，即使我只插入一个值并打开我的日志文件，我也会立即在其中看到插入。

我的目标可能吗？有人能解释一下这些变量是如何工作的吗?如果可能的话，可以为我的问题找到另一种解决方案吗？

任何清晰的信息都将不胜感激。

Answer 1

sync_binlog只控制文件同步到磁盘的时间。当您提交事务时，事务仍然被立即写入逻辑文件，它们只是由操作系统缓冲。如果使用程序(例如mysqlbinlog)读取同一个文件，则会读取所有内容，包括OS缓冲区中的内容。操作系统自动将缓冲区与磁盘上的内容合并。

我不知道如何使MySQL延迟在提交时写入二进制日志。有一个binlog缓冲区，但它用于在提交事务之前积累更改。提交之后，它应该立即写入binlog，因为其他会话也可能希望写入binlog，并且提交应该按照提交时间的顺序编写。

同样地，对innodb重做日志的写入也是连续的。您命名的刷新选项仅在文件同步到磁盘时控制。同时，对日志文件的写入仍在发生，但可能由操作系统缓冲。操作系统可以选择在innodb强制同步之前刷新缓冲的I/O。

我不认为你能做你想做的事。我不知道你为什么要这么做，因为它会破坏耐久性。假设您可以让InnoDB在5个事务提交之前不将事务保存到重做日志。如果您提交了其中的4个事务并突然崩溃，那么您将丢失这4个事务。

关于你的评论：

https://bugs.mysql.com/bug.php?id=69309有更多的解释：

每N秒写一次日志并冲洗一次。innodb_flush_log_at_timeout是在MySQL 5.6.6中引入的。它允许增加刷新之间的超时时间，以减少刷新，避免影响二进制日志组提交的性能。在MySQL 5.6.6之前，冲洗频率为每秒一次。innodb_flush_log_at_timeout的默认设置也是每秒一次。

bug日志声称这一解释将被添加到文档中，但我猜它从未出现过。

听起来，如果您的事务处理率太高，以致于同步日志文件是一个瓶颈，则此选项的目的是为了降低持久性。请参阅https://dev.mysql.com/worklog/task/?id=5223

在我看来，如果您的事务处理速度太高，以至于每秒无法跟上fsync，那么您需要考虑如何重新设计您的体系结构。即使您延长了fsyncs之间的间隔，这也会给您的数据带来风险，而且您不能永远扩展它。您需要一个更好的策略来管理不断增长的事务率。通常，这意味着将数据库拆分为“碎片”，以便每个事务都写入多个服务器中的一个。