Пул соединений PostgreSQL с PgBouncer

Введение

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

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

Настройка PgBouncer

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

Сначала создайте выделенный каталог для файлов конфигурации PgBouncer в папке вашего проекта.

mkdir -p /home/labex/project/pgbouncer
cd /home/labex/project/pgbouncer

Далее создайте основной файл конфигурации pgbouncer.ini с помощью редактора nano.

nano pgbouncer.ini

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

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /home/labex/project/pgbouncer/userlist.txt
admin_users = postgres
pidfile = /home/labex/project/pgbouncer/pgbouncer.pid
logfile = /home/labex/project/pgbouncer/pgbouncer.log
pool_mode = session
default_pool_size = 20
max_client_conn = 100

Давайте рассмотрим ключевые параметры:

• [databases]: Определяет строку подключения к целевой базе данных.
• listen_port: Порт, который будет прослушивать PgBouncer (6432). Ваше приложение будет подключаться к этому порту.
• auth_type: Указывает метод аутентификации. md5 — распространенный выбор.
• auth_file: Путь к файлу, содержащему имена пользователей и пароли.
• admin_users: Список пользователей через запятую, которым разрешено подключаться к консоли администратора PgBouncer.
• pool_mode: Установлен в session, что означает, что серверное соединение назначается клиенту на всю сессию.

Нажмите Ctrl+O для сохранения файла, затем Enter, и Ctrl+X для выхода из nano.

Теперь создайте файл аутентификации userlist.txt, на который вы ссылались в конфигурации.

nano userlist.txt

Добавьте следующую строку в файл. Пароль labex_password был установлен для пользователя postgres на этапе настройки лабораторной работы.

"postgres" "labex_password"

Сохраните и выйдите из редактора, нажав Ctrl+O, Enter и Ctrl+X.

Теперь вы успешно настроили PgBouncer для управления соединениями вашей базы данных PostgreSQL.

Запуск PgBouncer и проверка соединения

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

В терминале запустите PgBouncer в режиме демона (флаг -d), используя созданный вами файл конфигурации.

pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Вы можете проверить, что процесс PgBouncer запущен, с помощью следующей команды. Вы должны увидеть в списке процесс pgbouncer.

ps aux | grep pgbouncer

Вывод будет примерно таким:

labex      1234  0.0  0.0  12345   678 ?        Ss   12:00   0:00 pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Теперь проверьте соединение, используя psql для подключения к порту, который прослушивает PgBouncer (6432), а не к порту PostgreSQL по умолчанию (5432).

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres

Вам будет предложено ввести пароль. Введите labex_password. Если соединение успешно, вы увидите приглашение psql.

Чтобы убедиться, что вы подключены к базе данных, выполните простой запрос для проверки версии PostgreSQL.

SELECT version();

Вывод отобразит версию вашего сервера PostgreSQL, подтверждая, что PgBouncer успешно проксировал ваше соединение.

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0...
(1 row)

Наконец, выйдите из оболочки psql, набрав \q и нажав Enter.

\q

Симуляция нагрузки на клиента с помощью pgbench

Чтобы наблюдать работу пула соединений, вы будете использовать pgbench — стандартный инструмент для тестирования производительности, входящий в состав PostgreSQL. pgbench может симулировать одновременный доступ множества клиентов к базе данных.

Сначала вам нужно инициализировать среду pgbench. Это создаст несколько таблиц и заполнит их образцами данных. Выполните следующую команду, убедившись, что вы подключаетесь через порт PgBouncer (6432).

pgbench -i -h 127.0.0.1 -p 6432 -U postgres postgres

Вам будет предложено ввести пароль (labex_password). Флаг -i инициализирует базу данных для тестирования производительности. Вы должны увидеть вывод, указывающий на то, что таблицы были созданы и заполнены.

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.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.13 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.02 s).

Теперь запустите тестирование производительности для симуляции нагрузки. Следующая команда симулирует 10 одновременных клиентов (-c 10), причем каждый клиент выполняет 300 транзакций (-t 300).

pgbench -c 10 -t 300 -h 127.0.0.1 -p 6432 -U postgres postgres

Снова введите пароль, когда будет предложено. Тестирование производительности займет несколько секунд, а затем отобразит сводку результатов, включая количество транзакций в секунду (tps).

pgbench (14.18 (Ubuntu 14.18-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 300
number of transactions actually processed: 3000/3000
latency average = 5.935 ms
initial connection time = 1.342 ms
tps = 1685.027854 (without initial connection time)

Этот тест сгенерировал значительный трафик через PgBouncer, который мы рассмотрим на следующем шаге.

Мониторинг статистики PgBouncer

PgBouncer предоставляет специальную административную базу данных, которая позволяет отслеживать его активность и просматривать статистику пулов соединений.

Подключитесь к консоли администратора PgBouncer. Обратите внимание, что имя базы данных — pgbouncer.

psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer

При запросе введите пароль labex_password.

После подключения вы можете выполнять специальные команды SHOW. Сначала просмотрите общую статистику для всех баз данных.

SHOW STATS;

Эта команда отображает совокупные данные с момента последнего запуска PgBouncer. Вы увидите такие столбцы, как total_xact_count (общее количество транзакций) и total_query_count, которые теперь должны иметь высокие значения после теста pgbench.

pgbouncer=## SHOW STATS;
 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
 pgbouncer |                1 |                 1 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
 postgres  |             3019 |             21033 |        2860191 |     561691 |        17758077 |         16667957 |          277707 |             36 |             250 |    34117 |     6700 |          5882 |            792 |          3312
(2 rows)

Далее, чтобы увидеть состояние пулов соединений в реальном времени, используйте команду SHOW POOLS.

SHOW POOLS;

Эта команда предоставляет снимок состояния пулов соединений, показывая активные и неактивные соединения как для клиентов, так и для серверов.

• cl_active: Клиентские соединения, которые активно связаны с серверным соединением.
• sv_active: Серверные соединения, которые в настоящее время используются.
• sv_idle: Серверные соединения, которые неактивны и готовы к использованию новым клиентом.

 database  |   user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 pgbouncer | pgbouncer |         1 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
 postgres  | postgres  |         0 |          0 |             0 |         0 |      10 |       0 |         0 |        0 |       0 |          0 | session
(2 rows)

Изучая эту статистику, вы можете понять, насколько эффективно PgBouncer управляет соединениями и повторно использует их для обслуживания запросов клиентов.

Выйдите из оболочки psql, когда закончите.

\q

Резюме

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