Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 10, 2013 at 11:45:41AM -0400, Stephen Frost wrote:
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote:
> > > For this case, I think the suggestion made by MauMau would be better-
> > > tell the user (in the postgresql.conf comments) a command they can run
> > > with different memory settings to see what the auto-tuning would do.
> > > Perhaps even have a way to enable use of those new variables, but I
> > > don't really care for the idea of making a GUC that isn't anything
> > > except a control for defaults of *other* GUCs.
> >
> > Well, you then have two places you are doing the tuning --- one in
> > initdb, and another in the tool, and you can have cases where they are
> > not consistent. You could have a mode where initdb re-writes
> > postgresql.conf, but that has all sorts of oddities about changing a
> > config file.
>
> Not necessairly.. Have initdb call the tool to get the values to use
> when first writing out the config file (or make the logic into a library
> that initdb and the tool both use, or just both #include the same .h;
> it's not like it's going to be terribly complicated), and then the tool
> would be responsible for later changes to the postgresql.conf file, or
> we just tell the user how to make the changes recommended by the tool.
> I would *not* have initdb doing that, that's not its job.
>
> If the user is expected to be modifying the postgresql.conf file in this
> scenario anyway, I hardly see that having one-parameter-to-rule-them-all
> is actually better than having 3 or 4. If we're trying to get away from
> the user modifying postgresql.conf, then we're going to need a tool to
> do that (or use ALTER SYSTEM WHATEVER). For my part, I'm really much
> more interested in the "first 15 minutes", as was mentioned elsewhere,
> than how to help users who have been using PG for a year and then
> discover they need to tune it a bit.

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. In fact, the other constants would still
be hard-coded in from initdb, which isn't good.

I think the big win for a tool would be to query the user about how they
are going to be using Postgres, and that can then spit out values the
user can add to postgresql.conf, or to a config file that is included at
the end of postgresql.conf.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +