PgBouncer улучшает производительность PostgreSQL в условиях высокой нагрузки

Эффективное управление подключениями к базе данных имеет решающее значение для поддержания производительности и надежности ваших экземпляров PostgreSQL. Производительность PostgreSQL может ухудшаться при чрезмерном количестве одновременных подключений, что делает решения для пула подключений, такие как PgBouncer, необходимыми для сред с высоким трафиком. В этой статье мы рассмотрим шаги по настройке PgBouncer, легковесного пулера подключений для PostgreSQL, и его конфигурации для оптимизации подключений к базе данных.

Почему стоит использовать PgBouncer?

PostgreSQL может эффективно обрабатывать ограниченное количество одновременных подключений. Рекомендуется устанавливать максимальное количество подключений ниже 10 на одно ядро ЦП, с максимальным пределом в 20 на одно ядро ЦП. Если вашему приложению необходимо обрабатывать больше сеансов пользователей, использование пулера подключений, такого как PgBouncer, становится необходимым. PgBouncer снижает накладные расходы на установление и закрытие подключений за счет повторного использования пула установленных подключений.

Предварительные требования

Прежде чем начать, убедитесь, что у вас установлены следующие зависимости:

PostgreSQL
Python3 с psycopg2

Вы можете установить эти зависимости с помощью следующих команд:

dnf install python3-psycopg2-2.9.9-1PGDG.rhel9.x86_64.rpm # Зависимость
dnf install pgbouncer-1.22.1-42PGDG.rhel9.x86_64.rpm

Настройка PgBouncer

Настройка аутентификации пользователей
Сначала создадим файл userlist для управления аутентификацией пользователей в PgBouncer.
```
psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt
```

Редактирование конфигурации PgBouncer

Далее настроим файл pgbouncer.ini. Откройте файл в вашем любимом текстовом редакторе:

vi /etc/pgbouncer/pgbouncer.ini

Добавьте следующую конфигурацию:

[databases]
* = port=5432

[pgbouncer]
pool_mode = session
listen_addr = 10.5.**.**
default_pool_size = 100
reserve_pool_size = 20
max_client_conn = 500
admin_users = postgres
ignore_startup_parameters = extra_float_digits
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_port = 6432
auth_file = /etc/pgbouncer/userlist.txt
client_tls_sslmode = require
client_tls_ca_file = /etc/pgbouncer/root.crt
client_tls_key_file = /etc/pgbouncer/pgbouncer.key
client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt

Перезапуск и включение PgBouncer
После редактирования конфигурации перезагрузите и перезапустите службу PgBouncer для применения изменений:
```
systemctl restart pgbouncer
systemctl enable pgbouncer
```
Вы можете проверить статус службы PgBouncer, чтобы убедиться, что она работает правильно:
```
systemctl status pgbouncer
```
Подключение к PgBouncer
Теперь вы можете выполнять операции с базой данных через порт PgBouncer:
```
psql -p 6432
```
Для подключения к интерфейсу управления PgBouncer:
```
psql -p 6432 pgbouncer
```
Мониторинг и управление PgBouncer
Для мониторинга управления пулом:
```
show pools;
```
Для просмотра статистики:
```
show stats;
```
Для получения дополнительных команд вы можете обратиться к:
```
psql -Atq -h 10.*.**.** -p 5432 -U postgres -d postgres -c "SELECT concat('"', usename, '" "', passwd, '"') FROM pg_shadow" >> /etc/pgbouncer/userlist.txt
```
Обновление системных лимитов
Наконец, обновите файл службы systemd для увеличения лимитов файловых дескрипторов. Чтобы PgBouncer мог обрабатывать большое количество одновременных подключений, необходимо увеличить лимиты файловых дескрипторов. Файловые дескрипторы являются важными ресурсами, используемыми операционной системой для управления открытыми файлами и сокетами. Каждое подключение к базе данных через PgBouncer потребляет файловый дескриптор. Если лимит слишком низок, PgBouncer не сможет принять дополнительные подключения.

Эти шаги помогут вам настроить и оптимизировать PgBouncer для эффективного управления подключениями к базе данных PostgreSQL в высоконагруженных средах.