Re: PostgreSQL survey - Mailing list pgsql-advocacy

On 12/12/2011 03:42 PM, Kevin Grittner wrote:
> Our biggest server, which has just gone into production, is 32 cores
> with 256GB RAM.  We are able to comfortably support several TB of
> databases running tens of millions of database transactions per day
> on servers with 16 cores and 128GB RAM.

I think around 16 cores (two CPU sockets) and 64 to 128GB of RAM is the
sweet spot for PostgreSQL up to version 9.1.  I have customers with
servers going up to 48 cores and 256GB of RAM...they really don't
improve that much yet though.  Part if this isn't just Postgres, it's
the hardware.  Check out my "Bottom-Up Database Benchmarking" talk
slides at http://www.2ndquadrant.com/en/talks/ and stare carefully at
the "DDR3 Era" results.  The servers that hit the highest memory
throughput there are the 4 X 6172 (48 cores, AMD) and 4 X E7540 (48 HT
cores, Intel) servers.  But trace those curves back to where they
start.  Until you clear 6 active cores, they're sometimes significantly
slower than the smaller boxes.  That's the trade-off in the current
architecture; the systems with lots of cores and RAM segment things such
that no one core can really achieve great speeds on its own.  Those big
servers are only worthwhile when the workload is always heavy.  If it
drops to only a few processes...you'd do better with one of the
two-socket Intel boxes, like the 2 X X5560 (8 cores!) and 2 X E5620 (16
HT cores) shown there.  It's kind of embarassing when I discover my $250
i7-870 at home outruns a customer's 48 core beast when running a single
core job, because I get 10GB/s per core while they get 5GB/s.

If you do always prioritize for always busy (like Kevin's workload), the
bigger systems can still make perfect sense.  There's no denying that
they can hit major throughput when enough processes are running.  And
the scalability improvements coming in PostgreSQL 9.2 will help CPU
bound systems go even faster.  Just make sure you're really CPU bound
though.  I'm having one of these discussions right now with a customer
who's IO bound specifically on seeking; they really need to
re-prioritize their budget toward less cores and RAM than they'd planned
for, and use SSD storage instead.  I'm working on a white paper right
now about how long it takes to populate all the RAM usefully on a big
system.  I's not pretty seeing how long a server with 256GB of RAM but
regular storage takes to return to typical performance after a reboot,
the answer can be measured in hours sometimes.

 > 3. What OS you are using to run this mission critical system on
PostgreSQL ? Linux, Unix ?

Most of my customers are on Linux, with the same basic line-up Josh
Berkus already listed as other platforms.  The only major platform I
have multiple customers on I haven't seen mentioned yet is FreeBSD.
Main reason for Linux over FreeBSD is general popularity and broader
hardware support.  You still need to be pretty careful what server
hardware you use for FreeBSD, and it's tougher to hire people who know
it well than Linux.  Major reasons to choose FreeBSD include DTrace
(which still has advantages over Systemtap on Linux, especially in ease
of use/available sample code), and ZFS.

As someone who provides an answer to question (4), I don't want to
expand this into an extended ad.  Here's a list of places that include
some notable lists or stories about serious PostgreSQL or close to
standard commercial versions of PostgreSQL, and only one of them happens
to mention me twice:

http://www.postgresql.org/about/users/
http://en.wikipedia.org/wiki/PostgreSQL#Prominent_users
http://www.enterprisedb.com/success-stories/customers
http://www.2ndquadrant.com/en/case-studies/

I think our case studies have something interesting to say about good
ways to approach deploying an open-source stack, wouldn't mention them
otherwise.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us