Re: Auto-tuning work_mem and maintenance_work_mem

On 10/10/13 9:44 AM, MauMau wrote:
> From: "Robert Haas" <robertmhaas@gmail.com>
>> On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander <magnus@hagander.net> wrote:
>>> 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..
>>
>> +1 for all of that.  I completely agree.
>
> I vote for this idea completely, too.  It's nice to be able to specify usable RAM with something like "initdb
--system-memory8GB", because it provides flexibility for memory allocation --- use the whole machine for one PostgreSQL
instance,or run multiple instances on one machine with 50% of RAM for instance-A and 25% of RAM for instance B and C,
etc. But what is the default value of --system-memory?  I would like it to be the whole RAM.
 
>
> I hope something like pgtune will be incorporated into the core, absorbing the ideas in:
>
> - pgtune
> - https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
> - the book "PostgreSQL 9.0 High Performance" by Greg Smith
>
> Then initdb calls the tool.  Of course, DBAs can use the tool later.  Like pgtune, the tool would be nice if it and
initdbcan accept "--system-type" or "--workload" with arguments {OLTP | DW | mixed}.
 

+1 on all of the above. If putting one-time magic in initdb works maybe then we can look at runtime or even completely
dynamicmagic.
 

FWIW, I would be careful about allowing the tool to go completely crazy if --system-memory is set really high,
includingfor things like work_mem. Frequently if you've got a lot of memory you're going to want a serious chunk of it
usedby the filesystem/kernel cache, and not to just vanish into a random sort (esp since last I knew there were
diminishingreturns on sort work_mem...)
 

Of course, I'm in a world of 512G servers with 8GB shared buffers so...
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net