Re: postgresql.conf recommendations

On Sat, Feb 9, 2013 at 6:51 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Feb 7, 2013 at 7:41 AM, Charles Gomes <charlesrg@outlook.com> wrote:
>> I've benchmarked shared_buffers with high and low settings, in a server
>> dedicated to postgres with 48GB my settings are:
>> shared_buffers = 37GB
>> effective_cache_size = 38GB
>>
>> Having a small number and depending on OS caching is unpredictable, if the
>> server is dedicated to postgres you want make sure postgres has the memory.
>> A random unrelated process doing a cat /dev/sda1 should not destroy postgres
>> buffers.
>> I agree your problem is most related to dirty background ration, where
>> buffers are READ only and have nothing to do with disk writes.
>
> You make an assertion here but do not tell us of your benchmarking
> methods.

Well, he is not the only one committing that sin.

> My testing in the past has show catastrophic performance
> with very large % of memory as postgresql buffers with heavy write
> loads, especially transactional ones.  Many others on this list have
> had the same thing happen.

People also have problems by setting it too low.  For example, doing
bulk loads into indexed tables becomes catastrophically bad when the
size of the index exceeds shared_buffers by too much (where "too much"
depends on kernel, IO subsystem, and settings of vm.dirty* ) , and
increasing shared_buffers up to 80% of RAM fixes that (if 80% of RAM
is large enough to hold the indexes being updated).

Of course when doing bulk loads into truncated tables, you should drop
the indexes.  But if bulk loading into live tables, that is often a
cure worse than the disease.

> Also you supposed PostgreSQL has a better
> / smarter caching algorithm than the OS kernel, and often times this
> is NOT the case.

Even if it is not smarter as an algorithm, it might still be better to
use it.  For example,  "heap_blks_read", "heap_blks_hit", and friends
become completely useless if most block "reads" are not actually
coming from disk.

Also, vacuum_cost_page_miss is impossible to tune if some unknown but
potentially large fraction of those misses are not really misses, and
that fraction changes from table to table, and from wrap-around scan
to vm scan on the same table.

> In this particular instance the OP may not be seeing an issue from too
> large of a pg buffer, my point still stands, large pg_buffer can cause
> problems with heavy or even moderate write loads.

Sure, but that can go the other way as well.  What additional
instrumentation is needed so that people can actually know which is
the case for them?

Cheers,

Jeff