如何发现及处理 MySQL 主从延迟问题
发布网友
发布时间：2024-09-29 04:37
共1个回答
热心网友
时间：2024-10-04 09:35
MySQL复制涉及两个线程：IO_THREAD和SQL_THREAD。IO_THREAD连接主服务器，读取binlog事件，并将其复制到本地日志文件relay log。相反，SQL_THREAD在从节点上读取relay log，并尽可能快速地处理这些日志。复制延迟的出现，首先需要判断是发生在IO_THREAD还是SQL_THREAD。
IO_THREAD通常不会引起大的复制延迟，因为它只是从主服务器读取binlog。然而，这取决于网络连接、网络延迟等因素，即主从节点之间的速度。Slave的IO_THREAD可能会因带宽拥塞而变慢。如果Slave IO_THREAD能够快速读取binlog，那么在Slave上堆积relay log，这表明Slave IO_THREAD没有问题。
相反，如果延迟来自Slave SQL_THREAD，可能是由于从复制流中查询在Slave上的执行时间过长。可能的原因包括主从服务器的硬件差异、索引差异、工作负载差异。此外，OLTP工作负载有时会因“锁”导致复制延迟。例如，对MyISAM表的长时间读请求会阻塞SQL线程，或者对InnoDB表的任何事务都会创建IX锁并阻塞SQL线程中的DDL。此外，MySQL 5.6之前，Slave是单线程的，这也是导致Slave SQL_THREAD延迟的另一个原因。
下面通过master status / slave status示例，展示如何确定延迟是由于IO_THREAD还是SQL_THREAD。
这清楚地表明，Slave IO_THREAD滞后，因此Slave SQL_THREAD也因此滞后，从而导致复制延迟。Master日志文件是mysql-bin.018196（来自SHOW MASTER STATUS），而Slave IO_THREAD在mysql-bin.018192（来自Slave status的Master_Log_File）上读取数据，表明Slave IO_THREAD正在从该文件读取数据，而Master正在写入mysql-bin.018196，因此Slave IO_THREAD落后了4个binlog。同时，Slave SQL_THREAD正在读取同一文件，即mysql-bin.018192（Slave status中的Relay_Master_Log_File），这表明Slave SQL_THREAD以足够快的速度应用事件，但仍然滞后，可以通过比较Read_Master_Log_Pos与Exec_Master_Log_Pos来观察。
show slave status的输出中Master_Log_File和Relay_Master_Log_File值相同，可以通过Read_Master_Log_Pos - Exec_Master_Log_Pos计算Slave SQL_THREAD的滞后时间。这能大致了解Slave SQL_THREAD应用事件(apply event)的速度。如果Slave IO_THREAD滞后，那么Slave SQL_THREAD当然也会滞后。对于显示Slave状态输出字段的详细说明，请参考此处。
秒数Behind_Master显示了以秒为单位的巨大延迟，但可能产生误导，因为它仅度量最近执行的relay log与最近被IO_THREAD下载的relay log条目之间的时间戳差异。如果Master上有更多的relay log，Slave并不会将它们计入Seconds_behind_master的计算中。可以使用Percona工具包中的pt-heartbeat更准确地测量Slave日志的滞后情况。至此，我们学会了如何检查复制延迟，无论是Slave IO_THREAD还是Slave SQL_THREAD。现在，让我们提供一些提示和建议，以了解导致延迟的原因以及可能的修复方法。
通常，Slave IO_THREAD的滞后是由于主/从之间的网络速度过慢。大多数情况下，启用Slave压缩协议（slave_compressed_protocol）有助于缓解Slave IO_THREAD的滞后。另一个建议是禁用Slave上的binlog记录，因为它也是IO密集型的，除非您需要它进行时间点恢复。
要尽量减少Slave SQL_THREAD的滞后，关键在于优化查询。建议启用配置选项log_slow_slave_statements，这样Slave执行的耗时超过long_query_time的查询就会被记录到慢日志中。为了收集更多有关查询性能的信息，建议将配置选项log_slow_verbosity设置为"full"。
这样，我们就能看到是否有Slave SQL_thread执行的查询需要很长时间才能完成。如何在特定时间段内使用上述选项启用慢查询日志，请参考这里查看之前的文章。需要注意的是，log_slow_slave_statements变量在Percona Server 5.1中首次引入，现在从5.6.11版本起成为Vanilla MySQL的一部分。在上游版本的MySQL中，log_slow_slave_statements作为命令行选项引入。详情请参考这里，log_slow_verbosity是Percona Server的特定功能。
如果使用基于行的binlog格式，在Slave SQL_THREAD上出现延迟的另一个原因是：如果任何数据库表缺少主键或唯一键，就会在Slave SQL_THREAD上扫描表的所有行进行DML，从而导致复制延迟，因此要确保所有表都有主键或唯一键。有关详细信息，请查看此错误报告bugs.mysql.com/bug.php...您可以在Slave上使用以下查询来确定哪些数据库表缺少主键或唯一键。
在MySQL 5.6中，针对这种情况进行了一项改进，在使用内存散列的情况下，slave_rows_search_algorithms可以解决这个问题。
请注意，当我们读取巨大的RBR事件时，Seconds_Behind_Master并没有更新，因此“滞后”可能仅仅与此有关 - 我们还没有完成对事件的读取。例如，在基于行的复制中，庞大的事务可能会导致Slave端出现延迟，比如，如果你有一个1000万行的表，而你执行了DELETE FROM table WHERE id < 5000000操作，500万行将被发送到Slave端，每一行都是单独的，速度会慢得令人痛苦。因此，如果必须不时地从庞大的表中删除最旧的行，那么使用分区可能是一个不错的选择，在某些工作负载中，使用DROP旧分区可能比使用DELETE更好，因为这将是DDL操作。
为了更好地解释这个问题，假设分区1保存的行的ID从1到1000000，分区2的ID从1000001到2000000，以此类推，所以与其通过语句DELETE FROM table WHERE ID <= 1000000进行删除，不如执行ALTER TABLE DROP partition1。有关更改分区操作，请查阅手册 - 也请查阅我的同事Roman的这篇精彩文章，其中解释了复制延迟的可能原因。
pt-stalk是Percona工具包中最优秀的工具之一，它可以在出现问题时收集诊断数据。你可以按如下方式设置pt-stalk，这样只要出现Slave滞后，它就能记录诊断信息，我们随后就可以对这些信息进行分析，看看到底是什么原因导致了滞后。
你可以调整阈值，目前是300秒，结合-cycles选项，这意味着如果seconds_behind_master值大于等于300，持续60秒或更长时间，pt-stalk就开始捕获数据。添加--notify-by-email选项后，pt-stalk捕获数据时就会通过电子邮件通知。你可以相应调整pt-stalk的阈值，这样它就会在问题发生时触发采集诊断数据。
滞后Slave是一个棘手的问题，但也是MySQL复制中的常见问题。在这篇文章中，我试图涵盖MySQL复制Slave延迟的大多数方面。如果你知道复制延迟的其他原因，请在评论区与我分享。
