Re: Shared Buffer Size

2011/5/28 Carl von Clausewitz <clausewitz45(at)gmail(dot)com>:
> 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

this max_stack_depth is unsane it should be the result of (ulimit -u)
- 1MB maximum.
Except if you have a kernel build with a stack_depth of 64MB, which
would surprised me. (common kernel have 8MB or 16MB of stack_depth)

> 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!
>>>
>>
>
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support