compiling.ru

Разделение запросов к MySQL на r/o и r/w сервера.

(Mysql-proxy with read-write splitting.)

Появилась задача реализовать load balancing и failover для базы mysql.
Схема задумана следующая — два сервера с репликацией master-master, и у каждого master’a по одному slave’у — read-only. Поток запросов должен грамотно распределять mysql-proxy (http://forge.mysql.com/wiki/MySQL_Proxy).

См. рисунок:

Собственно настройка.
Для реализации модели использовалось два сервера, по два mysqld на каждом. Порты взяты 3307 для master’ов и 3308 для slave’ов.
Для каждого нода был создан отдельный конфиг my.cnf, ключевые параметры для удачного старта базы — индивидуальные параметры секции [mysqld]:
port = 3307
socket = /tmp/mysql.sock.3307
basedir=/usr/local/mysql
datadir=/usr/local/mysql/var.3307

Сам запуск сводится к непосредственному -
mysqld_safe --defaults-file=/etc/my.cnf.3307
Написание мегаудобных stop-start скриптов оставил на этап пред-продакшен внедрения.

Сама репликация — строго по манулалу добавил в конфиг:
master-host = host1
master-user = ula
master-password = secret
master-port = 3307
для мастера, и аналогичная для slave’a. Меняются только порты и хосты. Да, ula — это Юля, наша новая секретарша. Пусть будет.
Slave’ам так же задал непоколебимые роли read_only прямо в конфиге, и всем прописал bin-логи:
log-bin=mysql-bin
log-slave-updates
Последнее нужно для ведения лога обновлений всеми slave’ами. Конкретно мне это нужно, чтобы обновления полученные master’ом 1 с master 2 были так же записанные в лог и, как и собственные обновления, в дальнейшем переданы на зависимый slave.
Ну и конечно, прописал всем собственные значения increment’а и offset, чтобы не было путанницы.
auto_increment_increment=10
auto_increment_offset=3
Точнее, инкремент у всех 10, а оффсет у каждого свой, от 1 до 4.
И, конечно, у каждого нода свой server-id.
В случае, если потребуется сбросить slave/master – информацию также легко найти:
reset master;
change master to Master_Log_File='mysql-bin.000001', Master_Log_Pos=4;
для себя памятку я тоже сделал.

Приступаем к установке mysql-proxy.
Именно установка проблем не вызывает — качается дистрибутив с официальной страницы (http://dev.mysql.com/downloads/mysql-proxy/). (Мне под Solaris10 intel, 64-битную версию правда не на криэйтили, без зазрения совести скачал 32 bit. Для теста пойдёт.)
Архив распаковывается, и для порядка перемещается в /usr/local под кодовым названием ./mysql-proxy, рядом с обычным ./mysql.
Далее читаем много документации, конференции MySQL Forums — http://forums.mysql.com/list.php?146 в том числе, спасибо конечно разработчикам и сочувствующим – Diego Medina, Jan Kneschke, Giuseppe Maxia.
И приходим к выводу, что разделением запросов для read-only и read-write backend’ов по замыслу разработчиков должен заниматься скрипт rw-splitting.lua.
У самого бинарика mysql-proxy есть довольно неплохой хелп (—help-all), внимательное изучение которого, тем не менее, вызывает пару вопросов к функционированию разработки.
По совету опытных товарищей, в том числе калифорнийца Grig Gheorghiu (http://agiletesting.blogspot.com/2009/04/mysql-load-balancing-and-read-write.html) создаём скрипт запуска:

#!/bin/bash

MASTERDB01=host1
MASTERDB02=host2
SLAVEDB01=host1
SLAVEDB02=host2
ROOT_DIR=/usr/local

LUA_PATH=»$ROOT_DIR/mysql-proxy/lib/mysql-proxy/lua/?.lua» $ROOT_DIR/mysql-proxy/libexec/mysql-proxy \
—daemon \
—proxy-backend-addresses=$MASTERDB01:3307 \
—proxy-backend-addresses=$MASTERDB02:3307 \
—proxy-read-only-backend-addresses=$SLAVEDB01:3308 \
—proxy-read-only-backend-addresses=$SLAVEDB02:3308 \
—proxy-lua-script=$ROOT_DIR/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua \
—admin-lua-script=$ROOT_DIR/mysql-proxy/share/doc/mysql-proxy/admin.lua \
—proxy-address=host_frontend:3306 \
—admin-password=secret \
—log-level=error \
—keepalive \
—log-file=/proxy.log

Особо ценной является рекомендация установки директивы LUA_PATH и
«указания директории, содержащей Lua скрипты»
- как пишет Grig. –
» Если вы не сделаете этого, rw-splitting.lua скрипт не будет найден, и вы не будете знать об этом, пока не обратитесь к mysql-proxy. Вы увидите ошибки о том, что скрипт не найден. Заметьте, что LUA_PATH находится в той же команде, что вызывает и бинарик mysql-poxy.»

Собственно, параметры описаны в справке, значения параметров тоже тривиальны.
Обращаю внимание, на то, что можно задать два типа lua-скриптов, для административной части и для проксирования. Гибко настраивается логирование и данные аутенфикации административного модуля.

Тестирование.
Но, при первом тестировании наступает первое разочарование.
На одном из хостов-клиентов, запускаем скрипт тестирования, выполняющий последовательно 10 запросов с интервалом в 3 секунды к mysql-proxy:

#!/bin/bash

for ((n=1; n <= 10; n++)) ; do
sleep 3
echo ‘use dbname; select * from table_name;’ | mysql -u dbadev -p -h host_frontend —password=’secret’ &
done ;

Практика показала, что клиенту возвращается менее 50% ответов на запросы, с сообщением в логе mysql-proxy:

2009-06-29 10:16:19: (critical) proxy-plugin.c:258: read_query_result() in /usr/local/mysql-
proxy/share/doc/mysql-proxy/rw-splitting.lua tries to modify the resultset, but hasn't asked
to buffer it in proxy.query:append(..., { resultset_is_needed = true }). We ignore the chan
ge to the result-set.

Соответственно, у клиента ошибки:
ERROR 2006 (HY000) at line 1: MySQL server has gone away
или
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query.

Путём хитрой доработки rw-splitting.lua (спасибо Сергею Печенюку (http://1programmer.ru/)):
вместо
proxy.queries:append(1, packet)
поставить
proxy.queries:append(1, packet, { resultset_is_needed = true })

Заметные улучшения налицо. Впрочем, если покопать, можно найти замечательный diff от Diego Medina (https://code.launchpad.net/~diego-fmpwizard/mysql-proxy/bug-43424/+merge/4259), который пока не был закоммичен в официальный репозитарий. Исправленный rw-splitting можно посмотреть здесь.

Даже в более суровых условиях (100 пакетов без задержек), клиент уже не получает ошибок, и возвращается стабильно 100 ответов из 100 запросов.
Но, мы пойдём дальше и проверим нагрузочную способность пакетом из 1000 запросов:

#!/bin/bash

for ((n=1; n <= 1000; n++)) ; do
echo ‘use dbname; select * from table_name;’ | mysql -u dbadev -p -h host_frontend —password=’secret’ &
done ;

Очередное разочарование, появились потери запросов. В серии из 10 испытаний по 1000 запросов, в среднем было потеряно 6 запросов из 1000.
На одновременной выборке из 10 000 запросов, потери составили около 0,5-1,5% (время выполнения ~75 секунд).

Сам mysql-proxy комментирует потери ошибкой вида:
(critical) ioctl(37, FIONREAD, ...) failed: Connection reset by peer

Что немедленно натолкнуло на размышления об ограничении количества подключений к базе данных. Как следствие, параметр max_connections для всех нодов был увеличен до 1000.
Ошибка исчезла, но потери пакетов остались.
Кстати, особенно заметны потери сразу после рестарта mysql-proxy. После последовательных выполнений нескольких ёмких тестов, потери сводятся к минимальным.

Хорошо всё это или плохо, можно ориентироваться исходя из ожидаемого количества и частоты запросов. Очевидно, что для частоты в 100 запросов за 0,8 секунд проблем не возникает.

Непосредственно с функцией прокси-сервера проект вполне справляется. Все backend’ы загружены примерно равномерно.

Ну и напоследок, существует ещё одна особенность у mysql-proxy.
В случае, если не доступен ни один rw-backend (master), ответы на запросы не возвращаются, в том числе с работоспособных slave’ов.
Разработчики mysql-proxy признают, что проект ещё далёк от релиза, в частности Diego Medina
советует (http://forums.mysql.com/read.php?146,265193,265543#msg-265543) попробовать доработать lua-скрипты под собственные задачи.

P.S. Конечно же стоит упомянуть русскоязычное руководство по mysql-proxy http://boombick.org/blog/posts/34.

dan1005
(с) compiling.ru 2009

Popularity: 10%

Похожие посты: