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

Материал из largespace
Перейти к навигации Перейти к поиску
Нет описания правки
Нет описания правки
 
(не показаны 33 промежуточные версии этого же участника)
Строка 7: Строка 7:
!DISK
!DISK
!Linux
!Linux
!1C
!PG_version
!PG_version
!Тест Гилева до
!Тест Гилева до
Строка 17: Строка 18:
|NVMe 500 GB
|NVMe 500 GB
|Green Linux 21.1
|Green Linux 21.1
|8.3.23.1596
|14
|14
|19,63
|19,63
Строка 38: Строка 40:
wal_sync_method = fdatasync
wal_sync_method = fdatasync
|24.39
|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
|-
|Bare metal
|AMD Ryzen 7 2700x (8 cores)
|32 GB
|NVMe 500 GB
|Green Linux 21.1
|8.3.24.1106
|12
|23.26
|shared_buffers=8GB
seq_page_cost = 1.0
random_page_cost = 0.4
effective_io_concurrency = 200
effective_cache_size = 16GB
work_mem = 128MB
row_security = off
temp_buffers = 128MB
ssl = off
cpu_operator_cost = 0.00025
|TPC 26.46
G1C 68 MB, 161 user
|-
|-
|VM ESXi
|VM ESXi
Строка 44: Строка 105:
|SSD 480 GB
|SSD 480 GB
|Linux Mint 20.3
|Linux Mint 20.3
|8.3.22.1750
|12
|12
|n\a
|19.08
|shared_buffers = 8GB
|shared_buffers = 8GB
tem_buffers = 64MB
temp_buffers = 64MB
 
work_mem = 128MB
work_mem = 128MB
wal_sync_method = fdatasync
wal_sync_method = fdatasync
effective_io_concurrency = 200
effective_io_concurrency = 200
synchronous_commit = off
synchronous_commit = off
wal_buffers = 64MB
wal_buffers = 64MB
random_page_cost = 0.5
random_page_cost = 0.5
effective_cache_size = 16GB
effective_cache_size = 16GB
row_security = off
row_security = off
ssl = off
ssl = off
checkpoint_completion_target = 0.9
checkpoint_completion_target = 0.9
|21.01
|21.19
|-
|-
|VM ESXi
|VM ESXi
Строка 65: Строка 137:
|RAID 10 1 TB
|RAID 10 1 TB
|Linux Mint 21
|Linux Mint 21
|8.3.22.1709
|12
|12
|n\a
|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 ESXi
|Intel core I5-8400 (6 cores)
|32GB
|SSD 256GB
|Astra linux 1.7.3
|8.3.22.1923
|12
|34.25
|shared_buffers = 8GB
temp_buffers = 128MB
 
work_mem = 128MB
 
effective_io_concurrency = 200
 
synchronous_commit = off
 
random_page_cost = 0.5
 
effective_cache_size = 12GB
 
row_security = off
 
ssl = off
 
cpu_operator_cost = 0.00025
|37.04 TPC
147 MB G1C
105 users
|-
|-
|VM Proxmox
|VM Proxmox
Строка 75: Строка 251:
|SSD 240 GB
|SSD 240 GB
|Astra Linux 1.7.2
|Astra Linux 1.7.2
|8.3.22.1709
|12
|12
|26,18
|26,18
|shared_buffers = 3GB
|shared_buffers = 3GB
temp_buffers = 32MB
temp_buffers = 32MB
work_mem = 64MB
work_mem = 64MB
effective_io_concurrency = 200
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
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
random_page_cost = 0.5
seq_page_cost = 1
cpu_operator_cost = 0.0025
wal_sync_method = fdatasync
checkpoint_completion_target = 0.9
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
|shared_buffers = 6GB
temp_buffers = 128MB
work_mem = 128MB
effective_cache_size = 8GB
effective_io_concurrency = 200
row_security = off
ssl = off
synchronous_commit = off
random_page_cost = 0.5
stats_temp_directory = '/var/lib/pgsql_stats_tmp'
from_collapse_limit = 8
join_collapse_limit = 1
min_wal_size = 512MB
max_wal_size = 1GB
|30 TPC
106 MB G1C
126 users
|-
|VM Proxmox
|AMD Ryzen 5 5600G (6 cores)
|36GB
|NVMe 600GB
|Astra linux 1.7.3
|8.3.22.1923
|12
|21.55
|shared_buffers = 9GB
temp_buffers = 128MB
work_mem = 128MB
effective_cache_size = 18GB
effective_io_concurrency = 200
row_security = off
ssl = off
ssl = off
synchronous_commit = off
random_page_cost = 0.5
wal_sync_method = fdatasync
|38.46 TPC
145 MB G1C
140 users
|-
|VM Proxmox
|AMD Ryzen 7 5800X (8 cores)
|24GB
|SSD 450GB
|Astra linux 1.7.3
|8.3.22.1923
|12
|45,8
|shared_buffers = 6GB
temp_buffers = 128MB
work_mem = 128MB
effective_io_concurrency = 200
row_security = off
ssl = off
synchronous_commit = off
random_page_cost = 1.1
effective_cache_size = 12GB
wal_sync_method = fdatasync
|53,75 TPC
175 MB G1C
133 users
|-
|VM Proxmox
|AMD Ryzen 7 7700X (8 cores)
|32GB
|SSD 400GB
|Astra linux 1.7.3
|8.3.22.1923
|12
|23,15
|shared_buffers = 8GB
temp_buffers = 128MB
work_mem = 128MB
effective_io_concurrency = 200
row_security = off
row_security = off
ssl = off
synchronous_commit = off
random_page_cost = 1.1
effective_cache_size = 16GB
wal_sync_method = fdatasync
wal_sync_method = fdatasync
|
|71,43 TPC
252 MB G1C
 
122 users
|-
|VM Proxmox
|AMD Ryzen 7 7700X (8 cores)
|32GB
|SSD 750GB
|Astra linux 1.7.4
|8.3.22.2143
|pgpro-std-15
|27
|row_security = off
ssl = off
 
synchronous_commit = off
|76,92 TPC
243 MB G1C
 
147 users
|-
|VM Proxmox
|AMD Ryzen 9 7950X (16 cores)
|32GB
|NVMe 400GB
|Astra linux 1.7.4
|8.3.24.1106
|pgpro-std-15
|27
|row_security = off
ssl = off
 
synchronous_commit = off
|80,65 TPC
257 MB G1C
 
119 users
|}
|}

Текущая версия от 15:35, 25 июля 2023

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

Тип железа 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
Bare metal AMD Ryzen 7 2700x (8 cores) 32 GB NVMe 500 GB Green Linux 21.1 8.3.24.1106 12 23.26 shared_buffers=8GB

seq_page_cost = 1.0

random_page_cost = 0.4

effective_io_concurrency = 200

effective_cache_size = 16GB

work_mem = 128MB

row_security = off

temp_buffers = 128MB

ssl = off

cpu_operator_cost = 0.00025

TPC 26.46

G1C 68 MB, 161 user

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 ESXi Intel core I5-8400 (6 cores) 32GB SSD 256GB Astra linux 1.7.3 8.3.22.1923 12 34.25 shared_buffers = 8GB

temp_buffers = 128MB

work_mem = 128MB

effective_io_concurrency = 200

synchronous_commit = off

random_page_cost = 0.5

effective_cache_size = 12GB

row_security = off

ssl = off

cpu_operator_cost = 0.00025

37.04 TPC

147 MB G1C 105 users

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 shared_buffers = 6GB

temp_buffers = 128MB

work_mem = 128MB

effective_cache_size = 8GB

effective_io_concurrency = 200

row_security = off

ssl = off

synchronous_commit = off

random_page_cost = 0.5

stats_temp_directory = '/var/lib/pgsql_stats_tmp'

from_collapse_limit = 8

join_collapse_limit = 1

min_wal_size = 512MB

max_wal_size = 1GB

30 TPC

106 MB G1C 126 users

VM Proxmox AMD Ryzen 5 5600G (6 cores) 36GB NVMe 600GB Astra linux 1.7.3 8.3.22.1923 12 21.55 shared_buffers = 9GB

temp_buffers = 128MB

work_mem = 128MB

effective_cache_size = 18GB

effective_io_concurrency = 200

row_security = off

ssl = off

synchronous_commit = off

random_page_cost = 0.5

wal_sync_method = fdatasync

38.46 TPC

145 MB G1C 140 users

VM Proxmox AMD Ryzen 7 5800X (8 cores) 24GB SSD 450GB Astra linux 1.7.3 8.3.22.1923 12 45,8 shared_buffers = 6GB

temp_buffers = 128MB

work_mem = 128MB

effective_io_concurrency = 200

row_security = off

ssl = off

synchronous_commit = off

random_page_cost = 1.1

effective_cache_size = 12GB

wal_sync_method = fdatasync

53,75 TPC

175 MB G1C

133 users

VM Proxmox AMD Ryzen 7 7700X (8 cores) 32GB SSD 400GB Astra linux 1.7.3 8.3.22.1923 12 23,15 shared_buffers = 8GB

temp_buffers = 128MB

work_mem = 128MB

effective_io_concurrency = 200

row_security = off

ssl = off

synchronous_commit = off

random_page_cost = 1.1

effective_cache_size = 16GB

wal_sync_method = fdatasync

71,43 TPC

252 MB G1C

122 users

VM Proxmox AMD Ryzen 7 7700X (8 cores) 32GB SSD 750GB Astra linux 1.7.4 8.3.22.2143 pgpro-std-15 27 row_security = off

ssl = off

synchronous_commit = off

76,92 TPC

243 MB G1C

147 users

VM Proxmox AMD Ryzen 9 7950X (16 cores) 32GB NVMe 400GB Astra linux 1.7.4 8.3.24.1106 pgpro-std-15 27 row_security = off

ssl = off

synchronous_commit = off

80,65 TPC

257 MB G1C

119 users

Источник — https://wiki.largespace.ru/index.php?title=Кейсы_оптимальных_конфигураций_производительности_PostgreSQL_Linux&oldid=1028