Автоматическая оптимизация настроек 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

    154,83

    Обеспечиваем стабильную работу серверов

    Поделиться публикацией
    Комментарии 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
                Да не за что.
            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
                    Может тогда добавите в статью описание параметров, которые меняет утилита? Вместе с рекомендациями по их наиболее эффективным значениям. Потому как эта утилитка, по крайней мере у меня, попросту уставила максимально возможные.
                      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
                      Чтобы параметры менялись без участия человека — это было бы круто. Только вот это уже на грани ИИ, мне кажется.
                      Я просто хотел поделиться опытом и получить замечания, советы или дополнения. И я их получил, за что большое спасибо всем, кто написал полезную информацию и рекомендации. Все будет учтено обязательно.

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

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