Re: Tuning for PostGreSQL Database

From: Christopher Weimann (cweimann_at_k12hq.com)
Date: 07/25/03

  • Next message: Michael E. Conlen: "RE: Tuning for PostGreSQL Database" 
    Date: Fri, 25 Jul 2003 12:26:01 -0400
To: Tom Samplonius <tom@sdf.com>

    On Fri 07/25/2003-12:03:32AM -0700, Tom Samplonius wrote:
    >
    > Maybe you should continue to worry. PostgreSQL isn't MySQL (or a
    > typical server application). It reads all database pages into its shared
    > memory area. It is wasteful for the DBMS and the OS to both cache this
    > data. You'll want the PostgreSQL shared memory size to be around 75% the
    > size of RAM (on a dedicated DBMS server). In fact, many commercial DBMS
    > systems will use raw writes to bypass the OS cache!
    >

    I was concerned about the disk cache because of this link

    http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#shbuf

    which says...

       PostgreSQL counts a lot on the OS to cache data files and
       hence does not bother with duplicating its file caching effort.
       The shared buffers parameter assumes that OS is going to cache
       a lot of files and hence it is generally very low compared
       with system RAM. Even for a dataset in excess of 20GB, a
       setting of 128MB may be too much, if you have only 1GB RAM
       and an aggressive-at-caching OS like Linux.

    But now that I have looked a bit more I see that this link

    http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node6.html

    which says...

       Ideally, the POSTGRESQL shared buffer cache will be:

           * Large enough to hold most commonly-accessed tables
           * Small enough to avoid swap pagein activity

    So I have conflicting documentation.

    I have machine with 4Gig of ram. What is the maximum
    value of SHMMAX on FreeBSD?

    _______________________________________________
    freebsd-performance@freebsd.org mailing list
    http://lists.freebsd.org/mailman/listinfo/freebsd-performance
    To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"

  • Next message: Michael E. Conlen: "RE: Tuning for PostGreSQL Database"

    Relevant Pages

    • Re: Slow File Load Through ODBC Driver
      ... buffer cache from 772 Megs to 132Megs ... virtual) ram immediately to fox when fox generated a large select statement. ... XP professional (suspecting kernel memory handling) -- no effect; ...
      (microsoft.public.fox.programmer.exchange)
    • Re: speeding up my runtime on a c6713.
      ... it for cache, and possibly enabled the cache controller? ... I ran out of IRAM memory so I moved all my variables to ERAM. ... "Are you using all 256k of DSP ram, or have you reserved up to 64k of ...
      (comp.dsp)
    • Re: [OT] Interview with Con Kolivas on Linux failures
      ... On one side there are people who point out that programs often allocate memory that goes unused most of the time, and this memory can be better used as disk cache. ... The ultimate result of this was the addition of a "swappiness" knob to 2.6 kernels to allow people to tune this to their taste. ... I haven't felt the need to tweak swappiness on my desktop machine from the default of 60, but it's also got 1 gigabyte of RAM. ...
      (Debian-User)
    • Re: Tips sought for optimizing 10.4.10s performance
      ... If you're doing graphics or video editing, even more RAM. ... upgrade to disks with those speeds. ... 7200 RPM disks have only had 2MB of cache. ...
      (comp.sys.mac.system)
    • Re: speed of int vs bool for large matrix
      ... >>as much data that will fit the Level 1 cache. ... I am not speaking about file I/O but RAM I/O. ... Memory access go through the bus at 300 MHZ, ...
      (comp.lang.c)