Re: Auto-tuning work_mem and maintenance_work_mem

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.

> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company