Getting started with PGBouncer in PostgreSQL

Mar 26, 2020

Mins to Read

All

Resource management is one of the key thing in all RDBMS. DB Connections is one of them. In PostgreSQL, Allocating more connections without proper analysis of resources causes an overhead in Memory usage and the creation of an OS process for each connection will lead to a scheduling overhead in the time-sharing operating system. These things will cause a great impact on application performance and an increase in latency.

To overcome these situations, we can adapt connection pooling. Connection Pool is a cache of database connections so that connections can be reused when future requests to the database are required. The creation of a thread in Postgres requires many processes like opening a connection. user authentication and so on. Connection pooling will reduce these overheads. Also, if we reach the maximum connection limit, pooling will help us to avoid the end-user getting errors like max connections reached, instead it will keep on waiting until the connection is freed up in the database.

In this blog post, we will look at the most famous connection pooling tool for Postgres PGBouncer. At first, we will cover the configuration of PGBouncer followed by the performance improvements with and without PGBouncer.

PGBouncer Configuration:

First I had installed the PostgreSQL server(version 12.1) in the testing instance with the specification of Centos 7 OS, 2 CPU core, 2 GB RAM and 100GB SSD disk. Installing Postgresql is not the scope of this blog. Please refer to this official Documentation.

 
postgres=# select version();
                     version
---------------------------------------------------------------------
 PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

2. Now PGBouncer can be installed by passing the below command in Centos 7

 
[root@postgres ~]# yum install pgbouncer

3. After the successful installation, we can start working on the configuration.

 
[root@postgres ~]# pgbouncer 
Need config file.  See pgbouncer -h for usage. 
[root@postgres ~]#

4. Edit the configuration file called pgbouncer.ini in the path /etc/pgbouncer/pgbouncer.ini. By default, it contains a lot. Just make sure you have modified the below details in it.

 
;
; pgbouncer configuration example
; [databases]
* = host=localhost
[pgbouncer]
listen_port = 6432
listen_addr = *
admin_users = postgres 
auth_type = md5

; Place it in secure location
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
; default values
pool_mode = session
default_pool_size = 80

* = host=localhost –>For which database you want to enable pooling. Here I have added the * for this to access all.

Listen port and Listen Address –> Which IP address and port PGBouncer process listen to.

Auth_type and admin_users –> Authentication type to be used – Here I have mentioned md5 which requires a password for client connections and also requires users.txt file which comes under auth_file(It contains username and password to connect to the DB) and admin user to connect to the DB

Auth file, log and PID files –> Auth file is required for md5 authentication type which contains the username and its respective encrypted passwords. Keep the auth, log, PID files in the secure location

Pool mode and default Pool size –> Based on the pool mode, the connection will be returned to the pool. It has three values
- Session – Connection back to the pool when client closes the session

Transaction – Connection back to the pool when the transaction completes

Statement – Connection back to the pool when the statement completes.

Here I leave it as the value default – session and also increased the pool size to 80.Okay, Now we need to make an entry in userlist.txt file with username and password to access the DB as pgbouncer is a third party tool which does not have access to these user credentials in the DB.

Login the Postgres server as you usually do

 
[root@postgres ~]# sudo -u postgres psql
psql (12.1) Type "help" for help. 
postgres=#

Execute this query in the psql prompt

 
postgres=# select rolname,rolpassword from pg_authid where rolname='postgres';
  rolname  |     rolpassword 
----------+------------------------------------- 
 postgres | md53175bce1d3201d16594cebf9d7eb3f9d
 (1 row)

Add the entry in the username and password order of userlist.txt file.

 
[root@postgres ~]# cat /etc/pgbouncer/userlist.txt
 "postgres" "md53175bce1d3201d16594cebf9d7eb3f9d"

Now, restart the PGBouncer to take effect and check the status too

 
[root@postgres ~]# service pgbouncer restart 
Redirecting to /bin/systemctl restart pgbouncer.service 

   
[root@postgres ~]# service pgbouncer status 
Redirecting to /bin/systemctl status pgbouncer.service
pgbouncer.service - A lightweight connection pooler for PostgreSQL
   Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; disabled; vendor preset: disabled)    
Active: active (running) since Mon 2020-03-23 08:30:10 UTC; 1min 19s ago  
Main PID: 2976 (pgbouncer)    CGroup: /system.slice/pgbouncer.service           
 └─2976 /usr/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini   

[root@postgres ~]# netstat -ntulp | grep pgbouncer 
tcp        0 0 0.0.0.0:6432            0.0.0.0:* LISTEN   2976/pgbouncer 
tcp6       0 0 :::6432                 :::*      LISTEN   2976/pgbouncer

That’s it. Now we can access the postgres server through PGbouncer just by changing the port to 6432 and also we can connect to the PGBouncer database and execute some useful commands for the pool (show stats, show pools and so on)

 
bash-4.2$ psql -Upostgres -h127.0.0.1 --port 6432 
Password for user postgres: 
psql (12.1) Type "help" for help.   
postgres=#   

bash-4.2$ psql -p 6432 -h localhost -d pgbouncer 
Password for user postgres: 
psql (12.1, server 1.12.0/bouncer) Type "help" for help.   
pgbouncer=# SHOW STATS;

In applications, we just need to change the port of the connection pooling.

Performance Benchmarking Without and with PGBouncer:

Test 1:

To benchmark the postgresql server, I am using a utility called sysbench. We can install this utility by executing the following command

 
[root@postgres ~]# yum install sysbench

Created a test database in postgres server and populating the data in it

 
postgres=# create database benchmark;
CREATE DATABASE
postgres=#

[root@postgres log]# sysbench --db-driver=pgsql --oltp-table-size=10000 --oltp-tables-count=24 --threads=1 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=Aakash@007 --pgsql-db=benchmark /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua  run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Data loading is done, now we will generate some loads without PGBouncer

 
[root@postgres log]# sysbench --db-driver=pgsql --report-interval=2 --oltp-table-size=10000 --oltp-tables-count=24 --threads=64 --time=60 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=Aakash@007 --pgsql-db=benchmark /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
SQL statistics:
    queries performed:
        read:                            89208
        write:                           25352
        other:                           12798
        total:                           127358
    transactions:                        6332 (59.54 per sec.)
    queries:                             127358 (1197.51 per sec.)
    ignored errors:                      40 (0.38 per sec.)
    reconnects:                          0 (0.00 per sec.)

General statistics:
    total time:                          106.3507s
    total number of events:              6332

Latency (ms):
         min:                                    3.41
         avg:                                  717.55
         max:                                50688.52
         95th percentile:                      694.45
         sum:                              4543543.98

Threads fairness:
    events (avg/stddev):           98.9375/48.88
    execution time (avg/stddev):   70.9929/19.52

Doing the same test with PGBouncer

 
SQL statistics:

 queries performed:
        read:                            189784
        write:                           54193
        other:                           27125
        total:                           271102
    transactions:                        13547 (224.38 per sec.)
    queries:                             271102 (4490.21 per sec.)
    ignored errors:                      9 (0.15 per sec.)
    reconnects:                          0 (0.00 per sec.)

General statistics:
    total time:                          60.3742s
    total number of events:              13547

Latency (ms):
         min:                                   25.45
         avg:                                  284.49
         max:                                60369.53
         95th percentile:                      161.51
         sum:                              3854024.30

Threads fairness:
    events (avg/stddev):           211.6719/312.49
    execution time (avg/stddev):   60.2191/0.11

From the above stats, we can observe that the tps value is greatly increased (2x) with pgbouncer when compared without pgbouncer and also latency is also greatly reduced by half.

The below are the test results for different concurrent connections value

Test 2:

I reduced the max_connections to 50, performed the same test.

Without PGbouncer, I got the max connection reached error

 
Initializing worker threads...
Threads started!
FATAL: `thread_run' function failed: /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua:43: Failed to connect to the database
FATAL: Connection to database failed: FATAL:  sorry, too many clients already

Executed the same command with PGbouncer. The load test is started without any errors

 
Number of threads: 64
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 2s ] thds: 64 tps: 240.59 qps: 4912.63 (r/w/o: 3451.62/968.35/492.66) lat (ms,95%): 130.13 err/s: 0.50 reconn/s: 0.00
[ 4s ] thds: 64 tps: 236.45 qps: 4740.01 (r/w/o: 3327.81/939.30/472.90) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00

So far, we have seen the basic configuration and performance improvements with the usage of the PGBouncer. By tweaking even more config variables based on our needs in PGBouncer, we can get more performance improvements.