Re: Need for speed 2

From: Frank Wiles
Subject: Re: Need for speed 2
Date: ,
Msg-id: 20050825084711.2b08f485.frank@wiles.org
(view: Whole thread, Raw)
In response to: Need for speed 2  (Ulrich Wisser)
List: pgsql-performance

Tree view

Need for speed  (Ulrich Wisser, )
 Re: Need for speed  (Richard Huxton, )
 Re: Need for speed  (John A Meinel, )
 Re: Need for speed  ("Jeffrey W. Baker", )
 Re: Need for speed  (Alex Turner, )
  Re: Need for speed  ("Jim C. Nasby", )
 Re: Need for speed  (Dennis Bjorklund, )
  Re: Need for speed  (Ulrich Wisser, )
   Re: Need for speed  (Tom Lane, )
   Re: Need for speed  ("Jeffrey W. Baker", )
   Re: Need for speed  (Josh Berkus, )
   Re: Need for speed  (Ron, )
 Re: Need for speed  (Ron, )
  Re: Need for speed  (Matthew Nuzum, )
   Need for speed 2  (Ulrich Wisser, )
    Re: Need for speed 2  (Frank Wiles, )
    Re: Need for speed 2  (Ron, )
     Re: Need for speed 2  (Kelly Burkhart, )
      Re: Need for speed 2  (Alex Turner, )
    Re: Need for speed 2  ("Merlin Moncure", )
     What *_mem to increase when running CLUSTER  (Andrew Lazarus, )
      Re: What *_mem to increase when running CLUSTER  (Steve Poe, )
      Re: What *_mem to increase when running CLUSTER  (Tom Lane, )
 Re: Need for speed  ("Roger Hand", )
  Re: Need for speed  (Christopher Browne, )

On Thu, 25 Aug 2005 09:10:37 +0200
Ulrich Wisser <> wrote:

> Pentium 4 2.4GHz
> Memory 4x DIMM DDR 1GB PC3200 400MHZ CAS3, KVR
> Motherboard chipset 'I865G', two IDE channels on board
> 2x SEAGATE BARRACUDA 7200.7 80GB 7200RPM ATA/100
> (software raid 1, system, swap, pg_xlog)
> ADAPTEC SCSI RAID 2100S ULTRA160 32MB 1-CHANNEL
> 2x SEAGATE CHEETAH 15K.3 73GB ULTRA320 68-PIN WIDE
> (raid 1, /var/lib/pgsql)
>
> Database size on disc is 22GB. (without pg_xlog)
>
> Please find my postgresql.conf below.
>
> Putting pg_xlog on the IDE drives gave about 10% performance
> improvement. Would faster disks give more performance?

  Faster as in RPM on your pg_xlog partition probably won't make
  much of a difference.  However, if you can get a drive with better
  overall write performance then it would be a benefit.

  Another thing to consider on this setup is whether or not you're
  hitting swap often and/or logging to that same IDE RAID set.  For
  optimal insertion benefit you want the heads of your disks to
  essentially be only used for pg_xlog.  If you're having to jump
  around the disk in the following manner:

    write to pg_xlog
    read from swap
    write syslog data
    write to pg_xlog
    ...
    ...

  You probably aren't getting anywhere near the benefit you could.  One
  thing you could easily try is to break your IDE RAID set and put
  OS/swap on one disk and pg_xlog on the other.

> If one query contains so much data, that a full table scan is needed,
> I  do not care if it takes two minutes to answer. But all other
> queries  with less data (at the same time) still have to be fast.
>
> I can not stop users doing that kind of reporting. :(
>
> I need more speed in orders of magnitude. Will more disks / more
> memory do that trick?

  More disk and more memory always helps out.  Since you say these
  queries are mostly on not-often-used data I would lean toward more
  disks in your SCSI RAID-1 setup than maxing out available RAM based
  on the size of your database.

 ---------------------------------
   Frank Wiles <>
   http://www.wiles.org
 ---------------------------------



pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: Read/Write block sizes
From: Tom Lane
Date:
Subject: Re: What *_mem to increase when running CLUSTER