Tuning for PostGreSQL Database

From: Paul Pathiakis (paul_at_pathiakis.com)
Date: 07/20/03

  • Next message: Tom Samplonius: "Re: Tuning for PostGreSQL Database" 
    To: freebsd-performance@freebsd.org, freebsd-database@freebsd.org
Date: Sat, 19 Jul 2003 18:18:13 -0400

    Hi,

            I'm trying to setup a dedicated PostGreSQL Database server. First off, don't
    get me going on the inadequacies of the hardware setup, I am a sysadmin with
    a partial clue. :-) (The hardware was bought without any input from me for
    demo purposes.) It's an Intel board with twin 2.8 GHZ processors and Promise
    ATA Fastrack RAID built-in, 1 GB of RAM. Since this is just a
    throughput/response test against SuSe Linux Enterprise Server, Windows XP and
    FreeBSD, I'm going to break the mirror and try to do some intelligence about
    at least trying to log to a different drive. Anyhow, there are 2-3 drives in
    this box, no scsi, nothing. I want it to perform like no tomorrow on
    FreeBSD, not being a DB admin, I'm going to try to cover all the bases on
    this, so please let me know if I've missed something:

            First, this is a demo, I'm not worried about redundancy at this point.

            FreeBSD 4.8 or 5.1?

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

            Are there optimal parameters for sizing on the disk throughput? 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)

            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.

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

            Is there anything that I should put in the following files for tuning:

            systrcl.conf (semaphore and shared memory configurations, etc?)

            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.

            Thank you all!

            Paul Pathiakis

            

    _______________________________________________
    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: Tom Samplonius: "Re: Tuning for PostGreSQL Database"

    Relevant Pages

    • Re: corporate backers of freebsd
      ... FreeBSD and to not depend exclusively on one OS solution. ... Supporting FreeBSD drivers and subsystems, ... source tree. ... Last, but definitely not least, Google, through its wonderful `Summer ...
      (freebsd-questions)
    • Re: Hardware or OS problem? System Crashing...
      ... >> The sysadmin has no clue about either linux or freebsd! ... >> A corrupt JPEG cannot cause a crash of the OS, ... >> Windows XP, linux, and FreeBSD. ...
      (freebsd-questions)
    • Re: FreeBSD & Linux distro
      ... As a Sysadmin I have 2 cents to add to this discussion. ... goes weird these days I'm most often to find hardware is the ... Solaris, and FreeBSD, and two Debian servers. ... about backup software. ...
      (freebsd-questions)
    • Re: FreeBSD Security Advisory FreeBSD-SA-02:21.tcpip
      ... Why isn't it on the main FreeBSD FTP server and mirrors? ... You want a snapshot kernel supporting all that, ... I have used NFS as a method of distributing builds from a "master" box, ...
      (FreeBSD-Security)
    • Re: FAO Elder - a car to make you look like a Cock
      ... sysadmin and support company we use (useful for out of hours and ... sysadmins) are Linux all the way. ... not surprised that I find FreeBSD more logical to administer. ... given how little time it takes me this days to set up a mail server ...
      (uk.rec.cars.modifications)