Инструменты пользователя

Инструменты сайта


postgresql.conf

Различия

Здесь показаны различия между двумя версиями данной страницы.

Ссылка на это сравнение

postgresql.conf [2018/08/10 03:03] (текущий)
Строка 1: Строка 1:
 +====== postgresql.conf ======
 +~~Title: PostgreSql postgresql.conf ~~
 +{{htmlmetatags>​
 +metatag-description=(Соединения и аутентификация PostgreSql. Логирование PostgreSql (Error Reporting and Logging)
 +Настройка и использование Autovacuum Parameters. Тонкая настройка Tuning PostgreSQL)
 +}}
  
 +{{ ::​tuning-your-postgresql-database.jpg?​nolink |}}
 +
 +**Файл "​postgresql.conf"​ читается на запуске сервера PostgreSql, либо когда сервер получает сигнал # SIGHUP.**
 +
 +  * **Вывести настройки сервера PostgreSQL:​**<​file>​
 +SELECT pg_show_all_settings();​
 +SELECT name, source, setting, reset_val, context, category
 +FROM pg_show_all_settings() WHERE name = '​data_directory';</​file>​[[http://​www.postgresql.org/​docs/​current/​interactive/​sql-show.html|SHOW]]<​file>​
 +SHOW ALL;
 +SHOW shared_buffers;​
 +</​file><​file>​
 +SELECT ​ name, source, setting, reset_val, context FROM pg_settings;​
 +</​file>​Если в колонке context указано значение sighup -значит для изменения этого параметра сервер нужно перегрузить.
 +===== Соединения и аутентификация PostgreSql (Connections and Authentication) =====
 +[[http://​www.postgresql.org/​docs/​8.4/​static/​runtime-config-connection.html|Connections and Authentication]]
 +  * listen_addresses = '​*'​ - слушать на всех интерфейсах
 +  * max_connections = 50 - число клиентов,​ которые могут подсоединяться к базе данных одновременно не может быть бесконечным. Каждое подсоединение порождает ещё один процесс postmaster, что, естественно,​ требует ресурсов. Средней «паршивости» современный однопроцессорный компьютер со стандартным наполнении без особых проблем может обслуживать 100-200 соединений,​ но, например,​ 600 активных соединений будут уже явной проблемой. Любая попытка подсоединиться сверх указанного лимита приведёт к отказу от обслуживания. Плохо написанная программа в цикле открывающая,​ но не закрывающая за собой соединения,​ легко создаст проблему. Если число клиентов жёстко ограничено,​ то имеет смысл уменьшить этот параметр до минимально возможного значения.
 +  * superuser_reserved_connections = 3 Число соединений,​ которые зарезервированы для суперпользователя,​ чтобы он мог всегда зайти, разобраться в чём дело, а затем принять меры. Не стоит совсем отказываться зарезервированных соединений,​ причём одного зарезервированного соединения может оказаться не достаточно — 2 это минимум.
 +
 +{{ ::​database-configuring-postgresql-conf-for-remote-listening.png?​nolink&​600 |}}
 +===== Настройка потребление ресурсов PostgreSQL (Resource Consumption (Usage)) =====
 +[[http://​www.postgresql.org/​docs/​8.4/​static/​runtime-config-resource.html|Resource Consumption]]
 +Resource Consumption(Usage) - за исключением WAL (write ahead log).
 +<​file>​
 +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
 +</​file>​
 +  * **shared_buffers**. Объём совместно используемой памяти(разделяемой между процессами PostgreSQL памяти),​ выделяемой сервером PostgreSQL для кэширования данных,​ определяется числом страниц (shared_buffers) по 8 килобайт каждая. Естественно,​ данные умеет кэшировать не только сам PostgreSQL, но и операционная система сама по себе делает это очень неплохо. Поэтому нет необходимости отводить под кэш всю наличную оперативную память. Оптимальное число shared_buffers зависит от многих факторов,​ нужно учесть количество оперативной памяти компьютера,​ размер базы данных,​ число соединений и сложность запросов.
 +<note important>​Увеличение числа shared_buffers и других параметров памяти потребует изменения настроек разделяемой памяти ([[Shared memory]]) вашей операционной системы ([[http://​www.postgresql.org/​docs/​8.4/​interactive/​kernel-resources.html|Shared Memory and Semaphores]]). Параметр ядра Linux kernel.shmmax всегда должен быть больше параметра shared_buffers PostgreSQL. </​note>​
 +  * **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]], необходимое значение в столбце 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) =====
 +  * [[http://​www.postgresql.org/​docs/​current/​static/​runtime-config-logging.html|Error Reporting and Logging]]
 +<​file>​
 +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'​
 +</​file>​
 +
 +  * log_destination ​ куда сохранять информацию;​
 +  * logging_collector = on - разрешить сбор статистики. Информация об активности базы данных собирается в специальных системных таблицах;​
 +  * log_directory - каталог для сохранения журналов;​
 +  * log_filename - шаблон имени файла журнала;​
 +  * log_rotation_age - максимальное время жизни файла журнала. По его достижению будет начат новый файл;
 +  * 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 =====
 +<​file>​
 +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
 +</​file>​
 +
 +  * 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 Процент изменённых и удалённых записей по отношению к таблице по превышению которого запускается сборка мусора.
 +  * autovacuum_analyze_scale_factor = 0.1 То же, что и предыдущая переменная,​ но по отношению к анализу.
 +====== Тонкая настройка Tuning PostgreSQL ======
 +  * **Выбор сервера для PostgreSQL**
 +Как и для всякой программы,​ работающей с большим объёмом данных,​ дисковая подсистема является для PostgreSQL определяющей. Поэтому если есть возможность,​ то лучше вложиться именно в неё. В противоположность дисковой подсистеме процессор нагружается не очень сильно. Поэтому для сервера достаточно,​ чтобы процессор просто был, хотя лучше чтобы он был не один. К вопросу о дисках можно сказать,​ что чем их больше — тем лучше. По возможности следует выделить отдельный диск для журнала транзакций (pg_xlog).
 +Избыток дисков так же позволит собрать из них [[RAID]]. Хоть никто и не отменяет бэкапа,​ но дополнительная избыточность для дисковой подсистемы,​ как и источники бесперебойного питания,​ сэкономят массу сил и нервов. Относительно недорогие системы снабжены дешёвыми встроенными RAID-контроллерами на четыре диска. Не следует использовать эти контроллеры,​ а лучше настроить софтверный RAID и не надо использовать RAID 51 для небольшого числа дисков. До 6 дисков включительно лучше RAID 1+02 . Избыточность во всём — это только похоже на паранойю. На сервер,​ где уже работает PostgreSQL не следует «подселять» другие базы данных или программы,​ осуществляющие интенсивный обмен с дисковой памятью. А вот программы,​ которые вместо этого интенсивно используют процессор,​ например,​ [[Apache]], вполне уживаются если памяти достаточно.
 +  * Настройка ядра [[Linux]] и [[PostgreSQL]]
 +<​file>​
 +# 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
 +</​file>​
 +  * [[http://​www.postgresql.org/​docs/​8.1/​interactive/​kernel-resources.html|PostgreSQL Managing Kernel Resources]] приводятся рекомендации для [[sysctl]].
postgresql.conf.txt · Последние изменения: 2018/08/10 03:03 (внешнее изменение)