On Wed, May 7, 2014 at 9:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> I think I'm arguing myself towards using a BufferAccessStrategy of
>> BAS_BULKREAD for large IndexScans, BitMapIndexScans and
>> BitMapHeapScans.
>
> As soon as you've got some hard evidence to present in favor of such
> changes, we can discuss it. I've got other things to do besides
> hypothesize.
Let me throw out one last point:
It's pretty likely that s_b is going to be raised higher as a
percentage of RAM. I never really bought into the conventional
wisdom of 25% and have had to set it lower many times. Nevertheless,
it was a documented suggestion.
The core issues are:
1) There is no place to enter total system memory available to the
database in postgresql.conf
2) Memory settings (except for the above) are given as absolute
amounts, not percentages.
It would be a lot easier to standardize configurations particularly if
there was a way to electronically support #1 with auto-detection.
Then, e_c_s. s_b, work_mem, and various other settings could be given
using standard (and perhaps somewhat conservative) percentages using
the best and hopefully factually supported recommendations. I
oversee dozens of servers in a virtualized environment (as most
enterprise shops are these days). Everything is 'right sized', often
on demand, and often nobody bothers to adjust the various settings.
> In the meantime, it seems like there is an emerging consensus that nobody
> much likes the existing auto-tuning behavior for effective_cache_size,
> and that we should revert that in favor of just increasing the fixed
> default value significantly. I see no problem with a value of say 4GB;
> that's very unlikely to be worse than the pre-9.4 default (128MB) on any
> modern machine.
In lieu of something fancy like the above, adjusting the defaults
seems a better way to go (so I vote to revert).
merlin