11 «рецептов приготовления» MySQL в Битрикс24

в 12:59, , рубрики: amazon, mysql, Percona, SaaS, битрикс, Битрикс24, Блог компании 1С-Битрикс, Корпоративный портал, облако, системное администрирование, Социальный интранет, метки: , , , , , , , ,

11 «рецептов приготовления» MySQL в Битрикс24

Проектируя, разрабатывая и запуская наш новый большой проект — «Битрикс24», мы не только хотели сделать по-настоящему классный сервис для командной работы (к тому же еще и бесплатный — до 12 пользователей), но еще и собрать и накопить опыт по эксплуатации облачных веб-сервисов, «прокачать» свою компетенцию в разработке высоконагруженных отказоустойчивых проектов и — самое главное — поделиться этими знаниями как с нашими партнерами, так и со всеми веб-разработчиками, кому близка тема «хайлоада». :)

11 «рецептов приготовления» MySQL в Битрикс24 Конечно, в одной статье (и даже не в одной) невозможно описать универсальный «рецепт», который бы подошел абсолютно для всех проектов: для кого-то важнее производительность (иногда — даже в ущерб надежности), для кого-то — наоборот, отказоустойчивость превыше всего, где-то много маленьких таблиц, где-то — большой объем данных…

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

Начнем по порядку.

Мы не раз уже говорили и писали о том, что «Битрикс24» развернут в Амазоне. И раз мы так любим и активно используем различные облачные сервисы, первый же вопрос…

1. Почему не используем RDS?

Amazon Relational Database Service (Amazon RDS) — облачная база данных. Есть поддержка MS SQL, Oracle и — что было интересно нам — MySQL.

Долго присматривались, хотели использовать в своем проекте… В итоге отказались от этой идеи. Несколько ключевых причин:

  • Во-первых, система недостаточно гибкая и прозрачная. Например, вы не получаете полноценного root-доступа к базе. А это значит, что какие-то специфичные (конкретно для вашего проекта) настройки, возможно, не получится сделать.
  • Есть риск долгого даунтайма в случае какой-либо аварии. И практика показывает, что риск этот вполне реален. Да, чаще страдают те базы, которые расположены только в одной зоне, и для обеспечения отказоустойчивости можно использовать так называемые Multi-AZ DB Instances. Но — о них далее...
  • При использовании Multi-AZ DB Instances для базы постоянно пишется standby бэкап в другую AZ (Availability Zone), и в случае аварии в первой зоне происходит автоматическое переключение на другую. Но в этом случае один инстанс с базой стоит ровно в два раза дороже. При этом пользователь эффективно использует ресурсы только одной машины (в отличие от схемы с «мастер-мастер» репликацией, о которой мы недавно писали).

Все это не означает того, что RDS — плохой сервис, и его не надо никогда использовать. Это не так. Он не подошел конкретно для нас. И, возможно, для кого-то будет гораздо проще обеспечить масштабирование и отказоустойчивость именно средствами Амазона.

2. Master-Slave? Нет, Master-Master!

Стандартная схема репликации в MySQL «Master-Slave» давно и успешно применяется на многих проектах и решает несколько задач: масштабирование нагрузки (только на чтение) — перераспределение запросов (SELECT'ов) на слейвы, отказоустойчивость.

Но решает — не полностью.

1. Хочется масштабировать и запись.
2. Хочется иметь надежный failover и продолжать работу автоматически в случае каких-либо аварий (в master-slave в случае аварии на мастере нужно один из слейвов в ручном или полу-автоматическом режиме переключить в роль мастера).

Чтобы решить эти задачи, мы используем «мастер-мастер» репликацию. Не буду сейчас повторяться, этой технике у нас недавно был посвящен отдельный пост на Хабре.

3. MySQL? Нет, Percona Server!

Первые несколько месяцев (на прототипах, в процессе разработки, в начале закрытого бета-тестирования сервиса) мы работали на стандартном MySQL. И чем дольше работали, тем больше присматривались к различным форкам. Самими интересными, на наш взгляд, были Percona Server и MariaDB.

Выбрали мы в итоге Перкону — конечно, из-за похожего «перевернутого» логотипа. ;)

11 «рецептов приготовления» MySQL в Битрикс24

… и нескольких фич, которые оказались крайне важными для нас:

  • Percona Server оптимизирован для работы с медленными дисками. И это очень актуально для «облака» — диски там почти всегда традиционно медленнее (к сожалению), чем обычные диски в «железном» сервере. Проблему неплохо решает организация софтверного рейда (мы используем RAID-10), но и оптимизация со стороны серверного ПО — только в плюс.
  • Рестарт базы (при большом объеме данных) — дорогая долгая операция. В Перконе есть ряд фич, которые позволяют делать рестарт гораздо быстрее — Fast Shut-Down, Buffer Pool Pre-Load. Последняя позволяет сохранять состояние буфер-пула при рестарте и за счет этого получать «прогретую» базу сразу после старта (а не тратить на это долгие часы).
  • Множество счетчиков и расширенных отчетов (по пользователям, по таблицам, расширенный вывод SHOW ENGINE INNODB STATUS и т.п.), что очень помогает находить, например, самых «грузящих» систему клиентов.
  • XtraDB Storage Engine — обратно совместим со стандартным InnoDB, при этом гораздо лучше оптимизирован для хайлоад проектов.

Полный список можно посмотреть на сайте в разделе «Percona Server Feature Comparison».

Важный момент — переход со стандартного MySQL на Percona Server вообще не потребовал изменения какого-либо кода или логики приложения.

А, вот, сам процесс «переезда» был достаточно интересным. И благодаря использованию схемы с «мастер-мастер» репликацией, прошел совершенно незаметно для наших пользователей. Даунтайма просто не было.

Схема переезда была такой:

  • «Нулевой» шаг — подняли тестовый стенд их снэпшота машины с базой с реальными данными. И на нем стали отлаживать конфигурацию для стабильной работы. Ну и, конечно, подбирать специфичные для Перконы опции в конфигурационном файле.
  • Получив стабильную конфигурацию, переключили весь траффик на один ДЦ, а базу без нагрузки выключили из репликации.
  • На этой базе осуществили переход на Percona Server.
  • Включили базу в репликацию (стандартный MySQL и Percona Server совместимы).
  • Дождались синхронизации данных.
  • После этого проделали ту же процедуру со второй базой.

4. MyISAM? InnoDB?

Тут все просто.

  • На долгих запросах в MyISAM лочится вся таблица. В InnoDB — только те строки, с которыми работаем. Соответственно, в InnoDB долгие запросы в меньшей степени влияют на другие запросы и не отражаются на работе пользователей.
  • На больших объемах данных и при высокой нагрузке таблицы MyISAM «ломаются». Это — известный факт. В нашем сервисе это недопустимо.
  • Ну, и раз уж у нас есть «улучшенный» InnoDB — XtraDB, конечно, мы будем использовать именно этот storage engine. :)

* * *

Переходим от архитектурных вопросов к более практическим. :)

5. Все ли данные нужно реплицировать? Нет, не все.

Почти в любом проекте есть некритичные для потери или восстанавливаемые данные. В том числе — и в базе данных.

В нашем случае такими данными были сессии. Что было плохого в том, что реплицировалось все подряд?

  • В таблицах сессий наряду с SELECT'ами много операций записи (INSERT, UPDATE, DELETE). Это значит, что мы даем лишнюю нагрузку на slave базу. Ту нагрузку, которую можно избежать.
  • Кроме того, при достаточно большом значении query_cache_size мы столкнулись с тем, что активная работа с этими таблицами и их участие в репликации приводят к тому, что многие треды «подвисают» в состоянии «waiting for query cache lock» (видно в SHOW PROCESSLIST). Далее это чревато повышенной нагрузкой на CPU и общей деградацией производительности.

Исключение этих данных из репликации полностью решило проблему.

Как исключать? Есть разные способы.

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

SET sql_log_bin = 0;

2. Более простой и понятный способ — указать исключение в конфигурационном файле MySQL.

replicate-wild-ignore-table = %.b_sec_session

Такая конструкция исключает из репликации таблицы b_sec_session во всех базах.

Все немножко более сложно в том случае, если вам нужна более сложная логика. Например, не реплицировать таблицы table во всех базах, кроме базы db.

В таком случае вам придется немного порисовать схемки наподобие тех, которые дает MySQL, чтобы составить правильную комбинацию опций — фильтров.

6. Тип репликации.

Много споров вызывает вопрос, использовать ли STATEMENT-based или ROW-based репликацию. И тот, и другой вариант обладают и плюсами, и минусами.

По умолчанию в MySQL (Percona) 5.5 используется STATEMENT-based репликация.

На нашем приложении в такой конфигурации мы регулярно видели в логах строки: «Statement may not be safe to log in statement format».

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

В MySQL есть интересное решение, которое нас полностью устроило — использовать MIXED формат бинлога:

binlog-format = mixed

В этом случае по умолчанию репликация идет в режиме STATEMENT и переключается в ROW как раз в случае таких небезопасных операций.

7. Репликация сломалась. Что делать?

Репликация иногда все-таки ломается. Особенно страшно (поначалу :)) это звучит при работе с «мастер-мастер».

На самом деле, ничего страшного нет. Правда. :)

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

Самая простая (и самая часто случающаяся) проблема — ошибка вида «1062 Error 'Duplicate entry'».

Причины могут быть разными. Например, мы в случае какой-либо аварии с базой переключаем траффик на другой ДЦ. Если запрос уже был выполнен в ДЦ 1, но не успел среплицироваться в ДЦ 2 и был выполнен там повторно — да, получим именно такую ошибку.

Лечится выполнением вот таких команд на слейве:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Тем самым пропускаем лишний запрос. Далее смотрим состояние репликации:

SHOW SLAVE STATUSG

Если требуется, повторяем процедуру.

* * *

Да, мы сейчас детально рассматриваем самый простой вариант. Все бывает значительно хуже — рассыпается файловая система, бьются файлы и т.п.

Универсального рецепта «как все починить» нет. Но всегда важно помнить следующее:

  • Не паниковать.
  • Видеть состояние слейва с помощью SHOW SLAVE STATUSG.
  • Видеть состояние мастера с помощью SHOW MASTER STATUS.
  • Всегда вести лог (log-error = /var/log/mysqld.log) — в нем очень много полезной информации. Например, данные о том, до какой позиции слейв дочитал бинлог мастера. Очень помогает при авариях.
  • Если все совсем сломалось — подниматься из бэкапа.

8. Как поднимать из бэкапа один из серверов в «мастер-мастер» репликации?

Что же делать, если в схеме с двумя мастерами все-таки что-то пошло не так (например, во время аварии в Амазоне несколько дней назад у нас необратимо повредились файловые системы на нескольких серверах)?

Решение «в лоб» — перелить данные из одного сервера на другой и запустить репликацию с нуля — слишком долго.

В Амазоне мы используем механизмы снэпшотов дисков и создание образов (AMI) целых машин. Это позволяет очень быстро развернуть полную копию нужного сервера — например, по состоянию на несколько часов назад.

Если мы просто развернем машину из бэкапа, мы получим интересный эффект: мы начнем читать данные из бинлогов «живого» мастера (с момента, когда создавался бэкап), но прочитаем лишь половину их, так как по умолчанию записи с сервера с тем же server-id (из «будущего» относительно времени бэкапа) реплицироваться не будут. Это делается для того, чтобы избежать «зацикливаний» в «мастер-мастер».

Поступаем так:

1. Весь траффик идет на «живой» ДЦ. На тот сервер, который мы восстанавливаем нагрузки нет.
2. На сервере, поднятом из бэкапа, сразу останавливаем mysqld и вписываем в конфиг:

skip-slave-start 
replicate-same-server-id 
#log-slave-updates = 1 ; комментируем!

3. Запускаем mysqld и стартуем репликацию.
4. После того, как данные синхронизированы, возвращаем конфиг в исходное состояние:

#skip-slave-start 
#replicate-same-server-id 
log-slave-updates = 1 

5. Так как у нас — «мастер-мастер», нам нужно запустить репликацию и в обратную сторону. Останавливаем репликацию на том сервере, который мы восстанавливали, и выполняем:

SHOW MASTER STATUS;

Если репликацию не остановим, данные будут меняться.
6. Стартуем с нужной позиции репликацию на первом (живом) сервере:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS = ...;
START SLAVE;

Вписываем данные, полученные в пункте 5.
7. Стартуем репликацию и на втором сервере.

9. Где баланс между производительностью и надежностью репликации?

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

Мы для себя нашли баланс в такой комбинации опций:

sync_binlog = 1
sync_master_info = 0
sync_relay_log = 0
sync_relay_log_info = 0
innodb-flush-log-at-trx-commit  = 2

Бинлог для нас критически важен, поэтому sync_binlog = 1. Но при этом бинлоги хранятся на отдельном диске в системе, поэтому запись на этот диск не снижает производительность системы в целом.

10. Как вообще оценивать производительность системы?

Если у нас есть большие «тяжелые» запросы, то, конечно, мы банально ориентируемся на время их выполнения.

Чаще же (и в нашем случае — именно так) система обрабатывает много-много мелких запросов.

Конечно, можно использовать различные синтетические тесты для оценки производительности системы. И некоторую оценку они дадут. Но ведь хочется иметь какие-то реальные показатели (желательно — в цифрах :)), которые можно было бы применять «в бою».

В Percona Server есть замечательный инструмент:

SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;

+----------------+-------+----------------+
| time           | count | total          |
+----------------+-------+----------------+
|       0.000001 |     0 |       0.000000 |
|       0.000010 |  6555 |       0.024024 |
|       0.000100 | 56132 |       2.326873 |
|       0.001000 | 23165 |       6.686421 |
|       0.010000 |  9755 |      39.737027 |
|       0.100000 |  1437 |      40.831493 |
|       1.000000 |   141 |      31.785571 |
|      10.000000 |     9 |      17.891514 |
|     100.000000 |     0 |       0.000000 |
|    1000.000000 |     0 |       0.000000 |
|   10000.000000 |     0 |       0.000000 |
|  100000.000000 |     0 |       0.000000 |
| 1000000.000000 |     0 |       0.000000 |
| TOO LONG       |     0 | TOO LONG       |
+----------------+-------+----------------+
14 rows in set (0.00 sec)

Такая гистограмма распределения времени выполнения запросов очень хорошо помогает оценивать общее состояние системы.

Например, мы для себя определили некий критический порог — не более 5% запросов (от общего числа) с временем выполнения более 0.01 сек.

Чтобы отслеживать это состояние в динамике, написали простой плагин к Munin'у, который как раз и рисует график по данному соотношению. Очень удобно, и — главное — это живая понятная метрика.

11. Сбалансированность по памяти.

Настройки MySQL должны быть такими, чтобы потребление памяти было сбалансировано!

Вроде, простое и понятное правило, но о нем часто забывают. Каюсь, сами пару раз (в начале, на прототипе :)) получили OOM (Out of memory) и — как следствие — «убитый» операционной системой процесс mysqld.

В идеале — процесс mysqld должен работать так, чтобы полностью помещаться в оперативной памяти и не оперировать свопом.

Обязательно — все процессы системы должны помещаться в память+swap.

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

Формула примерно такова:

  • Размер глобальных буферов: key_buffer_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size
  • Размер буфера для одного коннекта: read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size
  • Максимально возможное использование памяти: глобальные буферы + буферы подключений * максимальное число коннектов

Если не очень хотите считать :), можно воспользоваться скриптом mysqltuner.pl, который помимо этой информации покажет много других данных по системе, безопасности, производительности и т.п.

# wget mysqltuner.pl
# perl mysqltuner.pl

* * *

Спасибо, что дочитали до этого места! :)

11 «рецептов приготовления» MySQL в Битрикс24

Мы рассмотрели лишь некоторую часть практических вопросов и приемов, которые мы используем в работе «Битрикс24». В том числе благодаря им, сервис растет и развивается.

Надеемся, что наш опыт поможет и вам в создании и развитии ваших проектов.

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

Автор: adamant

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js