SEO

-----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

iD8DBQFG4BYFPtu2XpovyZoRArU9AJ9o3OvYNxmQVhINTcRCAD y0/fv30wCfZ3oJ
WItsCN75Xxhv52AqF6AIXmk=
=3AfU
-----END PGP SIGNATURE-----

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