SEO

Hi guys,

I'm have the rare opportunity to spec the hardware for a new database
server. It's going to replace an older one, driving a social networking
web application. The current server (a quad opteron with 4Gb of RAM and
80Gb fast SCSI RAID10) is coping with an average load of ranging between
1.5 and 3.5.

The new machine spec I have so far:
2 x Intel Xeon 2.33 GHz Dual Core Woodcrest Processors
4 Gb RAM
5x73 GB Ultra320 SCSI RAID 5 (288 GB storage)

I've heard that RAID 5 is not necessarily the best performer. Also, are
there any special tricks when partition the file system?

Regards,

Willo


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Willo van der Merwe wrote:
> Hi guys,
>
> I'm have the rare opportunity to spec the hardware for a new database
> server. It's going to replace an older one, driving a social networking
> web application. The current server (a quad opteron with 4Gb of RAM and
> 80Gb fast SCSI RAID10) is coping with an average load of ranging between
> 1.5 and 3.5.
>
> The new machine spec I have so far:

What's the limiting factor on your current machine - disk, memory, cpup?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Richard Huxton wrote:
> Willo van der Merwe wrote:
>> Hi guys,
>>
>> I'm have the rare opportunity to spec the hardware for a new database
>> server. It's going to replace an older one, driving a social networking
>> web application. The current server (a quad opteron with 4Gb of RAM and
>> 80Gb fast SCSI RAID10) is coping with an average load of ranging between
>> 1.5 and 3.5.
>>
>> The new machine spec I have so far:
> What's the limiting factor on your current machine - disk, memory, cpup?
I'm a bit embarrassed to admit that I'm not sure. The reason we're
changing machines is that we might be changing ISPs and we're renting /
leasing the machines from the ISP.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Willo van der Merwe wrote:
> Richard Huxton wrote:
>> Willo van der Merwe wrote:
>>> Hi guys,
>>>
>>> I'm have the rare opportunity to spec the hardware for a new database
>>> server. It's going to replace an older one, driving a social
>>> networking web application. The current server (a quad opteron with
>>> 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load
>>> of ranging between 1.5 and 3.5.
>>>
>>> The new machine spec I have so far:
>> What's the limiting factor on your current machine - disk, memory,
>> cpup?
> I'm a bit embarrassed to admit that I'm not sure. The reason we're
> changing machines is that we might be changing ISPs and we're renting /
> leasing the machines from the ISP.
>
Before you get rid of the current ISP, better examine what is going on with
the present setup. It would be good to know if you are memory, processor, or
IO limited. That way you could increase what needs to be increased, and not
waste money where the bottleneck is not.

- --
.~. Jean-David Beyer Registered Linux User 85642.
/V\ PGP-Key: 9A2FC99A Registered Machine 241939.
/( )\ Shrewsbury, New Jersey http://counter.li.org
^^-^^ 07:10:01 up 28 days, 10:32, 4 users, load average: 5.48, 4.77, 4.37
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFG3+FwPtu2XpovyZoRAmp+AJ9R4mvznqJ24ZCPK8DcTA sz2d34+QCfQzhH
vmXnoJO0vm/A/f/Ol0TOy6o=
=9rsm
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Jean-David Beyer wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Willo van der Merwe wrote:
>
>> Richard Huxton wrote:
>>
>>> Willo van der Merwe wrote:
>>>
>>>> Hi guys,
>>>>
>>>> I'm have the rare opportunity to spec the hardware for a new database
>>>> server. It's going to replace an older one, driving a social
>>>> networking web application. The current server (a quad opteron with
>>>> 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load
>>>> of ranging between 1.5 and 3.5.
>>>>
>>>> The new machine spec I have so far:
>>>>
>>> What's the limiting factor on your current machine - disk, memory,
>>> cpup?
>>>
>> I'm a bit embarrassed to admit that I'm not sure. The reason we're
>> changing machines is that we might be changing ISPs and we're renting /
>> leasing the machines from the ISP.
>>
>>
> Before you get rid of the current ISP, better examine what is going on with
> the present setup. It would be good to know if you are memory, processor, or
> IO limited. That way you could increase what needs to be increased, and not
> waste money where the bottleneck is not.
>
Good advice. After running a vmstat and iostat, it is clear, to my mind
anyway, that the most likely bottleneck is IO, next is probably some
more RAM.
Here's the output:
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
0 0 29688 80908 128308 3315792 0 0 8 63 6 8 17
2 80 1


avg-cpu: %user %nice %sys %iowait %idle
17.18 0.00 1.93 0.81 80.08

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 14.57 66.48 506.45 58557617 446072213
sda1 0.60 0.27 4.70 235122 4136128
sda2 0.38 0.77 2.27 678754 2002576
sda3 2.37 0.49 18.61 429171 16389960
sda4 0.00 0.00 0.00 2 0
sda5 0.71 0.66 5.46 578307 4807087
sda6 0.03 0.01 0.24 6300 214196
sda7 0.02 0.00 0.19 2622 165992
sda8 60.19 64.29 474.98 56626211 418356226


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

* Willo van der Merwe:

> Good advice. After running a vmstat and iostat, it is clear, to my
> mind anyway, that the most likely bottleneck is IO, next is probably
> some more RAM.

> Here's the output:
> procs -----------memory---------- ---swap-- -----io---- --system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us
> sy id wa
> 0 0 29688 80908 128308 3315792 0 0 8 63 6 8 17
> 2 80 1

You need to run "vmstat 10" (for ten-second averages) and report a
couple of lines.

> avg-cpu: %user %nice %sys %iowait %idle
> 17.18 0.00 1.93 0.81 80.08

Same for iostat.

Your initial numbers suggest that your server isn't I/O-bound, though
(the percentage spent in iowait is much too small, and so are the tps
numbers).

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

* Willo van der Merwe:

> Florian Weimer wrote:
>> You need to run "vmstat 10" (for ten-second averages) and report a
>> couple of lines.

> 2 80 1
> 5 0 61732 37052 28180 3431956 0 0 14 987 2320 2021 38

> sda3 3.30 0.00 26.40 0 264
> sda8 97.90 0.00 783.20 0 7832

These values don't look I/O bound to me. CPU usage is pretty low,
too.

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Florian Weimer wrote:
> You need to run "vmstat 10" (for ten-second averages) and report a
> couple of lines.
>
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us sy
id wa
1 0 61732 47388 27908 3431344 0 0 10 65 1 4 17
2 80 1
5 0 61732 37052 28180 3431956 0 0 14 987 2320 2021 38
4 56 2
1 0 61620 43076 28356 3432256 0 0 0 367 1691 1321 28
3 67 1
3 0 61620 37620 28484 3432740 0 0 0 580 4088 6792 40
5 54 1
2 0 61596 33716 28748 3433520 0 0 24 415 2087 1890 44
4 49 2
3 0 61592 45300 28904 3416200 3 0 61 403 2282 2154 41
4 54 1
7 0 61592 30172 29092 3416964 0 0 19 358 2779 3478 31
6 63 1
1 0 61580 62948 29180 3417368 6 0 27 312 3632 4396 38
4 57 1
1 0 61444 62388 29400 3417964 0 0 6 354 2163 1918 31
4 64 1
2 0 61444 53988 29648 3417988 0 0 0 553 2095 1687 33
3 63 1
1 0 61444 63988 29832 3418348 0 0 6 352 1767 1424 22
3 73 1
1 1 61444 51148 30052 3419148 0 0 50 349 1524 834 22
3 74 2
1 0 61432 53460 30524 3419572 7 0 7 868 4434 6706 43
6 49 2
1 0 61432 58668 30628 3420148 0 0 0 284 1785 1628 27
3 69 1

iostat sda8 is the where the pg_data resides, sda3 is /var/log
avg-cpu: %user %nice %sys %iowait %idle
17.36 0.00 1.96 0.82 79.86
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda3 2.38 0.49 18.71 432395 16672800
sda8 62.34 74.46 491.74 66345555 438143794

avg-cpu: %user %nice %sys %iowait %idle
30.50 0.00 3.57 1.70 64.22
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda3 5.60 0.00 44.80 0 448
sda8 120.20 134.40 956.00 1344 9560

avg-cpu: %user %nice %sys %iowait %idle
20.68 0.00 3.43 1.35 74.54
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda3 3.30 0.00 26.40 0 264
sda8 97.90 0.00 783.20 0 7832

avg-cpu: %user %nice %sys %iowait %idle
22.31 0.00 2.75 0.68 74.27
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda3 2.10 0.00 16.78 0 168
sda8 60.34 0.80 481.92 8 4824

avg-cpu: %user %nice %sys %iowait %idle
11.65 0.00 1.60 1.03 85.72
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda3 1.70 0.00 13.61 0 136
sda8 59.36 0.00 474.87 0 4744


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Thu, Sep 06, 2007 at 11:26:46AM +0200, Willo van der Merwe wrote:
> Richard Huxton wrote:
> >Willo van der Merwe wrote:
> >>Hi guys,
> >>
> >>I'm have the rare opportunity to spec the hardware for a new database
> >>server. It's going to replace an older one, driving a social networking
> >>web application. The current server (a quad opteron with 4Gb of RAM and
> >>80Gb fast SCSI RAID10) is coping with an average load of ranging between
> >>1.5 and 3.5.
> >>
> >>The new machine spec I have so far:
> >What's the limiting factor on your current machine - disk, memory, cpup?
> I'm a bit embarrassed to admit that I'm not sure. The reason we're
> changing machines is that we might be changing ISPs and we're renting /
> leasing the machines from the ISP.

Get yourself the ability to benchmark your application. This is
invaluable^W a requirement for any kind of performance tuning.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFG5vwqdO30qud8SkgRAtekAJ4tqwtP1sJXdxXIzCE8aP 6brs2ffwCff2FB
dvup+7zMXlp+OzJgCadZ988=
=b+F1
-----END PGP SIGNATURE-----

Decibel! wrote:
> On Thu, Sep 06, 2007 at 11:26:46AM +0200, Willo van der Merwe wrote:
>
>> Richard Huxton wrote:
>>
>>> Willo van der Merwe wrote:
>>>
>>>> Hi guys,
>>>>
>>>> I'm have the rare opportunity to spec the hardware for a new database
>>>> server. It's going to replace an older one, driving a social networking
>>>> web application. The current server (a quad opteron with 4Gb of RAM and
>>>> 80Gb fast SCSI RAID10) is coping with an average load of ranging between
>>>> 1.5 and 3.5.
>>>>
>>>> The new machine spec I have so far:
>>>>
>>> What's the limiting factor on your current machine - disk, memory, cpup?
>>>
>> I'm a bit embarrassed to admit that I'm not sure. The reason we're
>> changing machines is that we might be changing ISPs and we're renting /
>> leasing the machines from the ISP.
>>
>
> Get yourself the ability to benchmark your application. This is
> invaluable^W a requirement for any kind of performance tuning.
>
I'm pretty happy with the performance of the database at this stage.
Correct me if I'm wrong, but AFAIK a load of 3.5 on a quad is not
overloading it. It also seem to scale well, so if application's demand
increases I see a minimal increase in database server load.

I was just looking for some pointers as to where to go to ITO hardware
for the future, as I can now spec a new machine. I mean is it really
worth while going for one of those RAID controllers with the battery
backed cache, for instance. If so, are there any specific ones to look
out for? Which is better RAID 5, a large RAID 10 or smaller RAID 10's?
Should I bother with RAID at all?



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate