Вопрос или проблема
Сегодня мои два сервера-реплики (один mysql 5.1 и второй MariaDB 5.5, мастер – mysql 5.1) начали отставать. Подобная ситуация довольно часто происходит, когда задержка достигает даже 10000 секунд, потому что у реплик хуже аппаратная конфигурация, чем у мастера, но сейчас я довольно напряжен. Задержка на обоих серверах продолжает расти и на данный момент достигает 25K секунд от мастера. Я начал исследовать, что идет не так. Пролистывание логов mysql на мастере и репликах не дало мне ничего. Серверы работают на Centos 5, а Mariadb – на Centos 6.
Это вывод статуса реплики MariaDB:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterserevr
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.006778
Read_Master_Log_Pos: 401041447
Relay_Log_File: relay-bin.020343
Relay_Log_Pos: 14867924
Relay_Master_Log_File: mysqld-bin.006777
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: ses,phar
Replicate_Do_Table:
Replicate_Ignore_Table: portal.aaa_jm_tmp,portal.newsletter
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 14867639
Relay_Log_Space: 1474785535
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: 26484
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: 1
1 row in set (0.00 sec)
Из нескольких выводов я заметил, что Relay_Log_Pos и Exec_Master_Log_Pos не увеличиваются. Я попытался перезапустить процессы реплики, но это ничего не изменило, и задержка продолжает расти. Следующим шагом было выяснить, на каком запросе остановилась репликация.
Используя mysqlbinlog
mysqlbinlog relay-bin.020343 > /root/RelayLogQueries1.txt
В RelayLogQueries1.txt я нашел позицию 14867924:
# at 14867924 #130927 10:03:21 server id 1 end_log_pos 14867709 Query thread_id=160780134 exec_time=3 error_code=0 SET TIMESTAMP=1380269001/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=9/*!*/; BEGIN /*!*/; # at 14867994 # at 14868101 # at 14868669 # at 14869417 # at 14869873 # at 14870663 # at 14871697 # at 14872055 # at 14872845 # at 14873747 # at 14874591 # at 14875387 # at 14876265 # at 14877039 # at 14877985 # at 14878299 # at 14879091 # at 14879853 # at 14880255 # at 14881029 . . . # at 117398235 # at 117399219 # at 117400203 # at 117401191 # at 117402179 # at 117403167 # at 117403969 # at 117404957 # at 117405945 # at 117406933 # at 117407921 # at 117408909 # at 117409897 # at 117410885 # at 117411873 # at 117412861 # at 117413849 # at 117414837 # at 117415785 # at 117416797 # at 117417839 # at 117418595 # at 117419585 #130927 10:03:21 server id 1 end_log_pos 14867816 Table_map: `test`.`pac_list` mapped to number 216570427 #130927 10:03:21 server id 1 end_log_pos 14868384 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14869132 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14869588 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14870378 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14871412 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14871770 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14872560 Update_rows: table id 216570427 #130927 10:03:21 server id 1 end_log_pos 14873462 Update_rows: table id 216570427 . . .
Теперь я в замешательстве, потому что сначала у меня нет представления о том, как интерпретировать этот лог (это нормально или нет), и, во-вторых, не знаю, как это исправить.
Иногда, когда я получаю ошибки репликации, этот трюк помогает:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Но сейчас у меня нет ошибок, и оба процесса IO и SQL реплики работают.
Может ли установка SQL_SLAVE_SKIP_COUNTER=1 восстановить репликацию??
Что я могу сделать, чтобы более тщательно диагностировать эту проблему и исправить ее без повторной настройки реплики с нуля (этого сценария я хочу избежать)
ИСПРАВЛЕНИЕ:
Задержка началась, когда один из разработчиков случайно скопировал одну из таблиц pac_list (200MB с 600000 записями) и назвал ее test.pac_list (в названии таблицы есть точка). Он хотел создать копию в базе данных test, но сделал что-то не так и создал таблицу test.pac_list в той же базе данных, где находится оригинальная таблица. После того как он понял свою ошибку, он удалил таблицу test.pac_list и создал таблицы pac_list в новой базе данных. Может ли это быть причиной такой большой задержки?
Выполните команду show full processlist, чтобы увидеть, какой запрос завис в репликации.
Кроме того, я вижу, что есть команда BEGIN:
14867924
SET TIMESTAMP=1380269001/!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=9/!/;
BEGIN
Так что, возможно, репликация заблокирована до END?
У вас есть огромная транзакция, через которую проходят реплики. Вам нужно подождать, пока она пройдет. Не пропускайте транзакцию, иначе вам может понадобиться повторная инициализация реплик.
Существует несколько способов ускорить работы реплик, если вы готовы принять риск повторной инициализации реплики в случае сбоя ОС или отключения питания.
Также вам стоит сильно беспокоиться о том, насколько далеко вы находитесь от окончания поддержки на версии 5.1.
Ответ или решение
Решение проблемы с отставанием в репликации MySQL
Проблема и контекст
Вы столкнулись с серьезной проблемой отставания репликации на двух слейвах (MariaDB 5.5 и MySQL 5.1) относительно вашего мастера (MySQL 5.1). Текущая задержка достигает 25,000 секунд, и вы заметили, что параметры Relay_Log_Pos и Exec_Master_Log_Pos не увеличиваются. Это указывает на возможные проблемы либо в репликации, либо в том, как слейвы обрабатывают транзакции.
Анализ состояния репликации
Ваши выходные данные из SHOW SLAVE STATUS\G показывают, что оба процесса – Slave_IO_Running и Slave_SQL_Running – работают. Однако Seconds_Behind_Master постоянно увеличивается, что указывает на то, что слейвы не могут обработать транзакции быстрее, чем они поступают.
Обратите внимание на следующее:
-
Транзакция с высоким объемом данных: Логи показывают, что репликация может быть заблокирована на этапе, где начинается большая транзакция (параметры
TIMESTAMPиBEGINвRelayLogQueries1.txtуказывают на это). Это может создать "бутылочное горлышко", когда слейв не может справиться с нагрузкой из-за большой транзакции. -
Задержка в обработке транзакций: Если слейвы имеют более слабую аппаратную конфигурацию по сравнению с мастером, это также может влиять на производительность. Ваша проблема может быть связана с недостаточными ресурсами (ЦП, память, диск) для обработки данных.
Диагностика проблемы
-
Процесс с блокировкой: Используйте команду
SHOW FULL PROCESSLIST, чтобы выяснить, какой запрос сейчас "висит" и мешает продвижению репликации. Вы можете столкнуться с длительной блокировкой транзакций, из-за чего слейвы не могут обработать новые данные. -
Обработка больших транзакций: Большие транзакции, такие как обновления или вставки, могут вызвать серьезные задержки. Если срочное исправление не требуется, дайте слейвам время для завершения текущих операций, так как принудительная пропускная способность может привести к неправильным данным.
-
Логи: Поскольку вы не нашли ошибок в логах MySQL на мастере и слейвах, попробуйте настроить логи для большей детальности. Возможно, стоит включить более подробное логирование для отладки.
Возможные пути решения
-
Увеличение ресурсов: Возможно, вам стоит рассмотреть возможность увеличения аппаратных ресурсов на слейвах, чтобы они могли быстрее обрабатывать репликацию.
-
Настройка параметров репликации: Проверьте конфигурацию
innodb_flush_log_at_trx_commit, чтобы оптимизировать записи в журнал. Однако изменение этих параметров может привести к потере данных в случае сбоя. -
Обратите внимание на настройку базы данных: Убедитесь, что на слейвах достаточно системных ресурсов (ЦП, ОЗУ, SSD в случае операций ввода-вывода).
-
Skipping transactions: Несмотря на ваши мысли о «SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;», я бы не рекомендовал это делать, если у вас нет ошибок в репликации или если вы не уверены в критичности данных. Лучше дождаться завершения транзакций.
Заключение
Сложные ситуации с задержкой в репликации требуют обдуманных шагов и наперед продуманных решений. Оценивайте каждый шаг, исходя из производительности вашей системы, не спешите к радикальным мерам. Регулярно наблюдайте за производительностью, и, по возможности, обновите свои версии баз данных, так как использование устаревших систем может представлять дополнительные риски и ограничения.
Если вы хотите избежать повторения подобной ситуации в будущем, следует рассмотреть возможность внедрения системы мониторинга репликации, чтобы вы могли быстро обнаруживать и реагировать на подобные проблемы в реальном времени.