From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Need help in performance tuning.
Date: Fri, 09 Jul 2010 10:49:17 -0500
Message-ID: <4C36FEAD02000025000332FC@gw.wicourts.gov> (raw)
In-Reply-To: <alpine.DEB.2.00.1007091621380.2534@aragorn.flymine.org>
References: <AANLkTiknhZhKtGDidBsMWtPJpRgln9DrrsCebCEYk5XE@mail.gmail.com>
<4C369346.6010501@postnewspapers.com.au>
<AANLkTin4Q4APRnbJ85FRasU7n8LuhKibKYcGnexsEbTt@mail.gmail.com>
<8760.1278650566@sss.pgh.pa.us>
<1278680186.1914.36.camel@bnicholson-desktop>
<4C36F57102000025000332F6@gw.wicourts.gov>
<alpine.DEB.2.00.1007091621380.2534@aragorn.flymine.org>
In case there's any doubt, the questions below aren't rhetorical.
Matthew Wakeling <matthew@flymine.org> wrote:
> Interesting idea. As far as I can see, you are suggesting solving
> the too many connections problem by allowing lots of connections,
> but only allowing a certain number to do anything at a time?
Right.
> A proper connection pool provides the following advantages over
> this:
>
> 1. Pool can be on a separate machine or machines, spreading load.
Sure, but how would you do that with a built-in implementation?
> 2. Pool has a lightweight footprint per connection, whereas
> Postgres doesn't.
I haven't compared footprint of, say, a pgpool connection on the
database server to that of an idle PostgreSQL connection. Do you
have any numbers?
> 3. A large amount of the overhead is sometimes connection setup,
> which this would not solve. A pool has cheap setup.
This would probably be most useful where the client held a
connection for a long time, not for the "login for each database
transaction" approach. I'm curious how often you think application
software uses that approach.
> 4. This could cause Postgres backends to be holding onto large
> amounts of memory while being prevented from doing anything,
> which is a bad use of resources.
Isn't this point 2 again? If not, what are you getting at? Again,
do you have numbers for the comparison, assuming the connection
pooler is running on the database server?
> 5. A fair amount of the overhead is caused by context-switching
> between backends. The more backends, the less useful any CPU
> caches.
Would this be true while a backend was blocked? Would this not be
true for a connection pool client-side connection?
> 6. There are some internal workings of Postgres that involve
> keeping all the backends informed about something going on. The
> more backends, the greater this overhead is. (This was pretty
> bad with the sinval queue overflowing a while back, but a bit
> better now. It still causes some overhead).
Hmmm... I hadn't thought about that. Again, any numbers (e.g.,
profile information) on this?
> 7. That lock would have a metric *($!-load of contention.
Here I doubt you. It would be held for such short periods that I
suspect that collisions would be relatively infrequent compared to
some of the other locks we use. As noted in the email, it may
actually normally be an "increment and test" within an existing
locked block. Also, assuming that any "built in" connection pool
would run on the database server, why would you think the contention
for this would be worse than for whatever is monitoring connection
count in the pooler?
-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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, matthew@flymine.org, bnichols@ca.afilias.info, tgl@sss.pgh.pa.us
Subject: Re: Need help in performance tuning.
In-Reply-To: <4C36FEAD02000025000332FC@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