Re: Simple postgresql.conf wizard

On Thu, 4 Dec 2008, Gregory Stark wrote:

>> Right now, my program doesn't fiddle with any memory settings if you've got
>> less than 256MB of RAM.
>
> What I'm suggesting is that you shouldn't have to special case this. That you
> should expect whatever formulas you're using to produce the same values as
> initdb if they were run on the same machine initdb is targeting.

The reason that approach isn't taken is that the model here assumes the OS
overhead is negligable relative to everything else going on. If you've
only got a small amount of RAM, that assumption is so badly broken that
you can't just extend the curves for everything down to there and expect
that what comes out will make any sense. I started to make a more
complicated bit that did scale down to the bottom by modeling the overhead
better, Josh talked me out of doing it for now.

> But actually I'm more concerned with the *non* memory related parameters. It
> may make sense to tweak those one way or the other for oltp or dss but "mixed"
> should be exactly what initdb produces since that's exactly what it's
> targeting -- a system that will have a wide mixture of queries and must
> function reasonably well for both data warehouse and oltp queries.

The only way this line of discussion will go is toward talking about what
should be changed in initdb to make it more representative of the current
real world, and I know that's not going anywhere (see
"default_statistics_target=10"). The idea that the sample configuration
is tuned usefully for any application whatsoever gets nothing from me but
a chuckle.

> And "desktop" seems like an outlier here. I suppose it's meant to capture
> whether postgres is on a dedicated box? But it's possible to have a
> non-dedicated oltp application or non-dedicated data warehouse box just as
> easily.

That's the target for something that's not a dedicated server--a desktop
PC you use as a general workstation, maybe you're installing PostgreSQL as
a developer that's competing with your web server and other apps;
something like that. There might be a better name for that.

>> Is 368MB of overhead unreasonable for a web application database today
>
> Well I think it's more than most people expect a single application install to
> take up before they start putting data in it.

Segments don't get allocated until you churn through that much WAL
activity; that figure is an upper-bound after you've pushed more than that
worth of data through WAL and into the database. The only example where
this overhead isn't dwarfed by the size of the resulting database is where
some small number of records are inserted, then constantly updated and
vacuumed. And you know what? The person doing that is likely to really
benefit from having checkpoint_segments set to a larger value. Update and
vacuum heavy workloads are exactly the sort where you end up checkpointing
too often with the default parameters.

> I'm really beginning to think the root of the problem is the name. If it were
> "transaction_log_max_space" and measured in megabytes people would be happy to
> say "ok, I'll make space for 100MB of logs" or whatever. Today they don't know
> what to set it to or what the impact of setting it will be.

Unless they do something crazy like read the documentation:

http://www.postgresql.org/docs/8.3/static/wal-configuration.html
"There will always be at least one WAL segment file, and will normally not
be more than (2 + checkpoint_completion_target) * checkpoint_segments + 1
files. Each segment file is normally 16 MB (though this size can be
altered when building the server). You can use this to estimate space
requirements for WAL."

Too complicated for most people you say? I agree; that's why I put some
annotated examples for what those translate into
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server like "32
(checkpoint every 512MB)".

What fun. I'm beginning to remember why nobody has ever managed to
deliver a community tool that helps with this configuration task before.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD