Исходная проблема, из-за которой я пришёл к 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=600wait_timeout – таймаут ожидания необходим, чтобы защищать приложением в том случае, когда клиенты ничего не делают, кроме как поглощают соединение. По истечении 600 сек бездействующих клиентов отключает по таймауту;
interactive_timeout – используется только для соединений с интерактивными клиентами, такими как клиенты MySQL из командной строки, с этим вроде как понятно. Обычно значение устанавливается как и wait_timeout , но в отдельных случаях может быть изменено.
net_read_timeout – количество секунд ожидания для получения дополнительных данных из соединения перед прекращением чтения. Аналогично и для клиентов, ведущих запись.
connection_timeout – сколько ждать ответа сервера перед тем, как выдать ошибку о том, что сервер не отвечает. Именно с данным параметром, значение которого увеличил с 30 до 600, ошибка Unable to get session lock within 60 seconds перестала появляться.
Вышеописанные значения были использованы для тестирования, при таких настройках ошибок не возникало, но к вопросу настройки таймаутов лучше подойти более тонко, т.к. от этого сильно зависит поведение и работоспособность приложения.