2011/5/28 Carl von Clausewitz <clausewitz45@xxxxxxxxx>: > 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@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! >>> >> > > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ ; PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
