Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 10, 2013 at 8:41 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
>>> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
>>> > Well, I like the idea of initdb calling the tool, though the tool then
>>> > would need to be in C probably as we can't require python for initdb.
>>> > The tool would not address Robert's issue of someone increasing
>>> > shared_buffers on their own.
>>>
>>> I'm really not impressed with this argument. Either the user is going
>>> to go and modify the config file, in which case I would hope that they'd
>>> at least glance around at what they should change, or they're going to
>>> move off PG because it's not performing well enough for them- which is
>>> really what I'm trying to avoid happening during the first 15m.
>>
>> Well, they aren't going around and looking at other parameters now or we
>> would not feel a need to auto-tune many of our defaults.
>>
>> How do we handle the Python dependency, or is this all to be done in
>> some other language? I certainly am not ready to take on that job.
>
> I don't see why it can't be done in C. The server is written in C,
> and so is initdb. So no matter where we do this, it's gonna be in C.
> Where does Python enter into it?
>
> What I might propose is that we have add a new binary tunedb, maybe
> compiled out of the src/bin/initdb.c directory. So you can say:
>
> initdb --available-memory=32GB
>
> ...and it will initialize the cluster with appropriate settings. Or
> you can say:
>
> tunedb --available-memory=32GB
>
> ...and it will print out a set of proposed configuration settings. If
> we want a mode that rewrites the configuration file, we could have:
>
> tunedb --available-memory=32GB --rewrite-config-file=$PATH
>
> ...but that might be overkill, at least for version 1.

I like this. And I agree that the edit-in-place might be overkill. But
then, if/when we get the ability to programatically modify the config
files, that's probably not a very complicated thing to add once the
rest is done.

>> One nice thing about a tool is that you can see your auto-tuned defaults
>> right away, while doing this in the backend, you have to start the
>> server to see the defaults. I am not even sure how I could allow users
>> to preview their defaults for different available_mem settings.
>
> Yep, agreed. And agreed that not being able to preview settings is a problem.

I'd even say it would be a *big* problem.

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