Пул соединений (connection pooling): зачем и как

Введение

Установка нового соединения с БД — относительно затратная по времени операция (сетевой handshake, аутентификация, статья 434) — пул соединений избегает повторения этой затраты для каждого отдельного запроса, поддерживая набор уже установленных, готовых к повторному использованию соединений.

Концепция

Без пула соединений каждая отдельная операция (или, в наивной реализации, даже каждый отдельный запрос) могла бы открывать новое соединение и закрывать его после использования — это создаёт заметные накладные расходы, особенно для приложений с большим количеством коротких, частых обращений к БД (типично для веб-серверов, обрабатывающих множество отдельных HTTP-запросов, каждый из которых может требовать собственного, короткого обращения к БД). Пул соединений поддерживает заранее установленный набор соединений, «выдаваемых» коду по запросу и возвращаемых в пул (а не закрываемых физически) после использования — это амортизирует затраты установки соединения между множеством последовательных операций, использующих одно и то же физическое соединение по очереди.

Пример кода: Пул соединений (connection pooling)

// Простой, концептуальный пул соединений Qt — каждый поток получает своё постоянное соединение
// (Qt-соединения не потокобезопасны для совместного использования из разных потоков,
// поэтому типичный паттерн — одно соединение на поток, а не общий пул для всех потоков сразу)
class DatabaseConnectionManager
{
public:
    QSqlDatabase &connectionForCurrentThread()
    {
        QThread *currentThread = QThread::currentThread();
        QString connectionName = QString("conn_%1").arg(reinterpret_cast<quintptr>(currentThread));

        if (!QSqlDatabase::contains(connectionName)) {
            QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL", connectionName);
            db.setHostName("localhost");
            db.setDatabaseName("mydb");
            db.setUserName("app_readwrite");
            db.setPassword(getPasswordFromSecureStorage()); // статья 377
            db.open();
        }

        return QSqlDatabase::database(connectionName);
    }
};

Концептуальная схема серверного пула соединений (например, PgBouncer для PostgreSQL):

[Приложение 1] --\
[Приложение 2] ---> [PgBouncer: пул из 20 реальных соединений] ---> [PostgreSQL сервер]
[Приложение N] --/

PgBouncer принимает множество "виртуальных" соединений от клиентских приложений,
но поддерживает значительно меньшее количество РЕАЛЬНЫХ соединений с самим сервером БД,
переиспользуя их между разными клиентскими запросами — снижает нагрузку на сервер БД
по сравнению с ситуацией, где каждое из множества приложений держит собственное прямое соединение

; Конфигурация PgBouncer (концептуальный пример) — ограничение реального количества
; соединений с сервером БД независимо от количества клиентских приложений
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction  ; соединение возвращается в пул после каждой транзакции
max_client_conn = 1000   ; до 1000 клиентских (виртуальных) соединений
default_pool_size = 20   ; но не более 20 РЕАЛЬНЫХ соединений с сервером БД

Пояснения к коду

DatabaseConnectionManager показывает специфичный для Qt паттерн — поскольку объекты QSqlDatabase не предназначены для безопасного совместного использования из разных потоков одновременно, типичная Qt-практика — выделение отдельного, постоянного соединения для каждого рабочего потока приложения (а не классический пул, где соединения многократно «выдаются» и «возвращаются» разным частям кода динамически), что само по себе уже даёт основную выгоду пулинга — переиспользование уже установленного соединения для множества последовательных операций в рамках одного потока, без необходимости устанавливать новое соединение для каждой отдельной операции. Конфигурация PgBouncer показывает альтернативный, серверный подход к пулингу — отдельный, специализированный промежуточный сервис, управляющий ограниченным количеством реальных соединений с БД независимо от (потенциально значительно большего) количества клиентских приложений/потоков, что особенно ценно для архитектур с большим количеством отдельных клиентских процессов (а не потоков одного процесса, как в Qt-примере), каждый из которых в противном случае установил бы собственное, отдельное прямое соединение с сервером БД.

Подводные камни

Отсутствие верхнего ограничения на количество одновременных соединений с БД, особенно в архитектуре с динамически создаваемыми рабочими потоками/процессами — каждое соединение с СУБД потребляет память и ресурсы на стороне сервера БД, и без явного ограничения (через пул с фиксированным максимальным размером) резкий всплеск нагрузки приложения может попытаться открыть значительно больше соединений, чем сервер БД способен эффективно обслуживать, что может привести к деградации производительности или вовсе к отказу в новых соединениях самим сервером БД при достижении его собственного предела.
Использование транзакционного режима пулинга (pool_mode = transaction в примере PgBouncer) для кода, рассчитывающего на сохранение состояния соединения между отдельными транзакциями (например, временные таблицы, специфичные настройки сессии, явно установленные через отдельную команду до начала транзакции) — в транзакционном режиме физическое соединение может быть передано другому клиенту сразу после завершения текущей транзакции, и любое состояние, специфичное для конкретного «логического» соединения, но не привязанное строго к рамкам одной транзакции, может неожиданно «потеряться» или, что хуже, оказаться видимым для другого, не связанного клиента, получившего то же физическое соединение позже.
Слишком маленький размер пула для реальной нагрузки приложения, что приводит к тому, что запросы, не успевшие сразу получить свободное соединение из пула, вынуждены ждать (что для приложения с высокой нагрузкой может создать заметные задержки или тайм-ауты) — размер пула должен быть подобран с учётом реальной, ожидаемой конкурентной нагрузки приложения, а не выбран произвольно или скопирован из примера/документации без анализа конкретной ситуации.
Открытие нового соединения для каждого отдельного, короткого запроса вместо использования пула/постоянного соединения, особенно в коде, написанном без должного понимания относительной затратности установки соединения — для приложений с частыми, многочисленными обращениями к БД эта затратность установки нового соединения для каждого отдельного запроса может составить заметную, ненужную долю общего времени выполнения, и переход на пул соединений (или хотя бы постоянное, переиспользуемое соединение, как в Qt-примере на один поток) — одна из наиболее прямых, эффективных оптимизаций для такого паттерна использования.