Тюнинг базы Postgres

Базы данных, как Вы возможно заметили, не ограничиваются на MySQL. Есть и другие! Кто-то выбирает для своих продуктов Postgres, и я этот выбор, в большинстве случаев, поддерживаю и считаю разумным. Для высоконагруженных систем Postgres имеет ряд преимуществ перед конкурентами, но об этом в другой раз.

И так, Вы поставили postgres, у Вас есть приложение (возможно в планах) с определенными требованиями к количеству/размеру таблиц, отношению записи/чтения и т.д. Стандартные настройки, как всегда подходят только для стандартных проектов, а стандартных проектов нет. Поэтому сервер придется настраивать под Ваши требования для обеспечения его оптимальной работы.

Для начала несколько слов о настройках Postgres. Конфигурационный файл обычно лежит по адресу:

/etc/postgresql/8.3/main/postgresql.conf

Естественно это пример для версии 8.3. Любые конфигурационные параметры принадлежат к различным уровням, список которых приведен ниже:

• Postmaster — требует перезапуска сервера
• Sighup — требует только сигнала HUP (работающий сервер перезагрузит конфигурационный файл без прекращения работы)
• User — значение может быть установлено в рамках сессии и актуально только внутри этой сессии
• Internal — устанавливает только во время компиляции, т.о. не изменяемо! (Только для справки)
• Backend — значение должно быть установлено до начала сессии
• Superuser — может быть изменено во время работы сервера, но только из под пользователя с правами superuser

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

На что стоит обратить внимание в настройках, и какие значения стоит поменять:

listen_addresses

По умолчанию Postgres принимает соединения только с локальных служб, т.к. слушает интерфейс localhost. Если Вы планируете сетевую подсистему, состоящую из более, чем одного компьютера (Ваш сервер баз данных будет находится на отдельном компьютере), Вам потребуется поменять этот параметр:

listen_addresses = '*'

Теперь postgres сможет принимать соединения от удаленных служб по протоколу TCP.

max_connections

Этот параметр определяет максимальное количество одновременных соединений, которые будет обслуживать сервер. В принципе, это число должно определяться исходя из требований к системе. Этот параметр в большей степени влияет на использование ресурсов. Если Вы только стартуете, устанавливайте это значение небольшим (16…32), постепенно увеличивая его (по мере необходимости — такой мерой будет получение ошибок от postgres “too many clients”).

Учтите! На поддержку каждого активного клиента, postgres тратит немалое количество ресурсов, и если Вам необходимо добиться производительности в несколько тысяч активных соединений, то стоит использовать менеджеры соединений, например: Pgpool.

shared_buffers

Этот параметр определяет, сколько памяти будет выделяться postgres для кеширования данных. В стандартной поставке значение этого параметра мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти.

effective_cache_size

Этот параметр помогает планировщику postgres определить количество доступной памяти для дискового кеширования. На основе того, доступна память или нет, планировщик будет делать выбор между использованием индексов и использованием сканирования таблицы. Это значение следует устанавливать в 50%…75% всей доступной оперативной памяти, в зависимости от того, сколько памяти доступно для системного кеша. Еще раз — этот параметр не влияет на выделяемые ресурсы – это оценочная информация для планировщика.

checkpoint_segments

На эту настройку стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация). Postgres записывает данные в базу данных порциями (WALL сегменты) — каждая размером в 16Mb. После записи определенного количества таких порций (определяется параметром checkpoint_segments) происходит чекпойнт. Чекпойнт — это набор операций, которые выполняет postgres для гарантии того, что все изменения были записаны в файлы данных (следовательно при сбое, восстановление происходит по последнему чекпойнту). Выполнение чекпоинтов каждые 16Мб может быть весьма ресурсоемким, поэтому это значение следует увеличить хотя бы до 10.

Для случаев с большим количеством записей, стоит увеличивать это значение в рамках от 32 до 256.

work_mem

Важный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение его позволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы). Будьте внимательны! Этот параметр указывает, сколько памяти выделять на каждую подобную операцию! Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной памяти. Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Для старта следует выставить его в 1Мб.

maintainance_work_mem

Этот параметр определяет количество памяти для различных статистических и управляющих процессов (например вакуумизация). Разработчики рекомендуют выделять 128…256Мб под эти нужды.

wal_buffers

Этот параметр стоит увеличивать в системах с большим количеством записей. Значение в 1Мб рекомендуют разработчики postgres даже для очень больших систем.

synchronous_commit

Обратите особое внимание на этот параметр! Он включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.

Допустим, в Вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы. Но жизненно важно обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Самое важное

Производительность Postgres можно значительно поднять, настроим несколько параметров. Убедитесь, что база данных подстроена под конфигурацию Вашего железа.