Re: Need for speed 2

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