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 -
Attachment:
pgp31GUPF7Ojn.pgp
Description: OpenPGP digital signature
_______________________________________________ infrastructure mailing list infrastructure@xxxxxxxxxxxxxxxxxxxxxxx http://lists.fedoraproject.org/admin/lists/infrastructure@xxxxxxxxxxxxxxxxxxxxxxx
