Re: Opteron scaling with PostgreSQL - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Wolfe
> Sent: Thursday, June 10, 2004 2:09 PM
> To: pgsql-general
> Subject: [GENERAL] 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

I would very much like to see the same test with Fsync on.
A test that does not reflect real-world use has less value than one that
just shows how fast it can go.

For a read-only database, fsync could be turned off.  For any other
system it would be hair-brained and nobody in their right mind would do
it.

> 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

It is very impressive how well the system scales.  I would like to see a
PostgreSQL system run against these guys:
http://www.tpc.org/

It might prove interesting to see how it stacks up against commercial
systems.
Certainly when it comes to Dollars per TPS, there would be a stupendous
leg up to start with!
;-)