From: Kenneth Marshall <ktm@rice.edu>
To: tuanhoanganh <hatuan05@gmail.com>
Cc: Gurjeet Singh <singh.gurjeet@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
Date: Thu, 23 Dec 2010 08:46:28 -0600
Message-ID: <20101223144628.GL10252@aart.is.rice.edu> (raw)
In-Reply-To: <AANLkTi=g2tVSGgW=3QVxB-Lm4YunDEypKtxLcNYGpZY9@mail.gmail.com>
References: <AANLkTikuSgM5t8JsWaP5gtd2vtgN3gLL_sKZzva2Ma_1@mail.gmail.com>
<iejcfr$d58$1@dough.gmane.org>
<AANLkTinfGOb8SRnf=1rKh5nJAqYZWaGeieH12DyDUxQg@mail.gmail.com>
<AANLkTinObqxMNt5y_zZiL24=BHrNTrt6rHngq-A9J-SA@mail.gmail.com>
<AANLkTin2_X5DKMxt+nrRX1GsqoBQU8_tfzsa0VLXT_xh@mail.gmail.com>
<AANLkTikYXFh4Ef3Q1v1f0hLy0JZ48Q5wKk-w1BqAh-CS@mail.gmail.com>
<AANLkTimTsW-w6w1561KRnUuuW9+26JncVQFu0rSo+0Ww@mail.gmail.com>
<AANLkTikWVK4nn5vpSqwx81EqD+Y5yVY6_OuJ2DoLyonb@mail.gmail.com>
<AANLkTimFt_iPFTMSfq-qvesjqq4KsdERBzYP=_6hdkoy@mail.gmail.com>
<AANLkTi=g2tVSGgW=3QVxB-Lm4YunDEypKtxLcNYGpZY9@mail.gmail.com>
On Thu, Dec 23, 2010 at 09:20:59PM +0700, tuanhoanganh wrote:
> Could you show me what parameter of pgbouncer.ini can do that. I read
> pgbouncer and can not make pgbouncer open and keep 200 connect to postgres
> (Sorry for my English)
>
> Thanks you very much.
>
> Tuan Hoang ANh
>
You need to use session pooling for that to work. From the man page:
In order not to compromise transaction semantics for connection
pooling, pgbouncer supports several types of pooling when
rotating connections:
Session pooling
Most polite method. When client connects, a server connection
will be assigned to it for the whole duration the client
stays connected. When the client disconnects, the server
connection will be put back into the pool. This is the
default method.
Transaction pooling
A server connection is assigned to client only during a
transaction. When PgBouncer notices that transaction is over,
the server connection will be put back into the pool.
Statement pooling
Most aggressive method. The server connection will be put back
into pool immediately after a query completes. Multi-statement
transactions are disallowed in this mode as they would break.
The fact that pgbouncer will not keep 200 connections open to
the database means that you do not have enough work to actually
keep 200 permanent connections busy. It is much more efficient
to use transaction pooling. You typically want the number of
persistent database connections to be a small multiple of the
number of CPUs (cores) on your system. Then set pgbouncer to
allow as many client connections as you need. This will give
you the best throughput and pgbouncer can setup and tear down
the connections to your clients much, much faster than making
a full connection to the PostgreSQL database.
Regards,
Ken
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: ktm@rice.edu, hatuan05@gmail.com, singh.gurjeet@gmail.com, magnus@hagander.net
Subject: Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question?
In-Reply-To: <20101223144628.GL10252@aart.is.rice.edu>
* 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