Автоматическая настройка и оптимизация сервера MySQL для повышения производительности

Всем привет.

Хочу обсудить с сообществом наш проект, с помощью которого можно за 1 минуту создать конфигурационный файл MySQL, оптимизированный под производительность (тюнинг MySQL). Таким образом можно достаточно быстро создавать конфигурационный файл для первичной оптимизации производительности MySQL.

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

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

Мы попытались упростить эту задачу, создав проект MySQLConfigurer, с помощью которого можно за 1 минуту создать конфигурационный файл MySQL, учитывающий текущую аппаратную конфигурацию системы и статус MySQL.

MySQLConfigurer это bash скрипт и онлайн сервис, который анализирует рекомендации MySQLTuner, информацию о системе, текущий статус MySQL и подготавливает конфигурационный файл MySQL с рекомендуемыми параметрами, помогающими повысить производительность.

Подробная инструкция об использовании размещена на странице проекта, достаточно выполнить всего 5 шагов:

  1. Скачиваем скрипт, код скрипта простой и его можно посмотреть на github.
  2. Запускаем скрипт.
  3. Получаем конфигурационный файл с рекомендуемым значением параметра есть комментарий в котором указано текущее значение.
  4. Копируем файл в директорию с конфигурационными файлами MySQL.
  5. Перезапускаем MySQL.

Мы провели тесты MySQL с помощью Sysbench на виртуальном сервере с операционной системой Debian 9 (2 CPU, 2GB Ram) на таблице в 10 млн. записей.

Тестирование проводилось на 2 конфигурациях: параметры MySQL по умолчанию и рекомендованные MySQLConfigurer. На каждой конфигурации провели по 2 теста: только чтение и чтение / запись.

Тесты показали увеличение производительности до 30% по сравнению с конфигурацией по умолчанию. Результаты тестов можно посмотреть по ссылке.

Сейчас MySQLConfigurer поддерживает MySQL версий 5.5, 5.6 и 5.7.

Буду благодарен за любую обратную связь по проекту MySQLConfigurer. Спасибо.

Страница проекта

Update 24.05.2020
Запущен сайт проекта releem.ru
Мы ставим перед собой задачу сделать инструмент, помогающий инженерам автоматически конфигурировать MySQL для увеличения производительности и снижения затрат на оборудование. Задача разового тюнинга MySQL станет не актуальной и освободившееся время инженеры смогут потратить на дальнейшее улучшение инфраструктуры своих проектов.

Update 24.06.2020
Выпустили релиз 0.3.0
  • Проверена совместимость с MySQL 5.5, MySQL 5.6, MySQL 5.7, MariaDB 10.1, MariaDB 10.2, MariaDB 10.3.
  • Добавлен расчет параметра 'key_buffer_size' для увеличения производительности MyIsam storage engine.
  • Добавлен расчет параметра 'innodb_buffer_pool_chunk_size' для MySQL 5.7.5 и более новых версий, MariaDB 10.2.2 и более новых версий.
  • Добавлен расчет параметра 'max_connections' на основе показателя 'Max_used_connections'.
  • Уточнен расчет параметра 'innodb_log_file_size'.
  • Исправлена документация. Подробно описано как безопасно применить конфигурационный файл MySQL.


Update 08.07.2020
Запущен сайт проекта releem.com
Выпустили релиз 0.3.1
  • Добавлен расчет параметра 'table_open_cache'
  • Добавлен расчет параметра 'table_definition_cache'


Update 24.08.2020
Выпустили релиз 0.3.2
  • Добавлена поддержка MySQL 8, MariaDB 10.4, MariaDB 10.5.
  • Исправлен расчет параметра 'key_buffer_size'
  • Протестирована совместимость MySQL 5.5, MySQL 5.6, MySQL 5.7, MySQL 8.0, MariaDB 10.1, MariaDB 10.2, MariaDB 10.3, MariaDB 10.4, MariaDB 10.5.
  • В документацию добавлена секция о безопасности.
  • В документацию добавлена информация об установке open_files_limit.


Update 22.11.2020
Выпустили релиз 0.4.0
  • Улучшена документация.
  • Добавлена опция -m для установки лимита памяти для MySQL. Применима, если MySQL стоит параллельно с веб-серверов и память необходимо распределить.
  • Исправлено скачивание MySQLTuner на каждом запуске.
  • Созданы группы в Slack и Telegram для поддержки.


Update 30.01.2021
Выпустили релиз 0.5.0
  • Быстрая установка. Достаточно запустить одну команду.
  • Улучшена документация.
  • Опубликовано описание тестирования производительности MySQL 5.7 на сайте releem.com.
  • Исправлена проблема с таймаутами. Некорректная рекомендация значений таймаутов.
  • Добавлена рекомендация «max_allowed_packet».
  • Добавлена рекомендация «read_rnd_buffer_size».
  • Улучшен расчет следующих параметров: «sort_buffer_size», «innodb_buffer_pool_size», «key_buffer_size», «innodb_buffer_pool_instances».

Средняя зарплата в IT

120 000 ₽/мес.
Средняя зарплата по всем IT-специализациям на основании 7 231 анкеты, за 1-ое пол. 2021 года Узнать свою зарплату
Реклама
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее

Комментарии 48

    0
    Сейчас MySQLConfigurer поддерживает MySQL версий 5.5, 5.6 и 5.7

    Когда планируется поддержка 8.0? А то 8-ка официально пригодна к использованию с 19.04.2018

    P.S. У нас проект на 8-ке с апреля 18-го, полёт нормальный

    UPDATE: MySQLTuner уже поддерживает
      0
      Дело в том, что сейчас проект используется в поддержке нескольких десятков серверов MySQL для автоматизации работы инженеров. Большинство из этих серверов имеют версию 5.5 — 5.7.
      Поэтому до 8-ки еще и не добрались, но по мере развития проекта планируем добавить.

      Добавил issue на github по поддержке MySQL 8.
        0
        Добавили поддержку MySQL 8.0. Можно пробовать.
      0

      Чем обоснована необходимость отправлять репорт MySQLTuner на сторонний сервис (https://api.servers-support.com/v1/mysql)?
      Было бы лучше локально парсить репорт и билдить предлагаемый конфиг локально...

        0
        У MySQLTuner особенность в том, что он делает выводы на основе среза информации о MySQL, который получен в данный момент времени. Но есть понимание, что зная исторические данные можно точнее настраивать конфигурацию и в перспективе лучше, чем инженер. Поэтому и был предложен именно такой формат взаимодействия.

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

        Документацию будем развивать и больше публиковать информации о методике расчета параметров.
          0
          Однако при этом на ваш сервис уходит пароль, который вводится в консоли…
            0
            Спасибо, за информацию.

            Инженеры подтвердили, если пароль для MySQL вводится вручную, то этот пароль MySQLTuner сохраняет в отчете JSON в разделе «MySQL Client». В случае если используется файл с данными для подключения пароль не сохраняется и не передается.

            Информацию из этого раздела мы не используем для обработки и исторического анализа метрик, поэтому сейчас готовим обновление для исключения сбора этой информации и удаления ее в ранее собранных отчетах.
              0
              И еще — понятно, что это не ваш баг, но mysqltuner пытается получить данные через hostname -I. Проблема в том, что у меня на сервере hostname не понимает -I, ему вместо этого надо дать -i. Вручную подправить две строчки в скрипте — не проблема, но он каждый раз скачивается заново. Это действительно необходимо — каждый раз качать сторонний скрипт?
              Ну и с mariaDB 10.4 так ничего и не решено. Тюнер отрабатывает, а потом — ох, ах, 500
                0
                Скачивать каждый раз необходимости особой нет, но планировали чтобы он был актуальный. Информацию принял и завел issue на эту тему. Мы обсудим и дадим обратную связь.

                Да, работы по новым версиям ведутся, но медленно. Много проделали работы по улучшению качества конфигурационных файлов MySQL для старых версий.
                  0
                  к вопросу о hostname уточните какая у Вас операционная система и какая версия?
                    0
                    hostname (GNU coreutils) 8.31.0.3.6bd78
                    ALTLinux P9
                    0
                    Добавлена поддержка MySQL 8, MariaDB 10.4, MariaDB 10.5. Можно пробовать.
                      0
                      Исправили, теперь Mysqltuner каждый раз не скачивается.
              0
              Ссылку на страницу проекта поправьте?
                0
                Исправил, спасибо за замечание
                0
                MySQLTuner в основном нужен для начальной настройки и понимания взаимосвязи опций. В последствии ориентироваться лучше на тот же PMM(percona monitoring and management)
                  0
                  Позволяет ли PMM сейчас получить какие-то рекомендации по параметрам конфигурации MySQL?
                    0
                    Позволяет в виде описания параметров конкретного графика и ссылки на статью в блоге Перконы.
                      0
                      Спасибо, посмотрим подробнее на реализацию.
                  0
                  Мы провели тесты MySQL с помощью Sysbench на виртуальном сервере с операционной системой Debian 9 (2 CPU, 2GB Ram) на таблице в 10 млн. записей.

                  Было бы интересно посмотреть тесты на более серьезном железе.
                    0
                    Завел Issue на github.

                    Проведем такое тестрирование.
                    0
                    синтетика — такая синтетика…
                    Честно, говоря, даже не задумывался, пилил себе сайт на работе для работы и ладно.
                    Но результат вашего скрипта просто ошеломил — если без него один тяжелый api запрос выполнялся 350-400мс, то после оптимизации — 130-170мс. Это даже больше, чем двухкратный прирост на рабочей БД. Хотя, конечно, это только один запрос, другой показал рост примерно на 30%, а кучу мелких даже проверять не стал. Тем более, что все это прогоняется через apache+php. Но все равно заметно сильно, сайт отзывчивее стал
                      0
                      Спасибо за обратную связь.

                      Нам такой результат, как бальзам на душу. Для этого и работаем.
                        0
                        Но ложка дегтя тоже есть, обнаружилась сегодня — начали отваливаться коннекты, которые раьше могли сутками висеть. Заподозрил это
                        interactive_timeout = 1200 ### Previous value : 28800
                        wait_timeout = 1200 ### Previous value : 28800

                        пока вернул старые значения, посмотрим, как будет себя вести
                          0
                          Информацию принял. Завел issue

                          Уточните, после того как вернули обратно была ли проблема решена?
                            0
                            Да, проблема ушла, все работает, так же, как и раньше. Отваливались коннекты из 1С через самописную dll, которая работает через стандартную либу mysql
                              0
                              Отлично. А увеличение производительности сохранилась, как и после первого применения рекоменданных параметров?
                                0
                                Да, все работает так же шустро, спасибо
                            0
                            Проблему с таймаутами исправили в последнем релизе github.com/Releem/mysqlconfigurer/releases/tag/0.5.0
                        0
                        Скрипт выполнялся 2 часа, в z_aiops_mysql.cnf {«message»: «Internal server error»}
                          0
                          Спасибо, что дали обратную связь. Сейчас такое поведение в основном связано с тем, что:
                          1. мы пока не поддерживаем MySQL версии 8.
                          2. MySQLTuner не смог собрать корректный отчет. (в основном из-за того что прав не достаточно)

                          Проверили запросы поступающие на сервис, но за последнее время не увидели ошибочных. Уточните, пожалуйста, когда и во сколько примерно делали запрос к сервису?
                            0
                            К сожалению не могу сказать. может поможет отчет был около 7Мб.
                              0
                              Сейчас вот это выдал:
                              # 500 Internal Server Error Oh no! Something bad happened. Please check supported MySQL versions. Thanks.
                              Версия mysql: percona-server-server-5.6 5.6.39-83.1-1.bionic
                                0
                                Проблему увидели.
                                В данном случае формат данных отчета MySQLTuner для percona отличается от тех на которых мы проводили тестирование (MySQL 5.5, 5.6 и 5.7).

                                Создал issue по этой проблеме. Я отпишусь тут по решению.
                                  0
                                  Пока не понятно по какой причине, но в Вашем случае при запуске MySQLTuner была установлена опция skipsize, в результате в отчет не попала информация о размерах данных в используемых механизмах хранения MySQL. Уточните, пожалуйста, Вы редактировали скрипт или запускали как есть?
                                    0
                                    Да, я добавлял эту опцию. Хотел сократить время работы скрипта.
                                      0
                                      Попробуйте, пожалуйста, не добавлять эту опцию, потому что без этих данных не сможем расчет сделать.
                                        0
                                        Уточните, а почему хотели сократить время выполнения скрипта, может во время работы скрипт сильно нагружает систему?
                                          0
                                          Запустил снова, на этот раз без --skipsize. Выполнялся 2 часа.
                                          В итоге {«message»: «Internal server error»}

                                          У меня много баз (~2.5К), по поводу нагрузки — нет не грузит, только диск теребит.
                                            0
                                            2,5К баз действительно много.

                                            Похоже мы уперлись в лимиты облака Amazon для сервиса Lambda — 6Mb на запрос. Видно, что запрос приходит, но до сервиса не доходит.

                                            Можно ли у Вас попросить полный файл отчета MySQLTuner в личку и мы передадим Вам подготовленный сервисом конфигурационный файл?
                                              0
                                              Отправил в личку
                                                0
                                                Спасибо. Результат работы сервиса отправил в личку.
                                0

                                Я хостер (и мы умеем тюнить мускль). Любопытства ради запустил скрипт на одном из продакшн серверов (сотни БД, десятки гигабайт, перкона 5.6). Результат, ну, удивляет.


                                query_cache_type = 1 ### Previous value: OFF

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


                                interactive_timeout = 1200 ### Previous value: 70
                                wait_timeout = 1200 ### Previous value: 70

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


                                table_open_cache = 65536 ### Previous value: 131072

                                Непонятное косметическое изменение, которое противоречит логике — таблиц там сильно за 64к было. mysqltuner предлагает: table_open_cache (> 131072), кстати.


                                innodb_buffer_pool_instances = 42 ### Previous value: 2
                                innodb_buffer_pool_size = 45097156608 ### Previous value: ...

                                Ну, кого волнует сколько вообще памяти на сервере и зачем она там ему? 42 потока на 4 ядрах (+HT), почему бы и нет, правда?


                                Бонусом — косметические правки по myisam, который на сервере практически не используется.


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


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

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

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

                                  Все замечания обсудим с командой и добавим соответствующие Issue.

                                  Уточните, пожалуйста, во сколько по времени Вы делали запрос к сервису для более детального анализа? (можно в личку)
                                  0
                                  Поддерживается ли MariaDB?
                                    0
                                    Здравствуйте, MariaDB поддерживается, но до версии 10.3

                                    Задача по поддержки более новых версий и MySQL 8 пока ещё в работе.

                                    Текущие поддерживаемые версии:
                                    • MySQL 5.7
                                    • MySQL 5.6
                                    • MySQL 5.5
                                    • MariaDB 10.1
                                    • MariaDB 10.2
                                    • MariaDB 10.3
                                      0
                                      Сейчас уже поддерживаем все новые версии MySQL и MariaDB.
                                        0
                                        Спасибо, попробую.

                                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                    Самое читаемое