Как настроить pgbouncer

Dec 25, 2022

Пул соединений в Postgres

Пул соединений - это метод создания пула соединений и кэширования этих соединений, чтобы их можно было использовать повторно.
PostgreSQL имеет процесс postmaster, который порождает новые процессы для каждого нового подключения к базе данных. Этот процесс занимает от 2 до 3 МБ памяти, что происходит каждый раз, когда вы создаете соединение с базой данных. Без пула соединений для каждого соединения процесс postmaster должен порождать новый процесс на задней стороне, используя 2-3 МБ памяти, и это создает проблему, если количество соединений слишком велико (обычно это наблюдается в критически важных приложениях). Чем больше количество соединений, тем больше памяти расходуется на создание этих соединений.
При использовании пула соединений, каждый раз, когда внешнее приложение запрашивает создание соединения с базой данных, соединение создается из пула. После завершения сеанса или транзакции соединение возвращается в пул.

 

Как же это помогает повысить производительность?

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

  • PGPOOL
  • PGBOUNCER

Оба этих инструмента были разработаны сообществом с учетом архитектуры PostgreSQL и предназначены специально для базы данных PostgreSQL. В то время как pgbouncer обеспечивает только пул соединений, pgpool обеспечивает пул соединений, балансировку нагрузки, высокую доступность и репликацию. Любой из этих инструментов можно использовать в зависимости от объема трафика, который вы ожидаете, и общей архитектуры вашей базы данных. Давайте посмотрим, как pgbouncer помогает улучшить производительность вашей базы данных, минимизируя время, необходимое для установления соединения с базой данных.
Как объяснялось ранее, Pgbouncer - это легкий пул соединений для PostgreSQL, который значительно сокращает время обработки и ресурсы для поддержания множества клиентских соединений с одной или несколькими базами данных. Pgbouncer поддерживает три типа пула при ротации соединений:

Session pooling: Серверное соединение назначается клиентскому приложению на весь срок действия клиентского соединения. PgBouncer возвращает серверное соединение обратно в пул, как только клиентское приложение отключается. Это метод по умолчанию.

Transaction pooling: Серверное соединение назначается клиентскому приложению на время транзакции. Когда PgBouncer обнаруживает завершение транзакции, он освобождает серверное соединение обратно в пул.
Statement pooling: Серверное соединение назначается клиентскому приложению для каждого утверждения. Когда утверждение завершается, серверное соединение возвращается в пул. В этом режиме не допускаются многоэтапные транзакции.

 

Как установить и настроить Pgbouncer:

Для этого я буду использовать Pgbouncer версии 1.9 на машине CentOS 7.
Установка:

  • Загрузите файл pgbouncer-1.9.0.tar.gz на вашу Linux-машину для установки. Вы можете загрузить его с сайта Pgbouncer Download.
  • Разархивируйте файл, в результате чего будет создана папка с именем pgbouncer-1.9.0.
  • Пожалуйста, убедитесь, что пакеты gcc, libevent-devel и openssl-devel установлены, прежде чем приступать к конфигурированию. Перейдите в папку pgbouncer-1.9.0 и запустите:
    ./configure -prefix=/opt/pgbouncer

-prefix позволит вам указать пользовательский каталог для установки. По умолчанию он будет установлен в /usr/local.

  • После настройки выполните следующую команду для установки Pgbouncer
    • make
    • make install
  • После установки ваши конфигурационные файлы и файл запуска Pgbouncer будут установлены в следующие директории
    • /opt/pgbouncer/share/doc/pgbouncer
    • /opt/pgbouncer/bin
       
  • Чтобы запустить Pgbouncer в режиме демона, выполните следующую команду:
    • pgbouncer -d /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
       

Конфигурация:

  • Для Pgbouncer важны два конфигурационных файла: pgbouncer.ini и userlist.txt.
    • pgbouncer.ini - Этот файл содержит все параметры, которые должны быть настроены для вашего Pgbouncer.
    • userlist.txt - Этот файл содержит имя пользователя и пароль вашей базы данных для аутентификации.
  • Я настроил pgbouncer.ini и userlist.txt следующим образом:
    • Pgbouncer.ini
pool-img01

Benchmarkdb и enterprise - это псевдонимы для двух строк подключения. Обратите внимание, что значение pool_size было установлено только для тестирования. Это не рекомендуемое значение.

  • Userslist.txt
pool-img02

Этот файл принимает входные данные в виде "username" "password". Пароль может быть как в обычном формате, так и в формате md5. Мы использовали пароль в формате md5 для обоих пользователей.

  • Теперь просто перезапустите службу pgbouncer, как показано ниже
    • pgbouncer -R -d /opt/pgbouncer/share/doc/pgbouncer/pgbouncer.ini
  • Вы можете проверить состояние пула и базы данных, войдя в базу данных pgbouncer, как показано ниже:
pool-img03

Теперь, чтобы проверить и подтвердить влияние пулера соединений, мы будем использовать pgbench для бенчмарка и pgbouncer в режиме пула сессий.

  • pgbench был инициализирован с масштабным фактором 30. pgbouncer имеет pool_size равный 100.
  • Мы будем тестировать сценарии с увеличением числа одновременных соединений, начиная с 300, 400 и 460. Для каждого соединения pgbench будет выполнять 20 транзакций, что приведет к общему количеству в 6000, 8000 и 9200 транзакций соответственно.
     

Ниже приведен результат анализа тестов

image-Conn

 

Общий анализ активности для соединений

  • Соединения Pgbouncer
pool-img04
  • Прямые соединения
pool-img05

Тесты можно проводить с различными настройками параметров pool_size и pool_mode для получения лучших результатов в соответствии с потребностями приложения.
Помимо параметров pool_size, pool_mode и деталей соединения, вы можете задать и другие параметры в конфигурационном файле pgbouncer. Ниже перечислены некоторые другие полезные параметры: -

  • min_pool_size - Этот параметр определяет минимальное количество серверных соединений в пуле. В любой момент времени при использовании pgbouncer вы будете видеть это количество соединений на вашем сервере, как простаивающих, так и активных.
  • reserve_pool_size - Дополнительные соединения, которые будут использоваться в случае проблем.
  • server_idle_timeout (seconds) - Все соединения с сервером будут удалены после простоя в течение этого количества секунд.
  • idle_transaction_timeout (seconds) - Закрывать соединения, которые находятся в состоянии "простоя в транзакции" в течение этого количества секунд.
  • max_client_conn - Общее количество клиентов, которые могут подключиться.
     

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