why is work_mem = 157286kB versus 16MB ?
On Thu, 10 Oct 2019 at 12:02, <kevin@xxxxxxxxx> wrote:
>
> From: Kevin Fenzi <kevin@xxxxxxxxx>
>
> The current settings cause database dumps to drive the load way up
> and make the entire application slow, so we need to adjust.
> Using pgtune, these values might well be better.
>
> shared_buffers + effective_cache_size should = total memory.
> random_page_cost should be lowered a bunch since we are on ssds there.
> 1.1 is only slightly more than 1.0 for sequential.
> effective_io_concurrency should also be raised a bunch for ssds.
> a few other values should be higher based on memory.
>
> Signed-off-by: Kevin Fenzi <kevin@xxxxxxxxx>
> ---
> inventory/host_vars/db-koji01.phx2.fedoraproject.org | 4 ++--
> roles/postgresql_server/templates/postgresql.conf | 12 +++++++-----
> 2 files changed, 9 insertions(+), 7 deletions(-)
>
> diff --git a/inventory/host_vars/db-koji01.phx2.fedoraproject.org b/inventory/host_vars/db-koji01.phx2.fedoraproject.org
> index 49a19d8..587e561 100644
> --- a/inventory/host_vars/db-koji01.phx2.fedoraproject.org
> +++ b/inventory/host_vars/db-koji01.phx2.fedoraproject.org
> @@ -45,5 +45,5 @@ nrpe_procs_warn: 600
> nrpe_procs_crit: 700
>
> db_backup_dir: ['/backups']
> -shared_buffers: "32GB"
> -effective_cache_size: "32GB"
> +shared_buffers: "30GB"
> +effective_cache_size: "90GB"
> diff --git a/roles/postgresql_server/templates/postgresql.conf b/roles/postgresql_server/templates/postgresql.conf
> index b148442..cbaaaae 100644
> --- a/roles/postgresql_server/templates/postgresql.conf
> +++ b/roles/postgresql_server/templates/postgresql.conf
> @@ -121,8 +121,8 @@ shared_buffers = {{ shared_buffers }} # min 128kB or max_connections*16kB
>
> # 8 MB is probably on the high side. We can probably do with 4MB. But we
> # were seeing a problem and we have the RAM so we're going to try this.
> -work_mem = 4MB # min 64kB
> -maintenance_work_mem = 1024MB # min 1MB
> +work_mem = 157286kB
> +maintenance_work_mem = 2GB # min 1MB
> #max_stack_depth = 2MB # min 100kB
>
> # - Free Space Map -
> @@ -169,7 +169,7 @@ wal_sync_method = fdatasync # the default is the first option
> # fsync_writethrough
> # open_sync
> #full_page_writes = on # recover from partial page writes
> -wal_buffers = 64kB # min 32kB
> +wal_buffers = 16MB # min 32kB
> # (change requires restart)
> #wal_writer_delay = 200ms # 1-10000 milliseconds
>
> @@ -212,7 +212,7 @@ checkpoint_warning = 180s # 0 is off
> # - Planner Cost Constants -
>
> #seq_page_cost = 1.0 # measured on an arbitrary scale
> -random_page_cost = 3.0 # same scale as above
> +random_page_cost = 1.1 # 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
> @@ -502,4 +502,6 @@ default_text_search_config = 'pg_catalog.english'
> #------------------------------------------------------------------------------
>
> #custom_variable_classes = '' # list of custom variable class names
> -
> +#
> +# Number of concurrent i/o operations at the same time. The default is 1.
> +effective_io_concurrency = 100
> --
> 1.8.3.1
> _______________________________________________
> infrastructure mailing list -- infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
> To unsubscribe send an email to infrastructure-leave@xxxxxxxxxxxxxxxxxxxxxxx
> Fedora Code of Conduct: https://docs.fedoraproject.org/en-US/project/code-of-conduct/
> List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines
> List Archives: https://lists.fedoraproject.org/archives/list/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
--
Stephen J Smoogen.
_______________________________________________
infrastructure mailing list -- infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
To unsubscribe send an email to infrastructure-leave@xxxxxxxxxxxxxxxxxxxxxxx
Fedora Code of Conduct: https://docs.fedoraproject.org/en-US/project/code-of-conduct/
List Guidelines: https://fedoraproject.org/wiki/Mailing_list_guidelines
List Archives: https://lists.fedoraproject.org/archives/list/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
