Автоматизированное создание конфигурационного файла MySQL, оптимизированного под производительность

Всем привет.

Хочу обсудить с сообществом наш проект, с помощью которого можно за 1 минуту создать конфигурационный файла 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. Спасибо.

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

Похожие публикации

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

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

    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

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

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

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

        Документацию будем развивать и больше публиковать информации о методике расчета параметров.
        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
                Скрипт выполнялся 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.

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

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

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