From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Gary Warner <gar@cis.uab.edu>
To: pgsql-performance@postgresql.org
Subject: Re: "large" spam tables and performance: postgres memory parameters
Date: Thu, 07 Jan 2010 11:35:48 -0600
Message-ID: <4B45C714020000250002DEF4@gw.wicourts.gov> (raw)
In-Reply-To: <1370423997.141099.1262877797658.JavaMail.root@zimbra.cis.uab.edu>
References: <950265612.141041.1262877637416.JavaMail.root@zimbra.cis.uab.edu>
<1370423997.141099.1262877797658.JavaMail.root@zimbra.cis.uab.edu>
Welcome out of the shadows, Gary! ;-)
Gary Warner <gar@cis.uab.edu> wrote:
> My biggest question mark there really has to do with how many
> users I have and how that might alter the results.
In benchmarks I've run with our software load, I've found that I get
best throughput when I use a connection pool which limits the active
database transaction count to (2 * CPU_count) + effective_spindles.
CPU_count should be fairly obvious; effective_spindles is
essentially "what's the maximum number of random read requests your
disk subsystem can productively handle concurrently?"
One or two others commented that their benchmark results seemed to
fit with that formula. I don't know just how far to trust it as a
generalization, but in the absence of anything else, it probably
isn't a horrible rule of thumb. If you expect to have more logical
connections than that number, you might want to establish a
connection pool which limits to that number. Be sure that if it is
"full" when a request to start a transaction comes in, the request
queues instead of failing.
To convince yourself that this really helps, picture a hypothetical
machine which can only make progress on one request at a time, but
will task-switch among as many requests as are presented. Then
picture 100 requests being presented simultaneously, each of which
needs one second of time to complete. Even without figuring in the
overhead of task switching or the cache effects, it's clear that a
connection "pool" of one connection improve response time by almost
50% with no cost in throughput. When you factor in the very real
costs of having large numbers of requests competing, both throughput
and response time win with connection pooling above some threshold.
Of course, with multiple CPUs, multiple spindles, network latency,
etc., the pool should be large enough to tend to keep them all busy.
Of course, the exact point at which a connection pool gives optimal
performance depends on so many factors that the only way to *really*
get it right is to test with a realistic load through your actual
software. The above is just intended to suggest a reasonable
starting point.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, gar@cis.uab.edu
Subject: Re: "large" spam tables and performance: postgres memory parameters
In-Reply-To: <4B45C714020000250002DEF4@gw.wicourts.gov>
References: <950265612.141041.1262877637416.JavaMail.root@zimbra.cis.uab.edu> <1370423997.141099.1262877797658.JavaMail.root@zimbra.cis.uab.edu> <4B45C714020000250002DEF4@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox