Этот пост был изначально написан Мухаммедом Ирфаном
Здесь, в группе поддержки Percona MySQL , мы часто сталкиваемся с проблемами, когда клиент жалуется на задержки репликации, и во многих случаях проблема заканчивается привязкой к ведомой репликации MySQL. Это, конечно, не является чем-то новым для пользователей MySQL, и у нас было несколько постов в блоге по производительности MySQL на эту тему за эти годы (в прошлом два особенно популярных поста были: « Причины задержки репликации MySQL » и « Управление подчиненным устройством»). Отставание с MySQL Replication .
Однако в сегодняшнем сообщении я расскажу о некоторых новых способах выявления задержек в репликации, включая возможные причины отстающих рабов, и о том, как решить эту проблему.
Как определить задержку репликации
Репликация MySQL работает с двумя потоками, IO_THREAD и SQL_THREAD. IO_THREAD подключается к мастеру, читает двоичные события журнала от мастера по мере их поступления и просто копирует их в локальный файл журнала, называемый relaylog . С другой стороны, SQL_THREAD считывает события из журнала ретрансляции, хранящегося локально на ведомом устройстве репликации (файл, который был записан потоком ввода-вывода), а затем применяет их как можно быстрее. Всякий раз, когда репликация задерживается, важно сначала выяснить, задерживается ли она на ведомом IO_THREAD или ведомом SQL_THREAD.
Обычно поток ввода-вывода не вызывает большой задержки репликации, поскольку он просто читает двоичные журналы с главного устройства. Тем не менее, это зависит от подключения к сети, задержки в сети … как быстро это между серверами. Подчиненный поток ввода-вывода может быть медленным из-за высокой пропускной способности. Обычно, когда ведомый IO_THREAD способен читать двоичные журналы достаточно быстро, он копирует и накапливает журналы ретрансляции на ведомом устройстве, что является одним из признаков того, что ведомый IO_THREAD не является виновником задержки ведомого устройства.
С другой стороны, когда ведомый SQL_THREAD является источником задержек репликации, это, вероятно, связано с тем, что запросы, поступающие из потока репликации, слишком долго выполняются на ведомом устройстве. Иногда это происходит из-за разного оборудования между главным и подчиненным, разных индексов схемы, рабочей нагрузки. Более того, рабочая нагрузка подчиненного OLTP иногда вызывает задержки репликации из-за блокировки. Например, если длительное чтение таблицы MyISAM блокирует поток SQL, или любая транзакция таблицы InnoDB создает блокировку IX и блокирует DDL в потоке SQL. Кроме того, примите во внимание, что подчиненный однопоточный до MySQL 5.6, который был бы другой причиной задержек на ведомом SQL_THREAD.
Позвольте мне показать вам через главный статус / статус ведомого, чтобы определить, что подчиненное устройство отстает от ведомого IO_THREAD или ведомого SQL_THREAD.
mysql-master> SHOW MASTER STATUS; +------------------+--------------+------------------+------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+--------------+------------------+------------------------------------------------------------------+ | mysql-bin.018196 | 15818564 | | bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:1-2331947 | +------------------+--------------+------------------+------------------------------------------------------------------+ mysql-slave> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Queueing master event to the relay log Master_Host: master.example.com Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.018192 Read_Master_Log_Pos: 10050480 Relay_Log_File: mysql-relay-bin.001796 Relay_Log_Pos: 157090 Relay_Master_Log_File: mysql-bin.018192 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 5395871 Relay_Log_Space: 10056139 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 230775 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166 Master_Info_File: /var/lib/mysql/i1/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166 Executed_Gtid_Set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166, ea75c885-c2c5-11e3-b8ee-5cf3fcfc9640:1-1370 Auto_Position: 1
Это ясно указывает на то, что ведомый IO_THREAD отстает, и, очевидно, из-за этого ведомый SQL_THREAD также отстает, что приводит к задержкам репликации. Как вы можете видеть, главный файл журнала — это mysql-bin.018196 (параметр файла из основного состояния), а ведомый IO_THREAD находится в mysql-bin.018192 ( главный_лог_файл из подчиненного состояния ), который указывает, что ведомый IO_THREAD читает из этого файла, в то время как на главном он пишет на mysql-bin.018196 , поэтому ведомый IO_THREAD отстает на 4 бинлога. Между тем, ведомый SQL_THREAD читает из того же файла, то есть mysql-bin.01819 2 (Relay_Master_Log_File из статуса ведомого)Это указывает на то, что ведомый SQL_THREAD применяет события достаточно быстро, но он также запаздывает, что можно наблюдать по разнице между Read_Master_Log_Pos & Exec_Master_Log_Pos из выходных данных показа ведомого состояния.
Вы можете рассчитать задержку ведомого SQL_THREAD из Read_Master_Log_Pos — Exec_Master_Log_Pos в целом, при условии, что выходные данные параметра Master_Log_File из статуса show slave и параметр Relay_Master_Log_File из выходных данных статуса show slave одинаковы. Это даст вам приблизительное представление о том, как быстро ведомый SQL_THREAD применяет события. Как я уже упоминал выше, ведомый IO_THREAD отстает, как в этом примере, тогда отстает и ведомый SQL_THREAD. Вы можете прочитать подробное описание полей вывода статуса show slave здесь.
Кроме того, параметр Seconds_Behind_Master показывает огромную задержку в секундах. Однако это может вводить в заблуждение, поскольку оно измеряет только разницу между временными метками журнала ретрансляции, который был выполнен последним, по сравнению с записью журнала ретрансляции, которая была недавно загружена IO_THREAD. Если на ведущем блоке больше блоков, ведомый не учитывает их в расчете Seconds_behind_master. Вы можете получить более точную оценку задержки ведомого устройства, используя pt-heartbeat из Percona Toolkit. Итак, мы научились проверять задержки репликации — либо ведомый IO_THREAD, либо ведомый SQL_THREAD. Теперь позвольте мне дать несколько советов и предложений о том, чем именно вызвана эта задержка.
Советы и предложения Что вызывает задержку репликации и возможные исправления
Обычно ведомый IO_THREAD отстает из-за медленной сети между ведущим / ведомым. В большинстве случаев включение slave_compressed_protocol помогает уменьшить задержку ведомого IO_THREAD. Еще одно предложение — отключить бинарное ведение журнала на ведомом устройстве, поскольку оно также требует интенсивного ввода-вывода, если только вы не потребовали его для восстановления на определенный момент времени.
Чтобы минимизировать ведомую задержку SQL_THREAD, сконцентрируйтесь на оптимизации запросов. Я рекомендую включить опцию конфигурации log_slow_slave_statements, чтобы запросы, выполняемые ведомым устройством, которые занимают больше времени long_query_time , регистрировались в медленном журнале. Чтобы собрать больше информации о производительности запросов, я бы также рекомендовал установить для параметра конфигурации log_slow_verbosity значение «full».
Таким образом, мы можем видеть, есть ли запросы, выполняемые ведомым SQL_thread, для выполнения которых требуется много времени. Вы можете следить за моим предыдущим постом о том, как включить медленный журнал запросов для определенного периода времени с упомянутыми опциями здесь . И как напоминание, log_slow_slave_statements как переменная были впервые введены в Percona Server 5.1, который теперь является частью vanilla MySQL начиная с версии 5.6.11. В вышестоящей версии MySQL Server log_slow_slave_statements были введены как опция командной строки. Подробности можно найти здесь, в то время как log_slow_verbosity является специфической функцией Percona Server.
Еще одна причина задержки для ведомого SQL_THREAD, если вы используете формат binlog на основе строк, состоит в том, что если в любой таблице вашей базы данных отсутствует первичный ключ или уникальный ключ, то он будет сканировать все строки таблицы на наличие DML на ведомом устройстве и вызывает задержки репликации, поэтому убедитесь, что все ваши таблицы должны иметь первичный ключ или уникальный ключ. Подробности см. В этом отчете об ошибке http://bugs.mysql.com/bug.php?id=53375. Вы можете использовать приведенный ниже запрос на ведомом устройстве, чтобы определить, в какой из таблиц базы данных отсутствует первичный или уникальный ключ.
mysql> SELECT t.table_schema,t.table_name,engine
FROM information_schema.tables t INNER JOIN information_schema .columns c
on t.table_schema=c.table_schema and t.table_name=c.table_name
GROUP BY t.table_schema,t.table_name
HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;
Одно из улучшений сделано для этого случая в MySQL 5.6, где в хеше памяти выручает slave_rows_search_algorithms .
Обратите внимание, что Seconds_Behind_Master не обновляется, пока мы читаем огромное событие RBR, поэтому «отставание» может быть связано именно с этим — мы не завершили чтение события. Например, при репликации на основе строк огромные транзакции могут вызвать задержку на ведомой стороне, например, если у вас есть таблица с 10 миллионами строк, и вы делаете «УДАЛИТЬ ИЗ таблицы, ГДЕ id <5000000», 5 миллионов строк будут отправлены на подчиненное устройство, каждая строка отдельно, что будет болезненно медленный. Таким образом, если вам нужно время от времени удалять самые старые строки из огромной таблицы, используя Partitioning, это может быть хорошей альтернативой для этого для некоторых видов рабочих нагрузок, где вместо использования DELETE используйте DROP, старый раздел может быть хорошим, и реплицируется только оператор, потому что это будет операция DDL. ,
Чтобы объяснить это лучше, предположим, что у вас есть partition1, содержащий строки идентификаторов от 1 до 1000000 , partition2 — идентификаторы от 1000001 до 2000000 и т. Д., Поэтому вместо удаления с помощью оператора «DELETE FROM table WHERE ID <= 1000000;» Вы можете сделать «ALTER TABLE DROP partition1;» вместо. Обратитесь к руководству по изменению работы с разделами — посмотрите этот замечательный пост от моего коллеги Романа, объясняющего возможные причины задержек репликации здесь.
pt-stalk — это один из лучших инструментов Percona Toolkit, который собирает диагностические данные при возникновении проблем. Вы можете настроить pt-stalk следующим образом, чтобы при возникновении задержки ведомого устройства он мог регистрировать диагностическую информацию, которую мы можем позже проанализировать, чтобы выяснить, что именно вызывает задержку.
Вот как вы можете настроить pt-stalk таким образом, чтобы он собирал диагностические данные при наличии ведомого лага:
------- pt-plug.sh contents
#!/bin/bash
trg_plugin() {
mysqladmin $EXT_ARGV ping &> /dev/null
mysqld_alive=$?
if [[ $mysqld_alive == 0 ]]
then
seconds_behind_master=$(mysql $EXT_ARGV -e "show slave status" --vertical | grep Seconds_Behind_Master | awk '{print $2}')
echo $seconds_behind_master
else
echo 1
fi
}
# Uncomment below to test that trg_plugin function works as expected
#trg_plugin
-------
-- That's the pt-plug.sh file you would need to create and then use it as below with pt-stalk:
$ /usr/bin/pt-stalk --function=/root/pt-plug.sh --variable=seconds_behind_master --threshold=300 --cycles=60 [email protected] --log=/root/pt-stalk.log --pid=/root/pt-stalk.pid --daemonize
Вы можете отрегулировать порог, в настоящее время равный 300 секундам, комбинируя его с параметром –cycles, это означает, что если значение seconds_behind_master равно> = 300 в течение 60 секунд или более, тогда pt-stalk начнет сбор данных. Добавление опции –notify-by-email будет уведомлять по электронной почте, когда pt- stalk собирает данные. Вы можете соответствующим образом настроить пороги pt-stalk таким образом, чтобы он запускал сбор диагностических данных во время проблемы.
Вывод
A lagging slave is a tricky problem but a common issue in MySQL replication. I’ve tried to cover most aspects of replication delays in this post. Please share in the comments section if you know of any other reasons for replication delay.