Настройка балансировки SQL-запросов через ProxySQL для Bitrix

Исходная проблема, из-за которой я пришёл к ProxySQL, состояла в том, что на одном из проектов, где используется старый Bitrix (установлен на RHEL 6), модуль “веб-кластер” с багами. Через него была настроена репликация Master-Slave, и периодически слейвы переставали видеться через модуль и все запросы шли на master-базу, тем самым загружая основной сервер с БД, что приводило к диким cpu iowait и как следствие – к недоступности порталов проекта. В силу того, что обновить кастомный битрикс возможности нет, как и времени на допиливание разработчиками модуля для устранения ошибок, было решено попробовать использовать стороннее решение для организации балансировки SQL-запросов для схемы Master-Slave.

В теории, изначально можно было бы использовать Nginx и TCP-балансировку, но в таком случае Nginx не воспринимал бы запросы SQL как таковые, т.к. он не умеет различать MySQL, а потому разделить их на те, которые адресованы для Slave, а которые для Master, нормальной возможности не было, используя Nginx.

Изучив инструменты, которые могли бы подойти для решения задачи, я остановился на ProxySQL, т.к. по отзывам и документации он самый стабильный и производительный.

Ахтунг!

То, как ниже описана примитивная реализация проксирования запросов до Master и Slave совсем не true-way, а костыли, но такой вариант тоже имеет право на существование при наличии всяческих обстоятельств.

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

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

Теория

ProxySQL – ПО с открытым исходным кодом (GPL license) для проксирования SQL-запросов. Представляет собой высокопроизводительный инструмент, который можно применять для HA.

Поддерживает MySQL и его форки (Percona, Maria & etc).

Развитие ProxySQL обусловлено отсутствием прокси с открытым исходным кодом, которые обеспечивают высокую производительность.

Архитектура

Имеет весьма сложную, но простую в использовании систему конфигурации.

ProxySQL:

  • умеет обновлять динамически конфиги, что полезно для HA и нулевого времени простоя;
  • поддерживает возможность изменения элементов конфигурации, не требуя рестарта процесса ProxySQL;
  • позволяет производить легкий откат конфигов.

Официальная схема взаимодействия уровней (слоёв):

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

RUNTIME

Представляет структуры данных ProxySQL в памяти, содержит всю информацию для проксирования запросов

MEMORY (main)

Является базой данных в памяти, доступ к которой осуществляется через MySQL-клиент.

База содержит следующие таблицы:

  • mysql_servers – список внутренних серверов, к которым подключается ProxySQL;
  • mysql_users – список пользователей и их учетные данные, которые подключаются к ProxySQL и внутренним серверам;
  • mysql_query_rules – список правил запросов, которые анализируются при маршрутизации трафика;
  • global_variables – список глобальных переменных. Полезно для конфигурирования во время выполнения;
  • mysql_collations – список сопоставлений MySQL, доступных для работы с прокси. Извлекаются из клиентской библиотеки.

DISK

Представляет собой базу данных SQLite3 на диске. Располагается в $(DATADIR)/proxysql.db.

При перезапуске сервиса ProxySQL, все конфиги, которые правились в Memory, будут очищены. Поэтому очень важно сохранять все конфигурации в слой DISK, откуда уже идёт загрузка в MEMORY.

CONFIG FILE

Является классическим инструментом конфигурации.

Запуск

При обычном запуске, ProxySQL ищет свой конфигурационный файл, в котором определяется рабочая директория. Что произойдёт далее, зависит от наличия или отсутствия файла БД (DISK) в директории, которая указана в конфиге.

Если файл с БД есть, то происходит инициализация конфгируации из постоянной БД на диске, т.е. диск загружается в память и распространяется на конфигурация во время выполнения

Если файла с БД нет, то он генерится на основе конфига и загружается в MEMORY и RUNTIME.

Установка

Для установки добавить репозиторий, заменив в baseurl …/centos/$releasever на нужную цифру, в данном случае RHEL 6, т.к. иначе выдаст 404 ошибку:

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/6
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

Во избежание ошибки Unable to parse unknown SET query я установил версию ProxySQL version 2.0.5-37-gc8e32ee, т.к. при выполнении некоторых запросов после настройки были глюки. Тема обсуждалась тут.

Проверить, что успешно запустился:

# service proxysql start
Starting ProxySQL: 2019-11-08 10:09:02 [INFO] Using config file /etc/proxysql.cnf
2019-11-08 10:09:02 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!

# service proxysql status
ProxySQL is running (10100).

Для управления через консольного клиента mysql нужно его установить:

yum install mysql

И проверить, что само подключение устаналивается:

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

Командой ниже можно проверить список имеющихся таблиц. В данном случае это уровень абстракции MEMORY, т.е. БД в памяти.

mysql> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_aws_aurora_hostgroups                |
| mysql_collations                           |
| mysql_galera_hostgroups                    |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_aws_aurora_hostgroups        |
| runtime_mysql_galera_hostgroups            |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
24 rows in set (0.00 sec)

Отсюда происходит перемещение конфигурации между уровнями посредством различных команд (написаны ниже). Цифра в начале команды для наглядности соответствует абстрактному уровню, представленному в официальной схеме взаимодействия (в начале статьи в разделе Архитектура):

  • [1] LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
    • загружает пользователей MySQL из БД в памяти в структуры данных RUNTIME или наоборот
  • [2] SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
    • сохраняет пользователей MySQL из RUNTIME в MEMORY
  • [3] LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
    • загружает постоянных пользователей MySQL из базы данных на диске в базу данных в памяти
  • [4] SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
    • сохраняет пользователей MySQL из базы данных в памяти в базу данных на диске
  • [5] LOAD MYSQL USERS FROM CONFIG
    • загружает из файла конфигурации пользователей в базу данных в памяти

Конфигурирование

Подготовка

На этом хватит теории и для полного понимания и закрепления пора переходить к практике. Исходные данные для понимания:

10.15.61.141 – ProxySQL & App
10.126.253.45 – SLAVE
10.126.253.46 – MASTER

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

Перед началом удобнее сразу создать двух пользователей на master и slave базах для доступа с сервера ProxySQL:

  • пользователь monitor , предварительно убедившись, что учетка monitor уже не используется для мониторинга самих слейвов. Через неё ProxySQL будет делать healthcheck:
GRANT REPLICATION SLAVE ON . TO 'monitor'@'10.15.61.141' IDENTIFIED BY 'monitor_pass';
  • пользователя, через которого подключается приложение к БД. В данном случае, т.к. ProxySQL находится на веб-ноде, где и само приложение, пользователь app_user уже должен быть с нужными правами:
GRANT ALL PRIVILEGES ON . TO 'app_user'@'10.15.61.141' identified by 'app_user_pass';

И ещё важный момент. ProxySQL в дальнейшем должен понимать, в какую группу отнести сервера: для чтения или для записи. Изначально я подумал, что это назначается вручную, но это была ошибка. ProxySQL, используя учётку monitoring, подключается к backend и смотрит значение переменной read_only в самом MySQL. Если значение 1, то запись в БД могут вносить только пользователи с привилегиями SUPER. Таким образом, можно быть уверенным, что на слейв придут запросы на изменение только со стороны мастера (если нет иных юзеров с правами SUPER). Этакий best practice. Поэтому для слейва нужно изменить значение этой переменной глобально и потом добавить его в конфиг:

mysql> 

SET @@GLOBAL.read_only=1;
my.cnf:

GLOBAL.read_only=1;

Добавление backend-серверов

Далее нужно залогиниться в консоль управления ProxySQL и добавить backend-сервера БД в нужный hostgroup:

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'10.126.253.46',3306); # master
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'10.126.253.45',3306); # slave

Да, здесь не опечатка, изначально и мастер и слейв сервера добавляются с одним hostgroup_id. Можно выбрать следующие поля (остальные пока не интересуют) и проверить:

SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname      | port | status |
+--------------+---------------+------+--------+
| 10           | 10.126.253.46 | 3306 | ONLINE |
| 10           | 10.126.253.45 | 3306 | ONLINE |
+--------------+---------------+------+--------+

Сохранение серверов:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Настройка мониторинга

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

Логин:

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

Пароль:

UPDATE global_variables SET variable_value='monitor_pass' WHERE variable_name='mysql-monitor_password';

Чтобы эти значения подгрузились, нужно внести их в рантайм и на диск:

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Теперь если выполнить команду ниже, можно увидеть, что ProxySQL успешно “пингует” бэкенды и в поле connect_error значение NULL – значит всё ок:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 10.126.253.45 | 3306 | 1573802777382753 | 1222                    | NULL          |
| 10.126.253.46 | 3306 | 1573802776718565 | 1490                    | NULL          |

Настройка таблицы для распределения запросов

Теперь нужно поработать с этой таблицей, которая пока пустая:

SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

С её помощью, перечисленные в ней hostgroups могут быть настроены для для направления запросов до мастера или до слейва, как раз-таки на основе значения read_only.

INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) VALUES (10,20,'production'); 

В строке выше была добавлена запись с writer_hostgroup (10) и reader_hostgroup (20). В последней пока нет узлов – они туда будут помещены автоматически (благодаря проверке read_only).

Если у сервера read_only = 0, он будет перемещен в группу хостов 10, т.к. это Master
Если у сервера read_only = 1, он будет перемещен в группу хостов 20, т.к. это Slave

И для применения этого осталось выполнить загрузку в рантайм:

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Теперь если выполнить команду ниже, видно, что в таблице сменился hostgroup_id на нужный:

SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname      | port | status |
+--------------+---------------+------+--------+
| 10           | 10.126.253.46 | 3306 | ONLINE |
| 20           | 10.126.253.45 | 3306 | ONLINE |
+--------------+---------------+------+--------+

Для проверки, что всё сохранилось и загружается, я делал рестарт сервиса ProxySQL и смотрел, не изменились ли записи в таблицах. Если нет, то всё ок, можно настраивать далее.

Настройка пользователей

Осталось добавить пользователя app_user, который будет подключаться к прокси, а тот уже, в свою очередь, обращаться в нужную базу.

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app_user','app_user_pass','10');

Стоит обратить внимание, что пользователю указывается default_hostgroup – группа, в которой мастер сервер.

Применение:

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Проверка, что пользователь добавлен:

SELECT username,password,active,default_hostgroup,default_schema,max_connections,max_connections FROM mysql_users;

И проверить, что можно подключиться (консольным клиентом с сервера приложений):

mysql -u app_user -p'app_user' -h 127.0.0.1 -P6033 -e "SELECT @@server_id"

+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

А также последняя проверка ещё раз, что ProxySQL может конектиться к бэку:

 select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;

На этом всё готово и можно переходить к самой сложной и важной части – настройки проксирования запросов.

Настройка правил запросов

Осталось настроить таблицу, в которой содержатся правила, на основе которых запросы будут отправляться в ту или иную группу:

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (1,1,'user_app','^SELECT.*FOR UPDATE$',10,1),(2,1,'user_app','^.*b_sec_session.*',20,1),(3,1,'user_app','^SELECT',20,1);

Проверка:

SELECT rule_id,active, username, match_digest, destination_hostgroup, apply, multiplex FROM mysql_query_rules;
+---------+--------+----------+----------------------+-----------------------+-------+-----------+
| rule_id | active | username | match_digest         | destination_hostgroup | apply | multiplex |
+---------+--------+----------+----------------------+-----------------------+-------+-----------+
| 1       | 1      | trp3     | ^SELECT.*FOR UPDATE$ | 10                    | 1     | NULL      |
| 2       | 1      | trp3     | ^.*b_sec_session.*   | 10                    | 1     | NULL      |
| 3       | 1      | trp3     | ^SELECT              | 20                    | 1     | NULL      |
+---------+--------+----------+----------------------+-----------------------+-------+-----------+

P.S. В оф. документации не указан пользователь (поле username) при наполнении таблицы mysql_query_rules. Без этого поля (без добавления оно будет NULL) у меня возникали непонятные ошибки и глюки.

Проверка, что по созданным правилам запросов выше есть хиты, т.е. всё работает:

SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules;

Сохранение:

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Теперь всё минимально настроено и в настройках приложения можно переключаться на инстанс ProxySQL, который слушает локально на том же сервере, где и само приложение на порту 6033 – 127.0.0.1:6033

После открытия портала, побегав по страницам, можно выполнить запрос и увидеть, что поле Queries растёт в нужной hostgroup, а значит всё ок:

select hostgroup,srv_host,status,Queries,Bytes_data_sent,Latency_us from stats_mysql_connection_pool where hostgroup in (20,10);
+-----------+---------------+--------+---------+-----------------+------------+
| hostgroup | srv_host      | status | Queries | Bytes_data_sent | Latency_us |
+-----------+---------------+--------+---------+-----------------+------------+
| 10        | 10.126.253.46 | ONLINE | 42256   | 4810810         | 311        |
| 20        | 10.126.253.45 | ONLINE | 12276   | 3022946         | 416        |
+-----------+---------------+--------+---------+-----------------+------------+

В общем и целом на этом настройка закончена. В админке битрикса лучше заранее перед настройкой ProxySQL выпилить старые настройки слейвов в модуле “Веб-кластер”, а также перезапустить сервис кеширования (memcached в моём случае), если таковой используется.

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

Возникающие ошибки:

В процессе настройки приложение себя вело странным образом, выдавая те или иные ошибки. При решении всегда смотреть в /var/lib/proxysql/proxysql.log

  • Unable to get session lock within 60 seconds (в браузере)
  • ERROR 2013 (HY000): Lost connection to MySQL server during query (в proxysql.log)

Данные ошибки были связаны с некорректными таймаутами в настройках серверов MySQL, на мастере и слейве в my.cnf:

net_read_timeout=600
interactive_timeout=600
wait_timeout=600
connect_timeout=600

wait_timeout – таймаут ожидания необходим, чтобы защищать приложением в том случае, когда клиенты ничего не делают, кроме как поглощают соединение. По истечении 600 сек бездействующих клиентов отключает по таймауту;

interactive_timeout – используется только для соединений с интерактивными клиентами, такими как клиенты MySQL из командной строки, с этим вроде как понятно. Обычно значение устанавливается как и wait_timeout , но в отдельных случаях может быть изменено.

net_read_timeout – количество секунд ожидания для получения дополнительных данных из соединения перед прекращением чтения. Аналогично и для клиентов, ведущих запись.

connection_timeout – сколько ждать ответа сервера перед тем, как выдать ошибку о том, что сервер не отвечает. Именно с данным параметром, значение которого увеличил с 30 до 600, ошибка Unable to get session lock within 60 seconds перестала появляться.

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

Используемые источники

Ваш комментарий будет первым

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *