Re: Shared Buffer Size

Hi Carl,

Thank you for your response, however, I am trying to understand the role of
shared_buffer. I notice you have increased this value in your settings.

I am just trying to understand the meaning of this statement-
"PostgreSQL depends on the OS for caching. (
http://www.varlena.com/GeneralBits/Tidbits/perf.html#shbuf)"

My question is how does it rely? What is happening internally? It would be
great if someone could explain with a simple example.

Thanks!

On Sat, May 28, 2011 at 1:42 AM, Carl von Clausewitz <clausewitz45(at)gmail(dot)com
> wrote:

> Hi Preetika,
>
> a few months ago, when I installed my first PostgreSQL, I have had the same
> problem. I've try to get any information about optimal memory config, and
> working, but there wasn't any "optimal memory setting calculator" on the
> internet, just some guide in the posgre documentation (
> http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html#SYSVIPC).
> I got FreeBSD 8.2 AMD64, with 8 GB of memory (this server is just for
> PostgreSQL and a little PHP app with 2 user), and I have theese setting in
> postgresql.conf (which are not the default):
>
> listen_addresses = '192.168.1.1' # what IP address(es) to listen on;
> port = 5432 # (change requires restart)
> max_connections = 200 # (There are 20 user, with Microsoft Access client
> and ODBC connections... (min 6 connection / user))
>
> shared_buffers = 1900MB # min 128kB
> temp_buffers = 64MB # min 800kB
> work_mem = 64MB # min 64kB
> maintenance_work_mem = 1024MB # min 1MB
> max_stack_depth = 64MB # min 100kB
>
> shared_preload_libraries = '$libdir/plpgsql.so' # (change requires
> restart)
>
> checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 15min # range 30s-1h
> checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
>
> effective_cache_size = 4096MB
>
> track_activities = on
> track_counts = on
> #track_functions = none # none, pl, all
> #track_activity_query_size = 1024 # (change requires restart)
>
> update_process_title = off
> deadlock_timeout = 1s
> max_locks_per_transaction = 256 # min 10
>
> And the sysctl.conf from BSD, which are relevant for theese postgre
> settings:
> kern.ipc.shmall=524288
> kern.ipc.shmmax=2147483648
> kern.ipc.semmap=512
> kern.ipc.shm_use_phys=1
>
> And the last one is the loader.conf from BSD, which are relevant for theese
> postgre settings:
> kern.ipc.semmni=512
> kern.ipc.semmns=1024
> kern.ipc.semmnu=512
>
> Theese settings based on my experience, with lot of reboot and restart and
> reload config - I hope this can help you, and I accept any comment, if I
> need to set everything else :-)
>
> Thanks,
> Carl
>
> 2011/5/27 preetika tyagi <preetikatyagi(at)gmail(dot)com>
>
>> Hi Derrick,
>>
>> Thank you for your response.
>> I saw this document and trying to understand "Interaction with the
>> Operating System Cache" which is mentioned in this document.
>>
>> I have the following question-
>> Hows does the shared buffer in Postgres rely on the Operating System
>> cache?
>> Suppose my RAM is 8 GB and shared_buffer is 24 MB in postgres. And there
>> are some dirty pages in shared_buffer and I need to write a dirty page back
>> to the disk to bring in a new page. What happens in this case? The dirty
>> page will be written to the disk considering the shared_buffer size as 24
>> MB? or it will not be written and will stay in RAM which is 8 GB?
>>
>> Thanks,
>> Preetika
>>
>>
>> On Fri, May 27, 2011 at 2:11 PM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com>wrote:
>>
>>> Check out the "Inside the PostgreSQL Buffer Cache" link here:
>>>
>>> http://projects.2ndquadrant.com/talks
>>>
>>> Thanks to Greg Smith (active here).
>>>
>>> Derrick
>>>
>>>
>>> On Fri, May 27, 2011 at 3:36 PM, preetika tyagi <preetikatyagi(at)gmail(dot)com
>>> > wrote:
>>>
>>>> Hi All,
>>>>
>>>> I am little confused about the internal working of PostgreSQL. There is
>>>> a parameter shared_buffer in postgres.conf and I am assuming that it is used
>>>> for buffer management in PostgreSQL. If there is a need to bring in a new
>>>> page in the buffer and size exceeds the shared_buffer limit, a victim dirty
>>>> page will be written back to the disk.
>>>>
>>>> However, I have read on many links that PostgreSQL depends on the OS for
>>>> caching. (http://www.varlena.com/GeneralBits/Tidbits/perf.html#shbuf)
>>>>
>>>> So my question is, the actual limit of the shared buffer will be defined
>>>> by OS or the shared_buffer parameter in the postgres.conf to figure whether
>>>> a victim dirty page needs to be selected for disk write or not?
>>>>
>>>> Thanks!
>>>>
>>>
>>>
>>
>