Re: postgresql.conf recommendations

On Mon, Feb 11, 2013 at 7:57 AM, Charles Gomes <charlesrg@outlook.com> wrote:
>
>
>> Date: Sat, 9 Feb 2013 14:03:35 -0700
>
>> Subject: Re: [PERFORM] postgresql.conf recommendations
>> From: scott.marlowe@gmail.com
>> To: jeff.janes@gmail.com
>> CC: charlesrg@outlook.com; strahinjak@nordeus.com; kgrittn@ymail.com;
>> johnnydtan@gmail.com; ac@hsk.hk; jkrupka@gmail.com; alex@paperlesspost.com;
>> pgsql-performance@postgresql.org
>
>>
>> On Sat, Feb 9, 2013 at 1:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> > 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.
>>
>> I'm not asking for a complete low level view. but it would be nice to
>> know if he's benchmarking heavy read or write loads, lots of users, a
>> few users, something. All we get is "I've benchmarked a lot" followed
>> by "don't let the OS do the caching." At least with my testing I was
>> using a large transactional system (heavy write) and there I KNOW from
>> testing that large shared_buffers do nothing but get in the way.
>>
>> all the rest of the stuff you mention is why we have effective cache
>> size which tells postgresql about how much of the data CAN be cached.
>> In short, postgresql is designed to use and / or rely on OS cache.
>>
> Hello Scott
>
> I've tested using 8 bulk writers in a 8 core machine (16 Threads).
>
> I've loaded a database with 17 partitions, total 900 million rows and later
> executed single queries on it.
>
> In my case the main point of having postgres manage memory is because
> postgres is the single and most important application running on the server.
>
>
>
> If Linux would manage the Cache it would not know what is important and what
> should be discarded, it would simply discard the oldest least accessed
> entry.

Point taken however,

> Let's say a DBA logs in the server and copies a 20GB file. If you leave
> Linux to decide, it will decide that the  20GB file is more important than
> the old not so heavily accessed postgres entries.

The linux kernel (and most other unix kernels) don't cache that way.
They're usually quite smart about caching.  While some older things
might get pushed out, it doesn't generally make room for larger files
that have been accessed just once.  But on a mixed load server this
may not be the case.

> If postgres was unable to manage 40GB of RAM, we would get into major
> problems because nowadays it's normal to buy 64GB servers, and  many of Us
> have dealt with 512GB Ram Servers.

It's not that postgres can't hadndle large cache, it's that quite
often the kernel is simply better at it.

> By the way, I've tested this same scenario with Postgres, Mysql and Oracle.
> And Postgres have given the best results overall. Especially with symmetric
> replication turned on.

Good to know.  In the past PostgreSQL has had some performance issues
with large shared_buffer values, and this is still apparently the case
when run on windows.  With dedicated linux servers running just
postgres, letting the kernel handle cache has yielded very good
results.  Most of the negative aspects on large buffers I've seen has
been on heavy write / high transactional dbs.