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

On Thu, Sep 5, 2013 at 3:01 AM, Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Jan  8, 2013 at 08:40:44PM -0500, Andrew Dunstan wrote:
>>
>> On 01/08/2013 08:08 PM, Tom Lane wrote:
>> >Robert Haas <robertmhaas@gmail.com> writes:
>> >>On Tue, Jan 8, 2013 at 7:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >>>...  And I don't especially like the idea of trying to
>> >>>make it depend directly on the box's physical RAM, for the same
>> >>>practical reasons Robert mentioned.
>> >>For the record, I don't believe those problems would be particularly
>> >>hard to solve.
>> >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.
>> >
>> >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 have developed the attached patch which implements an auto-tuned
> effective_cache_size which is 4x the size of shared buffers.  I had to
> set effective_cache_size to its old 128MB default so the EXPLAIN
> regression tests would pass unchanged.

That's not really autotuning though. ISTM that making the *default* 4
x shared_buffers might make perfect sense, but do we really need to
hijack the value of "-1" for that? That might be useful for some time
when we have actual autotuning, that somehow inspects the system and
tunes it from there.

I also don't think it should be called autotuning, when it's just a
"smarter default value".

I like the feature, though, just not the packaging.

-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/