Re: Freeze break: db-koji01 tuning

[Stephen John Smoogen]

+1 from me.

On 14 March 2016 at 15:05, Kevin Fenzi <kevin(a)scrye.com&gt; wrote:
>  Greetings.
>
>  Over the last few weeks, db-koji01's load has been climbing.
>  We have been getting a lot of noise from our monitoring when the query
>  we use to monitor doesn't return fast enough.
>
>
> https://admin.fedoraproject.org/collectd/bin/index.cgi?hostname=db-koji01...
>
>  There's very likely some upstream work to be doing adding indexes in
>  the right places, etc, but I think we can tune things a bit now to make
>  it better in the short term.
>
>  I looked over
>  https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and saw
>  that some of our settings were out of wack.
>
>  I'd like to apply the below patch and restart postgresql later tonight
>  when things aren't as busy. If it causes some issue we can back it out,
>  but I hope it might improve things.
>
>  I tried to setup the changes so they only apply to db-koji01 for now,
>  but after the freeze we might adjust other postgresql servers.
>
>  +1s?
>
>  kevin
>  --
>  diff --git a/inventory/host_vars/db-koji01.phx2.fedoraproject.org
> b/inventory/host_vars/db-koji01.phx2.fedoraproject.org
>  index 41f3677..855a220 100644
>  --- a/inventory/host_vars/db-koji01.phx2.fedoraproject.org
>  +++ b/inventory/host_vars/db-koji01.phx2.fedoraproject.org
>  @@ -39,4 +39,4 @@ nrpe_procs_warn: 600
>   nrpe_procs_crit: 700
>
>   host_backup_targets: ['/backups']
>  -shared_buffers: "4GB"
>  +shared_buffers: "8GB"
>  diff --git a/roles/postgresql_server/templates/postgresql.conf
> b/roles/postgresql_server/templates/postgresql.conf
>  index 603f9ea..4a8bdfb 100644
>  --- a/roles/postgresql_server/templates/postgresql.conf
>  +++ b/roles/postgresql_server/templates/postgresql.conf
>  @@ -121,8 +121,13 @@ 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 = 2MB                          # min 64kB
>  -maintenance_work_mem = 1024MB           # min 1MB
>  +{% if ansible_hostname.startswith("db-koji01") %}
>  +work_mem = 4MB                          # min 64kB
>  +maintenance_work_mem = 16MB             # min 1MB
>  +{% else %}
>  +work_mem = 2MB                          # min 64kB
>  +maintenance_work_mem = 1024MB           # min 1MB
>  +{% endif %}
>   #max_stack_depth = 2MB                  # min 100kB
>
>   # - Free Space Map -
>  @@ -215,7 +220,11 @@ random_page_cost = 3.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
>  +{% if ansible_hostname.startswith("db-koji01") %}
>  +effective_cache_size = 24GB
>  +{% else %}
>   effective_cache_size = 5GB
>  +{% endif %}
>
>   # - Genetic Query Optimizer -
>
>
>  _______________________________________________
>  infrastructure mailing list
>  infrastructure(a)lists.fedoraproject.org
>  http://lists.fedoraproject.org/admin/lists/[e-mail]...
>  


-- 
Stephen J Smoogen.