[PERFORM] Quad processor options

[Paul Tuckfield]

it's very good to understand specific choke points you're trying to
address by upgrading so you dont get disappointed. Are you truly CPU
constrained, or is it memory footprint or IO thruput that makes you
want to upgrade?

IMO The best way to begin understanding system choke points is vmstat
output.

Would you mind forwarding the output of "vmstat 10 120" under peak load
period? (I'm asusming this is linux or unix variant) a brief
description of what is happening during the vmstat sample would help a
lot too.

I am curious if there are any real life production quad processor
setups running postgresql out there. Since postgresql lacks a proper
replication/cluster solution, we have to buy a bigger machine.
Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI
hardware-raid 10.
Has anyone experiences with quad Xeon or quad Opteron setups? I am
looking at the appropriate boards from Tyan, which would be the only
option for us to buy such a beast. The 30k+ setups from Dell etc.
don't fit our budget.
Quad processor (xeon or opteron)
5 x SCSI 15K RPM for Raid 10 + spare drive
2 x IDE for system
ICP-Vortex battery backed U320 Hardware Raid
4-8 GB Ram
Would be nice to hear from you.
Regards,
Bjoern
---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bjoern Metzdorf]

We use XEON Quads (PowerEdge 6650s) and they work nice,
provided you configure the postgres properly.
Dell is the cheapest quad you can buy i think.
You shouldn't be paying 30K unless you are getting high CPU-cache
on each processor and tons of memory.

good to hear, I tried to online configure a quad xeon here at dell
germany, but the 6550 is not available for online configuration. at dell
usa it works. I will give them a call tomorrow.

I am actually curious, have you researched/attempted any
postgresql clustering solutions?
I agree, you can't just keep buying bigger machines.

There are many asynchronous, trigger based solutions out there (eRserver
etc..), but what we need is basically a master <-> master setup, which
seems not to be available soon for postgresql.

Our current dual Xeon runs at 60-70% average cpu load, which is really
much. I cannot afford any trigger overhead here. This machine is
responsible for over 30M page impressions per month, 50 page impressums
per second at peak times. The autovacuum daemon is a god sent gift :)

I'm curious how the recently announced mysql cluster will perform,
although it is not an option for us. postgresql has far superior
functionality.

They have 5 internal drives (4 in RAID 10, 1 spare) on U320,
128MB cache on the PERC controller, 8GB RAM.

Could you tell me what you paid approximately for this setup?

How does it perform? It certainly won't be twice as fast a as dual xeon,
but I remember benchmarking a quad P3 xeon some time ago, and it was
disappointingly slow...

Regards,
Bjoern

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

[Bjoern Metzdorf]

Well, from what I've read elsewhere on the internet, it would seem the
Opterons scale better to 4 CPUs than the basic Xeons do. Of course, the
exception to this is SGI's altix, which uses their own chipset and runs
the itanium with very good memory bandwidth.

This is basically what I read too. But I cannot spent money on a quad
opteron just for testing purposes :)

But, do you really need more CPU horsepower?
Are you I/O or CPU or memory or memory bandwidth bound? If you're sitting
at 99% idle, and iostat says your drives are only running at some small
percentage of what you know they could, you might be memory or memory
bandwidth limited. Adding two more CPUs will not help with that
situation.

Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid
controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1
Quantum ATLAS_V_18_SCA.

iostat show between 20 and 60 % user avg-cpu. And this is not even peak
time.

I attached a "vmstat 10 120" output for perhaps 60-70% peak load.

If your I/O is saturated, then the answer may well be a better RAID
array, with many more drives plugged into it. Do you have any spare
drives you can toss on the machine to see if that helps? Sometimes going
from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big boost in
performance.

Next drives I'll buy will certainly be 15k scsi drives.

In short, don't expect 4 CPUs to solve the problem if the problem isn't
really the CPUs being maxed out.
Also, what type of load are you running? Mostly read, mostly written, few
connections handling lots of data, lots of connections each handling a
little data, lots of transactions, etc...

In peak times we can get up to 700-800 connections at the same time.
There are quite some updates involved, without having exact numbers I'll
think that we have about 70% selects and 30% updates/inserts.

If you are doing lots of writing, make SURE you have a controller that
supports battery backed cache and is configured to write-back, not
write-through.

Could you recommend a certain controller type? The only battery backed
one that I found on the net is the newest model from icp-vortex.com.

Regards,
Bjoern

[Halford Dace]

- I'll bet you have a low value for shared buffers, like 10000. On
your 3G system
you should ramp up the value to at least 1G (125000 8k buffers)

In most cases this is almost the worst thing you can do. The only
thing
even worse would be setting it to 1.5 G.
Postgres is just happy with a moderate shared_buffers setting. We
usually recommend something like 10000. You could try 20000, but don't
increase it beyond that without strong evidence that it helps in your
particular case.
This has been discussed several times here, on -hackers and on
-general.
Search the archives for more information.

We have definitely found this to be true here. We have some fairly
complex queries running on a rather underpowered box (beautiful but
steam-driven old Silicon Graphics Challenge DM). We ended up using a
very slight increase to shared buffers, but gaining ENORMOUSLY through
proper optimisation of queries, appropriate indices and the use of
optimizer-bludgeons like "SET ENABLE_SEQSCAN = OFF"

Hal


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Daniel Kalchev]

It appears that your CPU is 'slow' while your disk subsystem is 'fast'.

I had once such situation with 15 kRPM drives and ~500MHz Pentium III. On that
system, the best solution was to either increase effective_cache_size or
decrease random_page_cost (the latter obviously has to do with the fast disk,
the former with the lots of RAM).

In any case, proper optimization of queries always helps. :-)

Daniel

Post by Halford Dace

- I'll bet you have a low value for shared buffers, like 10000. On
your 3G system
you should ramp up the value to at least 1G (125000 8k buffers)

In most cases this is almost the worst thing you can do. The only
thing
even worse would be setting it to 1.5 G.
Postgres is just happy with a moderate shared_buffers setting. We
usually recommend something like 10000. You could try 20000, but don't
increase it beyond that without strong evidence that it helps in your
particular case.
This has been discussed several times here, on -hackers and on
-general.
Search the archives for more information.

We have definitely found this to be true here. We have some fairly
complex queries running on a rather underpowered box (beautiful but
steam-driven old Silicon Graphics Challenge DM). We ended up using a
very slight increase to shared buffers, but gaining ENORMOUSLY through
proper optimisation of queries, appropriate indices and the use of
optimizer-bludgeons like "SET ENABLE_SEQSCAN = OFF"
Hal
---------------------------(end of broadcast)---------------------------

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

[Bjoern Metzdorf]

Post by Paul Tuckfield
Would you mind forwarding the output of "vmstat 10 120" under peak load
period? (I'm asusming this is linux or unix variant) a brief
description of what is happening during the vmstat sample would help a
lot too.

see my other mail.

We are running Linux, Kernel 2.4. As soon as the next debian version
comes out, I'll happily switch to 2.6 :)

Regards,
Bjoern

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