From 0fb0d1e580af4a415510e8f51419683f47ea5113 Mon Sep 17 00:00:00 2001 From: Guillaume Pellerin Date: Wed, 19 Jan 2022 23:39:19 +0100 Subject: [PATCH] improve postgres DB conf --- env/postgresql.conf | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) diff --git a/env/postgresql.conf b/env/postgresql.conf index 72618547..c04f6bb2 100644 --- a/env/postgresql.conf +++ b/env/postgresql.conf @@ -61,7 +61,7 @@ listen_addresses = '*' # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart) -max_connections = 100 # (change requires restart) +max_connections = 2000 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories # (change requires restart) @@ -118,7 +118,7 @@ max_connections = 100 # (change requires restart) # - Memory - -shared_buffers = 8GB # min 128kB +shared_buffers = 25GB # min 128kB # (change requires restart) #huge_pages = try # on, off, or try # (change requires restart) @@ -127,7 +127,7 @@ shared_buffers = 8GB # min 128kB # (change requires restart) # Caution: it is not advisable to set max_prepared_transactions nonzero unless # you actively intend to use prepared transactions. -work_mem = 20971kB # min 64kB +work_mem = 3276kB # min 64kB #hash_mem_multiplier = 1.0 # 1-1000.0 multiplier on hash table work_mem maintenance_work_mem = 2GB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem @@ -174,13 +174,13 @@ dynamic_shared_memory_type = posix # the default is the first option # - Asynchronous Behavior - -effective_io_concurrency = 512 # 1-1000; 0 disables prefetching +effective_io_concurrency = 200 # 1-1000; 0 disables prefetching #maintenance_io_concurrency = 10 # 1-1000; 0 disables prefetching -max_worker_processes = 32 # (change requires restart) +max_worker_processes = 30 # (change requires restart) max_parallel_maintenance_workers = 4 # taken from max_parallel_workers max_parallel_workers_per_gather = 4 # taken from max_parallel_workers #parallel_leader_participation = on -max_parallel_workers = 32 # maximum number of max_worker_processes that +max_parallel_workers = 30 # maximum number of max_worker_processes that # can be used in parallel operations #old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate # (change requires restart) @@ -390,7 +390,7 @@ random_page_cost = 1.1 # same scale as above #min_parallel_table_scan_size = 8MB #min_parallel_index_scan_size = 512kB -effective_cache_size = 24GB +effective_cache_size = 75GB # - Genetic Query Optimizer - -- 2.39.5