Re: Hardware spec]

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

Willo van der Merwe wrote:
> 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
>
>
1.) If this is when the system is heavily loaded, you have more capacity
than you need, on the average. Processors are idle, not in wait state. You
have enough memory (no swapping going on), disks not too busy (probably).
But if it is not heavily loaded, run these when it is.

2.) Did you let vmstat and iostat run just once, or are these the last of
several reports. Because these tell the average since boot for the first
report of each when they run the first time. If so, the numbers may not mean
much.

Here is iostat for my machine that is running right now. Only lines involved
in postgreSQL are shown:

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda8            409.33         0.67      3274.00         40     196440
sdb7              0.12         0.00         0.93          0         56
sdc1             44.73         0.13       357.73          8      21464
sdd1             23.43         0.00       187.47          0      11248
sde1            133.45         0.00      1067.60          0      64056
sdf1             78.25         0.00       626.00          0      37560

On sda8 is the Write-Ahead-Log.
on sdb7 are some small seldom-used relations, but also the input files that
sda and sdb are what my system uses for other stuff as well, though sda is
not too heavily used and sdb even less.
I am presently loading into the database. sdc1, sdd1, sde1, and sdf1 are the
 drives reserved for database only.

I would suggest getting at least two hard drives on the new system and
perhaps putting the WAL on one and the rest on the other. My sda and sdb
drives are around 72 GBytes and the sdc, sdd, sde, and sdf are about 18
GBytes. I believe the more spindles the better (within reason) and dividing
the stuff up with the indices separate from the associated data to reduce
seeking.

$ vmstat 30
procs -----------memory----------  ---swap--  -----io---- --system--
- -----cpu------
 r  b   swpd   free   buff   cache   si   so    bi    bo     in    cs us sy
id wa
 5  1   1340 248456 248496 6652672    0    0    28   149      2     1 94  2
   3  0
 4  2   1340 244240 248948 6656364    0    0     0  2670   1248 11320 80  4
14  2
 4  1   1340 241008 249492 6659864    0    0     0  2701   1222 11432 82  4
12  2
 5  0   1340 246268 249868 6653644    0    0     0  2799   1223 11412 83  4
12  2

My machine was idle most of the time since boot (other than running BOINC
stuff), but is busy loading data into the database at the moment. See how
the first line differs from the others? I have 8GBytes RAM on this 32-bit
machine running Red Hat Enterprise Linux 5.



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

iD8DBQFG4BYFPtu2XpovyZoRArU9AJ9o3OvYNxmQVhINTcRCADy0/fv30wCfZ3oJ
WItsCN75Xxhv52AqF6AIXmk=
=3AfU
-----END PGP SIGNATURE-----