inchirags@gmail.com Chirags PostgreSQL DBA Tutorial https://www.chirags.in
*************************************************************************************
Using PgBouncer to improve performance and reduce the load on PostgreSQL
*************************************************************************************
---------install and configure pgbouncer----------------
Let's assume we have a PostgreSQL server.
postgres@dept:~$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
Password for user postgres:
psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
postgres=#
Let's install pgbouncer:
root@dept:~# sudo apt-get install pgbouncer
The next step is to configure pgbouncer.
root@dept:~# sudo vi /etc/pgbouncer/pgbouncer.ini
In the [databases] block, add the following entry:
* = host=localhost port=5432
In the "Pooler personality questions" section, I define pool_mode=transaction.
...
;;;
;;; Pooler personality questions
;;;
;; When server connection is released back to pool:
;; session - after client disconnects (default)
;; transaction - after transaction finishes
;; statement - after statement finishes
pool_mode = transaction
...
In the "Connection limits" section, we set the total number of clients that can connect to some high value:
max_client_conn=5000.
...
;;;
;;; Connection limits
;;;
;; Total number of clients that can connect
max_client_conn = 5000
...
In the "Authentication settings" section, we set auth_type = md5 to authenticate users by password. The file with database login and password will be located at /etc/pgbouncer/userlist.txt
...
;;;
;;; Authentication settings
;;;
;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
...
In the "Users allowed into database 'pgbouncer'" section, we set the admin_users parameter. This is the database user who will have permission to make pgbouncer settings in the database.
...
;;;
;;; Users allowed into database 'pgbouncer'
;;;
;; comma-separated list of users who are allowed to change settings
;admin_users = user2, someadmin, otheradmin
admin_users = postgres
...
Now let's open the file with users at /etc/pgbouncer/userlist.txt.
root@dept:~# vi /etc/pgbouncer/userlist.txt
If you are using PostgreSQL versions starting from 14, then the default password_encryption method is scram-sha-256.
Here we place the username in double quotes and the scram-sha-256 password hash (in one line):
"postgres" "SCRAM-SHA-256$4096:kPG/5jtnbUx+myFzRoZ6CA==$S1/MVQVNkpMXI+s0PAKw2u4P3YHkDcVLAjl5lVyCGuA=:/yoSqXXuIvlNDxmj1izwlivbFRBWatiHQGh50p4bfVc="
To determine the scram-sha-256 password hash, you can use the following method:
psql -h db_host -p db_port -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"
OR
postgres=# SELECT usename, passwd FROM pg_shadow;
usename | passwd
----------+---------------------------------------------------------------------------------------------------------------------------------------
postgres | SCRAM-SHA-256$4096:kPG/5jtnbUx+myFzRoZ6CA==$S1/MVQVNkpMXI+s0PAKw2u4P3YHkDcVLAjl5lVyCGuA=:/yoSqXXuIvlNDxmj1izwlivbFRBWatiHQGh50p4bfVc=
(1 row)
(END)
To make our application use pgbouncer when connecting to the database, all we need to change is the port number: use 6432 instead of 5432.
Note :
If you are using PostgreSQL versions up to 13 inclusively, then the default password_encryption method is md5.
Here we place the username in double quotes and the md5 password hash (in one line):
"my_db_user" "md5badc318d987f61146c6ad8e15d84a111"
To determine the md5 password hash, you can use the following method:
echo "md5"$(echo -n 'YourdbpasswordYourdbusername' | md5sum | awk ' { print $1 } ')
After that, let's reload pgbouncer:
sudo service pgbouncer restart
And after that, we will be able to connect to the database through pgbouncer using port 6432 (pgbouncer default port).
: Note end
Let's run a performance test to compare the performance of connecting to PostgreSQL with and without pgbouncer, using the pgbench utility.
The maximum number of connections for my database is set to 100:
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
Connecting to the Postgres database server without using PgBouncer.
This command will start a test with 1000 concurrent clients for 60 seconds, connecting directly to the PostgreSQL database.
postgres@dept:~$ pgbench -c 1000 -T 60 mydatabase -h 127.0.0.1 -p 5432 -U postgres
Password:
pgbench (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
pgbench: error: could not count number of branches: ERROR: relation "pgbench_branches" does not exist
LINE 1: select count(*) from pgbench_branches
^
pgbench: hint: Perhaps you need to do initialization ("pgbench -i") in database "mydatabase".
postgres@dept:~$
Note: if you are getting error like above then run the below command.
postgres@dept:~$ pgbench -i mydatabase
Output:
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.51 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.27 s (drop tables 0.01 s, create tables 0.04 s, client-side generate 0.59 s, vacuum 0.35 s, primary keys 0.29 s).
Now, again run
postgres@dept:~$ pgbench -c 1000 -T 60 mydatabase -h 127.0.0.1 -p 5432 -U postgres
Password:
pgbench (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
starting vacuum...end.
pgbench: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
connection to server at "127.0.0.1", port 5432 failed: FATAL: sorry, too many clients already
pgbench: error: could not create connection for client 100
postgres@dept:~$
Simulating the work of 1000 clients interacting with a database where only 100 clients can be connected at maximum results in an error.
FATAL: sorry, too many clients already
Check into PostgreSQL Datbase maximun connection limit is
postgres@dept:~$ psql
psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
Type "help" for help.
postgres=# SHOW max_connections;
max_connections
-----------------
100
(1 row)
postgres=#
Connecting to the Postgres database server using pgbouncer:
When connecting to the database using pgbouncer, everything works without any issues.
postgres@dept:~$ pgbench -c 1000 -T 60 mydatabase -h 127.0.0.1 -p 6432 -U postgres
Password:
pgbench: error: connection to server at "127.0.0.1", port 6432 failed: FATAL: password authentication failed
pgbench: error: could not create connection for setup
postgres@dept:~$
Now, I am going to add md5 in /etc/postgresql/16/main/pg_hba.conf file.
Add below line.
# IPv4 local connections:
host all all 127.0.0.1/32 md5
root@dept:~# sudo systemctl restart pgbouncer
root@dept:~# sudo systemctl restart postgresql
root@dept:~# su - postgres
postgres@dept:~$ pgbench -c 1000 -T 60 mydatabase -h 127.0.0.1 -p 6432 -U postgres
Password:
pgbench (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
starting vacuum...end.
transaction type: builtin: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1000
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1797
number of failed transactions: 0 (0.000%)
latency average = 33878.157 ms
initial connection time = 33073.513 ms
tps = 29.517544 (without initial connection time)
postgres@dept:~$
Now, we can test with below command
Let's compare the number of transactions per second that the database performs when the application connects to the database without using pgbouncer and when it uses pgbouncer.
postgres@dept:~$ vi mysql.sql
select 1;
The -C option in the pgbench indicates that for every single transaction pgbench will close the open connection and create a new one. This is useful to measure the connection overhead.
In my test I perfom select-only transactions. The reason is I want to exclude measuring update contention, when lots of transactions blocked waiting for other transactions.
The application connects to the database without using pgbouncer:
postgres@dept:~$ pgbench -c 20 -T 60 mydatabase -h 127.0.0.1 -p 5432 -U postgres -C -f mysql.sql
Password:
pgbench (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
starting vacuum...end.
transaction type: mysql.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 475
number of failed transactions: 0 (0.000%)
latency average = 2535.545 ms
average connection time = 125.613 ms
tps = 7.887849 (including reconnection times)
postgres@dept:~$
#using PgBouncer
postgres@dept:~$ pgbench -c 20 -T 60 mydatabase -h 127.0.0.1 -p 6432 -U postgres -C -f mysql.sql
Password:
pgbench (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
starting vacuum...end.
transaction type: mysql.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 1540
number of failed transactions: 0 (0.000%)
latency average = 779.689 ms
average connection time = 37.899 ms
tps = 25.651237 (including reconnection times)
postgres@dept:~$
Here we can compare both the requests
latency average = 2535.545 ms - 779.689 ms --- improvement
tps = 7.887849 - 25.651237 --- improvement
#Useful admin console commands
Here are a few commands that can be used after connecting to the pgbouncer database. These may be helpful for troubleshooting purposes:
SHOW HELP: Displays the help page. It is useful when remembering commands.
SHOW STATS: Displays transaction count, timing, etc.
SHOW POOLS: Displays active and waiting clients and server counts. It also shows how long the oldest client waited in the queue. It is very helpful when determining pool_size.
SHOW SERVERS: Displays information about database connections made by PgBouncer.
SHOW CLIENTS: Displays information about clients that connected via PgBouncer.
SHOW DATABASES: Displays information about configured databases.
PAUSE [DB]: Useful when stopping connection to a specific database. PgBouncer waits for all queries to be completed, then puts new connections into a queue. You should be careful about timeouts on all sides. It is very useful when restarting PostgreSQL. You may want to increase the number of file descriptors. This command will not return before all queries have finished.
RESUME [DB]: Resumes the KILL, PAUSE, or SUSPEND the specific database.
RECONNECT [DB]: Closes and reconnects all server connections. If you are planning to do a switchover, consider using PAUSE command. If you have done a failover, consider using the KILL command.
DISABLE [DB]: Disallows new connections to the specific database.
ENABLE [DB]: Allows new connections to the specific database after a DISABLE command.
KILL [DB]: Drops all client and server connections and pauses the specific database.
RELOAD: It can be used for reloading the PgBouncer after a configuration change.
SHUTDOWN: It can be used to exit the PgBouncer process. Use systemd instead if possible.
Note : Flow the Process shown in video.
Please Like and Subscribe for more videos:
https://www.youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
____________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
