СУБД PostgreSQL

Основной файл настройки сервера СУБД PostgreSQL называется postgresql.conf . Указанные в этом файле параметры влияют на всю производительность сервера баз данных.

Расположение конфигурационного СУБД PostgreSQL в ОС Windows

{Installdir}:\PostgreSQL\data

Расположение конфигурационного СУБД PostgreSQL в ОС Linux

Рекомендации по настройке СУБД

Основные правила

Для SetCentrum10 рекомендуется размещать файлы БД и файлы логов транзакций на отдельные физические диски, используемые только для этих целей.

Чтобы логи транзакций находились на отдельном диске необходимо в ОС создать символическую ссылку папки /data/pg_xlog на отдельный диск.

Основные параметры postgresql.conf

• max_connection - количество одновременных соединений к базе, в зависимости от количества касс этот параметр варьируется 110, 210, 1000;
• shared_buffers - оперативная память под кеширование данных сервера (вне коннектов пользователей) (=¼ X);
• effective_cache_size - размер доступного места на диске под нужды сервера (=¾ X);
• checkpoint_segments - количество файлов транзакций (16Mb каждый), после заполнения которых будет сделана точка восстановления БД (checkpoint);
• checkpoint_completion_target - процент заполнения текущего чекпоинта до старта следующего;
• default_statistic_target - количество записей, на основе которых высчитывается статистика для построения запросов (лучше оставить дефолтные 100)
• work_mem - размер для сортировки в ОП. Указывается для 1 сортировки 1 пользовательского запроса, тогда появится 10 одновременных запросов и потребуется 10* work_mem памяти (увеличивать очень осторожно, иначе может не хватить памяти).
• maintenance_work_mem - размер для технических операций (типа VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY).
  • Такие операции выполняются в 1 поток синхронно.
  • Увеличение размера способствует ускорению операций DDL, VACUUM.

Параметр shared_buffers

• Рекомендуется установить равным 25% от общего размера оперативной памяти

• Учитывайте дальнейшую настройку сервера с учётом того, что минимум 25% надо оставить свободной для работы shared_buffers сервера Postgres.

• При изменении данного параметра обязательно скорректируйте настройки effective_cache_size, work_mem и maintenance_work_mem согласно сайзингу вашего сервера.

Параметр effective_cache_size

Не выделяет память, это лишь указание оптимизатору запросов о количестве оперативной памяти используемой в ОС для кэша файловой системы.

3 конфигурации сервера СУБД PostrgeSQL по умолчанию

Небольшой магазин. 4GB RAM (JVM - 2 GB, PG - 1.5GB, 100 connections)

• max_connections = 110
• max_prepared_transactions = 110
• shared_buffers = 256MB
• effective_cache_size = 2000MB
• work_mem = 10MB
• maintenance_work_mem = 64MB
• max_wal_size = 1GB # for version 10+ only
• random_page_cost = 1.4 # for SSD discs only
• checkpoint_completion_target = 0.9
• autovacuum_max_workers = 3
• autovacuum_naptime = 30s
• autovacuum_vacuum_scale_factor = 0.02
• autovacuum_analyze_scale_factor = 0.01
• autovacuum_vacuum_cost_limit = 1000
• logging_collector = on
• log_checkpoints = on
• log_min_duration_statement = 1s
• log_temp_files=0
• log_lock_waits = on
• log_statement = 'ddl'
• log_line_prefix = '%t %a %p %u@%d from %h [vxid:%v txid:%x] [%i]'
• log_filename = 'postgresql-%Y-%m-%d.log'
• log_rotation_age = 1d
• log_rotation_size = 0

Большой магазин. 8GB RAM (JVM - 4 GB, PG - 3GB, 200 connections)

• max_connections = 210
• max_prepared_transactions = 210
• shared_buffers = 1024MB
• effective_cache_size = 4000MB
• work_mem = 10MB
• maintenance_work_mem = 64MB
• max_wal_size = 1GB # for version 10+ only
• random_page_cost = 1.4 # for SSD discs only
• checkpoint_completion_target = 0.9
• autovacuum_max_workers = 3
• autovacuum_naptime = 30s
• autovacuum_vacuum_scale_factor = 0.02
• autovacuum_analyze_scale_factor = 0.01
• autovacuum_vacuum_cost_limit = 1000
• logging_collector=on
• log_checkpoints = on
• log_min_duration_statement = 1s
• log_temp_files=0
• log_lock_waits = on
• log_statement = 'ddl'
• log_line_prefix = '%t %a %p %u@%d from %h [vxid:%v txid:%x] [%i]'
• log_filename = 'postgresql-%Y-%m-%d.log'
• log_rotation_age = 1d
• log_rotation_size = 0'

Торговая сеть. 16GB RAM (JVM - 6 GB, PG - 6GB, 500 connections)

• max_connections = 510
• max_prepared_transactions = 510
• shared_buffers = 4000MB
• effective_cache_size = 8000MB
• work_mem = 10MB
• maintenance_work_mem = 64MB
• max_wal_size = 1GB # for version 10+ only
• random_page_cost = 1.4 # for SSD discs only
• checkpoint_completion_target = 0.9
• autovacuum_max_workers = 3
• autovacuum_naptime = 30s
• autovacuum_vacuum_scale_factor = 0.02
• autovacuum_analyze_scale_factor = 0.01
• autovacuum_vacuum_cost_limit = 1000
• logging_collector=on
• log_checkpoints = on
• log_min_duration_statement = 1s
• log_temp_files=0
• log_lock_waits = on
• log_statement = 'ddl'
• log_line_prefix = '%t %a %p %u@%d from %h [vxid:%v txid:%x] [%i]'
• log_filename = 'postgresql-%Y-%m-%d.log'
• log_rotation_age = 1d
• log_rotation_size = 0

SetRetail10 (JBOSS-сервер)

Про настройки службы описано в статье