Re: Tuning for PostGreSQL Database

From: Tom Samplonius (tom_at_sdf.com)
Date: 07/19/03

  • Next message: Dan Langille: "Re: Tuning for PostGreSQL Database" 
    Date: Sat, 19 Jul 2003 13:59:12 -0700 (PDT)
To: Paul Pathiakis <paul@pathiakis.com>

    On Sat, 19 Jul 2003, Paul Pathiakis wrote:

    > 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

      You don't really mention the nature of the test that you will running.
    Read intensive? Write intensive? 25/75 mix? Number of clients?

    > FreeBSD 4.8 or 5.1?

      Probably 5.1-RELEASE.

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

      As always, swap should be twice that of RAM. Ideally, depending on what
    the test consists of, you won't use swap at all.

    > 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)

      Well, it is database app, so very little on the filesystem is going to
    help. PostgreSQL will create a bunch of bunch of files, and randomly read
    and write into them.

    > 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, async off. Softupdates is just a better async.
    However, neither is going to help or hurt you much anyhow. Both
    softupdates and sync change how meta data is written to disk. Once the
    database files are created, meta data isn't much of any issue.

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

      Well, the database and the logs should be on separate devices. If the
    database can be broken up, do that too. I don't know that the OS files
    will be accessed much after boot, so that really isn't going to cause any
    significant load.

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

      Well, you need to increase the semaphores and shared memory
    configuration limits to match whatever you plan to give to PostgreSQL. I
    don't know whether the test consists of. If the test database is large,
    you'll want a large shared memory area. But I find that many people do
    tests with tiny non-real-world databases. It is pointless to make the
    shared area larger than the database size! Plus, you need some memory
    available for client connections. Don't know how many clients you will
    have connecting.

    > loader.conf (hw.ata.atapi_dma on or off? how do I make the
    ...

      DMA should probably be on, as long as your disks can handle it.

      You will have to test with HyperThreading too. If the client count is
    large, HyperThreading might help. However, it might increase latency.
    No idea if you test measures latency! If the test is just raw throughput
    with lots of clients, HTT is probably going to help. However, if you
    never have more than 4 connected clients, probably not.

    > 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

    Tom

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

    Relevant Pages

    • Re: Cool boat & travel computer
      ... For the "new" PCXT, the biggest FULL HEIGHT hard drive was the Tulin ... drives and 256K of RAM. ... these nice 9-track drives with bus adapter cards and drivers in Computer ... run the custom database, print perfect Meter Cards so the technicians on ...
      (rec.boats.cruising)
    • Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
      ... You'll not be using most of the system databases intensively so you don't need to seperate them, I mean locating them on different physical disks. ... If it's being used intensively in your environment then you should locate it's log and data files on different physical disks. ... For this question you must understand the reason why we should seperate data and log files. ... Of course these drives must be physically seperated so that you'll gain performance benefits. ...
      (microsoft.public.sqlserver.setup)
    • Re: File Corruption
      ... No one in the 3 institutions have access to their PC's hard drives. ... The "My Documents" is actually a spot on a network drive called the H ... This is probably the most common cause of corruption. ... There is never any valid excuse not to split a database. ...
      (microsoft.public.access.gettingstarted)
    • Re: File Corruption
      ... No one in the 3 institutions have access to their PC's hard drives. ... This is probably the most common cause of corruption. ... There is never any valid excuse not to split a database. ... single shared copy of a front end on a network, ...
      (microsoft.public.access.gettingstarted)
    • Re: Supporting multiple oracle versions in a trigger
      ... This is a very unscalable solution. ... Why would you do that in a database? ... > - Clients can add/modify their data collection requirements in minutes ... > - Clients can connect to the database with 3rd party sql tools and access ...
      (comp.databases.oracle.server)