On Thu, Oct 10, 2019 at 12:13:41PM -0400, Stephen John Smoogen wrote:
> why is work_mem = 157286kB versus 16MB ?
Thats what the pgtune generator gave me. ;)
I don't think there's any reason it couldn't be 16MB...
kevin
--
>
> 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.
Attachment:
signature.asc
Description: PGP signature
_______________________________________________ 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
