Распределение запросов MySQL

Question

[serzhb]

Здравствуйте!
Ситуация такая. Есть два сервера с MySQL, настроена репликация, один сервер — мастер, другой соответственно — слейв. Каким образом можно сделать так, чтобы запросы на изменение базы (INSERT, UPDATE, DELETE и т.д.) шли на мастер, а запросы на чтение (SELECT) на слейв. Может есть какая-нибудь настройка в mysql или стороннее решение?

Сам проект сделан на PHP и мы заранее не продумали этот вопрос. Вызов запросов идёт через обычные mysql_query.
Заранее спасибо!

Comments

[korvindest]

А вы не боитесь что при SELECT со слейва вы получите уже не актуальные данные?

Answer 1

[edogs]

Стандартное решение в том, что бы иметь тупо 2 разных коннекта.
Один идет на мастер, второй на слейв. Изменение на первый, чтение со второго.
Это самое естественное что может быть.

ну или www.linuxvirtualserver.org/, но это уже немного о другом.

Comments

[edogs]

Что касается того, что вызов запросов идет через обычный mysql_query. Даже если Вы не используете никакого доп.класса для этого, тупо
str_replace (mysql_query => mysql_query2)
а в mysql_query2 определяете первое слово — (селект/инсерт) и в зависимости от него выбираете сервер куда пихать запрос.

[serzhb]

Спасибо наверное так и сделаю. На самом деле это первое, что пришло в голову. Просто думал есть более красивое решение :)

[kozyabozya]

Я работал в одном проекте, было примерно так и сделано: в классе базы был простой анализатор запросов и выбор сервера.

Но рекомендую сразу сделать функцию в таком виде:
function mysql_query2( $query = '', $server = DB_SLAVE )

Т.к. некоторые селекты могут быть нужны все-таки с Master базы, тогда делаете
mysql_query2( 'select * from users limit 1', DB_MASTER );

Answer 2

[Виталий Перетятько]

Есть такая штука, как MySQL proxy, которая может прозрачно для клиента раскидывать соединения на мастер и слейв (естетсвенно с ограничениями) читат тут — forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting
жаль что пока не production ready.

по хорошему — переписать все обращения к базе через свой класс (в простейшем случае просто поиском-заменой по исходникам) и в нем анализировать — чтение или запись идет.

Comments

[serzhb]

Да, жаль, что там ещё только alpha версия, хотя почитаю ещё отзывы. Спасибо!

Answer 3

[edogs]

По поводу хетзнера и серверов. Обдумайте вариант взять www.hetzner.de/hosting/produkte_rootserver/ex5, конечно разоритесь на сетап, но 24Гб памяти вместо 8Гб решает в случае мускула достаточно сильно за счет кэша, особенно если у Вас много чтения.
Более того, иногда есть смысл повесить слэйв на сервер, где все таблицы находятся на ram диске. Для слейва рам не помеха, а вот для скорости чтение из оперативки это Вам не чтение с бытового винта, пусть даже на 7200.

Answer 4

[Tonik]

Если вам в целях HA, то есть несколько вариантов. И однозначно хорошего среди них нет — нужно смотреть по обстоятельствам. Опять же много зависит, от того какая нужная надежность.

Если нужно очень очень надежно и малейшая потеря данных недопустима, то придется платить производительностью в любом случае. Обычная, асинхронная репликация вам не подойдет, так как не будет 100% уверенности, что данные успели уйти на слейв.

Можно использовать DRDB + Heartbeat
Надежно, но пенальти на запись. И сеть между серверами должна быть хорошей.
Для FreeBSD можно нечто подобное соорудить при помощи репликации на уровне ZFS.
В 5.5 появилась репликация с подтверждением записи данных, хотя бы на один из слейвов.

Если кратковременный простой и небольшая потеря данных, допустимы (внимание, я не предлагаю плевать на юзерские данные! Но одно дело банк и финансы, другое — потерять два три голоса за авотарочку...), то асинхронная репликация, вполне хорошее решение.
Однако, что то (например nagios) должен мониторить SHOW SLAVE STATUS, что бы вовремя заметить если репликация слетела.

Переключаться на слейв можно либо на уровне конфига вашего приложения. Либо используя виртуальный IP, котроый в случаеумирания мастера быстро перекидывается на живой слейв.

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

Это все мое имхо…

Answer 5

[serzhb]

2korvindest, вопрос репликации для меня тема не очень знакомая, но знаю, что многие работают именно по такому принципу. Записывают на мастер, читают со слейва. Как мне кажется процесс репликации с сервера на сервер происходит мгновенно, если это не какой-то тяжелый запрос, конечно.

Дело в том, что я не вижу другого достойного решения для масштабирования mysql. Если кто-то подскажет, что-то лучше я буду только рад.

Comments

[Tonik]

Насчет мгновенно, это не всегда верно. См dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-4-4-1-3 И это параметр далеко не всегда = 0.
Делать SELECT со слейва это тиричный патерн, но у вас все же должен быть какой то механиз проверки, что слейв отстает на допустимое время?

А вообще, вы точно уверены что вам пора масшатбироваться на два сервера? Мой опыт показывает, что mysql можно неплохо отюнить и дать себе еще месяц другой передышки, за которые допилить код. Пиши в личку, если что, подскажу может что.

В целом — иметь два разных конекта на уровне кода это правильный путь.

[serzhb]

Репликация должна быть быстрой, так как сервера находятся в одной локальной сети, пинг 1-2 мс.
То что нужно два сервера, однозначно. Хотя бы из соображений отказоустойчивости, чтобы если что перевести все на один из двух оставшихся.
По поводу оптимизации, мы изначально старались делать логику приложения, чтобы по минимуму использовать ресурсы БД.
Плюс ко всему лишние данные удаляем, ставим индексы где это необходимо, оптимизируем таблицы.
Но нагрузку создает не только БД, но и apache, даже при том, что статику мы отдаем через nginx.
Можно отложить переход ещё на пару месяцев, но тогда уже все будет работать не пределе. Лучше сделать это заблаговременно.
Сейчас у нас БД на одном сервере, апаче на другом, удаленное соединение работает достаточно быстро, но все равно не так быстро как если бы база была локальная и это понятно. Мы уже проработали возможность балансить апач через nginx на два сервера, а вот с базой вопрос открытый.

Answer 6

[shagguboy]

а расскажите характеристики сервера, раз вам одного ящика не хватает

Comments

[serzhb]

Дело происходит на hetzner.de
Сначала взяли такой сервер:
www.hetzner.de/en/hosting/produkte_rootserver/x4

Его, кстати, неожиданно надолго хватило, но в конце-концов пошла хорошая нагрузка и мы решили заблаговременно добавить ещё один сервер, уже более мощный:
www.hetzner.de/hosting/produkte_rootserver/eq4

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

[shagguboy]

1) у меня дома на поиграться стоит мощнее того что вы называете сервером
2) нагрузка то какая*

[shagguboy]

новых серверов больше 4 вы не навтыкаете. там ускороение от новых серверов падает экспотенциально.

[edogs]

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

[Tonik]

возьмите сервер с SAS винтам. еще лучше с SSD. 8 гигов для СУБД это не серьезно. Воткните 16 или 32. Поверьте это будет сильно лучше, чем 4 сервера с 8ГБ :)
Если вы не гугль, с огромным количеством данных, то оттягивайте шардинг данных. Он усложняет разработку, а значит сделает ее дороже. scale up в виде памяти или винта пошустре, даст вам 4-6 месяцев форы. Потратьте их на то что бы сделать сервис более юзабельным, привлеките инвестора, напишите юнит тесты. Да имхо что угодно будет для сервиса полезней, чем попытка распределить СУБД на кучу серверов раньше времени :)
Вы уверены что конфиг MySQL идеален и вытягивает все из железа? А пробовали percona server? Он будет побыстрее mysql. Если не хватает производительности, не спасет ли вас HandlerSocket? А профилирование запросов и приложения чем делали? точно ли СУБД тормоизит?

Но конечно

[edogs]

Вы о чем вообще? О том что более крутое железо и настройки дают эффект? Естественно дают. Никто это не оспаривает, безусловно можно и нужно делать настройки и докупать памяти и ставить круче процессор — понятно что кластер на 386 вряд ли имеет глубокий смысл:)

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

[Tonik]

Я наверное не совсем туда ответил просто :)

но если затронули тему
> новых серверов больше 4 вы не навтыкаете. там ускороение от новых серверов падает
То я скорее соглашусь, с этим утверждение в рамках этого конкретного топика. Топикастер говорил просто о репликации все базы на слейв. И при таком сценарии, N слейвов НЕ дадут N кратный прирост производительности.

С тем что, базу можно вертикально расшардить по таблицам конечно спорить не будут. часто это даже хороший вариант. Но в вопросе речь была не об этом. Я же хотел сказать, что возможно топикастеру, на текущем этапе проекта это еще и не нужно…

[edogs]

В большей части мы с Вами согласимся, но все же… у ТС всего только 2-ой сервер намечается:) Имхо на нормальном проекте все же сервер баз данных должен быть физически отдельным. Та же оптимизация проще и независимость может пригодиться. Лично наше мнение (мы не будем настаивать на его правильности), что за одни и те же деньги — вместо 1 крутого всегда лучше взять 2 послабже и вынести БД на отдельный.

[Tonik]

Ну как бы не стороник, выискивать кто прав. С дискуссиях меня больше интересует узнать чтото новое :)

> Имхо на нормальном проекте все же сервер баз данных должен быть физически отдельным.
Наверное я и правда не очень ясно выразился. Это 110% поддерживаю. Какое то время апп сервер можно держать с СУБД на одном серваке, но как появится возможноть — лучше разнести.

Основная мысль которую я хотел донести, что очень часто лучше проапгрейдить сервер СУБД, чем делать шардинг, или хитрое дерево слейвов для чтения. Репликация в mysql ломается не так уж и сложно.
У одного большого сервер есть свои плюсы. Нет проблем рассинхронизации данных. Можно делать JOIN любых таблиц на уровне СУБД, а вытаскивать из 2-3 разных серверов и извращаться в коде приложения. Мониторить это дело проще, профилировать. Да куда не плюнь — плюсы сплошные :) А пока новый, мощный сервер дает нам фору времени — зарабатывать бабки. И на них… тадам! купить еще более мощный сервер! :) шутка.

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

[serzhb]

Спасибо за наводку на HandlerSocket и Percona Server, почитаю.
БД не тормозит, я нигде такого не сказал. Наоборот все пока летает.

Почему нужен ещё один сервер? Это не только прирост производительности, но и запасной сервер на случай если первый упадет (или наоборот), что является чуть ли не основной причиной. Сервис коммерческий, люди платят деньги, если он упадет я получу с десяток седых волос :) А так получиться гораздо быстрее восстановить работу ресурса в случае серьезной поломки на одном из серверов, независимо от скорости работы ребята из hetzner.

Скажу честно, не знаю выжимаю ли я все из своего железа. Скорее всего нет. Но стоит ли тратить время на возможные варианты оптимизации, если где-то на год (а может и больше) проблема решается покупкой одного сервера (за 49 евро/месяц), репликацией и небольшой настройкой nginx и apache? Это время как вы правильно заметили, можно потратить на те вещи, которые реально приносят деньги. Новые функции, повышение юзабили и т.д. :)

[Tonik]

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

Answer 7

[serzhb]

На начальном этапе не было смысла тратить лишние деньги на неоправданно мощный сервер. Кстати, первый сервер только спустя полгода начал показывать серьезную нагрузка. В пиковые моменты Load Average доходил до 7.0 и это при двух ядрах, хотя тормозов не было на самом сайте.

Comments

[serzhb]

Промахнулся :) Это ответ для shagguboy