Кейсы оптимальных конфигураций производительности 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
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	53,2 TPC 174MB G1C 133 users