Как администратор баз данных, я часто сталкиваюсь в своей работе с некоторыми проблемами задержки синхронизации master-slave MySQL.На самом деле существует множество причин этих проблем с медленной синхронизацией.Это может быть вызвано сетевой проблемой ведущего и ведомого, или это может быть вызвано из-за проблемы с пропускной способностью сети. Это может быть связано с большими транзакциями или задержками, вызванными однопоточной репликацией. Недавно я столкнулся с очень типичной проблемой задержки синхронизации и написал процесс анализа, надеясь получить более систематизированную методологию для большинства администраторов баз данных для устранения проблемы задержки синхронизации.
Сначала объясните предысторию (задача разбора без объяснения предыстории и сцены хулиганства)
В последнее время группа БД испытывает относительно большую задержку, эта группа БД специально используется для хранения данных мониторинга, и каждую минуту посредством загрузки данных импортируется большое количество данных. В целях экономии места исходный движок innodb, использовавший сжатые таблицы, был преобразован в движок TokuDB.Используются следующие версии и движки:
Версия MySQL: 5.7
Механизм хранения: TokuDB
После преобразования обнаруживается, что задержка master-slave постепенно увеличивается.В основном, он отстает от хоста примерно на 50 бинлогов каждый день, а данные задерживаются примерно на 7,5 часов.Хост генерирует около 160 бинлогов в день. Список binlog показан на следующем рисунке:
Благодаря знакомству с бизнесом причина задержки синхронизации ведущий-ведомый была быстро обнаружена, и проблема с задержкой была быстро решена. Вместо того, чтобы прямо говорить о решении, я хочу описать полный набор методов мышления для решения проблемы задержки master-slave, и давайте вместе с вами поразмышляем систематически. Подумайте о первопричине задержки и о том, как ее исправить. Я подумал, может быть, это будет иметь больше смысла. Лучше научить человека ловить рыбу, чем дать ему рыбу. Далее, давайте вместе поразмышляем.
Во-первых, раз произошла задержка master-slave, значит, скорость потребления на слейве не успевает за скоростью бинлога master. Давайте сначала подумаем о возможных причинах и проверим правильность догадки на основе подсказок на месте происшествия. По сути, так называемое устранение неполадок — это процесс выдвижения возможных предположений, а затем их непрерывное доказательство. Разница в том, что опыт у всех разный, и качество расследования не всегда одинаковое, вот и все. Тогда давайте откроем наши умы со всех возможных аспектов.
Интернет
Сеть может вызвать проблему задержки ведущий-ведомый.Например, полоса пропускания ведущего или ведомого заполнена, а сетевая задержка между ведущим и ведомым очень велика.Это может привести к тому, что двоичный журнал на ведущем не полностью передается ведомому, вызывая задержку.
Поток ввода-вывода моей группы БД вытащил соответствующий двоичный журнал в подчиненную БД почти в реальном времени, в основном устраняя задержку, вызванную сетью. Его также можно комбинировать с мониторингом качества сети, чтобы дополнительно подтвердить, что проблема связана с сетью.
Производительность машины
Раб использовал плохую машину? Раньше я сталкивался с некоторыми подчиненными бизнес-процессами, использующими плохие машины, что приводило к задержке между ведущими и подчиненными. Например, хост использует SSD, а ведомое устройство по-прежнему использует SATA. Концепция использования плохой машины в качестве ведомой машины должна быть изменена.В связи с растущим спросом на автоматическое переключение БД, особенно в финансовой отрасли, где я работаю, ведомая машина должна быть, по крайней мере, не хуже, чем основная конфигурация.
Раб с высокой нагрузкой? Есть много сервисов, которые будут выполнять статистику на подчиненной машине, что создаст большую нагрузку на подчиненный сервер, что приведет к большой задержке подчиненной машины.Это можно быстро найти с помощью команды top.
Есть ли проблема с подчиненным диском? Когда возникает проблема с диском, рейд-картой и политикой планирования, иногда одна задержка ввода-вывода может быть очень большой.Например, когда батарея рейд-карты заряжена и разряжена, режим обратной записи должен быть установлен без принудительной обратной записи. , Изменено для записи через. Используйте команду iostat, чтобы проверить состояние ввода-вывода на диске данных БД, определить, является ли время выполнения одного ввода-вывода очень большим, размер блока и состояние очереди диска и т. д. Вы можете сравнить правила планирования ввода-вывода для диска БД и настройки размера блока. Используйте iostat для просмотра операции ввода-вывода:
С вводом-выводом проблем нет.Одиночная задержка ввода-вывода очень мала, количество операций ввода-вывода очень низкое, а пропускная способность записи невелика. Правила планирования (cat /sys/block/fioa/queue/scheduler) и размер блока совпадают с настройками хоста, за исключением проблем с диском.
Судя по рабочим показателям, нагрузка на машину очень низкая, и производительность машины также может быть исключена.
большой бизнес
Есть ли что-то важное, что происходит часто? С этим могут столкнуться многие администраторы баз данных, например, выполнение большого количества операций удаления в режиме RBR или добавление неопределенного оператора (аналогично лимиту) при удалении в режиме MBR, операция изменения таблицы и т. д. вызовет задержки. . В этом можно быстро убедиться, просмотрев информацию, связанную со списком процессов, и используя mysqlbinlog для просмотра SQL в binlog. Эта идея также была исключена.
Замок
Проблема конфликта блокировок также может привести к медленному выполнению потока SQL ведомого устройства.Например, есть некоторые select.... для обновления SQL на ведомом устройстве или используется механизм MyISAM. Такие проблемы можно просмотреть, захватив список процессов и просмотрев таблицы, связанные с блокировками и транзакциями, в information_schema.
После проверки проблем с замком обнаружено не было.
параметр
Если часть параметров использует механизм innodb, вы можете настроить параметры innodb_flush_log_at_trx_commit и sync_binlog в соответствии с вашей собственной средой использования, чтобы повысить скорость репликации.Для TokuDB, используемой группой баз данных, вы можете оптимизировать такие параметры, как tokudb_commit_sync, tokudb_fsync_log_period, sync_binlog для внесения изменений. После настройки этих параметров задержка репликации будет иметь некоторое влияние.
Примечание. Эта корректировка может повлиять на безопасность данных и должна рассматриваться совместно с бизнесом.
Многопоточность
Проблема многопоточности может быть наиболее распространенной проблемой, с которой сталкиваются администраторы баз данных.В версиях 5.1 и 5.5 узкое место однопоточной репликации MySQL подвергалось широкой критике. MySQL официально поддерживает многопоточную репликацию, начиная с версии 5.6.
Легко подумать, что при однопоточной синхронизации один поток имеет узкое место при записи, что приводит к задержке между ведущим и подчиненным. Затем сначала настройте его на многопоточность, чтобы попробовать эффект.
Вы можете проверить, есть ли несколько потоков синхронизации, показав список процессов, или вы можете проверить, используется ли многопоточность, просмотрев параметры (показать переменные, такие как '%slave_parallel%')
Когда вы увидите результат на картинке выше, поздравляю, вы используете один поток. Используйте следующую команду для перехода к многопоточной репликации:
STOP SLAVE SQL_THREAD;SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';SET GLOBAL slave_parallel_workers=8;START SLAVE SQL_THREAD;
После модификации это показано на следующем рисунке:
Как показано на рисунке выше, в моей среде уже используется многопоточная репликация, поэтому корень проблемы не в том, следует ли включить многопоточную репликацию. Но когда я использую show processlist для просмотра состояния репликации, в большинстве случаев я обнаруживаю, что выполняется только 1 поток SQL, как показано на следующем рисунке:
Из приведенного выше рисунка видно, что в основном выполняется один поток, поэтому можно предварительно определить, что мощь многопоточности была проявлена не очень хорошо.Чтобы объяснить проблему более убедительно, найдите способ подсчета соотношение, используемое каждым потоком синхронизации. Статистический метод заключается в следующем:
1. Включите статистику, связанную с подчиненной машиной онлайн (по умолчанию она отключена из соображений производительности).Метод открытия может быть следующим:
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_transactions%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME = 'transaction';
2. Создайте представление для просмотра использования каждого потока синхронизации.Код выглядит следующим образом:
USE test;
CREATE VIEW rep_thread_count AS SELECT a.THREAD_ID AS THREAD_ID,a.COUNT_STAR AS COUNT_STAR FROM performance_schema.events_transactions_summary_by_thread_by_event_name a WHERE a.THREAD_ID in (SELECT b.THREAD_ID FROM performance_schema.replication_applier_status_by_worker b);
3. Через некоторое время подсчитайте коэффициент использования каждого потока синхронизации.SQL выглядит следующим образом:
SELECT SUM(COUNT_STAR) FROMrep_thread_count INTO @total;
SELECT 100*(COUNT_STAR/@total) AS thread_usage FROMrep_thread_count;
Результат выглядит следующим образом:
Из приведенных выше результатов видно, что в большинстве случаев выполняется один поток.При отслеживании сценария, в котором импортируется большой объем данных, определенно могут возникать узкие места. Если вы можете улучшить способность каждого потока выполняться одновременно, это может улучшить ситуацию с задержкой синхронизации, как ее решить?
групповое представление
Мы могли бы также исходить из принципа многопоточной синхронизации.В 5.7 функция многопоточной репликации была значительно улучшена, и поддерживается метод LOGICAL_CLOCK.В этом методе несколько параллельно выполняемых транзакций могут совершаться одновременно ., это означает, что между потоками нет конфликта блокировок, тогда мастер может пометить эту группу транзакций и безопасно выполнить их одновременно на подчиненной машине. Следовательно, все потоки могут быть отправлены одновременно, насколько это возможно, что может значительно улучшить параллелизм выполнения ведомой машины, тем самым уменьшив задержку ведомой машины.
С этой гипотезой естественно думать о человеческом контроле, насколько это возможно, чтобы все потоки отправлялись одновременно.На самом деле, чиновник предоставил нам аналогичные параметры.Параметры следующие:
binlog_group_commit_sync_delay
#Описание параметра см.:Dev.MySQL.com/doc/Furious/…
Примечание. Этот параметр задерживает ответ SQL, и среда, которая очень чувствительна к задержке, требует особого внимания, единицей измерения являются микросекунды.
binlog_group_commit_sync_no_delay_count
#Описание параметра см.:Dev.MySQL.com/doc/Furious/…
Примечания: Этот параметр имеет определенный защитный эффект.При достижении значения, установленного binlog_group_commit_sync_no_delay_count, он будет отправлен немедленно, независимо от того, достигнут ли порог, установленный binlog_group_commit_sync_delay.
Поскольку это отслеживаемая БД, в основном загружаются данные, а затем отображаются, задержка импорта около 1 секунды не влияет на бизнес, поэтому настройте два параметра следующим образом:
SET GLOBAL binlog_group_commit_sync_delay = 1000000;
SET GLOBAL binlog_group_commit_sync_no_delay_count = 20;
#Обратите внимание: эти два параметра следует настроить в соответствии с характеристиками бизнеса, чтобы избежать онлайн-сбоев.
Чтобы предотвратить накопление импортированного SQL, установите для SET GLOBAL binlog_group_commit_sync_no_delay_count значение 20 и зафиксируйте, когда будет достигнуто 20 транзакций, независимо от того, достигает ли оно 1 секунды. Уменьшите влияние на бизнес.
После установки этих двух параметров обнаруживается, что одновременная репликация значительно улучшилась за одно мгновение, и во многих случаях 8 потоков могут работать на полную мощность. Так что регулируйте нить на 16. После запуска периода событий снова подсчитывается коэффициент использования каждого потока синхронизации, и обнаруживается, что параллелизм значительно улучшился.Новый коэффициент показан на следующем рисунке:
При просмотре статуса подчиненного устройства обнаруживается, что задержка подчиненного устройства становится все меньше и меньше, и оно полностью догнало и стабильно работает в течение недели.
Итоги обзора
Наконец, краткое содержание:
Столкнувшись с проблемой задержки мастер-раб, вы можете открыть свой разум из следующих мест, искать ключи, найти основную причину проблемы и прописать правильное лекарство, от лекарства до болезни. не ограничиваясь следующими аспектами:
сеть
представление
Конфигурация (оптимизация параметров)
большой бизнес
Замок
Многопоточная репликация
групповое представление
Я надеюсь, что благодаря приведенному выше обзору всего устранения неполадок большинство администраторов баз данных, столкнувшихся с аналогичными проблемами задержки репликации, могут быть полностью прекращены.