Кейсы оптимальных конфигураций производительности PostgreSQL Linux
Кейсы оптимальных конфигураций производительности
| Тип железа | 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 |