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.
- 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:
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
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.