postgresql.conf
Если в колонке context указано значение sighup -значит для изменения этого параметра сервер нужно перегрузить.
Соединения и аутентификация PostgreSql (Connections and Authentication)
max_connections = 50 — число клиентов, которые могут подсоединяться к базе данных одновременно не может быть бесконечным. Каждое подсоединение порождает ещё один процесс postmaster, что, естественно, требует ресурсов. Средней «паршивости» современный однопроцессорный компьютер со стандартным наполнении без особых проблем может обслуживать 100-200 соединений, но, например, 600 активных соединений будут уже явной проблемой. Любая попытка подсоединиться сверх указанного лимита приведёт к отказу от обслуживания. Плохо написанная программа в цикле открывающая, но не закрывающая за собой соединения, легко создаст проблему. Если число клиентов жёстко ограничено, то имеет смысл уменьшить этот параметр до минимально возможного значения.
superuser_reserved_connections = 3 Число соединений, которые зарезервированы для суперпользователя, чтобы он мог всегда зайти, разобраться в чём дело, а затем принять меры. Не стоит совсем отказываться зарезервированных соединений, причём одного зарезервированного соединения может оказаться не достаточно — 2 это минимум.
Настройка потребление ресурсов PostgreSQL (Resource Consumption (Usage))
Resource Consumption Resource Consumption(Usage) — за исключением WAL (write ahead log).
shared_buffers = 2GB # ~ 1/8 RAM and for Linux kernel.shmmax=4294967296 (1/4 of RAM) work_mem = 128MB # ~ 1/20 RAM maintenance_work_mem = 1GB # ~ 1/4 RAM effective_cache_size = 4GB # ~ 2/3 RAM max_prepared_transactions = 0 # zero disables the feature
shared_buffers. Объём совместно используемой памяти(разделяемой между процессами PostgreSQL памяти), выделяемой сервером PostgreSQL для кэширования данных, определяется числом страниц (shared_buffers) по 8 килобайт каждая. Естественно, данные умеет кэшировать не только сам PostgreSQL, но и операционная система сама по себе делает это очень неплохо. Поэтому нет необходимости отводить под кэш всю наличную оперативную память. Оптимальное число shared_buffers зависит от многих факторов, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов.
Увеличение числа shared_buffers и других параметров памяти потребует изменения настроек разделяемой памяти (Shared memory) вашей операционной системы (Shared Memory and Semaphores). Параметр ядра Linux kernel.shmmax всегда должен быть больше параметра shared_buffers PostgreSQL.
work_mem. Под каждый запрос можно выделить личный ограниченный объём памяти для работы. Этот объём может использоваться для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно замедлить скорость обработки запросов. Предел для work_mem можно вычислить, разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений. При необходимости, например, выполнения очень объёмных операций, допустимый лимит можно изменять прямо во время выполнения запроса. Поэтому нет нужды изначально задавать теоретический предел.
effective_cache_size Этот параметр сообщает PostgreSQL примерный объём файлового кэша операционной системы, оптимизатор использует эту оценку для построения плана каждого запроса. Объём задаётся параметром effective_cache_size в postgresql.conf. Единица измерения – блоки величиной 8 кБ. Например, пусть в вашем компьютере 1,5 ГБ памяти, параметр shared_buffers установлен в 32 МБ, а параметр effective_cache_size в 800 МБ. Если запросу нужно 700 МБ данных, то PostgreSQL оценит, что все нужные данные уже есть в памяти и выберет более агрессивный план с использованием индексов и merge joins. Но если effective_cache_size будет всего 200 МБ, то оптимизатор вполне может выбрать более эффективный для дисковой системы план, включающий полный просмотр таблицы. В качестве начального значения можете использовать 25-50% доступной памяти (т.е. не занятой операционной системой и приложениями). Этот параметр в ОС можно посмотреть в настройках: Для Windows: в Диспетчере задач, Закладка Быстродействие, Физическая память-Системный кэш. Для Linux: наберите команду free как правильно смотреть свободное ОЗУ Linux, необходимое значение в столбце cached (в kB) Данное значение необходимо разделить на количество конкурентных запросов в один момент времени (среднее количество подключений к базе + запас).
maintenance_work_mem. Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и для добавления внешних ключей (FOREGIN KEY). Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске. Как и в случае work_mem эта переменная может быть установлена прямо во время выполнения запроса.
max_prepared_transactions. Определяет максимальное число подготовленных транзакций (команда PREPARE TRANSACTION). Подготовленные транзакции выполняются, но результат их не будет доступен пока их не подтвердят (COMMIT). Так же можно такие транзакции и отклонить (ROLLBACK). Если эта сущность нигде не используется, то переменную можно занулить.
Логирование PostgreSql (Error Reporting and Logging)
logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_line_prefix = '%t db=%d user=%u ' #log_statement = 'all' log_statement = 'ddl' #log_statement = 'mod'
logging_collector = on — разрешить сбор статистики. Информация об активности базы данных собирается в специальных системных таблицах;
log_rotation_size — размер файла, по достижению которого будет начата запись в новый файл, если значение 0 — опция не используется;
log_statement — позволит выводить в лог все SQL запросы (по умолчанию отсутствует -none (off)), может принимать значения none (off), ddl, mod, и all (all statements). ddl будет выводить в лог CREATE, ALTER, DROP, automatic analyze значения. mod к выводу ddl будет добавлены INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM, PREPARE, EXECUTE, EXPLAIN ANALYZE.
Настройка и использование Autovacuum Parameters
autovacuum = on log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and autovacuum_max_workers = 3 autovacuum_naptime = 10min autovacuum_vacuum_threshold = 1800 autovacuum_analyze_threshold = 900 #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum = on Если Вы лучше чем PostgreSQL знаете когда следует производить сборку мусора, то автоматику можно выключить. Хотя лучше её просто правильно настроить. С другой стороны сборка мусора оттягивает на себя ресурсы системы и если это не допустимо, то её можно отложить на некоторое время. При настройке службы автоматической сборки мусора и анализа следует понимать, что один из зарезервированных с помощью superuser_reserved_connections слотов может оказаться в нужный момент занят.
log_autovacuum_min_duration = -1 Установка равная нулю регистрирует все действия autovacuum. Минус один (по умолчанию) запрещает вывод в лог. Например, если вы установите значение равное 250 мс, то все действия autovacuum и analyzes, которые работают 250 мс и более, будут заноситься в журнал. Включение этого параметра может быть полезно для отслеживания autovacuum. Этот параметр может быть установлен только в файле postgresql.conf или в командной строке сервера.
autovacuum_naptime = 10min Время в секундах через которое база данных проверяется на необходимость в сборке мусора. По умолчанию это происходит раз в минуту.
autovacuum_vacuum_threshold = 1800 Порог на число удалённых и изменённых записей в любой таблице по превышению которого происходит сборка мусора (VACUUM).
autovacuum_analyze_threshold = 900 Порог на число вставленных, удалённых и изменённых записей в любой таблице по превышению которого запускается процесс анализа (ANALYZE).
autovacuum_vacuum_scale_factor = 0.2 Процент изменённых и удалённых записей по отношению к таблице по превышению которого запускается сборка мусора.
Тонкая настройка Tuning PostgreSQL
Как и для всякой программы, работающей с большим объёмом данных, дисковая подсистема является для PostgreSQL определяющей. Поэтому если есть возможность, то лучше вложиться именно в неё. В противоположность дисковой подсистеме процессор нагружается не очень сильно. Поэтому для сервера достаточно, чтобы процессор просто был, хотя лучше чтобы он был не один. К вопросу о дисках можно сказать, что чем их больше — тем лучше. По возможности следует выделить отдельный диск для журнала транзакций (pg_xlog). Избыток дисков так же позволит собрать из них RAID. Хоть никто и не отменяет бэкапа, но дополнительная избыточность для дисковой подсистемы, как и источники бесперебойного питания, сэкономят массу сил и нервов. Относительно недорогие системы снабжены дешёвыми встроенными RAID-контроллерами на четыре диска. Не следует использовать эти контроллеры, а лучше настроить софтверный RAID и не надо использовать RAID 51 для небольшого числа дисков. До 6 дисков включительно лучше RAID 1+02 . Избыточность во всём — это только похоже на паранойю. На сервер, где уже работает PostgreSQL не следует «подселять» другие базы данных или программы, осуществляющие интенсивный обмен с дисковой памятью. А вот программы, которые вместо этого интенсивно используют процессор, например, Apache, вполне уживаются если памяти достаточно.
# echo "kernel.shmmax=2147483648" >> /etc/sysctl.conf # nano postgresql.conf shared_buffers = 1GB checkpoint_segments = 16 effective_cache_size = 512MB work_mem = 8MB maintenance_work_mem = 256MB