Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D691A13364DE for ; Thu, 23 Dec 2010 10:46:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50004-03 for ; Thu, 23 Dec 2010 14:46:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id 22EAC133798E for ; Thu, 23 Dec 2010 10:46:30 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id 7139328F947; Thu, 23 Dec 2010 08:46:30 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id zRZquIA8OHS9; Thu, 23 Dec 2010 08:46:30 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh7.mail.rice.edu (Postfix) with ESMTP id 3F33A28F942; Thu, 23 Dec 2010 08:46:30 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id 7A7F82D5A6; Thu, 23 Dec 2010 08:46:28 -0600 (CST) Date: Thu, 23 Dec 2010 08:46:28 -0600 From: Kenneth Marshall To: tuanhoanganh Cc: Gurjeet Singh , Magnus Hagander , pgsql-performance@postgresql.org Subject: Re: PostgreSQL 9.0 x64 bit pgbench TPC very low question? Message-ID: <20101223144628.GL10252@aart.is.rice.edu> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201012/289 X-Sequence-Number: 41741 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