Re: Comments requested on IO performance : new db server

On Fri, Mar 9, 2012 at 5:15 AM, Rory Campbell-Lange
<rory(at)campbell-lange(dot)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
> --------------------------------------
> Read sequential
>
>    BS           MB/s             IOPs
>   512        0129.26        264730.80
>  1024        0229.75        235273.40
>  4096        0363.14        092965.50
>  16384        0475.02        030401.50
>  65536        0472.79        007564.65
> 131072        0428.15        003425.20
> --------------------------------------
> Write sequential
>
>    BS           MB/s             IOPs
>   512        0036.08        073908.00
>  1024        0065.61        067192.60
>  4096        0170.15        043560.40
>  16384        0219.80        014067.57
>  65536        0240.05        003840.91
> 131072        0243.96        001951.74
> --------------------------------------
> Random read
>
>    BS           MB/s             IOPs
>   512        0001.50        003077.20
>  1024        0002.91        002981.40
>  4096        0011.59        002968.30
>  16384        0044.50        002848.28
>  65536        0156.96        002511.41
> 131072        0170.65        001365.25
> --------------------------------------
> 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.

merlin