From f1f0af5f339a3c3f3f510eaad8296fb6d7e5b779 Mon Sep 17 00:00:00 2001 From: Tom Hughes Date: Wed, 25 Jan 2023 18:58:59 +0000 Subject: [PATCH] Bring various postgres settings in line with modern defaults --- cookbooks/postgresql/attributes/default.rb | 18 +++++++++--------- .../templates/default/postgresql.conf.erb | 4 ++-- roles/db-master.rb | 3 +-- roles/db.rb | 1 - roles/dev.rb | 3 +-- 5 files changed, 13 insertions(+), 16 deletions(-) diff --git a/cookbooks/postgresql/attributes/default.rb b/cookbooks/postgresql/attributes/default.rb index 419b44de8..fd1afcea1 100644 --- a/cookbooks/postgresql/attributes/default.rb +++ b/cookbooks/postgresql/attributes/default.rb @@ -4,10 +4,10 @@ default[:postgresql][:monitor_tables] = true default[:postgresql][:settings][:defaults][:port] = "5432" default[:postgresql][:settings][:defaults][:max_connections] = "100" default[:postgresql][:settings][:defaults][:ssl] = "true" -default[:postgresql][:settings][:defaults][:shared_buffers] = "32MB" +default[:postgresql][:settings][:defaults][:shared_buffers] = "128MB" default[:postgresql][:settings][:defaults][:temp_buffers] = "8MB" -default[:postgresql][:settings][:defaults][:work_mem] = "1MB" -default[:postgresql][:settings][:defaults][:maintenance_work_mem] = "16MB" +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][:max_worker_processes] = "8" @@ -20,22 +20,22 @@ default[:postgresql][:settings][:defaults][:wal_buffers] = "-1" default[:postgresql][:settings][:defaults][:wal_writer_delay] = "200ms" default[:postgresql][:settings][:defaults][:commit_delay] = "0" default[:postgresql][:settings][:defaults][:checkpoint_timeout] = "5min" +default[:postgresql][:settings][:defaults][:checkpoint_completion_target] = "0.9" default[:postgresql][:settings][:defaults][:max_wal_size] = "1GB" default[:postgresql][:settings][:defaults][:min_wal_size] = "80MB" -default[:postgresql][:settings][:defaults][:checkpoint_completion_target] = "0.5" default[:postgresql][:settings][:defaults][:archive_mode] = "off" -default[:postgresql][:settings][:defaults][:max_wal_senders] = "0" -default[:postgresql][:settings][:defaults][:max_replication_slots] = "0" -default[:postgresql][:settings][:defaults][:hot_standby] = "off" +default[:postgresql][:settings][:defaults][:max_wal_senders] = "10" +default[:postgresql][:settings][:defaults][:max_replication_slots] = "10" +default[:postgresql][:settings][:defaults][:hot_standby] = "on" default[:postgresql][:settings][:defaults][:hot_standby_feedback] = "off" default[:postgresql][:settings][:defaults][:random_page_cost] = "4.0" default[:postgresql][:settings][:defaults][:cpu_tuple_cost] = "0.01" -default[:postgresql][:settings][:defaults][:effective_cache_size] = "128MB" +default[:postgresql][:settings][:defaults][:effective_cache_size] = "4GB" default[:postgresql][:settings][:defaults][:default_statistics_target] = "100" default[:postgresql][:settings][:defaults][:jit] = "on" default[:postgresql][:settings][:defaults][:log_min_duration_statement] = "-1" -default[:postgresql][:settings][:defaults][:track_activity_query_size] = "1024" default[:postgresql][:settings][:defaults][:log_autovacuum_min_duration] = "-1" +default[:postgresql][:settings][:defaults][:track_activity_query_size] = "1024" default[:postgresql][:settings][:defaults][:autovacuum_max_workers] = "3" default[:postgresql][:settings][:defaults][:autovacuum_naptime] = "1min" default[:postgresql][:settings][:defaults][:autovacuum_vacuum_scale_factor] = "0.2" diff --git a/cookbooks/postgresql/templates/default/postgresql.conf.erb b/cookbooks/postgresql/templates/default/postgresql.conf.erb index 855e8dc6c..a8bc83c05 100644 --- a/cookbooks/postgresql/templates/default/postgresql.conf.erb +++ b/cookbooks/postgresql/templates/default/postgresql.conf.erb @@ -62,9 +62,9 @@ commit_delay = <%= @settings[:commit_delay] || @defaults[:commit_delay] %> # - Checkpoints - checkpoint_timeout = <%= @settings[:checkpoint_timeout] || @defaults[:checkpoint_timeout] %> +checkpoint_completion_target = <%= @settings[:checkpoint_completion_target] || @defaults[:checkpoint_completion_target] %> max_wal_size = <%= @settings[:max_wal_size] || @defaults[:max_wal_size] %> min_wal_size = <%= @settings[:min_wal_size] || @defaults[:min_wal_size] %> -checkpoint_completion_target = <%= @settings[:checkpoint_completion_target] || @defaults[:checkpoint_completion_target] %> # - Archiving - @@ -123,6 +123,7 @@ log_min_duration_statement = <%= @settings[:log_min_duration_statement] || @defa # - What to Log - +log_autovacuum_min_duration = <%= @settings[:log_autovacuum_min_duration] || @defaults[:log_autovacuum_min_duration] %> log_line_prefix = '%t ' #------------------------------------------------------------------------------ @@ -140,7 +141,6 @@ stats_temp_directory = '/run/postgresql/<%= @version %>-main.pg_stat_tmp' # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ -log_autovacuum_min_duration = <%= @settings[:log_autovacuum_min_duration] || @defaults[:log_autovacuum_min_duration] %> autovacuum_max_workers = <%= @settings[:autovacuum_max_workers] || @defaults[:autovacuum_max_workers] %> autovacuum_naptime = <%= @settings[:autovacuum_naptime] || @defaults[:autovacuum_naptime] %> autovacuum_vacuum_scale_factor = <%= @settings[:autovacuum_vacuum_scale_factor] || @defaults[:autovacuum_vacuum_scale_factor] %> diff --git a/roles/db-master.rb b/roles/db-master.rb index 0bb927739..3ebd79e82 100644 --- a/roles/db-master.rb +++ b/roles/db-master.rb @@ -6,8 +6,7 @@ default_attributes( :settings => { :defaults => { :archive_mode => "on", - :archive_command => "/usr/local/bin/openstreetmap-wal-g wal-push %p --walg-prevent-wal-overwrite=true", - :max_replication_slots => "1" + :archive_command => "/usr/local/bin/openstreetmap-wal-g wal-push %p --walg-prevent-wal-overwrite=true" } } } diff --git a/roles/db.rb b/roles/db.rb index f0cbe0f97..05cd66aa9 100644 --- a/roles/db.rb +++ b/roles/db.rb @@ -44,7 +44,6 @@ default_attributes( :wal_level => "logical", :max_wal_size => "1536MB", :checkpoint_completion_target => "0.8", - :max_wal_senders => "10", :cpu_tuple_cost => "0.1", :jit => "off", :log_min_duration_statement => "1000", diff --git a/roles/dev.rb b/roles/dev.rb index c41dda8dc..6fc9c6163 100644 --- a/roles/dev.rb +++ b/roles/dev.rb @@ -139,8 +139,7 @@ default_attributes( }, "15" => { :port => "5432", - :wal_level => "logical", - :max_replication_slots => "1" + :wal_level => "logical" } } }, -- 2.39.5