Re: Tuning for PostGreSQL Database

From: Sean Chittenden (sean_at_chittenden.org)
Date: 07/20/03

  • Next message: Paul Pathiakis: "Tuning for PostGreSQL Database" 
    Date: Sun, 20 Jul 2003 04:09:39 -0700
To: Paul Pathiakis <paul@pathiakis.com>

    > FreeBSD 4.8 or 5.1?

    4.8 is currently faster than 5.1.

    > OS configuration, any suggestions on SWAP layout, how much
    > swap, both drives?

    If this is a demo and you're looking for speed, SWAP isn't going to
    buy you anything. Have some swap that way idle bits can be paged out.
    As for your drives, use two drives for data (striped) and one drive
    for WAL logs. Put swap wherever, it won't matter.

    > Are there optimal parameters for sizing on the disk
    > throughput?

    Increase your file system cache.

    > That is, newfs parameters, i-node configuration, tunefs parameters,
    > async on the drives, etc? (consider nothing trivial, please feel
    > free to just lay into this like I'm a newbie - considering my
    > not-so-extensive experience with PostgreSQL)

    Read the /usr/local/pgsql/post-install-notes, for starters.

    > Should I keep softupdates on or off? This is a db and I am
    > constrained by data integrity issues, however, I'm looking for as
    > much speed as possible given the limited setup.

    Softupdates, on.

    > what is the best layout of OS, PostgreSQL, and PostgreSQL
    > logging on a 2 disk configuration? 3 disk configuration?

    See above.

    > Is there anything that I should put in the following files for
    > tuning:
    >
    > systrcl.conf (semaphore and shared memory configurations,
    > etc?)

    See the archives, there are bunches of recommendations for this. You
    might also want to check the performance@PostgreSQL.org list's
    archives too.

    > loader.conf (hw.ata.atapi_dma on or off? how do I make the
    > hardware scream as fast as possible at tuning level and then with
    > considerations to running PostgreSQL? Give as much too me as you
    > can. I'm the sysadmin and I love how easily supportable FreeBSD is
    > with regards to software, package, OS, maintenance. People are
    > vying for XP and Suse. However, I'll end up supporting this and I
    > know how easy FreeBSD is to support.

    > If there's additional reading, let me know. I'm more than
    > happy to follow links, read more about PostgreSQL, etc.

    Use the most recent version of PostgreSQL, 7.4-devel (most recent CVS
    snapshot if you can, it's in BETA and very stable, IMHO). Once you
    get your demo installed, read through:

    http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
    http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
    http://developer.postgresql.org/docs/postgres/performance-tips.html

    A fair number of the PgSQL experts recommend using FreeBSD's,
    actually:

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

    Make liberal use of EXPLAIN ANALYZE [query], VACUUM ANALYZE, and
    possibly CLUSTER. If you have any probs once you have something up
    and running, come back with details and we'll get your demo to fly if
    it isn't already (should be after reading the above links though).

    -sc 

    -- 
Sean Chittenden
_______________________________________________
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: Paul Pathiakis: "Tuning for PostGreSQL Database"

    Relevant Pages

    • RH 7.3 system crashing
      ... is used as a Samba server, cashing name server, internal web server ... SCSI drives setup as RAID5. ... restarted samba, apache and backup script ... Suspected swap area may be bad on drive. ...
      (comp.os.linux.misc)
    • Re: couple newbie questions (install & doc)
      ... a Linux power user, or at least quite familiar with it. ... Plan on swap size to be twice the ... real performance problems with swap spread over three hard drives. ... > suggestions as to how I should partition my disk2? ...
      (alt.os.linux.suse)
    • Re: Raid 0 Swap?
      ... Don't do that - swap on raid0. ... That is, from 2*70=140 disk drives, 5 failed in a year. ... less chance of failure than one in 140. ... It is probably possible to corrupt the whole filesystem this way too, ...
      (Linux-Kernel)
    • RE: Having a hard time copying root file system to new drive... [SOLVED]
      ... From rescue mode you might mount the /boot partition ... booting and edit this on the fly instead of a rescue boot again. ... I KNOW that having a mislabeled swap mislabeled as seen by initrd does not ... for example) and these drives will be mounted RO in ...
      (Fedora)
    • Re: How do I determine appropriate swapspace settings? And other partitioning questions.
      ... giving 50% over physical as swap. ... > Linux is different. ... I am presently running on two IDE hard drives, ... How does Linux like to have it's partitions metered ...
      (comp.os.linux)