From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers |
Date: | 2013-01-09 02:01:55 |
Message-ID: | 50ECCF93.3060101@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-01-09 02:10:24 | Re: PL/perl should fail on configure, not make |
Previous Message | Daniel Farina | 2013-01-09 01:59:43 | Re: Cascading replication: should we detect/prevent cycles? |