具有MySQL主主复制的高磁盘IO

Question

我们一直在评估我们的MySQL数据库的主动-被动主-主复制设置，运行在版本8.0上。在诊断一些常见的缓慢问题时，我们惊讶地看到这两台服务器上的磁盘IO都很高，从500 on /S到1500 on/S不等。

我发现在这两台服务器上停止从服务器可以将磁盘IO恢复到预期的水平，即15 MB/S- 30 MB/s，而且我担心我们在某个地方出现了错误配置。

这是我查过的：

• 服务器ID是唯一的。
• 我们正在进行基于行的复制。
• 我们使用的是半同步复制插件。
• 写操作是在LVM卷中进行的，LVM卷只包含数据目录。
• vmstat显示没有交换。
• 从网络的角度来看，我们最多看到服务器之间每秒有几兆比特。

以下是经过编辑的配置：

[mysqld]
...

# General
server_id                        = <unique_between_servers>                         
event-scheduler                  = ON                              
sync_binlog                      = 1                               
log_bin                          = /var/lib/mysql/mysql-bin        
binlog_format                    = ROW                             
innodb_file_per_table                                              
innodb_flush_log_at_trx_commit   =  1                              
skip-external-locking                                                                                                


# Replication
relay_log                        = /var/lib/mysql/mysql-relay-bin
relay_log_recovery               = ON
log_slave_updates                = 1
sync_master_info                 = 1
sync_relay_log                   = 1
sync_relay_log_info              = 1
rpl_semi_sync_master_enabled     = 1
rpl_semi_sync_master_timeout     = 10000    # 10 seconds
rpl_semi_sync_slave_enabled      = 1


# Fine Tuning
key_buffer_size          = 16M
max_allowed_packet       = 16M
thread_stack             = 192K
thread_cache_size        = 8
myisam-recover-options   = BACKUP           
max_connections          = 800
max_connect_errors       = 1000
innodb_buffer_pool_size  = 192G    # Server has 256GB RAM
innodb_log_buffer_size   = 256M
innodb_log_file_size     = 2G
innodb_flush_method      = O_DIRECT
innodb_lock_wait_timeout = 60
...

我的问题听起来类似于2013年的这个人的死胡同 (你是谁，DenverCoder9 9？你看到什么了？)。

编辑：

为了解决这个问题，我们简化了测试的设置。我们使用了一个主程序和一个从程序，它们都运行8.0.11，配置与上面相同，并且我们看到了以下根据iostat的写模式(master是蓝色的，从是红色的)：

(X轴上的“时间”是当前时间的分钟和秒。)

主服务器上每分钟的1+ GB峰值被跟踪到一个脚本中，该脚本更新两个小表(每个表1000-2000行)，磁盘上的大小约为10-15MB。这些表有键，但键不是主键或唯一键。脚本将删除表中的所有行，然后通过每行执行一条INSERT语句重新填充它们，所有语句都在事务中执行。尽管存在明显的问题，但我仍然不明白如何对这么小的表进行操作会导致如此多的磁盘IO。

在没有解决这些问题的情况下，我们返回到主从主从-被动设置，并看到以下内容(主动母版为蓝色，被动母版为红色)：

所以我还有几个问题要问。为什么奴隶总是在第一张图中写得比主人多很多？另外，为什么在主-母复制中会出现上面看到的乒乓效应，其中写到主程序会导致从机上产生大量IO，而在主程序上又会产生大量IO？

Answer 1

• ALTERs被复制了？它们可以是低复制带宽但高I/O。
• 内存是多少？innodb_buffer_pool_size相当大。
• 您的表有很多辅助索引吗？
• 以前是否有很多活动导致更新次要的、非UNIQUE的索引？变更缓冲区位于buffer_pool中，并异步刷新。请参阅innodb_change_buffer_max_size，但不一定要更改它。
• 很多TRIGGERs？
• 桌子有多大？
• 尽管有ROW，一个完整的UPDATE会不会成为STATEMENT呢？
• LVM的牛可能加倍(或三倍？)I/O活动。(但这只能部分解释你所看到的东西。)
• 正在发生一些大的SELECTs吗？(打开慢速日志，long_query_time的值很小。)

不，我看不出有什么配置变化要做。

More

一些可能相关的问题(特别是在更新问题之后)

• 对于1K行的单个UPDATE，基于行的复制相当冗长。每一行都是复制流中的一个单独的记录，而且它相当大(与更新本身相比)。这些记录将被缓冲并写入主磁盘(binlog)和奴隶的磁盘(中继日志)。我会假设它们是缓冲的，等等，这样的文字量是相同的。但也许我错了。
• 从服务器上的"sql线程“读取所有这些1行更新，大概一次一个。再一次，人们会希望有这样的缓冲，这样就不会增加I/O。
• 这两台机器都需要通过变更缓冲区更新任何辅助键。然而，这是一项延迟的行动。事实上，它可能会被延迟太久，以至于它不会出现在您所呈现的图表上。
• 在主-母版设置中，我不知道“循环”何时停止。也就是说，server_id 123复制到它的奴隶，server_id 234，然后复制到它的所有奴隶，包括123。一旦发现123=123，复制数据包就会被丢弃。但是，在返回到123并被写到那里的中继日志之前，这种情况会发生吗？我不知道。您可以通过查看active Master上的中继日志的增长来找出它。
• 奴隶还开了什么原木？大概和大师一样。普通日志？慢速原木？其他?

场景详细说明

• SET autocommit=0;
• DELETE特定表中的所有1-2K行。如果同时进行基于行的复制，那么就会通过复制流发送大量内容.
• INSERT一行返回
• INSERT一行返回
• . 1-2K行插入
• COMMIT;，直到这一点，任何东西都会传递给奴隶。我怀疑这是一次编写二进制日志的时候。

嗯，我想这仍然没有回答你的问题，但也许它提供了一些更多的洞察力。

如何替换表

这是一种更有效的方法来替换所有的表。它避免了桌子永远无法使用的情况：

CREATE TABLE new LIKE real;
load the new data into the table (LOAD DATA or multi-row INSERT(s) is best)
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Answer 2

详细信息

使用主动-被动主-主安装程序时的复制过程如下(基于使用mysqlbinlog检查二进制和中继日志)：

• 活动主程序写入其数据库和二进制日志(两个位置)。
• 被动主程序从其活动对等端获取二进制日志，将它们写入中继日志，并将它们应用到数据库中，并编写自己的二进制日志(三个位置)。
• 主动主程序从其被动对等端获取二进制日志，将每个条目中的服务器ID识别为自己的日志，并在不将它们写入磁盘的情况下删除它们。循环就在这里结束。

另外，图的底部有一条主要线索：

每当被动主机与磁盘IO挂钩时，主动主程序就会具有零活动，就好像它是阻塞的一样(扰流板:它是)。

这就是我们的结论：

• 我们环境中的几个脚本正在更新表，方法是删除它们的所有行并使用许多单行INSERT语句(每个表中有数千个INSERT语句)重新填充它们。
• 多个单行INSERT语句而不是一个多行INSERT语句导致二进制和中继日志中的元数据大大增加。
• 主动主机可以处理它在磁盘IO中的增加，但是被动主机需要执行大约2-3倍的磁盘IO，并且在出现大尖峰后落后于主主机。(我仍然不能完全解释2-3倍的差异，但这似乎是由许多单行INSERT语句造成的。)
• 当被动主服务器落后时，在切换到异步复制之前，主动主服务器将阻塞高达rpl_semi_sync_master_timeout毫秒(默认为10秒)。
• 一旦进入异步模式，活动主程序将再次飙升，以处理阻塞时生成的查询。

修复问题

我们采取了两个步骤来解决这个问题。

首先，我们将rpl_semi_sync_master_timeout缩短为100毫秒。这减少了主动主从在落后时阻塞的时间。我们这样做是为了防止在这种情况再次出现时数据库依赖的应用程序过度挂起。我们愿意承担失去同步的额外风险。

其次，我们重写了导致尖峰的INSERT语句。这些脚本现在使用多行INSERT语句重新填充给定的表，而不是紧循环中的许多单行INSERT语句。

通过这些更改，图现在看起来如下所示。请注意，主动数据库和被动数据库之间的磁盘IO增量要小得多，并且峰值要小得多：

Answer 3

建议考虑您的my.cnf-ini 米舍尔德部分

innodb_buffer_pool_instances=8  # from 1 to reduce mutex contention
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every second

在每一个情况下。

发布更多要求的信息将为您的安装提供更多性能优化建议。

请查看我的个人资料以获得联系信息。