Using PgBouncer to improve performance and reduce the load on PostgreSQL
Tutorial Link:
https://www.chirags.in/tutorial/index...
---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:
postgres=#
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.4 .....
postgres=#
Let's install pgbouncer:
root@dept:~# sudo apt-get install 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
;;;
pool_mode = transaction
max_client_conn=5000.
...
;;;
;;; Connection limits
;;;
max_client_conn = 5000
...
;;;
;;; Authentication settings
;;;
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
...
;;;
;;; Users allowed into database 'pgbouncer'
;;;
admin_users = postgres
postgres=# SELECT usename, passwd FROM pg_shadow;
usename | passwd
---------+--------------------------------------------------------------------------------------------------------------------------------------
postgres | SCRAM-SHA-256$4096:kPG/5jtnbUx+myFzRoZ6CA==$S1/MVQVNkpMXI+s0PAKw2u4P3YHkDcVLAjl5lVyCGuA=:/yoSqXXuIvlNDxmj1izwlivbFRBWatiHQGh50p4bfVc=
(1 row)
(END)
add /etc/pgbouncer/userlist.txt.
root@dept:~# vi /etc/pgbouncer/userlist.txt
"postgres" "SCRAM-SHA-256$4096:kPG/5jtnb.."
The maximum number of connections for my database is set to 100:
postgres=# show max_connections;
max_connections
-----------------
100
(1 row)
postgres@dept:~$ pgbench -i mydatabase
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....
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 Database maximum 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.
.......
postgres@dept:~$
postgres@dept:~$ vi mysql.sql
select 1;
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:
.....
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:~$
postgres@dept:~$ pgbench -c 20 -T 60 mydatabase -h 127.0.0.1 -p 6432 -U postgres -C -f mysql.sql
Password:
.......
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 compaire both the requests
latency average = 2535.545 ms - 779.689 ms --- improvement
tps = 7.887849 - 25.651237 --- improvement
Note : Flow the Process shown in video.
😉Please Like and Subscribe for more videos:
/ @chiragstutorial
💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment
Tutorial Link :
https://www.chirags.in/tutorials/Post...
#postgres
#postgresql
#pointintimerecovery
#postgresqlTutorial
#pg_basebackup
#chiragstutorial
#chiragsdatabasetutorial
#database
#psql
#pgbouncer
#patroni
Watch video Using PgBouncer to improve performance and reduce the load on PostgreSQL online without registration, duration hours minute second in high quality. This video was added by user Chirags Tutorial 03 September 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 11 once and liked it people.