From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Andrew Dunstan <andrew(at)dunslane(dot)net>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Josh Berkus <josh(at)agliodbs(dot)com> |
Subject: | Re: Auto-tuning work_mem and maintenance_work_mem |
Date: | 2013-10-09 17:07:12 |
Message-ID: | 20131009170712.GC22450@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Oct 9, 2013 at 12:41:53PM -0400, Stephen Frost wrote:
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > I think we should try to hit the existing defaults, which would mean we
> > would use this computation:
>
> For my 2c, I was hoping this would improve things for our users by
> raising the tiny 1M default work_mem, so I don't agree that we should
> simply be coming up with an algorithm to hit the same numbers we already
> have today.
>
> > (shared_buffers / 4) / max_connections + 768k / BUFSZ
> >
> > This would give us for a default 128MB shared buffers and 100
> > max_connections:
> >
> > (16384 / 4) / 100 + (768 * 1024) / 8192
> >
> > which gives us 136, and that is 136 * 8192 or 1088k, close to 1MB.
> >
> > For 10x shared buffers, 163840, it gives a work_mem of 4040k, rather
> > than the 10M I was computing in the original patch.
> >
> > How is that?
>
> So this would only help if people are already going in and modifying
> shared_buffers, but not setting work_mem? I'd rather see better
> defaults for users that don't touch anything, such as 4MB or even
> larger.
OK, but changing the default if shared_buffers is _not_ changed is a
separate discussion.
We can have the discussion here or in another thread. I am thinking the
right fix is to allocate larger shared_buffers, especially now that we
don't require a larger System V shared memory segement. Basically, for
128MB of shared buffers, I figured the calculation was fine, and when we
increase the default shared_buffers, we will then get a better default,
which is why I am quoting the 2GB shared_buffers defaults in my emails.
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-10-09 17:09:15 | Re: Auto-tuning work_mem and maintenance_work_mem |
Previous Message | Joshua D. Drake | 2013-10-09 17:02:12 | Re: Auto-tuning work_mem and maintenance_work_mem |