Date: Thu, 25 Aug 2005 08:47:11 -0500
From: Frank Wiles <frank@wiles.org>
To: Ulrich Wisser <ulrich.wisser@relevanttraffic.se>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need for speed 2
Message-Id: <20050825084711.2b08f485.frank@wiles.org>
In-Reply-To: <430D6EED.10603@relevanttraffic.se>
References: <6.2.3.4.0.20050817140116.05d317b0@pop.earthlink.net>
	<f3c0b408050817133325dfa491@mail.gmail.com>
	<430D6EED.10603@relevanttraffic.se>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 25 Aug 2005 09:10:37 +0200
Ulrich Wisser <ulrich.wisser@relevanttraffic.se> 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 <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------