Re: Opteron scaling with PostgreSQL

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)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!
;-)