Date: Sat, 10 Mar 2012 10:19:29 +0000
From: Rory Campbell-Lange <rory@campbell-lange.net>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comments requested on IO performance : new db server
Message-ID: <20120310101929.GA9760@campbell-lange.net>
References: <20120309111558.GA22643@campbell-lange.net>
	<CAHyXU0wx5bDCRbO1M2ECBV+6ksCpRxhDR6aCHyCXCzuzph=9+Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAHyXU0wx5bDCRbO1M2ECBV+6ksCpRxhDR6aCHyCXCzuzph=9+Q@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)

On 09/03/12, Merlin Moncure (mmoncure@gmail.com) wrote:
> On Fri, Mar 9, 2012 at 5:15 AM, Rory Campbell-Lange
> <rory@campbell-lange.net> wrote:
> > I've taken the liberty of reposting this message as my addendum to a
> > long thread that I started on the subject of adding a new db server to
> > our existing 4-year old workhorse got lost in discussion.
> >
> > Our workload is several small databases totalling less than 40GB of disk
> > space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz
> > and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set
> > of tests on the machine we intend to buy. The test rig had the following
> > setup:
> >
> > LSI MegaRAID SAS 9260-8i
> > Firmware: 12.12.0-0090
> > Kernel: 2.6.39.4
> > Hard disks: 4x WD6000BLHX
> > Test done on 256GB volume
> > BS = blocksize in bytes
> >
> > The test tool is fio. I'd be grateful to know if the results below are
> > considered acceptable. An ancillary question is whether a 4096 block
> > size is a good idea. I suppose we will be using XFS which I understand
> > has a default block size of 4096 bytes.
> >
> > RAID 10
> > --------------------------------------
...
> > --------------------------------------
> > Random write
> >
> >    BS           MB/s             IOPs
> >   512        0000.53        001103.60
> >  1024        0001.15        001179.20
> >  4096        0004.43        001135.30
> >  16384        0017.61        001127.56
> >  65536        0061.39        000982.39
> > 131072        0079.27        000634.16
> > --------------------------------------
> 
> since your RAM is larger than the database size, read performance is
> essentially a non-issue.    your major gating factors are going to be
> cpu bound queries and  random writes -- 1000 IOPS essentially puts an
> upper bound on your write TPS, especially if your writes are frequent
> and randomly distributed, the case that is more or less simulated by
> pgbench with large scaling factors.
> 
> Now, 1000 write tps is quite alot  (3.6 mil transactions/hour) and
> your workload will drive the hardware consideration.

Thanks for your comments, Merlin. With regard to the "gating factors" I
believe the following is pertinent:

CPU

My current server has 2 * quad Xeon  E5420 @ 2.50GHz. The server
occasionally reaches 20% sutained utilisation according to sar.
This cpu has a "passmark" of 7,730. 
http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5420+%40+2.50GHz

My proposed CPU is an E5620 @ 2.40GHz with CPU "passmark" of 9,620
http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5620+%40+2.40GHz

Since the workload will be very similar I'm hoping for about 20% better
CPU performance from the new server, which should drop max CPU load by
5% or so.

Random Writes

I'll have to test this. My current server (R10 4*15K SCSI) produced the
following pgbench stats while running its normal workload:
 
    -c  -t     TPS
    5   20000  446
    10  10000  542
    20   5000  601
    30   3333  647

I'd be grateful to know what parameters I should use for a "large
scaling factor" pgbench test.

Many thanks
Rory

-- 
Rory Campbell-Lange
rory@campbell-lange.net

Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928