Re: Auto-tuning work_mem and maintenance_work_mem

On 10/10/13 9:44 AM, MauMau wrote:
> From: "Robert Haas" <robertmhaas(at)gmail(dot)com>
>> On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander <magnus(at)hagander(dot)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-memory 8GB", 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 initdb can 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 dynamic magic.

FWIW, I would be careful about allowing the tool to go completely crazy if --system-memory is set really high, including for things like work_mem. Frequently if you've got a lot of memory you're going to want a serious chunk of it used by the filesystem/kernel cache, and not to just vanish into a random sort (esp since last I knew there were diminishing returns 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net