By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,105 Members | 2,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,105 IT Pros & Developers. It's quick & easy.

poor cpu utilization on dual cpu box

P: n/a

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
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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%3A150000443%2Cd%3A22708228%2Cslk%3Atext% 2Csec%3Amail> - with improved product search


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

iD8DBQE/lqjpPkvkZVZzNY0RAtw/AKDHhIQSAhOVN/+OMIjeChpwky80BwCgqSOu
MhEDKjU23Rb4TONykGzM8wQ=
=IRJU
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.