pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAre many idle connections bad?
5+ messages / 4 participants
[nested] [flat]
* Are many idle connections bad?
@ 2015-07-25 14:50 Craig James <cjames@emolecules.com>
2015-07-25 15:04 ` Re: Are many idle connections bad? Tom Lane <tgl@sss.pgh.pa.us>
2015-07-26 00:43 ` Re: Are many idle connections bad? Jeff Janes <jeff.janes@gmail.com>
2015-07-30 06:28 ` Re: Are many idle connections bad? Alex Hunsaker <badalex@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Craig James @ 2015-07-25 14:50 UTC (permalink / raw)
To: pgsql-performance
The canonical advice here is to avoid more connections than you have CPUs,
and to use something like pg_pooler to achieve that under heavy load.
We are considering using the Apache mod_perl "fast-CGI" system and perl's
Apache::DBI module, which caches persistent connections in order to improve
performance for lightweight web requests. Due to the way our customers are
organized (a separate schema per client company), it's possible that there
would be (for example) 32 fast-CGI processes, each of which had hundreds of
cached connections open at any given time. This would result in a thousand
or so Postgres connections on a machine with 32 CPUs.
But, Apache's fast-CGI mechanism allows you to specify the maximum number
of fast-CGI processes that can run at one time; requests are queue by the
Apache server if the load exceeds this maximum. That means that there would
never be more than a configured maximum number of active connections; the
rest would be idle.
So we'd have a situation where there there could be thousands of
connections, but the actual workload would be throttled to any limit we
like. We'd almost certainly limit it to match the number of CPUs.
So the question is: do idle connections impact performance?
Thanks,
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are many idle connections bad?
2015-07-25 14:50 Are many idle connections bad? Craig James <cjames@emolecules.com>
@ 2015-07-25 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-07-25 16:06 ` Re: Are many idle connections bad? Craig James <cjames@emolecules.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2015-07-25 15:04 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
Craig James <cjames@emolecules.com> writes:
> ... This would result in a thousand
> or so Postgres connections on a machine with 32 CPUs.
> So the question is: do idle connections impact performance?
Yes. Those connections have to be examined when gathering snapshot
information, since you don't know that they're idle until you look.
So the cost of taking a snapshot is proportional to the total number
of connections, even when most are idle. This sort of situation
is known to aggravate contention for the ProcArrayLock, which is a
performance bottleneck if you've got lots of CPUs.
You'd be a lot better off with a pooler.
(There has been, and continues to be, interest in getting rid of this
bottleneck ... but it's a problem in all existing Postgres versions.)
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are many idle connections bad?
2015-07-25 14:50 Are many idle connections bad? Craig James <cjames@emolecules.com>
2015-07-25 15:04 ` Re: Are many idle connections bad? Tom Lane <tgl@sss.pgh.pa.us>
@ 2015-07-25 16:06 ` Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 5+ messages in thread
From: Craig James @ 2015-07-25 16:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Sat, Jul 25, 2015 at 8:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Craig James <cjames@emolecules.com> writes:
> > ... This would result in a thousand
> > or so Postgres connections on a machine with 32 CPUs.
>
> > So the question is: do idle connections impact performance?
>
> Yes. Those connections have to be examined when gathering snapshot
> information, since you don't know that they're idle until you look.
> So the cost of taking a snapshot is proportional to the total number
> of connections, even when most are idle. This sort of situation
> is known to aggravate contention for the ProcArrayLock, which is a
> performance bottleneck if you've got lots of CPUs.
>
> You'd be a lot better off with a pooler.
>
OK, thanks for the info, that answers the question.
Another choice we have, since all schemas are in the same database, is to
use a single "super user" connection that has access to every schema. Each
fast-CGI would then only need a single connection. That's a lot more work,
as it requires altering our security, altering all of the SQL statements,
etc. It moves the security model from the database to the application.
A pooler isn't an idea solution here, because there is still overhead
associated with each connection. Persistent connections are blazingly fast
(we already use them in a more limited fast-CGI application).
Craig
>
> (There has been, and continues to be, interest in getting rid of this
> bottleneck ... but it's a problem in all existing Postgres versions.)
>
> regards, tom lane
>
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are many idle connections bad?
2015-07-25 14:50 Are many idle connections bad? Craig James <cjames@emolecules.com>
@ 2015-07-26 00:43 ` Jeff Janes <jeff.janes@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2015-07-26 00:43 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On Sat, Jul 25, 2015 at 7:50 AM, Craig James <cjames@emolecules.com> wrote:
> The canonical advice here is to avoid more connections than you have CPUs,
> and to use something like pg_pooler to achieve that under heavy load.
>
> We are considering using the Apache mod_perl "fast-CGI" system and perl's
> Apache::DBI module, which caches persistent connections in order to improve
> performance for lightweight web requests. Due to the way our customers are
> organized (a separate schema per client company),
>
And presumably with a different PostgreSQL user to go with each schema?
> it's possible that there would be (for example) 32 fast-CGI processes,
> each of which had hundreds of cached connections open at any given time.
> This would result in a thousand or so Postgres connections on a machine
> with 32 CPUs.
>
Why would it need so many cached connections per fast-CGI process? Could
you set up affinity so that the same client (or at least the same web
session) usually ends up at the same fast-CGI process (when it is
available), so the other fast-CGI processes don't need to cache DBI
connections for every DB user, but just for the ones they habitually serve?
>
> But, Apache's fast-CGI mechanism allows you to specify the maximum number
> of fast-CGI processes that can run at one time; requests are queue by the
> Apache server if the load exceeds this maximum. That means that there would
> never be more than a configured maximum number of active connections; the
> rest would be idle.
>
> So we'd have a situation where there there could be thousands of
> connections, but the actual workload would be throttled to any limit we
> like. We'd almost certainly limit it to match the number of CPUs.
>
> So the question is: do idle connections impact performance?
>
In my hands, truly idle connections are very very cheap, other than the
general overhead of a having a process in the process table and some local
memory. Where people usually run into trouble are:
1) that the idle connections are only idle "normally", and as soon as the
system runs into trouble the app starts trying to use all of those
usually-idle connections. So you get increased use at the exact moment
when you can't deal with it--when the system is already under stress. It
sounds like you have that base covered.
2) That the idle connections are "idle in transaction", not truly idle, and
this causes a variety of troubles, like vacuum not working effectively and
hint bits that are permanently unsettable.
2b) A special case of 2 is that transaction has inserted a bunch of
uncommitted tuples and then gone idle (or is just doing some other time
consuming things) before either committing them or rolling them back. This
can create an enormous amount of contention the proclock, as every process
which stumbles across the tuple then has to ask every other active process
"Is this your tuple? Are you done with it?". This could be particularly
problematic if for example you are bulk loading a vendor catalog in a
single transaction and therefore have a bunch of uncommitted tuples that
are hanging around for along time.
If you have reasonably good load generator, it is pretty easy to spin up a
bunch of idle connections and see what happens on your own hardware with
your own workload and your own version of PostgreSQL.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are many idle connections bad?
2015-07-25 14:50 Are many idle connections bad? Craig James <cjames@emolecules.com>
@ 2015-07-30 06:28 ` Alex Hunsaker <badalex@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Alex Hunsaker @ 2015-07-30 06:28 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On Sat, Jul 25, 2015 at 8:50 AM, Craig James <cjames@emolecules.com> wrote:
> The canonical advice here is to avoid more connections than you have CPUs,
> and to use something like pg_pooler to achieve that under heavy load.
>
> We are considering using the Apache mod_perl "fast-CGI" system and perl's
> Apache::DBI module, which caches persistent connections in order to improve
> performance for lightweight web requests. Due to the way our customers are
> organized (a separate schema per client company), it's possible that there
> would be (for example) 32 fast-CGI processes, each of which had hundreds of
> cached connections open at any given time. This would result in a thousand
> or so Postgres connections on a machine with 32 CPUs.
>
I don't have any hard performance numbers, but I ditched Apache::DBI years
ago in favor of pgbouncer.
BTW if you are starting something new, my advice would be to use PSGI/Plack
instead of apache/mod_perl. Granted, you can still use apache & mod_perl
with PSGI if you want. It's more flexible in that it gives you the option
of switching to another server willy nilly. I've found Starlet and Gazelle
to be easier to work with and more performant than apache + mod_perl.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-07-30 06:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-07-25 14:50 Are many idle connections bad? Craig James <cjames@emolecules.com>
2015-07-25 15:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-07-25 16:06 ` Craig James <cjames@emolecules.com>
2015-07-26 00:43 ` Jeff Janes <jeff.janes@gmail.com>
2015-07-30 06:28 ` Alex Hunsaker <badalex@gmail.com>
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