Using PgBouncer to improve performance and reduce the load on PostgreSQL in Hindi
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
Смотрите видео Using PgBouncer to improve performance and reduce the load on PostgreSQL in Hindi онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Chirags Tutorial 07 Сентябрь 2024, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 7 раз и оно понравилось людям.