СУБД PostgreSQL

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

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

{Installdir}:\PostgreSQL\data

Open

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

Open

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

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

Для 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 памяти (

  Open

  

  увеличивать очень осторожно, иначе может не хватить памяти).

• 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