Время на прочтение
В нашей прошлой статье — «11 «рецептов приготовления» MySQL в Битрикс24» — мы, в основном, рассматривали архитектурные решения: стоит ли использовать облачные сервисы (типа Amazon RDS), какой форк MySQL выбрать и т.п.
Судя по отзывам, тема грамотной эксплуатации MySQL в больших «хайлоад» проектах — очень большая и важная. Поэтому мы решили рассказать еще о некоторых нюансах настройки и администрирования БД, с которыми сталкивались при разработке «Битрикс24» и которые используем ежедневно.
Еще раз напомню, что эта статья (как и предыдущая) не является универсальным «рецептом» идеальной настройки MySQL на все случаи жизни. 🙂 Такого не бывает. 🙂 Но искренне верю, что она будет полезной для вас для решения отдельных конкретных задач.
А в конце статьи — сюрприз для самых терпеливых читателей. 🙂
1. Настройка QUERY CACHE
Написано огромное количество статей, описывающих, как именно работает Query Cache в MySQL, и как его настраивать и использовать.
И все равно, несмотря на это, самое частое заблуждение системных администраторов, настраивающих базу данных — «Чем больше дадим памяти под кэш, тем лучше».
Это не так.
MySQL плохо оперирует Query Cache’м большого размера. На практике сталкивались с тем, что при query_cache_size более 512M все чаще появляются процессы, кратковременно подвисающие в состоянии «waiting for query cache lock» (видно в SHOW PROCESSLIST).
Кроме того, если все ваши запросы попадают в кэш, неразумно увеличивать его. R AM в системе драгоценна!
Поэтому всегда важно понимать, что происходит в вашем проекте, и насколько эффективно используется Query Cache с текущими настройками.
Ключевая информация для вас — здесь:
Самое важное — соотношения Qcache_hits и Qcache_inserts, Qcache_inserts и Qcache_not_cached, а также Qcache_lowmem_prunes — количество вытесненных из кэша запросов — и Qcache_free_memory.
Лучше всего не просматривать эту статистику лишь эпизодически, а иметь под рукой аналитику. Ее можно собирать с помощью тех или иных средств мониторинга. Например, Munin:
Просматривая графики в динамике — раз в день, например, мы в итоге пришли к тому, что для нас вполне хватает таких настроек для одного сервера:
query_cache_size = 128M
query_cache_limit = 2M
Конечно, возможно, для вашего проекта картина будет иной.
Размер Buffer Pool’а — одна из важнейших настроек InnoDB. Это размер буфера памяти, который используется MySQL в процессе работы для кэша данных и индексов таблиц (сразу напомним, что для того, чтобы избежать двойного кэширования — самим MySQL и операционной системой, стоит указать в настройках innodb_flush_method = O_DIRECT).
Со стороны разработчиков MySQL одно из величайших преступлений — поставить значение по умолчанию 8M. 🙂
На самом деле, в идеале значение innodb_buffer_pool_size должно быть таким, чтобы в память помещалась вся ваша база. При этом важно помнить о сбалансированности системы по памяти (об этом мы говорили в прошлой статье) — если «задрать» значение innodb_buffer_pool_size так, что вся система уйдет в swap, ничего хорошего не получится.
Хорошим индикатором правильной настройки служит Buffer pool hit rate:
Если значение близко к «1000 / 1000» — все хорошо. Иначе — надо увеличивать innodb_buffer_pool_size. Если при этом не хватает памяти — добавлять память.
По умолчанию InnoDB использует для Buffer Pool один инстанс.
При этом есть возможность выделить несколько блоков — и работает с ними MySQL в InnoDB в ряде случаев гораздо эффективнее.
Buffer Pool стоит разбивать на несколько инстансов в том случае, если он у вас превышает 2 Гб. Размер каждого инстанса стоит делать 1 Гб и более.
И вот здесь важный вопрос, с которым многие путаются: innodb_buffer_pool_size — это общий размер пула или размер одного инстанса?
Ответ есть прямо в документации — это общий размер. Поэтому, например, вот такая конфигурация:
innodb_buffer_pool_size = 4096M
innodb_buffer_pool_instances = 4
Интересный параметр, который, вроде бы (если верить официальной документации :)), и не очень сильно влияет на производительность, тем не менее на практике дает некоторый выигрыш, если его правильно настроить.
innodb_io_capacity задает предел операций ввода-вывода (в IOPS’ах) для тех операций InnoDB, которые выполняются в бэкграунде (например, сброс страниц из Buffer Pool’а на диск).
Значение по умолчанию — 200.
Слишком маленькое значение приведет к тому, что эти операции будут «отставать». Слишком большое приведет к тому, что данные из Buffer Pool’а будут сбрасываться слишком быстро.
В идеале стоит поставить значение, соответствующее реальной производительности вашей дисковой системы (опять же — в IOPS’ах).
По умолчанию MySQL в InnoDB хранит данные и индексы всех таблиц в одном тейблспейсе — файле ibdata1.
Если же в настройках используется опция innodb_file_per_table, то в этом случае для каждой таблицы создается отдельный файл table_name.ibd, в котором хранятся данные и индексы.
Есть ли смысл в этой опции?
На мой личный взгляд: если использовать стандартный MySQL, то использовать ее не нужно. Хранение таблиц в отдельных файлах может снизить производительность, так как в системе кардинально возрастет количество «дорогих» ресурсоемких операций открытия файла.
Мы сами используем Percona Server. И используем innodb_file_per_table.
Еще один преступный 🙂 заговор разработчиков MySQL. Значение max_connect_errors по умолчанию равно 10.
Это значит, что в любом более-менее активном проекте в случае какого-либо неожиданного сбоя — даже кратковременного (например, прописали неверный пароль в скриптах; или случились какие-то сетевые проблемы) — после указанного числа неуспешных попыток установить соединение хост, устанавливающий соединение, будет заблокирован. До тех пор, пока не будет рестартован сервер MySQL или не будет выполнена команда FLUSH HOSTS.
Это значит, что до ручного вмешательства (только если вы заранее не повесили на cron скрипт, который раз в несколько минут выполняет FLUSH HOSTS :)) ваш проект не будет работать. Неприятно, если такое случится ночью, а у вас нет круглосуточного мониторинга.
Лучше обезопасить себя заранее и поставить значение max_connect_errors большим. Например:
max-connect-errors = 10000
7. Временные таблицы
Если количество RAM в системе позволяет — с временными таблицами лучше всегда работать в памяти.
Организовать это достаточно просто. В настройках MySQL:
tmpdir = /dev/shm
В настройках файловых систем и разделов (если речь идет про Linux — в файле /etc/fstab):
8. Размер временных таблиц
Есть два похожих параметра, отвечающих за размер таблиц в памяти:
max_heap_table_size = 64M
tmp_table_size = 64M
max_heap_table_size — максимальный размер таблиц типа MEMORY, которые может создавать пользователь.
tmp_table_size — максимальный размер временной таблицы, которая будет создана в памяти (больше — на диске).
Чем меньше дисковой активности, тем лучше. Поэтому, если позволяет количество RAM в системе (помним о сбалансированности по памяти), лучше со всеми временными таблицами работать в памяти.
9. table_cache и table_definition_cache
table_cache = 4096
table_definition_cache = 4096
Эти две опции отвечают за то, какое максимальное количество таблиц будет храниться в кэше открытых таблиц.
Значение table_cache напрямую зависит от количества таблиц в вашей системе, от количества открываемых таблиц в запросе (связанных через JOIN’ы, например) и от количества открытых коннектов к базе.
table_definition_cache определяет размер кэша для структур таблиц (.frm) файлов. Чем больше их в системе, тем больше значение table_definition_cache нужно установить.
10. Борьба за долгие запросы
В прошлой статье мы уже упоминали о том, что в Percona Server есть хороший инструмент определения общей производительности системы (SELECT * FROM INFORMATION_SCHEMA. QUERY_RESPONSE_TIME).
Кроме того, в любом MySQL есть возможность логировать все «медленные» запросы и отдельно разбирать их.
При использовании Percona Server лог медленных запросов становится гораздо более информативным.
log_output = FILE
slow_query_log = 1
slow_query_log_file = mysql_slow.log
long_query_time = 1
#percona
log_slow_verbosity = microtime,query_plan,innodb
Все запросы, выполняющиеся дольше 1 секунды, мы записываем в файл mysql_slow.log. В отличие от стандартного лога, выглядит он примерно так:
Мы видим не только время выполнения запроса, количество «просмотренных» строк и т.п., но и гораздо более детальную информацию — Full Scan’ы, использование временных таблиц, состояние InnoDB.
Все это очень помогает в аналитике медленных запросов и их отладке.
11. Подробная статистика без Percona
Даже если вы используете стандартный MySQL, то и в нем есть хорошие инструменты отладки запросов (конечно, если вы их уже «поймали» и идентифицировали 🙂 — например, с помощью того же лога медленных запросов).
Есть такая штука, как Profile’ы. Вы их используете? Нет? Зря!
Включаем профайлинг и смотрим любой запрос:
Сразу видим, что является «узким» местом — сеть, работа с диском, использование кэша или что-либо еще.
12. Как использовать информацию из profile?
Если вы научились находить одиночные долгие запросы (это не так сложно — лог медленных запросов и SHOW PROCESSLIST в помощь), если вы правильно оцениваете состояние системы в целом (SELECT * FROM INFORMATION_SCHEMA. QUERY_RESPONSE_TIME, внешний мониторинг системы, например, nagios – real time, munin – аналитика), то крайне важно понимать, что в системе может влиять на производительность:
Понимая это и анализируя данные из profile или, например, из расширенного лога медленных запросов, вы всегда сможете грамотно оценить дальнейшую стратегию работы с базой — в каких случаях потребуется апгрейд железа, в каких — изменение тех или иных настроек, и где — возможно, реорганизация структуры данных и запросов.
Удачной отладки и успешной эксплуатации баз данных любого объема и с любой нагрузкой! 🙂
Спасибо, что дочитали до этого места! 🙂
Надеюсь, наши советы по работе с MySQL окажутся полезными для вас!
И раз уж мы рассказываем о нашем опыте эксплуатации MySQL именно в проекте «Битрикс24», мы бы хотели сделать небольшой подарок для всех читателей нашего блога на Хабре.
Зарегистрируйтесь в «Битрикс24» по указанной ссылке и получите в два раза больше диска — 10 Гб — на бесплатном тарифе!
Если вдруг вы еще не знаете, что такое «Битрикс24» — подробное описание есть на нашем сайте. 🙂
Проектируя, разрабатывая и запуская наш новый большой проект — «Битрикс24», мы не только хотели сделать по-настоящему классный сервис для командной работы (к тому же еще и бесплатный — до 12 пользователей), но еще и собрать и накопить опыт по эксплуатации облачных веб-сервисов, «прокачать» свою компетенцию в разработке высоконагруженных отказоустойчивых проектов и — самое главное — поделиться этими знаниями как с нашими партнерами, так и со всеми веб-разработчиками, кому близка тема «хайлоада». 🙂
Мы постарались описать те «изюминки», которые не раз помогали нам в работе в решении тех или иных практических задач. Надеемся, они окажутся полезными и для вас. 🙂
Начнем по порядку.
1. Почему не используем RDS?
Amazon Relational Database Service (Amazon RDS) — облачная база данных. Есть поддержка MS SQL, Oracle и — что было интересно нам — MySQL.
Все это не означает того, что RDS — плохой сервис, и его не надо никогда использовать. Это не так. Он не подошел конкретно для нас. И, возможно, для кого-то будет гораздо проще обеспечить масштабирование и отказоустойчивость именно средствами Амазона.
2. Master-Slave? Нет, Master-Master!
Стандартная схема репликации в MySQL «Master-Slave» давно и успешно применяется на многих проектах и решает несколько задач: масштабирование нагрузки (только на чтение) — перераспределение запросов (SELECT’ов) на слейвы, отказоустойчивость.
Но решает — не полностью.
1. Хочется масштабировать и запись.
2. Хочется иметь надежный failover и продолжать работу автоматически в случае каких-либо аварий (в master-slave в случае аварии на мастере нужно один из слейвов в ручном или полу-автоматическом режиме переключить в роль мастера).
Чтобы решить эти задачи, мы используем «мастер-мастер» репликацию. Не буду сейчас повторяться, этой технике у нас недавно был посвящен отдельный пост на Хабре.
3. MySQL? Нет, Percona Server!
Первые несколько месяцев (на прототипах, в процессе разработки, в начале закрытого бета-тестирования сервиса) мы работали на стандартном MySQL. И чем дольше работали, тем больше присматривались к различным форкам. Самими интересными, на наш взгляд, были Percona Server и MariaDB.
Выбрали мы в итоге Перкону — конечно, из-за похожего «перевернутого» логотипа. 😉
Полный список можно посмотреть на сайте в разделе «Percona Server Feature Comparison».
Важный момент — переход со стандартного MySQL на Percona Server вообще не потребовал изменения какого-либо кода или логики приложения.
А, вот, сам процесс «переезда» был достаточно интересным. И благодаря использованию схемы с «мастер-мастер» репликацией, прошел совершенно незаметно для наших пользователей. Даунтайма просто не было.
Схема переезда была такой:
4. MyISAM? InnoDB?
Тут все просто.
Переходим от архитектурных вопросов к более практическим. 🙂
5. Все ли данные нужно реплицировать? Нет, не все.
Почти в любом проекте есть некритичные для потери или восстанавливаемые данные. В том числе — и в базе данных.
В нашем случае такими данными были сессии. Что было плохого в том, что реплицировалось все подряд?
Исключение этих данных из репликации полностью решило проблему.
Как исключать? Есть разные способы.
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
Если требуется, повторяем процедуру.
Да, мы сейчас детально рассматриваем самый простой вариант. Все бывает значительно хуже — рассыпается файловая система, бьются файлы и т.п.
Универсального рецепта «как все починить» нет. Но всегда важно помнить следующее:
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. Стартуем с нужной позиции репликацию на первом (живом) сервере:
Вписываем данные, полученные в пункте 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;
Такая гистограмма распределения времени выполнения запросов очень хорошо помогает оценивать общее состояние системы.
Например, мы для себя определили некий критический порог — не более 5% запросов (от общего числа) с временем выполнения более 0.01 сек.
Чтобы отслеживать это состояние в динамике, написали простой плагин к Munin’у, который как раз и рисует график по данному соотношению. Очень удобно, и — главное — это живая понятная метрика.
11. Сбалансированность по памяти.
Настройки MySQL должны быть такими, чтобы потребление памяти было сбалансировано!
Вроде, простое и понятное правило, но о нем часто забывают. Каюсь, сами пару раз (в начале, на прототипе :)) получили OOM (Out of memory) и — как следствие — «убитый» операционной системой процесс mysqld.
В идеале — процесс mysqld должен работать так, чтобы полностью помещаться в оперативной памяти и не оперировать свопом.
Обязательно — все процессы системы должны помещаться в память+swap.
Зачастую подсчеты, сколько памяти может потребить mysqld, оказываются для многих неочевидными.
Формула примерно такова:
Если не очень хотите считать :), можно воспользоваться скриптом mysqltuner.pl, который помимо этой информации покажет много других данных по системе, безопасности, производительности и т.п.
# wget mysqltuner.pl
# perl mysqltuner.pl
Мы рассмотрели лишь некоторую часть практических вопросов и приемов, которые мы используем в работе «Битрикс24». В том числе благодаря им, сервис растет и развивается.
Надеемся, что наш опыт поможет и вам в создании и развитии ваших проектов.
И уже сейчас видно, что объема одной статьи совершенно недостаточно. В ближайшее время постараемся продолжить тему использования MySQL в больших проектах, поделимся новыми рецептами и опишем наиболее интересные и востребованные темы более подробно.
партнер компании 1с-битрикс
сайт фрилансера Сергея Эстрина
Как устанавливать параметры MysqlСмотрим файл /etc/init.d/mysql и находим параметр CONF — в нем находится путь к файлу конфигурации mysql (обычно это /etc/mysql/my.cnf).
Чтобы параметры вступили в силу, нужно перезапустить сервер mysql. Сделать это можно при помощи команды /etc/init.d/mysql restart (Debian, Ubuntu) или /etc/init.d/mysqld restart (Fedora, Cent OS).
Наиболее важные параметры
Перечислю наиболее важные параметры, значения которых желательно установить
Параметры для типа таблиц InnoDB
Параметры для типа таблиц MyISAM
