Alterations Made for 2021 Dedicated OPS Server - CReSIS/OLD_OPS GitHub Wiki

postgresql.conf

The postgresql.conf file specifies config options for the postgres database (such as allocated memory). It lives in the data dir at /db/pgsql/12/postgresql.conf and is found automatically by postgres. The version of this file used in virtual boxes is present in this repo under conf/configs/postgresql.conf. That file is used to overwrite the default in the data dir during provisioning.

The config file for the dedicated OPS server makes the following changes from the default config:

@@ -118,17 +118,17 @@ max_connections = 100                     # (change requires restart)

 # - Memory -

-shared_buffers = 128MB                 # min 128kB
+shared_buffers = 64GB                  # min 128kB
                                        # (change requires restart)
 #huge_pages = try                      # on, off, or try
                                        # (change requires restart)
-#temp_buffers = 8MB                    # min 800kB
+temp_buffers = 8MB                     # min 800kB
 #max_prepared_transactions = 0         # zero disables the feature
                                        # (change requires restart)
 # Caution: it is not advisable to set max_prepared_transactions nonzero unless
 # you actively intend to use prepared transactions.
-#work_mem = 4MB                                # min 64kB
-#maintenance_work_mem = 64MB           # min 1MB
+work_mem = 20MB                                # min 64kB
+maintenance_work_mem = 256MB           # min 1MB
 #autovacuum_work_mem = -1              # min 1MB, or -1 to use maintenance_work_mem
 #max_stack_depth = 2MB                 # min 100kB
 #shared_memory_type = mmap             # the default is the first option
@@ -366,10 +366,10 @@ min_wal_size = 80MB
 # - Planner Cost Constants -

 #seq_page_cost = 1.0                   # measured on an arbitrary scale
-#random_page_cost = 4.0                        # same scale as above
-#cpu_tuple_cost = 0.01                 # same scale as above
-#cpu_index_tuple_cost = 0.005          # same scale as above
-#cpu_operator_cost = 0.0025            # same scale as above
+random_page_cost = 2.0                 # same scale as above
+cpu_tuple_cost = 0.003                 # same scale as above
+cpu_index_tuple_cost = 0.001           # same scale as above
+cpu_operator_cost = 0.0005             # same scale as above
 #parallel_tuple_cost = 0.1             # same scale as above
 #parallel_setup_cost = 1000.0  # same scale as above

@@ -384,7 +384,7 @@ min_wal_size = 80MB

 #min_parallel_table_scan_size = 8MB
 #min_parallel_index_scan_size = 512kB
-#effective_cache_size = 4GB
+effective_cache_size = 184GB