Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 10, 2013 at 5:02 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Oct 9, 2013 at 07:33:46PM -0700, Peter Geoghegan wrote:
>> I hear a lot of complaints about "the first 15 minutes experience" of
>> Postgres. It's easy to scoff at this kind of thing, but I think we
>> could do a lot better there, and at no real cost - the major blocker
>> to doing something like that has been fixed (of course, I refer to the
>> SysV shared memory limits). Is the person on a very small box where
>> our current very conservative defaults are appropriate? Why not ask a
>> few high-level questions like that to get inexperienced users started?
>> The tool could even have a parameter that allows a packager to pass
>> total system memory without bothering the user with that, and without
>> bothering us with having to figure out a way to make that work
>> correctly and portably.
>
> I think the simplest solution would be to have a parameter to initdb
> which specifies how much memory you want to use, and set a new variable
> available_mem from that, and have things auto-tune based on that value
> in the backend.

I think it would be even simpler, and more reliable, to start with the
parameter to initdb - I like that. But instead of having it set a new
variable based on that and then autotune off that, just have *initdb*
do these calculations you're suggesting, and write new defaults to the
files (preferably with a comment).

That way if the user *later* comes in and say changes shared_buffers,
we don't dynamically resize the work_mem into a value that might cause
his machine to die from swapping which would definitely violate the
principle of least surprise..

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/