Администрирование нагруженных серверов
239,14
рейтинг
27 ноября 2012 в 12:25

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

Буду рад любым вопросам / замечаниям / дополнениям!
Автор: @akhaustov
Centos-admin.ru
рейтинг 239,14
Администрирование нагруженных серверов

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

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

Самое читаемое Разработка