Re: poor cpu utilization on dual cpu box - Mailing list pgsql-general

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