Установка пулера соединений PgBouncer для PostgreSQL

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

Введение

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

Что такое пулер соединений и зачем он нужен

Даже одно приложение может открыть сразу несколько соединений к базе данных. А если таких приложений много, да еще они запущены в несколько экземпляров (например, микросервисы), тогда базе данных нужно одновременно держать очень много активных соединений. Postgres для каждого подключения создает отдельный процесс в операционной системе, поэтому при большом количестве соединений на сервере с базой данных могут закончиться вычислительные ресурсы, и это скажется на производительности.

Кроме того, само по себе создание подключения — довольно долгая операция. Открывается соединение с БД, выполняется аутентификация, открытие сокета и так далее. А если при каждом запросе от приложения будет создаваться новое подключение — приложение будет работать медленнее, чем могло бы.

Поэтому существуют пулеры соединений. Это своего рода прослойка между клиентом и сервером базы данных. Пулеры объединяют, группируют и переиспользует коннекты между разными клиентами. Для этого пулеры создают виртуальные коннекты или сессии. Когда клиент закрывает свое соединение — закрывается лишь сессия, а само физическое подключение возвращается в пул. И когда другой клиент открывает новое соединение — ему вернется тот же самый физический коннект.

принцип работы пулеров соединений — *Принцип работы пулеров в PostgreSQL*

Существует несколько реализаций пулеров, например, pgBouncer, pgPool, odissey. В этой статье мы рассмотрим PgBouncer — один из самых популярных пулеров.

У PgBouncer есть три способа управления пулом подключений:

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

Далее мы покажем установку и настройку PgBouncer, а также проверим его в действии.

Создание виртуальной машины

Для начала создадим виртуальную машину, где у нас будет установлен PostgreSQL. В консоли облачной платформы Selectel перейдем в раздел Облачная платформа, затем Серверы и нажмем кнопку Создать сервер.

создание ВМ в облаке — *Первый шаг создания облачного сервера в Selectel*

В качестве ОС выберем образ Ubuntu 22.04. Для небольшого сервера PostgreSQL будет достаточно конфигурации с 1 CPU, 2 ГБ оперативной памяти и 5 ГБ диска. В настройках сети не забудьте выделить машине внешний IP-адрес, если планируете подключаться к ней из интернета. Также сохраните пароль от root-пользователя и проверьте, что выбран правильный SSH-ключ.

конфигурация виртуальной машины — *Выбор конфигурации облачного сервера для PostgreSQL*

После того, как виртуальная машина будет создана, скопируйте IP-адрес и подключитесь по SSH.

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

Установка и настройка PgBouncer для PostgreSQL

Пулер может находиться на том же хосте, где работает PostgreSQL, или на отдельном. Мы будем все делать на одном хосте с базой данных. Установим PgBouncer из стандартных репозиториев Ubuntu:


    sudo apt-get -y install pgbouncer

После завершения установки отредактируем файл /etc/pgbouncer/pgbouncer.ini. Укажем, на каком хосту и порту PgBouncer должен искать запущенный сервер PostgreSQL. Для этого в раздел [databases] добавим:


    * = host=localhost port=5432

Затем изменим параметр listen_addr. Тут нужно указать IP-адрес, на котором будет доступен PgBouncer. Мы укажем listen_addr = *, чтобы PgBouncer был доступен на всех IP-адрес виртуальной машины (внутренних и внешних).

Далее поменяем параметр auth_type. По умолчанию PgBouncer настроен на тип авторизации trust, использовать которую небезопасно и рекомендуется лишь в ознакомительных целях. Мы сразу заменим этот параметр на md5.

Также отредактируем параметр max_client_conn, который отвечает за максимальное количество одновременных соединений. Установим его значение равным 500, это пригодится нам при тестировании.

Обратите внимание на значение параметра listen_port (в него не нужно вносить изменение). Тут указывается порт, на котором будет доступен PgBouncer. Стандартный порт для PostgreSQL — 5432, а для PgBouncer — 6432. При этом подключаться к БД можно будет по обоим портам, но при необходимости стандартный порт можно закрыть.

Итоговый файл конфигурации выглядит так:


    [databases]

* = host=localhost port=5432

[pgbouncer]

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

listen_addr = *
listen_port = 6432

unix_socket_dir = /var/run/postgresql

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

max_client_conn = 500

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

Создадим файл /etc/pgbouncer/userlist.txt и запишем в него строки формата:


    “<ИМЯ_ПОЛЬЗОВАТЕЛЯ>” “<ПАРОЛЬ>”

Но хранить пароли в открытом виде небезопасно, поэтому вместо пароля лучше записать его хеш. Выполним команду:


    echo -n '<ПАРОЛЬ>' | md5sum

Она выдаст хеш пароля, его и будем использовать вместе с префиксом md5 вначале:


    “<ИМЯ_ПОЛЬЗОВАТЕЛЯ>” “md5<ПАРОЛЬ>”

Настройка PgBouncer завершена, запустим его:


    systemctl enable --now pgbouncer

Тестирование PgBouncer

Чтобы проверить работу PgBouncer воспользуемся утилитой pgbench. Она устанавливается вместе с сервером БД и используется для проверки производительности PostgreSQL. Сначала нужно подготовить БД к бенчмарку, для этого запустим команду:


    pgbench -p 5432 -i -U selectel_user -h localhost selectel_db

Теперь запустим по очереди два теста: на стандартном порту 5432, и на порту 6432 с пулером. Сымитируем запросы 500 клиентов в 16 потоков, тест будет продолжаться 60 секунд.


    pgbench -p 5432 -c 500 -j 16 -T 60 -U selectel_user -h localhost selectel_db

Пока бенчмарк выполняется, выполним команду в соседнем окне:


    ps aux | grep selectel_user | cat -b

Увидим, что в ОС одновременно создано 500 физических подключений к БД:


    <...>
499  postgres    4557  0.1  1.4 245172 28944 ?        Ss   16:45   0:00 postgres: 14/main: selectel_user selectel_db ::1(34508) UPDATE waiting
500  postgres    4558  0.1  1.3 245172 28200 ?        Ss   16:45   0:00 postgres: 14/main: selectel_user selectel_db ::1(34514) UPDATE waiting

Дождемся результатов теста:


    number of transactions actually processed: 11716
latency average = 2219.578 ms
tps = 225.268041 (without initial connection time)

Теперь выполним этот же бенчмарк на порту 6432, где запущен PgBouncer:


    pgbench -p 6432 -c 500 -j 16 -T 60 -U selectel_user -h localhost selectel_db

Пока выполняется тест, снова проверим количество физических коннектов к БД:


    ps aux | grep selectel_user | cat -b

Результат:


    <...>
19  postgres    1060  1.2  1.5 245044 31716 ?        Ss   16:51   0:00 postgres: 14/main: selectel_user selectel_db ::1(59072) UPDATE waiting
20  postgres    1061  1.4  1.5 245052 31676 ?        Ss   16:51   0:00 postgres: 14/main: selectel_user selectel_db ::1(59086) UPDATE waiting

Видим, что открыто всего 20 соединений – PgBouncer открыл в 25 раз меньше физических подключений. Дождемся результатов тестов:


    number of transactions actually processed: 19485
latency average = 1573.937 ms
tps = 317.674686 (without initial connection time)

Видим, что производительность запросов с PgBouncer возросла:. Даже при том, что сами PgBouncer и pgbench тоже потребляют ресурсы.

Количество выполненных транзакций больше (19 485 против 11 716).
Средняя задержка меньше (1573 мс против 2219 мс).
Количество операций в секунду выше (317 против 225).

Заключение

Вы узнали, что такое пулер соединений, научились устанавливать PgBouncer и настраивать его. Проверили его работу в действии и убедились, что при большом количестве соединений он может значительно повысить скорость работы с БД.

Мы рассмотрели лишь основные настройки PgBouncer. Но есть еще много других параметров как в пулере, так и в самой БД. Может быть довольно непросто настроить всю эту связку для нужд высокопроизводительной системы. В этом случае рекомендуем попробовать наш PostgreSQL as a Service. Пулер соединений в нем устанавливается автоматически, вам не придется разбираться во всех тонкостях его конфигурирования и разбираться с настройкой мониторинга. Также вам не придется заботиться о резервном копировании, так как мы создаем бекапы сами.

Базы данных