Set default effective_io_concurrency and random_page_cost to SSD values

Since all the drives with DBs on them are SSDs, set these GUCs to values
more appropriate to a generic SSD. NVMe drives could have even higher
concurrency, but this is a reasonable start.

diff --git a/cookbooks/postgresql/attributes/default.rb b/cookbooks/postgresql/attributes/default.rb
index 878407aecf854432f264f63334c6689a7a8c074e..19d871ba1c975589560e5465e52dc2d990a94a1c 100644 (file)
--- a/cookbooks/postgresql/attributes/default.rb
+++ b/cookbooks/postgresql/attributes/default.rb
@@ -9,7 +9,7 @@ default[:postgresql][:settings][:defaults][:temp_buffers] = "8MB"
 default[:postgresql][:settings][:defaults][:work_mem] = "4MB"
 default[:postgresql][:settings][:defaults][:maintenance_work_mem] = "64MB"
 default[:postgresql][:settings][:defaults][:max_stack_depth] = "2MB"
-default[:postgresql][:settings][:defaults][:effective_io_concurrency] = "1"
+default[:postgresql][:settings][:defaults][:effective_io_concurrency] = "256"
 default[:postgresql][:settings][:defaults][:max_worker_processes] = "8"
 default[:postgresql][:settings][:defaults][:max_parallel_workers_per_gather] = "2"
 default[:postgresql][:settings][:defaults][:max_parallel_workers] = "8"
@@ -30,7 +30,7 @@ default[:postgresql][:settings][:defaults][:wal_keep_size] = "0"
 default[:postgresql][:settings][:defaults][:hot_standby] = "on"
 default[:postgresql][:settings][:defaults][:hot_standby_feedback] = "off"
 default[:postgresql][:settings][:defaults][:seq_page_cost] = "1.0"
-default[:postgresql][:settings][:defaults][:random_page_cost] = "4.0"
+default[:postgresql][:settings][:defaults][:random_page_cost] = "1.1"
 default[:postgresql][:settings][:defaults][:cpu_tuple_cost] = "0.01"
 default[:postgresql][:settings][:defaults][:effective_cache_size] = "4GB"
 default[:postgresql][:settings][:defaults][:default_statistics_target] = "100"

diff --git a/roles/eddie.rb b/roles/eddie.rb
index 319be0b9d7a5e3405c33d206a57d9126b2f529c8..61e4524557657c1214b2eeae51cba28679c20139 100644 (file)
--- a/roles/eddie.rb
+++ b/roles/eddie.rb
@@ -19,9 +19,7 @@ default_attributes(
         :shared_buffers => "64GB",
         :work_mem => "64MB",
         :maintenance_work_mem => "1GB",
-        :effective_cache_size => "180GB",
-        :effective_io_concurrency => "256",
-        :random_page_cost => "1.1"
+        :effective_cache_size => "180GB"
       }
     }
   },

diff --git a/roles/karm.rb b/roles/karm.rb
index cc3a425b9d254d2ed0193a4fe055ebad224fa92b..ac28c48d4f3866a35e7880cb328dd2e9712feed4 100644 (file)
--- a/roles/karm.rb
+++ b/roles/karm.rb
@@ -25,9 +25,8 @@ default_attributes(
         :shared_buffers => "64GB",
         :work_mem => "64MB",
         :maintenance_work_mem => "1GB",
-        :effective_cache_size => "180GB",
-        :effective_io_concurrency => "256",
-        :random_page_cost => "1.1"
+        :effective_cache_size => "180GB"
+
       }
     }
   },

diff --git a/roles/prometheus.rb b/roles/prometheus.rb
index 85a1f182955f499f898ff045c470333037c5102f..8dfe36a874659f984fa9f8d07076b0f7aa707dd0 100644 (file)
--- a/roles/prometheus.rb
+++ b/roles/prometheus.rb
@@ -9,7 +9,6 @@ default_attributes(
         :shared_buffers => "48GB",
         :work_mem => "8MB",
         :maintenance_work_mem => "2GB",
-        :effective_io_concurrency => "200",
         :max_worker_processes => "67",
         :max_parallel_workers_per_gather => "28",
         :max_parallel_workers => "56",

diff --git a/roles/snap-01.rb b/roles/snap-01.rb
index 15db38629854a6d205ab05380e84f82c379d2363..d7fb452a5b52d14da2aa4576a1552f7129768180 100644 (file)
--- a/roles/snap-01.rb
+++ b/roles/snap-01.rb
@@ -25,9 +25,7 @@ default_attributes(
         :shared_buffers => "128GB",
         :work_mem => "128MB",
         :maintenance_work_mem => "2GB",
-        :effective_cache_size => "360GB",
-        :effective_io_concurrency => "256",
-        :random_page_cost => "1.1"
+        :effective_cache_size => "360GB"
       }
     }
   },

diff --git a/roles/snap-02.rb b/roles/snap-02.rb
index 8dc70a658c5f535e8173a5401fd9efd641b6feac..80a2b3598723f6777948b886d961e2f20b708633 100644 (file)
--- a/roles/snap-02.rb
+++ b/roles/snap-02.rb
@@ -19,9 +19,7 @@ default_attributes(
         :shared_buffers => "128GB",
         :work_mem => "128MB",
         :maintenance_work_mem => "2GB",
-        :effective_cache_size => "360GB",
-        :effective_io_concurrency => "256",
-        :random_page_cost => "1.1"
+        :effective_cache_size => "360GB"
       }
     }
   },

diff --git a/roles/snap-03.rb b/roles/snap-03.rb
index 3bae5690cae86eee0674d5d1df7fbce0b22dcd21..749859eb137d44e74b7aedaa4e14891d6884e546 100644 (file)
--- a/roles/snap-03.rb
+++ b/roles/snap-03.rb
@@ -25,9 +25,7 @@ default_attributes(
         :shared_buffers => "128GB",
         :work_mem => "128MB",
         :maintenance_work_mem => "2GB",
-        :effective_cache_size => "360GB",
-        :effective_io_concurrency => "256",
-        :random_page_cost => "1.1"
+        :effective_cache_size => "360GB"
       }
     }
   },

diff --git a/roles/tile.rb b/roles/tile.rb
index 65c0a577c75ea51def8c48b9948d78448f4f4eb3..6b159b56851fb9c7af7c0082d7e6bfe0ab7ba6c4 100644 (file)
--- a/roles/tile.rb
+++ b/roles/tile.rb
@@ -46,7 +46,6 @@ default_attributes(
         :commit_delay => "10000",
         :max_wal_size => "2880MB",
         :max_wal_senders => "0",
-        :random_page_cost => "1.1",
         :jit => "off",
         :track_activity_query_size => "16384",
         :autovacuum_vacuum_scale_factor => "0.05",