Pgbouncer User Manual

[Edit] Pgbouncer (1) [Edit] name

Pgbouncer-postgresql The light weight of the connection pool. [edit] Summary

  Pgbouncer [-d][-r][-v] <pgbouncer.ini>
  pgbouncer-v|-h

[Edit] Description

Pgbouncer is a PostgreSQL connection pool. Any target application can connect Pgbouncer as a PostgreSQL server, and then Pgbouncer will either process the connection to the server or reuse the existing connection.

The goal of Pgbouncer is to reduce the performance loss caused by a new connection to the PostgreSQL.

To coordinate the relationship between transactional semantics and connection pooling, Pgbouncer supports several modes of connection rotation of varying degrees of roughness:

session pooling/ The most polite way to connect to a conversation pool. 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.

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.

The Pgbouncer management interface contains new show commands that can be used to observe system state when connected to a particular virtual database pgbouncer. [edit] Quick Start

The basic setup steps are listed below.

1. Create a configuration file Pgbouncer.ini. See the Pgbouncer Administrator's manual for details. The simplest:

     [Databases]
     template1 = host=127.0.0.1 port=5432 dbname=template1
     
     [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 = Someuser

2. Create Users.txt files:

     "Someuser" "Same_password_as_in_server"

3. Start Pgbouncer:

     $ pgbouncer-d Pgbouncer.ini

4. Connect your application (or Psql client) to Pgbouncer instead of connecting directly to the PostgreSQL server.

     $ psql-h 127.0.0.1-p 6543-u someuser template1

5. Pgbouncer and issue show help by connecting to a special management database; command to start managing Pgbouncer:

     $ psql-h 127.0.0.1-p 6543-u someuser pgbouncer Show help
     ;
     NOTICE:  Console usage
     DETAIL: show
       [help| Config| databases| fds| pools| clients| servers| sockets| lists| VERSION]
       SET key = arg
       RELOAD
       PAUSE
       SUSPEND
       RESUME
       SHUTDOWN

6. If you modify configuration parameters that are configurable in some configuration files, you can reload them with the following command:

     pgbouncer=# RELOAD;

Edit command line arguments

-D runs in the background. Without this parameter, the process will be executed in the foreground.

-R to do an online reboot. This means connecting to a running process, loading open sockets from inside and using these sockets to continue running.

-V increases the length of output information.

-V display version.

- h displays brief help. [edit] admin terminal

You can use the administration console by connecting to the standard Listen_addr/listen_port and declaring pgbouncer as the connection database.

  $ psql-h 127.0.0.1-p 6543 Pgbouncer

The connection to the console is allowed only if the configuration parameter admin_users or stats_users.

In addition, if the connection is through a UNIX socket, and the client and the running process have the same UNIX user uid, the user is allowed to log on without a password via pgbouncer. [edit] Show command

The show command outputs some rows of data, which are described below. [edit] show STATS;

Displays statistics.

Database statistics are proportional to each of the databases.

The total number of SQL requests processed by the Total_requests pgbouncer connection pool.

The total number of bytes of network traffic that the Total_received Pgbouncer received.

Total number of bytes of network traffic emitted by Total_sent Pgbouncer.

Total_query_time Pgbouncer is active in the total amount of time spent with PostgreSQL, in milliseconds.

Avg_req average number of requests per second in the last statistical process.

Avg_recv The average amount of data received per second (from the client).

Avg_sent The average amount of data sent (to the client) per second.

Avg_query average query time, in milliseconds. [edit] show SERVERS;

Type S, representing the server.

User Pgbouncer the username used to connect to the server.

Database name on server side.

State Pgbouncer The status of the server connection, active,used or idle.

Addr The IP address of the PostgreSQL server.

Port PostgreSQL Server.

Local_addr The originating connection address on the local machine.

Local_port The originating connection port on the local machine.

Connect_time the time the connection was established.

Request_time the time the request was issued.

PTR the internal object address of this connection. Used as a unique ID.

Link this server corresponds to the client address. [edit] show CLIENTS;

Type C, which represents the client.

User Client connection.

Database name.

State Client Connection status, active,used,waiting or one of idle.

Addr The IP address of the client.

Port clients are connected to.

LOCAL_ADDR the End-to-end address that is connected to the local machine.

Local_port to the End-to-end port on the local machine.

Connect_time the timestamp of the last client connection.

Request_time the timestamp of the last client request.

The address of the internal object that PTR this connection. Used as a unique ID.

Link this client connects to the address of the corresponding server. [edit] show pools;

A new connection pool record is maintained for each pair (database, user).

Database name.

User username.

Cl_active the number of current active (active) client connections.

Cl_waiting the number of client connections currently waiting (waiting).

Sv_active the current active (active) Number of server connections.

Sv_idle the number of server connections currently idle (idle).

sv_used the number of server connections currently used (in use).

sv_tested the number of server connections currently tested (tested).

Sv_login the number of current login (login) to PostgreSQL servers.

The length of time, in seconds, that the first (oldest) client in the maxwait queue waits. If this number starts to rise, it means that the server in the current connection pool is not processing the request fast enough. The reason may be that the server is overloaded, or it may just be pool_size too small. [edit] show LISTS;

Displays the following internal information, which is displayed in a field (not by row):

Number of databases databases.

The number of users.

Number of pools connection pools.

Number of free_clients idle clients.

Used_clients the number of clients that have been used.

Login_clients the number of clients in the logged on state.

Free_servers the number of idle servers.

Used_servers number of servers already used. [edit] show USERS;

Each line displays a user, and the word Deming is name. [edit] show DATABASES;

Name records of database names that have been configured.

Hostname to which the host Pgbouncer is connected.

Port number to which port Pgbouncer is connected.

Database Pgbouncer the names of the databases that are actually connected.

Force_user When a user is part of a connection string, the connection between Pgbouncer and PostgreSQL is coerced into the given user, regardless of the client user.

Pool_size the maximum number of server-side connections. [edit] show FDS;

Displays the list of FD that is in use. If the user name of the connected user is "Pgbouncer", it is connected through a UNIX socket and has the same UID as the running process, and the actual FD list is passed through this connection. This mechanism is used to do online restart.

The numeric value of the FD file descriptor.

One of the task Pooler,client or servers.

User uses the connection users for this FD.

Database using the connection of this FD.

Addr uses the IP address of the connection for this FD, which is UNIX if the UNIX socket is used.

Port number that uses the connection port for this FD.

Cancel the key word for this connection.

Link corresponds to the server/client FD. If idle (idle) is null. [edit] show CONFIG;

Displays the current configuration settings, one row for configuration, and the following fields:

Key configuration variable Name

Value configuration values

changeable yes or no to show whether the variable can be modified at run time. If no, then this variable can only be modified at startup time. [edit] Process Control command [edit] PAUSE;

Pgbouncer tries to disconnect from all servers, first waits for all queries to end. This command will not return until all queries have been completed. [edit] SUSPEND;

All sockets ' buffers are flushed out, and then pgbouncer stops listening on the top. This command does not return until all sockets have been flushed. [edit] RESUME;

Start work again from the front PAUSE or SUSPEND command. [edit] SHUTDOWN;

The Pgbouncer process will exit. [edit] RELOAD;

The pgbouncer process will overload its configuration file and then update the settings that can be modified. [edit] signals

Sighup overload CONFIG. The same as the RELOAD command on the console.

SIGINT safe shutdown. and to issue PAUSE on the control table; Then SHUTDOWN; The same.

Sigterm immediately shut down. and to issue SHUTDOWN on the control table; is the same. [edit] LIBEVENT settings

From Libevent Documentation:

We can turn off the support for Epoll,kqueue,devpoll,poll or select by setting the environment variable separately EVENT_NOEPOLL,EVENT_NOKQUEUE,EVENT_NODEVPOLL,EVENT_ Nopoll or Event_noselect.

Display the kernel notification method that it uses by setting the environment variable event_show_method,libevent. [Edit] Authentication file format

Pgbouncer needs its own database of users. The user is loaded from a text file, and the format should be the same as the PostgreSQL pg_auth/pg_pwd file.

"UserName1" "Password" ... "UserName2" "md12342345234" ...

You need at least two fields, surrounded by double quotes. The first is the username, the second is plaintext, or the password after the MD5 hash. Pgbouncer ignores the remaining sections of the row.

Such a file format allows direct pgbouncer to the user files in the PostgreSQL data directory. [edit] ALSO

Pgbouncer (5)-Configuration settings description.

Pgbouncer introduction from "Http://www.pgsqldb.org/mwiki/index.php/Pgbouncer_%E7%94%A8%E6%88%B7%E6%89%8B%E5%86%8C"