Re: Shared Buffer Size

[preetika tyagi <preetikatyagi@xxxxxxxxx>]

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@xxxxxxxxx> 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@xxxxxxxxx>

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.rice@xxxxxxxxx> 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@xxxxxxxxx> 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!