mysql 查询 读写 延迟_排查复制延迟问题 - Azure Database for MySQL | Microsoft Docs

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

排查 Azure Database for MySQL 中的复制延迟问题Troubleshoot replication latency in Azure Database for MySQL

01/13/2021

本文内容

适用于：

Azure Database for MySQL-单服务器

Azure Database for MySQL-灵活服务器

使用只读副本功能可将数据从 Azure Database for MySQL 服务器复制到只读副本服务器。The read replica feature allows you to replicate data from an Azure Database for MySQL server to a read-only replica server. 可以通过将读取和报告查询从应用程序路由到副本服务器来横向扩展工作负荷。You can scale out workloads by routing read and reporting queries from the application to replica servers. 此设置可减小源服务器上的压力。This setup reduces the pressure on the source server. 它还改进了应用程序在缩放时的整体性能和延迟。It also improves overall performance and latency of the application as it scales.

副本使用 MySQL 引擎的原生二进制日志 (binlog) 文件基于位置的复制技术以异步方式进行更新。Replicas are updated asynchronously by using the MySQL engine's native binary log (binlog) file position-based replication technology.

辅助只读副本上的复制延迟取决于多个因素。The replication lag on the secondary read replicas depends several factors. 这些因素包括但不限于：These factors include but aren't limited to:

网络延迟。Network latency.

源服务器上的事务量。Transaction volume on the source server.

源服务器和辅助只读副本服务器的计算层。Compute tier of the source server and secondary read replica server.

在源服务器和辅助服务器上运行的查询。Queries running on the source server and secondary server.

在本文中，你将了解如何排查 Azure Database for MySQL 中的复制延迟问题。In this article, you'll learn how to troubleshoot replication latency in Azure Database for MySQL. 你还将了解副本服务器上复制延迟增加的一些常见原因。You'll also understand some common causes of increased replication latency on replica servers.

备注

本文包含对字词 从属 的引用，这是 Microsoft 不再使用的术语。This article contains references to the term slave, a term that Microsoft no longer uses. 在从软件中删除该术语后，我们会将其从本文中删除。When the term is removed from the software, we'll remove it from this article.

复制概念Replication concepts

启用了二进制日志时，源服务器会将已提交的事务写入二进制日志中。When a binary log is enabled, the source server writes committed transactions into the binary log. 二进制日志用于复制。The binary log is used for replication. 默认情况下，会为最多支持 16 TB 存储的所有新预配的服务器启用此日志。It's turned on by default for all newly provisioned servers that support up to 16 TB of storage. 每台副本服务器上运行两个线程。On replica servers, two threads run on each replica server. 一个线程是 IO 线程，另一个线程是 SQL 线程：One thread is the IO thread, and the other is the SQL thread:

IO 线程连接到源服务器并请求更新的二进制日志。The IO thread connects to the source server and requests updated binary logs. 此线程接收二进制日志更新。This thread receives the binary log updates. 这些更新保存在副本服务器上，位于称为“中继日志”的本地日志中。Those updates are saved on a replica server, in a local log called the relay log.

SQL 线程读取中继日志，然后在副本服务器上应用数据更改。The SQL thread reads the relay log and then applies the data changes on replica servers.

监视复制延迟Monitoring replication latency

Azure Database for MySQL 在 Azure Monitor 中提供了以秒为单位的复制延迟指标。Azure Database for MySQL provides the metric for replication lag in seconds in Azure Monitor. 此指标仅适用于只读副本服务器。This metric is available only on read replica servers. 它由 MySQL 中提供的 seconds_behind_master 指标计算得出。It's calculated by the seconds_behind_master metric that's available in MySQL.

若要了解复制滞后时间增加的原因，请使用 MySQL 工作台 或 Azure Cloud Shell连接到副本服务器。To understand the cause of increased replication latency, connect to the replica server by using MySQL Workbench or Azure Cloud Shell. 然后运行以下命令。Then run following command.

备注

在代码中，将示例值替换为你的副本服务器名称和管理员用户名。In your code, replace the example values with your replica server name and admin username. 对于 Azure Database for MySQL，管理员用户名需要包含 @\。The admin username requires @\ for Azure Database for MySQL.

mysql --host=myreplicademoserver.mysql.database.azure.com --user=myadmin@mydemoserver -p

下图显示了 Cloud Shell 终端中的该体验过程：Here's how the experience looks in the Cloud Shell terminal:

Requesting a Cloud Shell.Succeeded.

Connecting terminal...

Welcome to Azure Cloud Shell

Type "az" to use Azure CLI

Type "help" to learn about Cloud Shell

user@Azure:~$mysql -h myreplicade