Оптимизация репликации в Mysql

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

Мониторинг

После настройки репликации следует постоянно следить за несколькими параметрами на слейве:

mysql -e "SHOW SLAVE STATUSG"

# Запрос вернет статистику и настройки слейва

Стоит обратить внимание на такие показатели:

Slave_IO_State: Waiting for master to send event
                 ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 ...
                   Last_Errno: 0
                   Last_Error: 
                 ...
        Seconds_Behind_Master: 0
                 ...

# Статус работы реплики

Показатели Slave_IO_Running и Slave_SQL_Running отражают нормальную работу реплики. Оба должны иметь значение Yes. Когда один из этих показателей равен No, в Last_Error будет виден текст ошибки репликации.

Параметр Seconds_Behind_Master отражает количество секунд, на которое слейв отстает от мастера. Этот показатель должен быть равен нулю (иногда может вырастать до нескольких секунд).

Отключение sync_binlog

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

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

Проверить текущее значение можно так:

mysql -e "show variables like 'sync_binlog'"

# Проверка режима синхронизации бинлога

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+

# Синхронизацию лучше отключить

Отключить синхронизацию можно без перезагрузки сервера, для этого достаточно выполнить:
SET GLOBAL sync_binlog = 0;

Однако не забудьте исправить этот параметр и в my.cnf, чтобы он сохранился после перезагрузки:

...
[mysqld]
sync_binlog = 0
...

В io мы всегда отключаем синхронизацию бинлога. Это увеличило пропускную способность наших Mysql узлов в 2…3 раза, разгрузив дисковые подсистемы мастеров.

Многопоточная репликация

До недавнего времени Mysql реплика работала всего в один поток. Тогда, даже если мастер и слейв идентичны по характеристикам, слейв все равно может отставать от мастера.

В версии Mysql 5.6 внедрена поддержка параллельной репликации. Она позволяет слейву обрабатывать бинлог параллельно в несколько потоков. Такой режим включается настройкой в my.cnf на слейве:

...
[mysqld]
slave-parallel-workers = 2
...

# Включение репликации в 2 потока

Число задает количество потоков и может принимать значения от 2 до 1024. Значение 0 отключит многопоточную обработку бинлога.

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

Понятно, что если у вас всего одна база данных, вы не получите прирост в производительности. Зато в версии Mysql 5.7 можно изменить тип распределения операций с помощью настройки в my.cnf:

...
[mysqld]
slave-parallel-workers = 2
...

# Изменения типа параллелизации обработки бинлога

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

TL;DR

• Мониторьте Seconds_Behind_Master на слейве, он должен быть равен нулю.
• Отключайте синхронизацию бинлога на мастере: sync_binlog = 0.
• Используйте параллельную обработку бинлога на слейве: slave-parallel-workers = 2 (или другое количество потоков) в новых версиях Mysql.