Поделиться через

Устранение неполадок задержки репликации в База данных Azure для MySQL — гибкий сервер

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: База данных Azure для MySQL — отдельный сервер База данных Azure для MySQL — гибкий сервер

Внимание

База данных Azure для MySQL один сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL гибкого сервера. Дополнительные сведения о миграции на гибкий сервер База данных Azure для MySQL см. в статье "Что происходит с одним сервером База данных Azure для MySQL?"

Примечание.

В этой статье содержится ссылка на термин, который корпорация Майкрософт больше не использует. Когда этот термин будет удален из программного обеспечения, мы удалим его из статьи.

Компонент Реплика чтения позволяет реплицировать данные с сервера службы "База данных Azure для MySQL" на сервер реплик только для чтения. Вы можете масштабировать рабочие нагрузки горизонтально, переправляя запросы на чтение и отчетность из приложения на серверы реплики. Эта настройка снижает давление на исходный сервер и повышает общую производительность и задержку приложения по мере масштабирования.

Реплики чтения обновляются асинхронно с помощью технологии репликации на основе позиции файла собственного двоичного журнала (binlog) ядра MySQL. Дополнительные сведения см. в статье о конфигурации репликации на основе позиции файла binlog MySQL.

Запаздывание репликации на вторичных репликах чтения зависит от нескольких факторов. Эти функции включают, помимо прочего, перечисленные ниже.

  • Задержки сети.
  • Объем транзакций на исходном сервере.
  • Уровень вычислений исходного сервера и сервера вторичной реплики чтения.
  • Запросы, выполняющиеся на исходном сервере и сервере-получателе.

В этой статье объясняются принципы устранения задержки репликации в Базе данных Azure для MySQL. Вы также получите лучшее представление о некоторых распространенных причинах увеличения задержки репликации на серверах реплик.

Примечание.

Эта статья содержит упоминания термина slave (ведомый) . Корпорация Майкрософт больше не использует его. Когда этот термин будет удален из программного обеспечения, мы удалим его из статьи.

Основные понятия репликации

Когда включен двоичный журнал, сервер-источник записывает в него зафиксированные транзакции. Двоичный журнал используется для репликации. Он включен по умолчанию для всех новых подготовленных серверов, поддерживающих хранилище объемом до 16 ТБ. На каждом сервере реплики выполняются два потока. Один поток — это поток операций ввода-вывода, другой — поток SQL.

  • Поток ввода-вывода подключается к исходному серверу и запрашивает обновленные двоичные журналы. Этот поток получает обновления двоичного журнала. Обновления сохраняются на сервере реплики в локальном журнале, который называется журналом ретрансляции.
  • Поток SQL считывает журнал ретрансляции, а затем применяет изменения данных на серверах реплик.

Мониторинг задержки репликации

Служба "База данных Azure для MySQL" также предоставляет в Azure Monitor метрику задержки репликации в секундах. Эта метрика доступна только на серверах реплики чтения. Она вычисляется по метрике seconds_behind_master, доступной в MySQL.

Чтобы понять причину увеличения задержки репликации, подключитесь к серверу реплики с помощью MySQL Workbench или Azure Cloud Shell. Затем выполните следующую команду.

Примечание.

В коде замените значения из примеров на имя сервера реплики и имя пользователя администратора. Для имени администратора требуется указать значение @\<servername> службы "База данных Azure для MySQL".

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

Ниже показано, как выглядит этот процесс в терминале Cloud Shell.

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 myreplicademoserver.mysql.database.azure.com -u myadmin@mydemoserver -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64796
Server version: 5.6.42.0 Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

В том же окне терминала Cloud Shell выполните следующую команду:

mysql> SHOW SLAVE STATUS;

Вот типичный пример выходных данных:

Мониторинг задержки репликации

Выходные данные содержат множество сведений. Как правило, необходимо обратить внимание только на строки, описанные в следующей таблице.

Метрическая Description
Slave_IO_State Представляет текущее состояние потока ввода-вывода. Как правило, при синхронизации исходного (главного) сервера указано состояние "Ожидание отправки события главным сервером". Такое состояние, как, например "Подключение к главному серверу", указывает на то, что реплика потеряла соединение с исходным сервером. Убедитесь, что исходный сервер запущен, или проверьте, не блокирует ли брандмауэр подключение.
Master_Log_File Представляет двоичный файл журнала, в который записывает данные исходный сервер.
Read_Master_Log_Pos Указывает, куда исходный сервер записывает данные в двоичный файл журнала.
Relay_Master_Log_File Представляет двоичный файл журнала, который считывается сервером-репликой с исходного сервера.
Slave_IO_Running Указывает, запущен ли поток ввода-вывода. Это значение должно быть равно Yes. Если значение равно NO, то репликация, скорее всего, нарушена.
Slave_SQL_Running Указывает, запущен ли поток SQL. Это значение должно быть равно Yes. Если значение равно NO, то репликация, скорее всего, нарушена.
Exec_Master_Log_Pos Указывает позицию в файле Relay_Master_Log_File, которую использует реплика. При наличии задержки эта позиция должна быть меньше значения Read_Master_Log_Pos.
Relay_Log_Space Указывает общий суммарный размер всех существующих файлов журналов ретрансляции. Максимально допустимый размер можно проверить путем запроса SHOW GLOBAL VARIABLES, например relay_log_space_limit.
Seconds_Behind_Master Отображает задержку репликации в секундах.
Last_IO_Errno Отображает код ошибки потока ввода-вывода при его наличии. Дополнительные сведения об этих кодах см. в справочнике по сообщениям об ошибках сервера MySQL.
Last_IO_Error Отображает сообщение об ошибке потока ввода-вывода при его наличии.
Last_SQL_Errno Отображает код ошибки потока SQL при его наличии. Дополнительные сведения об этих кодах см. в справочнике по сообщениям об ошибках сервера MySQL.
Last_SQL_Error Отображает сообщение об ошибке потока SQL при его наличии.
Slave_SQL_Running_State Указывает текущее состояние потока SQL. В этом состоянии нормальным является вариант System lock. Также нормальным является состояние Waiting for dependent transaction to commit. Это состояние указывает, что реплика ожидает обновления зафиксированных транзакций в других рабочих потоках SQL.

Если Slave_IO_Running имеет значение Yes, а Slave_SQL_Running — значение Yes, то репликация работает нормально.

Затем проверьте Last_IO_Errno, Last_IO_Error, Last_SQL_Errno и Last_SQL_Error. В этих полях отображается номер ошибки и сообщение об ошибке для самой последней ошибки, вызвавшей зависание потока SQL. Номер ошибки 0 и пустое сообщение означают отсутствие ошибки. Изучите любое ненулевое значение ошибки, проверив ее код в справочнике по сообщению об ошибке сервера MySQL.

Распространенные сценарии длительной задержки репликации

В следующих разделах рассматриваются ситуации, в которых часто встречается высокая задержка репликации.

Задержка в сети или высокая загрузка ЦП на исходном сервере

Если вы видите следующие значения, то, скорее всего, задержка репликации вызвана высокой задержкой в сети или высокой загрузкой ЦП на исходном сервере.

Slave_IO_State: Waiting for master to send event
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller than Master_Log_File, e.g. mysql-bin.00010

В этом случае поток ввода-вывода запущен и находится в состоянии ожидания на исходном сервере. Исходный сервер уже записал данные в двоичный файл журнала под номером 20. Реплика получила данные только до файла 10. Основными факторами высокой задержки репликации в этом сценарии являются скорость сетевого подключения или высокая загрузка ЦП на исходном сервере.

В Azure задержка сети в регионе обычно измеряется в миллисекундах. Между регионами задержка находится в диапазоне от миллисекунд до секунд.

В большинстве случаев задержка соединения между потоками ввода-вывода и исходным сервером вызвана высокой загрузкой ЦП на исходном сервере. Потоки ввода-вывода обрабатываются медленно. Эту проблему можно обнаружить с помощью Azure Monitor, проверив использование ЦП и количество одновременных подключений на исходном сервере.

Если на исходном сервере не отображается высокая загрузка ЦП, проблема может быть связана с задержкой в сети. Если задержка в сети непредвиденно высокая, проверьте на странице состояния Azure наличие известных проблем или простоев.

Объемные пакеты транзакций на исходном сервере

Если вы видите следующие значения, к задержке репликации, вероятно, приводит большой объем транзакций на исходном сервере.

Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
Master_Log_File: the binary file sequence is larger then Relay_Master_Log_File, e.g. mysql-bin.00020
Relay_Master_Log_File: the file sequence is smaller then Master_Log_File, e.g. mysql-bin.00010

В выходных данных видно, что реплика при получении двоичного журнала отстает от исходного сервера. Но поток операций ввода-вывода реплики указывает на то, что свободного места в журнале ретрансляции уже нет.

Задержка не вызвана скоростью сетевого подключения. Реплика пытается нагнать процесс. Но размер обновленного двоичного журнала превышает верхний предел на размер журнала ретрансляции.

Чтобы устранить эту проблему, включите журнал медленных запросов (запросов с задержкой) на исходном сервере. Журналы медленных запросов помогают обнаружить долго выполняющиеся транзакции на исходном сервере. Затем скорректируйте обнаруженные запросы, чтобы уменьшить задержку на сервере.

Задержка репликации такого плана обычно вызвана загрузкой на исходном сервере. Когда на исходных серверах обрабатывается недельная или месячная порция данных, задержка репликации, увы, неизбежна. Серверы реплики в конечном итоге нагоняют процесс, когда интенсивная обработка данных на исходном сервере завершается.

Медленная работа на сервере реплики

Если вы видите указанные ниже значения, проблема может быть на сервере реплики.

Slave_IO_State: Waiting for master to send event
Master_Log_File: The binary log file sequence equals to Relay_Master_Log_File, e.g. mysql-bin.000191
Read_Master_Log_Pos: The position of master server written to the above file is larger than Relay_Log_Pos, e.g. 103978138
Relay_Master_Log_File: mysql-bin.000191
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: The position of slave reads from master binary log file is smaller than Read_Master_Log_Pos, e.g. 13468882
Seconds_Behind_Master: There is latency and the value here is greater than 0

В этом сценарии в выходных данных видно, что поток ввода-вывода и поток SQL работают нормально. Реплика считывает тот же двоичный файл журнала, который ведется исходным сервером. Однако определенная задержка на сервере реплики отражает те же операции, что и исходный сервер.

В следующих разделах описаны распространенные причины такой задержки.

В таблице нет первичного ключа или уникального ключа

База данных Azure для MySQL использует репликацию на основе строк. Исходный сервер записывает события в двоичный журнал, отражая изменения в отдельных строках таблиц. Затем поток SQL реплицирует эти изменения в соответствующие строки таблицы на сервере реплики. Если в таблице отсутствует первичный ключ или уникальный ключ, то поток SQL просматривает все строки в целевой таблице, чтобы применить изменения. Это сканирование может вызвать задержку репликации.

В MySQL первичный ключ — это связанный индекс, который обеспечивает высокую производительность запросов, поскольку не может содержать значения NULL. При использовании подсистемы хранилища InnoDB данные таблицы физически упорядочены для быстрого поиска и сортировки на основе первичного ключа.

Перед созданием сервера реплики рекомендуется добавить первичный ключ в таблицы на исходном сервере. Чтобы улучшить задержку репликации, добавьте первичные ключи на исходном сервере, а затем повторно создайте реплики чтения.

Используйте следующий запрос, чтобы узнать, в каких таблицах на исходном сервере отсутствует первичный ключ:

select tab.table_schema as database_name, tab.table_name 
from information_schema.tables tab left join 
information_schema.table_constraints tco 
on tab.table_schema = tco.table_schema 
and tab.table_name = tco.table_name 
and tco.constraint_type = 'PRIMARY KEY' 
where tco.constraint_type is null 
and tab.table_schema not in('mysql', 'information_schema', 'performance_schema', 'sys') 
and tab.table_type = 'BASE TABLE' 
order by tab.table_schema, tab.table_name;

Долго выполняющиеся запросы на сервере реплики

Рабочая нагрузка на сервере реплики может вести к отставанию потока SQL от потока ввода-вывода. Одной из распространенных причин высокой задержки репликации являются долго выполняющиеся запросы на сервере реплики. Чтобы устранить эту проблему, включите журнал запросов с задержкой (медленных запросов) на сервере реплики.

Медленные запросы могут увеличивать потребление ресурсов или замедлять работу сервера, и реплике не удается нагнать исходный сервер. В этом случае скорректируйте медленные запросы. Более быстрые запросы предотвращают блокирование потока SQL и значительно улучшают задержку репликации.

Запросы DDL на исходном сервере

На исходном сервере команда языка описания данных DDL, например ALTER TABLE, иногда занимает много времени. Во время выполнения команды DDL на исходном сервере могут параллельно выполняться тысячи других запросов.

При репликации DDL для обеспечения согласованности базы данных ядро MySQL выполняет DDL в одном потоке репликации. Во время выполнения этой задачи все остальные реплицируемые запросы блокируются и вынуждены дожидаться завершения операции DDL на сервере реплики. Эта задержка возникает даже в операциях DDL с подключением к сети. Операции DDL увеличивают задержку репликации.

Если на исходном сервере включен журнал запросов с задержкой (медленных запросов), вы можете обнаружить эту проблему, проверив, не выполнялась на исходном сервере команда DDL. Вы можете задействовать алгоритм INPLACE для изменения таблицы (ALTER TABLE) путем удаления, переименования и создания индекса. Возможно, вам потребуется скопировать данные таблицы и перестроить ее.

Как правило, алгоритм INPLACE поддерживает параллельное выполнение DML. Но при подготовке и запуске операции можно временно выполнить монопольную блокировку метаданных таблицы. Таким образом, для инструкции CREATE INDEX можно с помощью предложений ALGORITHM и LOCK повлиять на способ копирования таблицы и уровень параллелизма операций чтения и записи. Чтобы предотвратить выполнение операций DML, также можно добавить индекс FULLTEXT или SPATIAL.

В следующем примере создается индекс с помощью предложений ALGORITHM и LOCK.

ALTER TABLE table_name ADD INDEX index_name (column), ALGORITHM=INPLACE, LOCK=NONE;

К сожалению, избежать задержки репликации для инструкции DDL, требующей блокировки, невозможно. Чтобы уменьшить потенциальные последствия, выполняйте операции DDL таких видов в часы наименьшей нагрузки, например в ночное время.

Снижение характеристик сервера реплики

В Базе данных Azure для MySQL реплики чтения используют ту же конфигурацию сервера, что и исходный сервер. Конфигурацию сервера-реплики можно изменить после ее создания.

При ухудшении характеристик сервера реплики рабочая нагрузка может потреблять больше ресурсов, что, в свою очередь, может вести к задержке репликации. Чтобы обнаружить эту проблему, используйте Azure Monitor и проверьте потребление ресурсов ЦП и памяти сервером реплики.

В этом случае рекомендуется сохранить в конфигурации сервера реплики значения не ниже соответствующих значений исходного сервера. Такая конфигурация позволяет реплике синхронизировать работу с исходным сервером.

Улучшение задержки репликации путем настройки параметров исходного сервера

По умолчанию в Базе данных Azure для MySQL репликация оптимизирована для работы с параллельными потоками в репликах. Когда рабочие нагрузки с высокой степенью параллелизма на исходном сервере приводят к отставанию сервера реплики, улучшить задержку репликации можно путем настройки параметра binlog_group_commit_sync_delay на исходном сервере.

Параметр binlog_group_commit_sync_delay задает время ожидания (в микросекундах) фиксации журнала в двоичном формате перед синхронизацией файла этого журнала. Преимущество этого параметра заключается в том, что вместо немедленного применения каждой зафиксированной транзакции сервер-источник отправляет обновления двоичного журнала в виде пакета. Эта задержка сокращает количество операций ввода-вывода в реплике и помогает повысить производительность.

Попробуйте установить для параметра binlog_group_commit_sync_delay значение в районе 1000. Затем понаблюдайте за задержкой репликации. Будьте внимательны при настройке этого параметра и используйте его только для рабочих нагрузок с высоким уровнем параллелизма.

Внимание

На сервере реплики для параметра binlog_group_commit_sync_delay рекомендуется задавать значение 0. В отличие от исходного сервера, сервер реплики не отличается высоким параллелизмом, и увеличение значения binlog_group_commit_sync_delay на нем может опосредованно привести к увеличению задержки репликации.

Для рабочих нагрузок с низким уровнем параллелизма, включающих множество одноэлементных транзакций, параметр binlog_group_commit_sync_delay может увеличить задержку. Задержка может увеличиться, так как поток ввода-вывода ожидает обновления больших двоичных файлов журнала, даже если фиксируется лишь несколько транзакций.

Дополнительные параметры устранения неполадок

Если команда "Показать состояние раба" не предоставляет достаточно сведений для устранения неполадок с задержкой репликации, попробуйте просмотреть эти дополнительные параметры для изучения активных или ожидающих процессов.

Просмотр таблицы потоков

В performance_schema.threads таблице показано состояние процесса. Процесс с состоянием ожидания блокировки lock_type указывает, что на одной из таблиц есть блокировка, предотвращающая обновление таблицы потоком репликации.

SELECT name, processlist_state, processlist_time FROM performance_schema.threads WHERE name LIKE '%slave%';

Дополнительные сведения см. в разделе "Общие потоки".

Просмотр таблицы replication_connection_status

В таблице performance_schema.replication_connection_status отображается текущее состояние потока операций ввода-вывода репликации, обрабатывающего подключение реплики к источнику, и он часто изменяется. Таблица содержит значения, которые зависят от соединения.

SELECT * FROM performance_schema.replication_connection_status;

Просмотр таблицы replication_applier_status_by_worker

В performance_schema.replication_applier_status_by_worker таблице показано состояние рабочих потоков, последняя просмотренная транзакция вместе с последним номером ошибки и сообщением, которые помогают найти транзакцию с проблемой и определить первопричину.

Чтобы пропустить ошибки или транзакции, выполните следующие команды в репликации данных:

az_replication_skip_counter

or

az_replication_skip_gtid_transaction

SELECT * FROM performance_schema.replication_applier_status_by_worker;

Просмотр инструкции SHOW RELAYLOG EVENTS

В show relaylog events инструкции показаны события в журнале ретрансляции реплики.

· Для репликации на основе GITD (реплика чтения) инструкция показывает транзакцию GTID и файл binlog и его положение, можно использовать mysqlbinlog для получения содержимого и инструкций, выполняемых. · Для репликации позиции binlog MySQL (используемой для репликации данных), она отображает инструкции, которые помогут узнать, на каких транзакциях таблицы выполняются транзакции таблиц.

Проверка выходных данных монитора innoDB standard и блокировки

Вы также можете попробовать проверить стандартный монитор InnoDB и вывод монитора блокировки, чтобы устранить блокировки и взаимоблокировки и свести к минимуму задержку репликации. Монитор блокировки совпадает со стандартным монитором, за исключением того, что он содержит дополнительные сведения о блокировке. Чтобы просмотреть эти дополнительные сведения о блокировке и взаимоблокировке, выполните команду show engine innodb status\G.

Следующие шаги

Ознакомьтесь с обзором репликации файла binlog MySQL.