Кейсы оптимальных конфигураций производительности PostgreSQL Linux: различия между версиями
Irit (обсуждение | вклад) Нет описания правки |
Irit (обсуждение | вклад) Нет описания правки |
||
| Строка 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 | ||
| Строка 44: | Строка 46: | ||
|NVMe 500 GB | |NVMe 500 GB | ||
|Green Linux 21.1 | |Green Linux 21.1 | ||
|8.3.23.1596 | |||
|12 | |12 | ||
|23.26 | |23.26 | ||
| Строка 64: | Строка 67: | ||
wal_sync_method = fdatasync | wal_sync_method = fdatasync | ||
| | |||
cpu_operator_cost = 0.00025 | |||
|23.92 | |||
|- | |- | ||
|VM ESXi | |VM ESXi | ||
| Строка 71: | Строка 76: | ||
|SSD 480 GB | |SSD 480 GB | ||
|Linux Mint 20.3 | |Linux Mint 20.3 | ||
|8.3.22.1750 | |||
|12 | |12 | ||
|19.08 | |19.08 | ||
|shared_buffers = 8GB | |shared_buffers = 8GB | ||
temp_buffers = 64MB | |||
work_mem = 128MB | work_mem = 128MB | ||
| Строка 102: | Строка 108: | ||
|RAID 10 1 TB | |RAID 10 1 TB | ||
|Linux Mint 21 | |Linux Mint 21 | ||
|8.3.22.1709 | |||
|12 | |12 | ||
|n\a | |n\a | ||
| | | | ||
| | | | ||
|- | |||
|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 | |||
|37.04 | |||
|- | |- | ||
|VM Proxmox | |VM Proxmox | ||
| Строка 112: | Строка 141: | ||
|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 | ||
| Строка 124: | Строка 154: | ||
random_page_cost = 0.5 | random_page_cost = 0.5 | ||
seq_page_cost = 0.5 | |||
checkpoint_completion_target = 0.9 | checkpoint_completion_target = 0.9 | ||
| Строка 130: | Строка 162: | ||
row_security = off | row_security = off | ||
| | |||
cpu_operator_cost = 0.00025 | |||
wal_sync_method = fdatasync | |||
|30.12 | |||
|- | |||
|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 | |||
random_page_cost = 0.5 | |||
seq_page_cost = 0.5 | |||
cpu_operator_cost = 0.0025 | |||
wal_sync_method = fdatasync | |||
checkpoint_completion_target = 0.9 | |||
synchronous_commit = off | |||
|32.05 | |||
|} | |} | ||
Версия от 19:18, 12 марта 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 |
| 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 | ||
| 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 |
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 | 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 random_page_cost = 0.5 seq_page_cost = 0.5 cpu_operator_cost = 0.0025 wal_sync_method = fdatasync checkpoint_completion_target = 0.9 synchronous_commit = off |
32.05 |