Opteron scaling with PostgreSQL

   Some time ago, I asked about how well PostgreSQL scales with the
number of processors in an Opteron system.  To my surprise, no one
seemed to know!  Well, a couple of days ago, a shiny, new Celestica
A8440 showed up at my office, so I decided to run it through the paces.
  Hopefully, this will be useful to someone else as well!

Hardware info
-------------
Celestica A8440
4xOpteron 848
8 gigs PC3200 reg/ECC memory

Software info
-------------
Fedora core 2 x86-64
PostgreSQL 7.4.2
Added compile options: -O3 -m64
Startup options:  256 MB shared buffer, fsync OFF to eliminate the disk
system as a variable, 128 megs sort memory


Testing method
--------------
    I logged 10,000 queries from our production DB server, and wrote a Perl
program to issue them via an arbitrary number of "workers".  Before each
run, the database was "warmed up" by going through two preliminary runs
to ensure that caches and buffers were populated.

    Instead of removing processors (which would have also reduced the
memory), I used the boot argument "maxcpus" to limit the number of CPUs
that Linux would use.

Preliminary thoughts
--------------------
    After playing around, I found that the optimal size for the shared
buffer was 256 megs.  To the opposite of my expectations, using more
shared buffer resulted in a lower throughput.

Results!
--------

maxcpus        max queries per second
-------        ----------------------
1        378 qps @ 32 connections (baseline)
2        609 qps @ 96 connections (161% of baseline)
3        853 qps @ 48 connections (225% of baseline)
4        1033 qps @ 64 connections (273% of baseline)


   A graph of the throughputs for various numbers of CPUs and
connections can be found at  http://www.codon.com/PG-scaling.gif

steve