poor cpu utilization on dual cpu box

[Simon Sadedin]

Folks,

I’m hoping someone can give me some pointers to resolving an issue with postgres and it’s ability to utilize multiple CPUs effectively.

The issue is that no matter how much query load we throw at our server it seems almost impossible to get it to utilize more than 50% cpu on a dual-cpu box. For a single connection we can use all of one CPU, but multiple connections fail to increase the overall utilization (although they do cause it to spread across CPUs).

The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading disabled) running a fairly standard Redhat 9 distribution. We are using postgres on this platform with a moderate sized data set (some hundreds of megs of data). The tests perform no updates and simply hit the server with a single large complex query via a multithreaded java/jdbc client. To avoid network distortion we run the client on the localhost (its cpu load is minimal). We are running with shared buffers large enough to hold the entire database and sort memory of 64m, should easily be enough to prevent sorting to disk.

At this point I’ve tried everything I can think of to diagnose this - checking the pg_locks table indicates that even under heavy load there are no ungranted locks, so it would appear not to be a locking issue. Vmstat/iostat show no excessive figures for network or io waits. The only outlandish figure is that context switches which spike up to 250,000/sec (seems large). By all indications, postgres is waiting internally as if it is somehow singlethreaded. However the documentation clearly indicates this should not be so.

Can anyone give me some pointers as to why postgres would be doing this? Is postgres really multi-process capable or are the processes ultimately waiting on each other to run queries or access shared memory?

On a second note, has anyone got some tips on how to profile postgres in this kind of situation? I have tried using gprof, but because postgres spawns its processes dynamically I always end up profiling the postmaster (not very useful).

Thanking in advance for any help!

Cheers,

Simon.

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

[Andreas Fromm]

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

It is probably not really a pg issue that you don't get your CPUs fully
used. I would guess that one CPU saturates the memory bus or the disk
bandwidth depending on where your data is. This is the typical behavior
for memory bounded applications where you don't get the full performanco
out of your box and the only thing you can do is to improve your
application to reuse data which is in the caches, which is something you
probably won't be able to do, due to the nature of databse applications.

Regards
Andreas Fromm
Simon Sadedin wrote:

Folks,

Im hoping someone can give me some pointers to resolving an issue with
postgres and its ability to utilize multiple CPUs effectively.

The issue is that no matter how much query load we throw at our server
it seems almost impossible to get it to utilize more than 50% cpu on a
dual-cpu box. For a single connection we can use all of one CPU, but
multiple connections fail to increase the overall utilization (although
they do cause it to spread across CPUs).

The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading
disabled) running a fairly standard Redhat 9 distribution. We are
using postgres on this platform with a moderate sized data set (some
hundreds of megs of data). The tests perform no updates and simply hit
the server with a single large complex query via a multithreaded
java/jdbc client. To avoid network distortion we run the client on the
localhost (its cpu load is minimal). We are running with shared
buffers large enough to hold the entire database and sort memory of 64m,
should easily be enough to prevent sorting to disk.

At this point Ive tried everything I can think of to diagnose this -
checking the pg_locks table indicates that even under heavy load there
are no ungranted locks, so it would appear not to be a locking issue.
Vmstat/iostat show no excessive figures for network or io waits. The
only outlandish figure is that context switches which spike up to
250,000/sec (seems large). By all indications, postgres is waiting
internally as if it is somehow singlethreaded. However the
documentation clearly indicates this should not be so.

Can anyone give me some pointers as to why postgres would be doing
this? Is postgres really multi-process capable or are the processes
ultimately waiting on each other to run queries or access shared memory?

On a second note, has anyone got some tips on how to profile postgres in
this kind of situation? I have tried using gprof, but because postgres
spawns its processes dynamically I always end up profiling the
postmaster (not very useful).

Thanking in advance for any help!

Cheers,

Simon.

------------------------------------------------------------------------
Do you Yahoo!?
The New Yahoo! Shopping
<http://shopping.yahoo. com/?__yltc=s%3A150 000443%2Cd%3A22 708228%2Cslk%3A text%2Csec%3Ama il> - with improved product search

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/lqjpPkvkZVZzNY0 RAtw/AKDHhIQSAhOVN/+OMIjeChpwky80B wCgqSOu
MhEDKjU23Rb4TON ykGzM8wQ=
=IRJU
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org