Pgbouncer is a lightweight connection pooling software for PostgreSQL. Any application can connect Pgbouncer as a PostgreSQL server, and then Pgbouncer will either process a connection to the server or reuse an existing connection. The main purpose of using Pgbouncer is to reduce the performance loss caused by a new connection to the PostgreSQL. We know that for each new connection, PostgreSQL will start a process, the number of connections, how many background service processes will start, even if these connections do nothing, will occupy a background service process, each service process will occupy system resources, Pgbouncer is a connection pooling software, which can realize the function of connection reuse and reduce the number of connections actually to the database. In order to coordinate the relationship between transactional semantics and connection pooling, Pgbouncer supports several different levels of connection rotation: Session pooling/conversation Connection pool The most polite way. When the client connects, he is assigned a server connection and is assigned to it throughout the client connection. This server connection will be put back into the connection pool when the client disconnects. This approach does not reduce the number of connections to the database. Transaction pooling/Transaction Connection pool server connections are assigned to clients only in the process of a transaction. When Pgbouncer notes that the transaction is over, the server is put back into the connection pool. Statement pooling/Statement Connection The most aggressive mode of the pool. After each query completes, the connection to the server is immediately put back into the connection pool. Multiple-statement transactions are not allowed in this mode because their transaction semantics are corrupted.
Here we will briefly explain how to use Pgbouncer: Download from the website Http://pgfoundry.org/projects/pgbouncer, I downloaded the pgbouncer-1.3.4.tgz, unzip the file into a directory, compile and install: Tar zxvf pgbouncer-1.3.4.tgz cd pgbouncer-1.3.4./configure m Ake make install
Pgbouncer run requires a configuration file Pgbouncer.ini, which reads as follows:
[Databases]
OSDBA = host=127.0.0.1 port=5432 user=osdba dbname=osdba
[Pgbouncer]
Listen_port = 6543
LISTEN_ADDR = 127.0.0.1
Auth_type = MD5
Auth_file = Users.txt
LogFile = Pgbouncer.log
Pidfile = Pgbouncer.pid
Admin_users = Bntest |
In the [databases] summary, the format is: <database> = xxxxxx,<database> refers to the data Recovery library in Pgbouncer, followed by "host=127.0.0.1 port=5432 USER=OSDBA DBNAME=OSDBA "is the connection string used by the Pgbouncer connected background database. My database here is OSDBA. In the [Pgbouncer] summary, the main set of Pgbouncer listening IP is the port. A user authentication file is also required users.txt,users.txt the following contents:
The content format is "username" "Password". You can use the user name and password in this configuration to connect Pgbouncer. Here we can start the Pgbouncer:
osdba@osdba-laptop:~$ pgbouncer-d pgbouncer.ini 2010-10-01 21:46:24.205 11484 LOG File descriptor limit:1024 (H:1024), M AX_CLIENT_CONN:100, Max FDs possible:130
Here you can use the Psql connection pgbouncer:
osdba@osdba-laptop:~$ psql-h 127.0.0.1-p 6543-u bntest osdba for user Password (osdba:psql) Type ' help ' for 9.0.0 P.
osdba=# \d List of relations schema | name | Type | owner --------+------------------------+----------+------- public | Pg_stat_statements | View | OSDBA public | Pgstatspack_database | Table | OSDBA public | Pgstatspack_indexes | Table | OSDBA public | Pgstatspack_sequences | Table | OSDBA public | Pgstatspack_settings | Table | OSDBA public | Pgstatspack_snap | Table | OSDBA public | pgstatspack_statements | Table | OSDBA public | Pgstatspack_tables | Table | OSDBA public | Pgstatspack_version | Table | OSDBA public | Pgstatspackid &Nbsp;| Sequence | OSDBA public | T | Table | OSDBA (rows)
osdba=#
This can also be connected to a special database on the Pgbouncer pgbouncer, where you can view the Pgbouncer run status and shutdown Pgbouncer: osdba@osdba-laptop:~$ psql-h 127.0.0.1-p 6543-u bntest pgbouncer Password for user bntest:psql (9.0.0, Server 8.0/bouncer) Warning:psql version 9.0, Server ver Sion 8.0. Some Psql features might not work. Type ' help ' for help.
pgbouncer=# Show help; Notice:console usage Detail:show help| Config| databases| pools| clients| servers| VERSION Show stats| fds| sockets| active_sockets| lists| MEM SET key = arg RELOAD PAUSE [<db>] SUSPEND RESUME [<db>] SHUTDOWN Show
You can see that the command shutdown can stop pgbouncer running, and the command reload can reload the new configuration file. The concrete use method is visible: Http://www.pgsqldb.org/mwiki/index.php/Pgbouncer_%E7%94%A8%E6%88%B7%E6%89%8B%E5%86%8C
The above configuration does not specify the mode of the connection pool, if not specified, default is session mode. We change the connection mode to Statement,pgbouncer.ini configuration file as follows:
[Databases]
OSDBA = host=127.0.0.1 port=5432 user=osdba dbname=osdba
[Pgbouncer]
Listen_port = 6543
LISTEN_ADDR = 127.0.0.1
Auth_type = MD5
Auth_file = Users.txt
LogFile = Pgbouncer.log
Pidfile = Pgbouncer.pid
Admin_users = Bntest
Pool_mode = statement |
At this point, if we use the "begin;" An error occurs when starting a transaction: osdba@osdba-laptop:~$ psql-h 127.0.0.1-p 6543-u bntest osdba Password for user bntest:psql (9.0.0) Type "h ELP "for help. osdba=# begin; Error:long transactions not allowed server closed the connection unexpectedly this probably means the server terminated Abnormally before or while processing the request. The connection to the server is lost. Attempting reset:succeeded.
Some other information: Http://wiki.postgresql.org/wiki/PgBouncer http://pgbouncer.projects.postgresql.org/doc/config.html http ://www.pgsqldb.org/mwiki/index.php/pgbouncer_%e4%bb%8b%e7%bb%8d
