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

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

MySQL

Для оптимизации mysql существует простая и удобная в использовании утилита mysqltuner.
Раздобыть ее в безвозмездное пользование можно на github, а именно тут. А еще проще загрузить одной легко запоминающейся командой:

wget 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.

Буду рад любым вопросам / замечаниям / дополнениям!