Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

All,

>> Well, the problem of "find out the box's physical RAM" is doubtless
>> solvable if we're willing to put enough sweat and tears into it, but
>> I'm dubious that it's worth the trouble. The harder part is how to know
>> if the box is supposed to be dedicated to the database. Bear in mind
>> that the starting point of this debate was the idea that we're talking
>> about an inexperienced DBA who doesn't know about any configuration knob
>> we might provide for the purpose.

Frankly, you'd need to go through a whole decision tree to do this right:

- How much RAM do you have?
- Is that RAM shared with other services?
- Is this a DW or OLTP server?
... etc.

We just don't want to get into that in the core code. However ...

>> I'd prefer to go with a default that's predictable and not totally
>> foolish --- and some multiple of shared_buffers seems like it'd fit the
>> bill.
>
> +1. That seems to be by far the biggest bang for the buck. Anything else
> will surely involve a lot more code for not much more benefit.

I don't think we're going far enough here. I think there should be an
optional setting in postgresql.conf called:

available_ram

The, shared_buffers, wal_buffers, and effective_cache_size (and possible
other future settings) can be set to -1. If they are set to -1, then we
use the figure:

shared_buffers = available_ram * 0.25
(with a ceiling of 8GB)
wal_buffers = available_ram * 0.05
(with a ceiling of 32MB)
effective_cache_size = available_ram * 0.75
(with a floor of 128MB)

If they are set to an amount, then we use the amount they are set to.

It would be nice to also automatically set work_mem, maint_work_mem,
temp_buffers, etc. based on the above, but that would be considerably
more difficult and require performance testing we haven't done yet.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com