Re: Simple postgresql.conf wizard

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> On Wed, 2008-12-03 at 13:30 -0500, Robert Haas wrote:
>> I'm not sure what "mixed" mode is supposed to be, but based on what
>> I've seen so far, I'm a skeptical of the idea that encouraging people
>> to raise default_statistics_target to 50 and turn on
>> constraint_exclusion is reasonable.
>
> Why?

Well did you have any response to what I posited before? I said "mixed" should
produce the same settings that the default initdb settings produce. At least
on a moderately low-memory machine that initdb targets.

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.

>> I'm also a bit surprised that
>> there doesn't seem to be anything here that depends on the size of the
>> database, even order-of-magnitude. It seems like the right value for
>> checkpoint_segments, at least, might depend on that.
>
> What does checkpoint_segments have to do with the size of the database?

I had the same reaction but I think he's right.

checkpoint_segments is the maximum amount of space you want the WAL to take up
(ideally). Presumably on a small database you don't want hundreds of megabytes
of WAL for a 10M database. But on a terabyte data warehouse sitting on a big
SAN you're not going to be concerned with how much space the WAL files are
taking. In fact, really it would be nice if we allowed units of space (MB, GB,
etc) for checkpoint_segments.

I used to think of checkpoint_segments in terms of transaction rate and
maximum tolerable recovery time but really if those are your constraints
you're better off using checkpoint_timeout I think.

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