Re: Simple postgresql.conf wizard

Greg Smith <gsmith@gregsmith.com> writes:

> On Wed, 3 Dec 2008, Gregory Stark wrote:
>
>> It sure seems strange to me to have initdb which presumably is targeting a
>> "mixed" system -- where it doesn't know for sure what workload will be run --
>> produce a different set of values than the tuner on the same machine.
>
> It's been a long time since the output from initdb was targeting anything but a
> minimal system with an untuned kernel and limited resources.  If you invert the
> normal tuning recommendations, as if its initial configuration were the output
> from typical practice, it would be aiming at a system with approximately 128MB
> of RAM.  That feels about right to me; when I had 128MB of RAM in my high-end
> P2-300 server running PG 7.0, 32MB of shared_buffers was huge and 3 checkpoints
> segments was plenty.  I don't recall regularly dirtying things fast enough to
> see "checkpoints occuring too fast" then like you can do trivially nowadays.

Well I think there's also an assumption in initdb that Postgres can't assume
it's on a dedicated machine. So whether it's 32MB on a dedicated 128MB machine
or 32MB on a 256MB machine where it's only expected to be half the workload of
the machine it works out to about the same thing.

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

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.

>           Completion  Max        Max
> Type    Segs    Target    Segments    Usage
> web    8    0.7    23        368MB
> oltp    16    0.9    47        752MB
> dw    64    0.9     187        3GB
> mixed    16    0.9    47        752MB
> desktop    3    0.5    9        144MB

(incidentally using tab stops in emails is probably a bad idea because of
quoting as above)

Uhm, I hadn't actually seen this list before. I don't understand how "web" is
different from "oltp". A web service really is just one (very typical) example
of an oltp application.

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. It's an orthogonal issue from the oltp/data-warehouse axis.

> 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. It would probably work better if
we were asking how big their database was and then could say, well, you said
you had 10G of data so 300MB of overhead isn't going to be so bad.


> The reality here is that it's the recovery playback time that's the real bear.

I agree, but then that's what checkpoint_timeout is for, no? It might take
longer to replay but the recovery time should bear some relation to how long
it took to write out the wal. More so than to the sheer size of the wal. 

> whereas complaints about the logs filling with checkpoint warnings on systems
> set to the default seem to pop up all the time.

"filling"? The cure to having too much space taken up by logs is to take up
space with, well, logs?

The logs are filling up with warnings which explain exactly what parameter to
adjust. Are there really complaints about this?

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.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!