From 70574222913bd96fdbcbdc1443fa6b367b876814 Mon Sep 17 00:00:00 2001 From: Tom Hughes Date: Thu, 14 Jan 2021 20:25:10 +0000 Subject: [PATCH] Add some addition postgres configuration options --- cookbooks/postgresql/attributes/default.rb | 7 +++++ .../templates/default/postgresql.conf.erb | 27 +++++++++++++++++++ 2 files changed, 34 insertions(+) diff --git a/cookbooks/postgresql/attributes/default.rb b/cookbooks/postgresql/attributes/default.rb index c7eeecff8..4882e77b1 100644 --- a/cookbooks/postgresql/attributes/default.rb +++ b/cookbooks/postgresql/attributes/default.rb @@ -9,6 +9,9 @@ default[:postgresql][:settings][:defaults][:work_mem] = "1MB" default[:postgresql][:settings][:defaults][:maintenance_work_mem] = "16MB" default[:postgresql][:settings][:defaults][:max_stack_depth] = "2MB" default[:postgresql][:settings][:defaults][:effective_io_concurrency] = "1" +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" default[:postgresql][:settings][:defaults][:wal_level] = "minimal" default[:postgresql][:settings][:defaults][:fsync] = "on" default[:postgresql][:settings][:defaults][:synchronous_commit] = "on" @@ -28,12 +31,16 @@ 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][: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][:autovacuum_max_workers] = "3" +default[:postgresql][:settings][:defaults][:autovacuum_naptime] = "1min" default[:postgresql][:settings][:defaults][:autovacuum_vacuum_scale_factor] = "0.2" default[:postgresql][:settings][:defaults][:autovacuum_analyze_scale_factor] = "0.1" +default[:postgresql][:settings][:defaults][:shared_preload_libraries] = [] +default[:postgresql][:settings][:defaults][:max_locks_per_transaction] = "64" default[:postgresql][:settings][:defaults][:user_name_maps] = {} default[:postgresql][:settings][:defaults][:early_authentication_rules] = [] default[:postgresql][:settings][:defaults][:late_authentication_rules] = [] diff --git a/cookbooks/postgresql/templates/default/postgresql.conf.erb b/cookbooks/postgresql/templates/default/postgresql.conf.erb index e2892f68a..6b38d55d6 100644 --- a/cookbooks/postgresql/templates/default/postgresql.conf.erb +++ b/cookbooks/postgresql/templates/default/postgresql.conf.erb @@ -46,6 +46,13 @@ max_stack_depth = <%= @settings[:max_stack_depth] || @defaults[:max_stack_depth] # - Asynchronous Behavior - effective_io_concurrency = <%= @settings[:effective_io_concurrency] || @defaults[:effective_io_concurrency] %> +max_worker_processes = <%= @settings[:max_worker_processes] || @defaults[:max_worker_processes] %> +<% if @version.to_f >= 9.6 -%> +max_parallel_workers_per_gather = <%= @settings[:max_parallel_workers_per_gather] || @defaults[:max_parallel_workers_per_gather] %> +<% end -%> +<% if @version.to_f >= 10 -%> +max_parallel_workers = <%= @settings[:max_parallel_workers] || @defaults[:max_parallel_workers] %> +<% end -%> #------------------------------------------------------------------------------ # WRITE AHEAD LOG @@ -107,6 +114,7 @@ effective_cache_size = <%= @settings[:effective_cache_size] || @defaults[:effect # - Other Planner Options - +default_statistics_target = <%= @settings[:default_statistics_target] || @defaults[:default_statistics_target] %> <% if @version.to_f >= 12 -%> jit = <%= @settings[:jit] || @defaults[:jit] %> <% end -%> @@ -136,6 +144,7 @@ track_activity_query_size = <%= @settings[:track_activity_query_size] || @defaul #------------------------------------------------------------------------------ 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] %> autovacuum_analyze_scale_factor = <%= @settings[:autovacuum_analyze_scale_factor] || @defaults[:autovacuum_analyze_scale_factor] %> @@ -151,3 +160,21 @@ lc_monetary = 'en_GB.UTF-8' lc_numeric = 'en_GB.UTF-8' lc_time = 'en_GB.UTF-8' default_text_search_config = 'pg_catalog.english' + +# - Shared Library Preloading - + +shared_preload_libraries = '<%= (@settings[:shared_preload_libraries] || @defaults[:shared_preload_libraries]).sort.join(",") %>' + +#------------------------------------------------------------------------------ +# LOCK MANAGEMENT +#------------------------------------------------------------------------------ + +max_locks_per_transaction = <%= @settings[:max_locks_per_transaction] || @defaults[:max_locks_per_transaction] %> + +#------------------------------------------------------------------------------ +# CUSTOMIZED OPTIONS +#------------------------------------------------------------------------------ + +<% Hash(@settings[:customized_options]).sort.each do |name, value| -%> +<%= name %> = <%= value %> +<% end -%> -- 2.39.5