company_banner

Автоматическая оптимизация настроек MySQL, PostgreSQL

    Оптимизация настроек всегда дело тонкое и выставить именно те параметры, которые дадут максимальную производительность, зачастую можно только уже в процессе работы приложения, когда уже есть статистика нагрузки и видны узкие места.
    Но очень полезно сделать и первичную оптимизацию при запуске СУБД. В этом посте рассмотрены пути автоматической оптимизации MySQL и PostgreSQL утилитами mysqltuner и pgtune.


    MySQL


    Для оптимизации mysql существует простая и удобная в использовании утилита mysqltuner.
    Раздобыть ее в безвозмездное пользование можно на github, а именно тут. Или загрузить одной командой:
    wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
    


    Пользоваться просто: загружаем mysqltuner.pl на сервер с mysql, ставим права на запуск для файла (или запускаем так: perl mysqltuner.pl), на запрос логина / пароля даем учетку с привилегированными правами и смотрим рекомендации.
    Рекомендации заносим в конфиг, перезапускаем mysql-server. Или применяем «налету» через консоль mysql, если проект уже запущен и перезапуск нежелателен.
    Кроме советов по настройкам mysql, утилита так же показывает информацию о индексах в таблицах и фрагментации, если mysql уже какое-то время используется.
    Про индексы нередко забывают, что сильно повышает потребление ресурсов системы.
    Простановку индексов лучше поручить тем, кто проектировал структуру базы, но можно и самостоятельно.
    Для дефрагментации запускаем OPTIMIZE TABLE из консоли mysql, но удобнее сделать для всех таблиц разом через интерфейс типа phpMyAdmin.

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

    Для большей производительности полезно использовать Percona Server на замену стандартному MySQL Server.
    О пользе можно судить из графиков производительности.
    Про Percona Server уже достаточно много статей на Хабре, но в будущем поделюсь и своим опытом использования этой сборки.

    PostgreSQL


    Для тюнинга настроек PostgreSQL так же существует полезная утилита под названием pgtune.

    В отличие от mysqltuner, утилита не дает рекомендаций, а сразу создает конфигурационный файл postgresql.conf с параметрами, оптимальными для системы, на которой запущен PostgreSQL.

    Схема использования следующая:

    pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune


    где $PGDATA — путь к директории с конфигом сервера postgresql.conf. На выходе получаем файл postgresql.conf.pgtune, в котором выставлены подобранные утилитой параметры. Эти параметры утилита записывает в конец файла после блока

    #------------------------------------------------------------------------------
    # pgtune wizard run on YYYY-MM-DD
    # Based on XXXXXXX KB RAM in the server
    #------------------------------------------------------------------------------
    


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

    -M или --memory — полный размер ОЗУ на сервере, на основе которого выделяются ресурсы памяти для PostgreSQL;
    -T или --type — Указывает тип базы данных: DW, OLTP, Web, Mixed, Desktop;
    -с или --connections — Максимально возможное количество подключений к базе; Если значение не указано, определяется на основе типа базы;
    -D или --debug — Включает режим отладки в PostgreSQL
    -S или --settings — Устанавливает к директории, в которойрасположен конфигурационный файл.

    После завершения работы утилиты редактируем сгенерированный файл postgresql.conf.pgtune при необходимости (например, выставить нестандартный порт или настроить логирование ), заменяем им конфигурационный файл postgresql.conf и перезапускаем PostgreSQL-server.

    Буду рад любым вопросам / замечаниям / дополнениям!
    Southbridge 135,72
    Обеспечиваем стабильную работу серверов
    Поделиться публикацией
    Комментарии 21
    • +1
      Было бы совсем неплохо указать, какие именно моменты оптимизирует утилита под MySQL кроме рекомендаций по индексам.
      • 0
        Эта утилита не оптимизирует, а дает рекомендации.
        По всем параметрам и переменным, плюс еще рекомендации по индексам и оптимизации таблиц.
        • 0
          К слову, рекомендациям не всегда нужно следовать. Бывает, что настройки лучше выставить самостоятельно.
      • 0
        wget mysqltuner.pl

        Только его переименовать надо, а то он скачивает index.html.
        • 0
          Очень странно. У меня никаких проблем не возникало:
          wget mysqltuner.pl
          --2012-11-27 14:52:59--  http://mysqltuner.pl/
          Resolving mysqltuner.pl... 198.61.150.28, 2001:4801:7901:0:abc5:ba2c:0:1
          Connecting to mysqltuner.pl|198.61.150.28|:80... connected.
          HTTP request sent, awaiting response... 302 Moved Temporarily
          Location: https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl [following]
          --2012-11-27 14:53:00--  https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
          Resolving raw.github.com... 207.97.227.243
          Connecting to raw.github.com|207.97.227.243|:443... connected.
          HTTP request sent, awaiting response... 200 OK
          Length: 41847 (41K) [text/plain]
          Saving to: “mysqltuner.pl”
          
          100%[====================================================================>] 41,847      --.-K/s   in 0.1s    
          
          2012-11-27 14:53:00 (406 KB/s) - “mysqltuner.pl” saved [41847/41847]
          

          В результате получаю сохраненный скрипт.
          Можно брать из github по указанной в статье ссылке.
          • 0
            korpserver ~ # wget mysqltuner.pl
            --2012-11-27 14:57:07--  http://mysqltuner.pl/
            Resolving mysqltuner.pl... 198.61.150.28
            Connecting to mysqltuner.pl|198.61.150.28|:80... connected.
            HTTP request sent, awaiting response... 302 Moved Temporarily
            Location: https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl [following]
            --2012-11-27 14:57:07--  https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
            Resolving raw.github.com... 207.97.227.243
            Connecting to raw.github.com|207.97.227.243|:443... connected.
            HTTP request sent, awaiting response... 200 OK
            Length: 41847 (41K) [text/plain]
            Saving to: 'index.html'
            
            100%[============================================================================================>] 41,847       253KB/s   in 0.2s   
            
            2012-11-27 14:57:08 (253 KB/s) - 'index.html' saved [41847/41847]
            

            в результате получаю скрипт в index.html, и надо переименовывать.

            korpserver ~ # wget -V
            GNU Wget 1.14 built on linux-gnu.
            
            • 0
              Похоже не у всех будет срабатывать из-за 302 перенаправления.
              Указал прямую ссылку.
              Спасибо за уточнение!
        • 0
          wget --content-disposition
          Вам в помощь
        • 0
          А покажите то, что сгенерил pgtune и сделайте free -g.
          Очень интересно, насколько он оптимально нагенерил, без указания объема памяти того-же.
          • 0
            Вот, пожалуйста:
            #------------------------------------------------------------------------------
            # pgtune wizard run on 2012-11-27
            # Based on 16203092 KB RAM in the server
            #------------------------------------------------------------------------------
            
            default_statistics_target = 100
            maintenance_work_mem = 960MB
            checkpoint_completion_target = 0.9
            effective_cache_size = 11GB
            work_mem = 96MB
            wal_buffers = 8MB
            checkpoint_segments = 16
            shared_buffers = 3840MB
            max_connections = 80
            


            Это только то, что сгенерировано. Применял к дефолтному конфигу postgresql.conf
            И еще:
                           total       used       free     shared    buffers     cached
            Mem:            15          1         14          0          0          0
            -/+ buffers/cache:          1         14
            
          • 0
            На всяий случай обращу внимание, что при задании shared_buffers pgtune глубоко плевать на настройки ядра.
            kernel.shmmax и kernel.shmall необходимо подогнать, иначе есть шанс, что БД не запустится.
          • 0
            Для постгреса можно еще глянуть эту книгу.
            • 0
              Книгу эту знаю. Очень полезная вещь.
              • 0
                Может тогда добавите в статью описание параметров, которые меняет утилита? Вместе с рекомендациями по их наиболее эффективным значениям. Потому как эта утилитка, по крайней мере у меня, попросту уставила максимально возможные.
                • 0
                  Чуть позже добавлю обязательно с подробными комментариями.
            • +1
              Ещё рекомендую утилиту для mysql для выявления медленных запросов: mysqlsla. Она умеет анализировать slow-логи (да и обычные логи) и показывать различную статистику. Причём утилита достаточно умная и умеет собирать вместе запросы, которые различаются только параметрами.

              Auto-detected logs as slow logs
              Report for slow logs: mysql-slow.log
              1.31k queries total, 70 unique
              Sorted by 't_sum'
              Grand Totals: Time 10.95k s, Lock 0 s, Rows sent 6.41M, Rows Examined 258.51M
              
              
              Count         : 24  (1.83%)
              Time          : 381.532477 s total, 15.897187 s avg, 2.452116 s to 59.170035 s max  (3.48%)
                95% of Time : 274.736667 s total, 12.48803 s avg, 2.452116 s to 31.764067 s max
              Lock Time (s) : 1.235 ms total, 51 <B5>s avg, 40 <B5>s to 63 <B5>s max  (0.28%)
                95% of Lock : 1.111 ms total, 51 <B5>s avg, 40 <B5>s to 60 <B5>s max
              Rows sent     : 0 avg, 0 to 0 max  (0.00%)
              Rows examined : 0 avg, 0 to 0 max  (0.00%)
              Database      : 
              Users         : 
                      test@ 127.0.0.1 : 100.00% (24) of query, 91.59% (1198) of all users
              
              Query abstract:
              SET timestamp=N; INSERT INTO api_sessions (token, user_id, full_auth) VALUES ('S', 'S', 'S')1;
              
              Query sample:
              SET timestamp=1353967395;
              INSERT INTO `api_sessions` (`token`, `user_id`, `full_auth`) VALUES ('asdfasdfasf', '1', '');


              • 0
                Буду пользоваться. Обычно slow-логи приходилось читать самостоятельно.
                Спасибо!
              • +3
                Исходя из заголовка я уж подумал, что будет рассказ про _автоматическую_ оптимизацию, когда нужные параметры изменяются без участия человека. А тут рассказ про запуск mysqltuner.pl которому сто лет в обед. Единственное что тут нового, это редирект с сайта mysqltuner.pl на github. Pgtune — тоже ничего нового.
                • 0
                  Чтобы параметры менялись без участия человека — это было бы круто. Только вот это уже на грани ИИ, мне кажется.
                  Я просто хотел поделиться опытом и получить замечания, советы или дополнения. И я их получил, за что большое спасибо всем, кто написал полезную информацию и рекомендации. Все будет учтено обязательно.

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

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