СУБД 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

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

Сервер магазина SetRetail

Магазин у дома", до 4 касс, 6 GB RAM на сервере

max_connections = 154
max_prepared_transactions = 154
work_mem = 7MB
maintenance_work_mem = 64MB
shared_buffers = 819MB
effective_cache_size = 1229MB
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1
max_parallel_workers = 8
max_worker_processes = 8
max_wal_size = 1GB
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 = 60s
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

Супермаркет, до 15 касс, 6 GB RAM на сервере

max_connections = 165
max_prepared_transactions = 165
work_mem = 10MB
maintenance_work_mem = 64MB
shared_buffers = 1229MB
effective_cache_size = 1843MB
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1
max_parallel_workers = 8
max_worker_processes = 8
max_wal_size = 1GB
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 = 60s
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

Гипермаркет, до 40 касс, 8 GB RAM на сервере

max_connections = 190
max_prepared_transactions = 190
work_mem = 12MB
maintenance_work_mem = 64MB
shared_buffers = 1638MB
effective_cache_size = 2458MB
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1
max_parallel_workers = 8
max_worker_processes = 8
max_wal_size = 1GB
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 = 60s
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

Гипермаркет, до 120 касс, 16 GB RAM на сервере

max_connections = 270
max_prepared_transactions = 270
work_mem = 16MB
maintenance_work_mem = 128MB
shared_buffers = 3277MB
effective_cache_size = 4915MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 12
max_worker_processes = 12
max_wal_size = 1GB
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 = 60s
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

Сервер SetCentrum для центрального офиса

До 10 серверов магазинов или до 10 касс, 8 GB RAM на сервере

max_connections = 160
max_prepared_transactions = 160
work_mem = 14MB
maintenance_work_mem = 64MB
shared_buffers = 1638MB
effective_cache_size = 2458MB
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1
max_parallel_workers = 8
max_worker_processes = 8
max_wal_size = 1GB
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 = 60s
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

До 50 серверов магазинов или до 50 касс, 12 GB RAM на сервере

max_connections = 200
max_prepared_transactions = 200
work_mem = 17MB
maintenance_work_mem = 64MB
shared_buffers = 2458MB
effective_cache_size = 3686MB
max_parallel_workers_per_gather = 1
max_parallel_maintenance_workers = 1
max_parallel_workers = 8
max_worker_processes = 8
max_wal_size = 1GB
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 = 60s
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

До 100 серверов магазинов или до 100 касс, 16 GB RAM на сервере

max_connections = 250
max_prepared_transactions = 250
work_mem = 17MB
maintenance_work_mem = 128MB
shared_buffers = 3277MB
effective_cache_size = 4915MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 12
max_worker_processes = 12
max_wal_size = 1GB
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 = 60s
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

До 200 серверов магазинов или до 200 касс, 24 GB RAM на сервере

max_connections = 350
max_prepared_transactions = 350
work_mem = 19MB
maintenance_work_mem = 128MB
shared_buffers = 4915MB
effective_cache_size = 7373MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 12
max_worker_processes = 12
max_wal_size = 1GB
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 = 60s
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

Сервер SetCentrum для центрального офиса (размер сети от 200 касс)

До 300 серверов магазинов или до 300 касс, 24 GB RAM на сервере

max_connections = 450
max_prepared_transactions = 450
work_mem = 15MB
maintenance_work_mem = 128MB
shared_buffers = 4915MB
effective_cache_size = 7373MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 12
max_worker_processes = 12
max_wal_size = 1GB
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 = 60s
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

До 400 серверов магазинов или до 400 касс, 32 GB RAM на сервере

max_connections = 550
max_prepared_transactions = 550
work_mem = 16MB
maintenance_work_mem = 256MB
shared_buffers = 6554MB
effective_cache_size = 9830MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 16
max_worker_processes = 16
max_wal_size = 1GB
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 = 60s
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

До 500 серверов магазинов или до 500 касс, 32 GB RAM на сервере

max_connections = 650
max_prepared_transactions = 650
work_mem = 13MB
maintenance_work_mem = 256MB
shared_buffers = 6554MB
effective_cache_size = 9830MB
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 16
max_worker_processes = 16
max_wal_size = 1GB
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 = 60s
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

Выделенный сервер Postgres для центрального офиса (размер сети от 500 касс)

До 1000 серверов магазинов или до 1000 касс, 96 GB RAM на сервере

max_connections = 1150
max_prepared_transactions = 1150
work_mem = 47MB
maintenance_work_mem = 1024MB
shared_buffers = 39322MB # рекомендуется включить huge_pages. Для этого требуются дополнительные настройки в ОС
effective_cache_size = 58982MB
max_parallel_workers_per_gather = 3
max_parallel_maintenance_workers = 3
max_parallel_workers = 24
max_worker_processes = 24
max_wal_size = 1GB
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 = 60s
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

До 1500 серверов магазинов или до 1500 касс, 128 GB RAM на сервере

max_connections = 1650
max_prepared_transactions = 1650
work_mem = 45MB
maintenance_work_mem = 1024MB
shared_buffers = 52429MB # рекомендуется включить huge_pages. Для этого требуются дополнительные настройки в ОС
effective_cache_size = 78643MB
max_parallel_workers_per_gather = 3
max_parallel_maintenance_workers = 3
max_parallel_workers = 24
max_worker_processes = 24
max_wal_size = 1GB
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 = 60s
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

До 2000 серверов магазинов или до 2000 касс, 192 GB RAM на сервере

max_connections = 2150
max_prepared_transactions = 2150
work_mem = 52MB
maintenance_work_mem = 1024MB
shared_buffers = 78643MB # рекомендуется включить huge_pages. Для этого требуются дополнительные настройки в ОС
effective_cache_size = 117965MB
max_parallel_workers_per_gather = 3
max_parallel_maintenance_workers = 3
max_parallel_workers = 24
max_worker_processes = 24
max_wal_size = 1GB
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 = 60s
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 (WildFly-сервер)

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

Скрипт настройки PostgreSQL под системные требования

confluence.macros.advanced.include.unable-to-render Включенная страница не может быть найдена.

Браузер не поддерживается