+1 from me. On 14 March 2016 at 15:05, Kevin Fenzi <kevin@xxxxxxxxx> 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.phx2.fedoraproject.org&plugin=load×pan=2678400&action=show_selection&ok_button=OK > > 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@xxxxxxxxxxxxxxxxxxxxxxx > http://lists.fedoraproject.org/admin/lists/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx > -- Stephen J Smoogen. _______________________________________________ infrastructure mailing list infrastructure@xxxxxxxxxxxxxxxxxxxxxxx http://lists.fedoraproject.org/admin/lists/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
