Some of you may know the case: As soon as the number of users grow, the number of resource problems increases. Have you ever thought about using a connection pooler? Too complex, too much administration effort? In this post I like to explain, how the connection pooler can help you with your memory, as well as showing you the simplicity of setup connection pooling with PgBouncer.
Introduction
By default PostgreSQL forks it’s main process to child processes for every new connection. In the course of time this can lead to more and more processes on the server. On one hand, this is pretty cool, because it can provide more stability and a nice view of resource utilization per connection. But if there are many short time connections, the disadvantages will predominate. The more connections you have, the more RAM will be used.
The solution for that problem can be a connection pooler like PgBouncer, an opensource connection pooling middleware espacially designed for Postgres. It will act like a wrapper around the database connections. It has the internals for the connection between the database and the pool, but everything is hidden from the application that connects.
Installation
For the installation of pgBouncer you can decide between two possibilities
1. using yum
2. building from git (https://pgbouncer.github.io/install.html#building-from-git)
To keep it simple, we go on with method 1.
postgres@centos-mini:/u02/pgdata/11/PG1/ [PG1] sudo yum install pgbouncer Loaded plugins: fastestmirror Determining fastest mirrors epel/x86_64/metalink | 28 kB 00:00:00 * base: pkg.adfinis-sygroup.ch * epel: pkg.adfinis-sygroup.ch * extras: pkg.adfinis-sygroup.ch * updates: pkg.adfinis-sygroup.ch base | 3.6 kB 00:00:00 epel | 5.3 kB 00:00:00 extras | 2.9 kB 00:00:00 ius | 1.3 kB 00:00:00 pgdg10 | 3.6 kB 00:00:00 pgdg11 | 3.6 kB 00:00:00 updates | 2.9 kB 00:00:00 (1/10): base/7/x86_64/group_gz | 165 kB 00:00:06 (2/10): extras/7/x86_64/primary_db | 153 kB 00:00:00 (3/10): epel/x86_64/group_gz | 90 kB 00:00:06 (4/10): epel/x86_64/primary_db | 6.9 MB 00:00:08 (5/10): epel/x86_64/updateinfo | 1.0 MB 00:00:08 (6/10): pgdg11/7/x86_64/primary_db | 337 kB 00:00:01 (8/10): base/7/x86_64/primary_db | 6.0 MB 00:00:10 (10/10): updates/7/x86_64/primary_db | 2.8 MB 00:00:01 (11/10): ius/x86_64/primary | 139 kB 00:00:06 (12/10): pgdg10/7/x86_64/primary_db | 384 kB 00:00:06 ius 631/631 Resolving Dependencies --> Running transaction check ---> Package pgbouncer.x86_64 0:1.12.0-1.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================================================================================================================== Package Arch Version Repository Size =========================================================================================================================================================================================================== Installing: pgbouncer x86_64 1.12.0-1.rhel7 pgdg10 207 k Transaction Summary =========================================================================================================================================================================================================== Install 1 Package Total download size: 207 k Installed size: 477 k Is this ok [y/d/N]: y Downloading packages: pgbouncer-1.12.0-1.rhel7.x86_64.rpm | 207 kB 00:00:06 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgbouncer-1.12.0-1.rhel7.x86_64 1/1 Verifying : pgbouncer-1.12.0-1.rhel7.x86_64 1/1 Installed: pgbouncer.x86_64 0:1.12.0-1.rhel7 Complete!
Configuration
pgbouncer.ini
PgBouncer has one central congfiguration file called pgbouncer.ini, per default it is located under /etc/pgbouncer and it is used to configure the PgBouncer pool.
You can define a lot of parameters in this file, most of them commented out by default and you can start with a very minimal configuration. It includes generic settings as logile, listen_addr, listen_port… as well as connectivity, console access control, admin_users and many, many more. The full list of parameters can be found in the pgbouncer documentation, which is really detailled and gives you a good overview.
Lets have a look to a easy sample of pgbouncer.ini file
cat /etc/pgbouncer/pgbouncer.ini [databases] bouncer= host=localhost dbname=bouncer [pgbouncer] listen_addr=127.0.0.1 listen_port= 6432 auth_type = md5 auth_file= /etc/pgbouncer/userlist.txt admin_users=bounce pool_mode=session max_client_conn = 100 default_pool_size = 20 logfile = /etc/pgbouncer/log/pgbouncer.log pidfile = /etc/pgbouncer/pgbouncer.pid
The ini-file has two sections, first the [database] section defines the alias(es) for the database(s). Just start a new line for every database. You can also define user and port etc. and afterwards the [pgbouncer] section, where the pool configuration is done. You can also start with a very minimal configuration.
One of the most important parameters is pool_mode, which defines how a server connection can be reused. 3 modes can be defined:
– session: This is the default value: Connections are released back to the pool after disconnection.
– transaction: Releases the connection to the pool once a transaction finishes.
– statement: After a query finishes, the connection is released back to he pool.
The other parameters in section pgbouncer shortly explained:
listen_addr: List of addresses where to listen for TCP connection.
listen_port: Listening port
admin_users: Users from the auth_file which get access to a special pgbouncer database. This database provides performance-related information about PgBouncer.
max_client_conn: This is the maximum number of client connections allowed. The default value is 100, but there is also a formula to calculate the value
default_pool_size: The number of server connections allowed per user/database pair. The default value is 20.
logfile: This one is self-explaining. The log file location.
pidfile: The location of the PID file.
auth_type and auth_file: This two belong together. Auth_type specifies how to authenticate users (pam|hba|md5) against PgBouncer and auth_file contains the usernames and passwords.
userlist.txt
As already mentioned this file has a very simple structure, username and password. You can either write the password in plain text or the MD5 has of the password. Of course it is not very secure to put the plain text password in here.
cat /etc/pgbouncer/userlist.txt "bounce" "md51db1c086e81505132d1834e06d07420e" "postgres" "md53175bce1d3201d16594cebf9d7eb3f9d"
Start PgBouncer
Now all the configuration is done and PgBouncer can be started. It is possible to start PgBouncer on command line and you see the log output directly:
postgres@centos-mini:/etc/pgbouncer/ [PG1] /bin/pgbouncer pgbouncer.ini 2019-11-06 19:40:05.862 CET [13498] LOG kernel file descriptor limit: 1024 (hard: 4096); max_client_conn: 100, max expected fd use: 192 2019-11-06 19:40:05.864 CET [13498] LOG listening on 127.0.0.1:16432 2019-11-06 19:40:05.864 CET [13498] LOG listening on unix:/tmp/.s.PGSQL.16432 2019-11-06 19:40:05.864 CET [13498] LOG process up: PgBouncer 1.12.0, libevent 2.0.21-stable (epoll), adns: libc-2.17, tls: OpenSSL 1.0.2k-fips 26 Jan 2017 2019-11-06 19:41:05.868 CET [13498] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us 2019-11-06 19:41:39.325 CET [13498] LOG C-0x18da360: db1/[email protected]:58648 login attempt: db=db1 user=user1 tls=no 2019-11-06 19:41:39.326 CET [13498] LOG C-0x18da360: db1/[email protected]:58648 closing because: client unexpected eof (age=0s) 2019-11-06 19:41:47.577 CET [13498] LOG C-0x18da360: db1/[email protected]:58652 login attempt: db=db1 user=user1 tls=no 2019-11-06 19:41:47.579 CET [13498] LOG S-0x18e0c30: db1/user1@[::1]:5432 new connection to server (from [::1]:37654) 2019-11-06 19:42:05.869 CET [13498] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 157 us 2019-11-06 19:43:05.872 CET [13498] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 2 B/s, xact 1522 us, query 1522 us, wait 0 us
Furthermore it is possible to create a service which starts automatically in the background after every reboot:
cat /etc/systemd/system/pgbouncer.service
[Unit]
Description=A lightweight connection pooler for PostgreSQL
After=syslog.target
After=network.target
[Service]
Type=simple
User=postgres
Group=postgres
# Path to the init file
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.ini
PIDFile=/var/run/pgbouncer/pgbouncer.pid
# Where to send early-startup messages from the server
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
ExecStart=/bin/pgbouncer ${BOUNCERCONF}
ExecReload=/bin/kill -HUP $MAINPID
KillSignal=SIGINT
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
[Install]
WantedBy=multi-user.target
postgres@centos-mini:/etc/ [PG1] sudo systemctl start pgbouncer
postgres@centos-mini:/etc/ [PG1] sudo systemctl status pgbouncer
● pgbouncer.service - A lightweight connection pooler for PostgreSQL
Loaded: loaded (/etc/systemd/system/pgbouncer.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2019-11-07 15:17:09 CET; 4s ago
Main PID: 17298 (pgbouncer)
CGroup: /system.slice/pgbouncer.service
└─17298 /bin/pgbouncer /etc/pgbouncer/pgbouncer.ini
Nov 07 15:17:09 centos-mini systemd[1]: Started A lightweight connection pooler for PostgreSQL.
Nov 07 15:17:09 centos-mini pgbouncer[17298]: 2019-11-07 15:17:09.127 CET [17298] LOG kernel file descriptor limit: 1024 (hard: 4096); max_client_conn: 100, max expected fd use: 172
Nov 07 15:17:09 centos-mini pgbouncer[17298]: 2019-11-07 15:17:09.127 CET [17298] LOG listening on 127.0.0.1:6432
Nov 07 15:17:09 centos-mini pgbouncer[17298]: 2019-11-07 15:17:09.127 CET [17298] LOG listening on unix:/tmp/.s.PGSQL.6432
Nov 07 15:17:09 centos-mini pgbouncer[17298]: 2019-11-07 15:17:09.127 CET [17298] LOG process up: PgBouncer 1.12.0, libevent 2.0.21-stable (epoll), adns: libc-2.17, tls: OpenSSL 1.0.2k-fips 26 Jan 2017
15:17:13 postgres@centos-mini:/etc/ [PG1]
PgBouncer is running now and you can connect to the database using PgBouncer.
postgres@centos-mini:/etc/ [PG1] psql -U bounce -p 6432 -h localhost bouncer Password for user bounce: psql (11.4 dbi services build) Type "help" for help. bouncer=> bouncer=>
Monitoring
I already mentioned the admin users before and want to explain them a little bit more detailed now.
PgBouncer allows users with admin rights to connect to the virtual database “pgbouncer”. You can use this database to see who is connecting, how many active connections are in a pool and of course many more. As a good starting point, use “SHOW HELP” as soon as you are connected.
postgres@centos-mini:/etc/ [PG1] psql -U bounce -p 6432 -h localhost pgbouncer
Password for user bounce:
psql (11.4 dbi services build, server 1.12.0/bouncer)
Type "help" for help.
pgbouncer=# SHOW HELP;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE []
RESUME []
DISABLE
ENABLE
RECONNECT []
KILL
SUSPEND
SHUTDOWN
SHOW
pgbouncer=#
pgbouncer=# SHOW POOLS;
database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
bouncer | bounce | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | session
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
(2 rows)
pgbouncer=# SHOW CLIENTS;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls
------+--------+-----------+--------+-----------+-------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+-----
C | bounce | bouncer | active | 127.0.0.1 | 40322 | 127.0.0.1 | 6432 | 2019-11-07 15:24:40 CET | 2019-11-07 15:24:40 CET | 0 | 0 | 0 | 0x1bd9598 | | 0 |
C | bounce | bouncer | active | 127.0.0.1 | 40332 | 127.0.0.1 | 6432 | 2019-11-07 15:25:12 CET | 2019-11-07 15:25:12 CET | 0 | 0 | 0 | 0x1bd97b0 | | 0 |
C | bounce | pgbouncer | active | 127.0.0.1 | 40314 | 127.0.0.1 | 6432 | 2019-11-07 15:21:50 CET | 2019-11-07 15:25:36 CET | 221 | 440169 | 0 | 0x1bd9380 | | 0 |
(3 rows)
Conclusion
The above configuration is only a simple example how the configuration can look like. Of course there are many more specifics you can define. PgBouncer is a great tools for connection pooling and can help you to scale down the memory usage of your server. The connections of a pool are always available and in contrast to forking processes, it does not need reach out the database to establish a connection every time. The connections are just there.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/05/open-source-author.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/STH_web-min-scaled.jpg)