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