Re: Scaling further up - Mailing list pgsql-performance

Here's what I recorded today from iostat (linux, iostat -x -k, sda3 is
the pg slice, logs included) during peak time on the RAID-10 array -
What i see is mostly writes, and sometimes, quite a bit of writing,
during which the average wait times shoot up.

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s
avgrq-sz avgqu-sz   await  svctm  %util
/dev/sda3   18.81 113.21  3.90 36.33  181.54 1207.75    90.77   603.88
34.54     0.49    0.73   0.22   0.87
/dev/sda3    0.00 208.00  0.00 150.00    0.00 2884.00     0.00  1442.00
19.23     0.75    0.50   0.33   5.00
/dev/sda3    0.00 239.00  0.00 169.00    0.00 3264.00     0.00  1632.00
19.31     2.15    1.27   0.33   5.50
/dev/sda3    0.00 224.50  0.00 158.00    0.00 3060.00     0.00  1530.00
19.37     1.90    1.20   0.28   4.50
/dev/sda3    0.00 157.00  0.00 117.00    0.00 2192.00     0.00  1096.00
18.74     0.40    0.34   0.30   3.50
/dev/sda3    0.00 249.50  0.00 179.00    0.00 3596.00     0.00  1798.00
20.09    21.40   10.78   0.39   7.00
/dev/sda3    0.00 637.50  0.00 620.50    0.00 9936.00     0.00  4968.00
16.01  1137.15  183.55   1.85 115.00
/dev/sda3    0.00 690.00  0.00 548.50    0.00 9924.00     0.00  4962.00
18.09    43.10    7.82   0.46  25.50
/dev/sda3    0.00 485.00  0.00 392.00    0.00 7028.00     0.00  3514.00
17.93    86.90   22.21   1.14  44.50
/dev/sda3    0.00 312.50  0.00 206.50    0.00 4156.00     0.00  2078.00
20.13     3.50    1.69   0.53  11.00
/dev/sda3    0.00 386.50  0.00 275.50    0.00 5336.00     0.00  2668.00
19.37    16.80    6.10   0.60  16.50
/dev/sda3    0.00 259.00  0.00 176.50    0.00 3492.00     0.00  1746.00
19.78     3.25    1.84   0.40   7.00
/dev/sda3    0.00 196.00  0.00 99.00    0.00 2360.00     0.00  1180.00
23.84     0.10    0.10   0.10   1.00
/dev/sda3    0.00 147.00  0.00 100.00    0.00 1976.00     0.00   988.00
19.76     0.50    0.50   0.45   4.50
/dev/sda3    0.00 126.50  0.00 94.50    0.00 1768.00     0.00   884.00
18.71     0.20    0.21   0.21   2.00
/dev/sda3    0.00 133.50  0.00 106.50    0.00 1920.00     0.00   960.00
18.03     0.50    0.47   0.47   5.00
/dev/sda3    0.00 146.50  0.00 118.00    0.00 2116.00     0.00  1058.00
17.93     0.20    0.21   0.17   2.00
/dev/sda3    0.00 156.00  0.00 128.50    0.00 2276.00     0.00  1138.00
17.71     0.35    0.27   0.27   3.50
/dev/sda3    0.00 145.00  0.00 105.00    0.00 2000.00     0.00  1000.00
19.05     0.25    0.24   0.24   2.50
/dev/sda3    0.00  72.96  0.00 54.51    0.00 1019.74     0.00   509.87
18.71     0.17    0.31   0.31   1.72
/dev/sda3    0.00 168.50  0.00 139.50    0.00 2464.00     0.00  1232.00
17.66     0.65    0.47   0.39   5.50
/dev/sda3    0.00 130.50  0.00 100.00    0.00 1844.00     0.00   922.00
18.44     0.00    0.00   0.00   0.00
/dev/sda3    0.00 122.00  0.00 101.00    0.00 1784.00     0.00   892.00
17.66     0.25    0.25   0.25   2.50
/dev/sda3    0.00 143.00  0.00 121.50    0.00 2116.00     0.00  1058.00
17.42     0.25    0.21   0.21   2.50
/dev/sda3    0.00 134.50  0.00 96.50    0.00 1848.00     0.00   924.00
19.15     0.35    0.36   0.36   3.50
/dev/sda3    0.00 153.50  0.00 115.00    0.00 2148.00     0.00  1074.00
18.68     0.35    0.30   0.30   3.50
/dev/sda3    0.00 101.50  0.00 80.00    0.00 1452.00     0.00   726.00
18.15     0.20    0.25   0.25   2.00
/dev/sda3    0.00 108.50  0.00 92.50    0.00 1608.00     0.00   804.00
17.38     0.25    0.27   0.27   2.50
/dev/sda3    0.00 179.00  0.00 132.50    0.00 2492.00     0.00  1246.00
18.81     0.55    0.42   0.42   5.50
/dev/sda3    1.00 113.00  1.00 83.00   16.00 1568.00     8.00   784.00
18.86     0.15    0.18   0.12   1.00
/dev/sda3    0.00 117.00  0.00 97.50    0.00 1716.00     0.00   858.00
17.60     0.20    0.21   0.21   2.00
/dev/sda3    0.00 541.00  0.00 415.50    0.00 7696.00     0.00  3848.00
18.52   146.50   35.09   1.37  57.00
/dev/sda3    0.00 535.00  0.00 392.50    0.00 7404.00     0.00  3702.00
18.86   123.70   31.67   1.31  51.50
/dev/sda3    0.00 993.50  0.00 697.50    0.00 13544.00     0.00  6772.00
19.42   174.25   24.98   1.25  87.00
/dev/sda3    0.00 245.00  0.00 108.50    0.00 2832.00     0.00  1416.00
26.10     0.55    0.51   0.51   5.50

-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, March 02, 2004 4:16 PM
To: Anjan Dave
Cc: fred@redhotpenguin.com; William Yu; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Scaling further up


On Tue, 2 Mar 2004, Anjan Dave wrote:

> "By lots I mean dozen(s) in a raid 10 array with a good controller."
>
> I believe, for RAID-10, I will need even number of drives.

Correct.

> Currently,
> the size of the database is about 13GB, and is not expected to grow
> exponentially with thousands of concurrent users, so total space is
> not of paramount importance compared to performance.
>
> Does this sound reasonable setup?
> 10x36GB FC drives on RAID-10
> 4x36GB FC drives for the logs on RAID-10 (not sure if this is the
> correct ratio)? 1 hotspare
> Total=15 Drives per enclosure.

Putting the Logs on RAID-10 is likely to be slower than, or no faster
than
putting them on RAID-1, since the RAID-10 will have to write to 4
drives,
while the RAID-1 will only have to write to two drives.  now, if you
were
reading in the logs a lot, it might help to have the RAID-10.

> Tentatively, I am looking at an entry-level EMC CX300 product with 2GB

> RAID cache, etc.

Pick up a spare, I'll get you my home address, etc... :-)

Seriously, that's huge.  At that point you may well find that putting
EVERYTHING on a big old RAID-5 performs best, since you've got lots of
caching / write buffering going on.

> Question - Are 73GB drives supposed to give better performance because

> of higher number of platters?

Generally, larger hard drives perform better than smaller hard drives
because they a: have more heads and / or b: have a higher areal density.

It's a common misconception that faster RPM drives are a lot faster,
when,
in fact, their only speed advantage is slight faster seeks.  The areal
density of faster spinning hard drives tends to be somewhat less than
the
slower spinning drives, since the maximum frequency the heads can work
in
on both drives, assuming the same technology, is the same.  I.e. the
speed
at which you can read data off of the platter doesn't usually go up with
a
higher RPM drive, only the speed with which you can get to the first
sector.