2011-03-31

Оптимизация производительности PostgreSQL

Появилась у нас в конторе мегапрограмма по оперативному учету. Программеры ее уже год как ваяют, практически все работники конторы в ней заняты, без нее в облэнерго сейчас жизни нет. Не работает программа - контора в определенном смысле парализована.
Сейчас в системе около 700 пользователей, при этом активных бывает до 100 рыл.
Естественно вопрос производительности сейчас первостепенный (если считать с нуля, то задача номер 0 отказоустойчивость будет решена в ближайшее время). Моя задача как админа выжать все возможное из базы данных. Это PostgreSQL версии 8.2, с которого сейчас осуществляется тестовая миграция на 9.0.
Здесь будет приведен файл параметров postgresql.conf с расширенными комментариями по каждому пункту.
Установка каждого параметра выбиралась исходя из существующего опыта эксплуатации, исследования открытых источников, как минимум 3-х, официальной документации, а также тестов производительности приложения на сервере в максимально идентичными настройками БД 8.2. и 9.0.


#########################################################
# Файл параметров PostgreSQL postgresql.conf
# Попытка заставить базу EnergyNET летать соколом.
# Указаные настройки подразумевают, что на сервере, где
# крутится PostgreSQL никого кроме него нет, не считая
# операционной системы.
#########################################################

# shared_buffers
#
# Смысл параметра: PostgreSQL не читает данные напрямую
# с диска и не пишет их сразу на диск. Данные загружаются
# в общий буфер сервера, находящийся в разделяемой памяти,
# серверные процессы читают и пишут блоки в этом буфере,
# а затем уже изменения сбрасываются на диск.
# Если процессу нужен доступ к таблице, то он сначала
# ищет нужные блоки в общем буфере. Если блоки
# присутствуют, то он может продолжать работу, если
# нет — делается системный вызов для их загрузки.
# Загружаться блоки могут как из файлового кэша ОС, так и
# с диска, и эта операция может оказаться весьма «дорогой».
# Если объём буфера недостаточен для хранения часто
# используемых рабочих данных, то они будут постоянно
# писаться и читаться из кэша ОС или с диска, что крайне
# отрицательно скажется на производительности.
# Принцип таков, что значение параметра устанавливаем
# в размере 25-40% от оперативной памяти. От версии
# PostgreSQL не зависит.
#
shared_buffers = 500MB # 2GB ОЗУ
#shared_buffers = 3000MB # 12GB ОЗУ

# temp_buffers
#
# Смысл параметра: Максимальное количество памяти, выделяемой
# каждой сессии для работы с временными таблицами.
# Необходимо помнить, что как только сессия заняла размер,
# указанный в temp_buffers, эта память не освобождается,
# пока сессия не завершится. На системах с большим
# количеством одновременно работающих пользователей
# некорректное (завышенное) значение этого параметра
# запросто может привести к бешеному свопу, и как
# следствие, краху операционной системы. А если при
# этом еще и fsync = off, то готовьтесь восстанавливать
# базу из резервных копий.
# У нас временные таблицы не используются, так что
# можно оставить значение по умолчанию. То есть
# вообще удалить из postgresql.conf
#
temp_buffers = 8MB

# max_prepared_transactions
#
# Смысл параметра: устанавливает максимальное количество
# PREPARED TRANSACTION которые могут устанавливаться в
# состояние prepared единовременно.
# У нас 98% всех транзакций - PREPARED.
# Минимально необходимое значение параметра = max_connections
#
max_prepared_transactions = 600

# work_mem
#
# Смысл параметра: Задаёт объём памяти, который используют
# внутренние операции сортировки и хэш-таблицы перед тем
# как перейти на использование временных файлов на диске.
# Если объём памяти недостаточен для сортироки некоторого
# результата, то серверный процесс будет использовать
# временные файлы. Если же объём памяти слишком велик,
# то это может привести к своппингу.
# В сложном запросе параллельно может быть запущено несколько
# операций сортировки и хэширования, и каждая может занять
# столько памяти, сколько задано в этом параметре, прежде
# чем начнет использовать временные файлы. Кроме того, эти
# операции могут одновременно выполняться в нескольких сессиях.
# Таким образом, используемая память может оказаться в несколько
# раз больше, чем work_mem. Операции сортировки используются
# для ORDER BY, DISTINCT и операций соединения методом
# слияния (merge joins). Хэш-таблицы используются при
# операциях соединения методом хэширования (hash joins),
# операциях аггрегирования, основанных на хэшировании и
# обработке IN-подзапросов на основе хэширования.
# Своими словами: если некая операция из перечисленных выше требует
# для выполнения больше памяти, чем указано в параметре work_mem,
# для этой операции будет создан временный файл в каталоге pgsql_tmp
# соответствующей БД. Так что оценить, какой объем необходимо задать
# данному параметру можно просто влянув в этот каталог в пиковые
# часы нагрузки. Если суммарный объем созданных там файлов поместится
# в оперативную память и останется хотя-бы 30% резерва, с учетом
# остальных работающих на сервере задач, то можно задавать значение, равное
# размеру примерно одинаковых по объему файлов. Тут конечно тоже без
# фанатизма, ибо если временный файл больше 100 метров, то нужно
# трижды подумать...
#
work_mem = 32MB

# autovacuum
#
# Смысл простой - выполнение процессов VACUUM в автоматическом
# режиме во время работы сервера.
# У нас VACUUM и ANALYZE будут выполняться ночью
# через CRON, так что включать автовакуум не будем.
# Ну незачем нам еще и в рабочее время систему
# нагружать некритичными вещами.
#
autovacuum = off

# effective_io_concurrency
#
# Смысл параметра: Задаёт число операций чтения/записи на
# диск, которые по мнению PostgreSQL могут выполняться
# одновременно. Увеличение этого значения может повысить
# число операций ввода/вывода, которое любая сессия
# PostgreSQL может попытаться выполнить параллельно.
# Допустимые значения лежат в диапазоне от 1 до 1000,
# либо 0, если вы хотите запретить выполнение асинхронных
# операций ввода/вывода.
# Для оценки необходимого значения этого параметра можно
# начать с числа отдельных устройств, включая RAID 0
# или RAID 1, используемых базой данных. Для RAID 5
# чётность устройств не учитывается. Однако, если база
# данных часто занята множеством запросов, получаемых от
# различных одновременных сессий, даже низкие значения
# могут держать занятым дисковый массив. Значение выше
# необходимого для поддержания занятости дисков приведёт
# только к дополнительной загрузке процессора.
# По простому, ставим значение параметра в число,
# равное количеству шпинделей в дисках, на
# которых расположена база данных. Потом пробуем уменьшить
# или увеличить, и оцениваем результат при каждом изменении
# параметра. Вообще параметр новый, малоопробованый,
# рекомендаций мало. Так что будем тестировать.
#
effective_io_concurrency = 4 # 4 диска в RAID10

# fsync
#
# Смысл параметра: Данный параметр отвечает за сброс данных
# из кэша на диск при завершении транзакций. Если
# установить его значение fsync = off то данные не будут
# записываться на дисковые накопители сразу после завершения
# операций. Это может существенно повысить скорость
# операций insert и update, но есть риск повредить базу,
# если произойдет сбой (неожиданное отключение питания,
# сбой ОС, сбой дисковой подсистемы).
# Самый сука стремный параметр. Если есть возможность,
# ВСЕГДА используйте fsync = on. Если с производительностью
# жопа, то при значении off большую часть проблем можно
# порешать. Вот такая неоднозначная хрень. На винде
# желательно его не выключать в следствии глючности самой
# винды.
# Если fsync = off, то обязательно установить
# full_page_writes = off
# Если fsync = on, а с производительность проблемы, то
# лучше поиграться с synchronous_commit, но fsync не
# трогать.
#
fsync = on

# full_page_writes
#
# Смысл параметра: Если этот параметр включен, сервер
# PostgreSQL пишет весь контент каждой страницы диска в WAL
# во время первого изменения этой страницы после контрольной
# точки. Это необходимо, поскольку если в процессе записывания
# страницы произойдет системный сбой, на диске может оказаться
# страница, в которой смешаны старые и новые данные. Изменения
# на уровне строк данных, которые обычно хранятся в WAL, может
# быть не достаточно для восстановления страницы после
# системного сбоя. Хранение образа всей страницы гарантирует,
# что страница будет восстановлена правильно, но это будет
# стоить увеличения количества данных, которые нужно будет
# занести в WAL. (Так как чтение WAL всегда начинается с
# контрольной точки, удобно делать это при первом изменении
# каждой страницы после контрольной точки. Таким образом,
# одним из способов уменьшения стоимости записи страниц
# является увеличение интервала между контрольными точками.)
# Отключение этого параметра ускоряет работу, но может привести
# к повреждению базы данных в случае системного сбоя или
# отключения питания. Риски схожи с отключением fsync, хотя в
# данном случае они меньше.
#
full_page_writes=on

# synchronous_commit
#
# Смысл параметра: Определяет, должна ли транзакция ждать,
# пока записи WAL будут перенесены на диск, прежде чем команда
# вернёт клиенту сообщение об успешном выполнении. По
# умолчанию и с точки зрения безопасности этот параметр должен
# быть включен. Если параметр выключен, возможен промежуток
# времени между сообщением об успешном выполнении транзакции
# и моментом, когда транзакция на самом деле защищена от сбоя
# на сервере. В отличие от fsync, отключение этого параметра
# не может привести к риску противоречия в базе данных: сбой
# может привести к потере последних прошедших транзакций, но
# состояние базы данных при этом будет таким, как будто эти
# транзакции просто были прерваны. Таким образом, отключение
# synchronous_commit может оказаться полезным, если
# производительность важнее, чем точность в проведении
# транзакций.
#
synchronous_commit = off

effective_cache_size: указывает планировщику на размер самого большого объекта в базе данных, который теоретически может быть закеширован. На выделенном сервере имеет смысл выставлять effective_cache_size в 2/3 от всей оперативной памяти; на сервере с другими приложениями сначала нужно вычесть из всего объема RAM размер дискового кэша ОС и память, занятую остальными процессами.


Комментариев нет: