Просмотр кода страницы Кейсы оптимальных конфигураций производительности PostgreSQL Linux

Кейсы оптимальных конфигураций производительности
{| class="wikitable"
|+
!Тип железа
!CPU
!RAM
!DISK
!Linux
!1C
!PG_version
!Тест Гилева до
!Параметры оптимизации
!Тест Гилева после
|-
|Bare metal
|AMD Ryzen 7 2700x (8 cores)
|32 GB
|NVMe 500 GB
|Green Linux 21.1
|8.3.23.1596
|14
|19,63
|shared_buffers=8GB
seq_page_cost = 0.5

random_page_cost = 0.5

effective_io_concurrency = 500

effective_cache_size = 16GB

work_mem = 128MB

row_security = off

temp_buffers = 128MB

ssl = off

wal_sync_method = fdatasync
|24.39
|-
|Bare metal
|AMD Ryzen 7 2700x (8 cores)
|32 GB
|NVMe 500 GB
|Green Linux 21.1
|8.3.23.1596
|12
|23.26
|shared_buffers=8GB
seq_page_cost = 0.5

random_page_cost = 0.5

effective_io_concurrency = 500

effective_cache_size = 16GB

work_mem = 128MB

row_security = off

temp_buffers = 128MB

ssl = off

wal_sync_method = fdatasync

cpu_operator_cost = 0.00025
|23.92
|-
|VM ESXi
|Xeon Silver 4208 (8 cores)
|32 GB
|SSD 480 GB
|Linux Mint 20.3
|8.3.22.1750
|12
|19.08
|shared_buffers = 8GB
temp_buffers = 64MB

work_mem = 128MB

wal_sync_method = fdatasync

effective_io_concurrency = 200

synchronous_commit = off

wal_buffers = 64MB

random_page_cost = 0.5

effective_cache_size = 16GB

row_security = off

ssl = off

checkpoint_completion_target = 0.9
|21.19
|-
|VM ESXi
|Xeon E5-2643 v2 x2 (12 cores)
|48 GB
|RAID 10 1 TB
|Linux Mint 21
|8.3.22.1709
|12
|n\a
|random_page_cost = 1.3
seq_page_cost = 1.0

effective_io_concurrency = 200

ssl = off

row_security = off

synchronous_commit = off

shared_buffers = 8GB

effective_cache_size = 16GB

temp_buffers = 128MB

cpu_operator_cost = 0.001

work_mem = 64MB

checkpoint_completion_target = 0.9

max_wal_size = 2GB

min_wal_size = 512MB

wal_sync_method = fdatasync

wal_buffers = -1

maintenance_work_mem = 1GB

commit_delay = 2000
|26.04
|-
|VM ESXi
|Intel core I5-8400 (6 cores)
|32GB
|NVMe 500GB
|Linux Mint 20
|8.3.22.1709
|12
|34.01
|shared_buffers = 8GB
temp_buffers = 128MB

work_mem = 256MB

effective_io_concurrency = 500

synchronous_commit = off

seq_page_cost = 0.5

random_page_cost = 0.5

effective_cache_size = 12GB

row_security = off

ssl = off

cpu_operator_cost = 0.00025

checkpoint_completion_target = 0.9

maintenance_work_mem = 2GB

min_wal_size = 512MB

max_wal_size = 2GB

commit_delay = 1000
|37.04
|-
|VM Proxmox
|AMD Ryzen 7 2700x (8 cores)
|12 GB
|SSD 240 GB
|Astra Linux 1.7.2
|8.3.22.1709
|12
|26,18
|shared_buffers = 3GB
temp_buffers = 32MB

work_mem = 64MB

effective_io_concurrency = 200

synchronous_commit = off

random_page_cost = 0.5

seq_page_cost = 0.5

checkpoint_completion_target = 0.9

ssl = off

row_security = off

cpu_operator_cost = 0.00025

wal_sync_method = fdatasync
|30.12
|-
|VM Proxmox
|AMD Ryzen 7 2700x (8 cores)
|12 GB
|SSD 240GB
|Astra linux 1.7.2
|8.3.22.1709
|pgpro 15
|27.17
|shared_buffers = 3GB
temp_buffers = 128MB

effective_cache_size = 9GB

from_collapse_limit = 8

join_collapse_limit = 8

work_mem = 128MB

synchronous_commit = off

row_security = off

ssl = off

cpu_operator_cost = 0.00025

wal_sync_method= fdatasync

effective_io_concurrency = 200

random_page_cost = 0.4
|29.76
|-
|VM Proxmox
|Intel core I5-10600 (8 cores)
|32GB
|SSD 480GB
|Debian 11
|8.3.22.1709
|12
|25.51
|shared_buffers = 8GB
temp_buffers = 128MB

work_mem = 128MB

effective_cache_size = 16GB

ssl = off

row_security = off

effective_io_concurrency = 200

random_page_cost = 0.5

seq_page_cost = 1

cpu_operator_cost = 0.0025

wal_sync_method = fdatasync

checkpoint_completion_target = 0.9

synchronous_commit = off
|32.89
|-
|VM Proxmox
|Intel core I5 10600 (8 cores)
|24GB
|SSD 480GB
|Astra linux 1.7.2
|8.3.22.1709
|14
|20.49
|row_security = off
ssl = off

synchronous_commit = off

random_page_cost = 0.4

stats_temp_directory = '/var/lib/pgsql_stats_tmp'
|30.86
|}