Пулы соединений: как выбрать размер и почему больше - значит медленнее

У каждой базы данных есть максимальное число одновременных соединений, и оно ниже, чем предполагает большинство людей. Превышение не замедляет работу плавно. Оно даёт ошибку, причём под нагрузкой, когда новый вид отказа нужен вам меньше всего:

FATAL: sorry, too many clients already

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

Сколько на самом деле стоит соединение#

В PostgreSQL соединение - это процесс операционной системы. Postmaster создаёт для каждого своего бэкенда через fork, и тот живёт, пока клиент не отключится. У него есть несколько мегабайт собственной памяти, плюс структуры кэша и буферов, к которым он обращается, плюс сколько бы work_mem ни выделил выполняемый запрос. Сотня простаивающих соединений - это сотня процессов, которые должно планировать ядро, и заметный кусок памяти, потерянный до выполнения первого запроса.

Открытие соединения тоже не бесплатно. Новое соединение - это fork, аутентификация, согласование TLS, если вы им пользуетесь, и подготовка сессии, которую выполняет ваш фреймворк. В локальной сети это несколько миллисекунд, через интернет с TLS - десятки. Если веб-запрос выполняет 30 мс настоящей работы, то 20 мс на открытие соединения каждый раз удваивают задержку впустую.

MongoDB дешевле на соединение - поток вместо процесса, - но не бесплатна, и у сервера всё равно есть потолок. Пул драйвера - это то, чем вы реально управляете, и те же рассуждения применимы к нему.

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

Почему больше соединений - это медленнее#

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

Сервер базы данных действительно может делать одновременно ограниченное число дел: примерно столько, сколько у него ядер CPU, плюс некоторый запас на то, что ждёт диска. Сверх этой точки дополнительные соединения работы не ускоряют. Они встают в очередь, но вместо того чтобы вежливо стоять в вашем пуле, где это ничего не стоит, они стоят внутри базы данных, где каждый ждущий запрос держит память, блокировки и место в планировщике, а переключение контекста между сотнями бэкендов сжигает CPU, который мог бы выполнять запросы.

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

Есть аккуратный способ это увидеть. Параллелизм равен пропускной способности, умноженной на задержку. Если ваше приложение выполняет 500 запросов в секунду, а средний запрос занимает 4 мс, то в среднем в любой момент заняты два соединения. Два. Пул на пятьдесят, скопированный из блога, ничего не ускоряет; это страховка от всплеска, с которым очередь справилась бы лучше.

Арифметика, которую никто не делает#

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

По умолчанию max_connections в PostgreSQL равен 100, и три из них резервируются параметром superuser_reserved_connections, чтобы администратор всё равно мог зайти. Ваш реальный бюджет - 97. Заполнив его до 98, вы получите отказ при следующем деплое, когда старые и новые процессы кратко работают вместе.

Считайте каждый процесс, который подключается, а не каждый сервер. В стеке на PHP или WordPress пула в обычном смысле нет вовсе: каждый воркер PHP-FPM держит собственное соединение, поэтому pm.max_children и есть размер вашего пула, а сорок дочерних процессов - это сорок соединений. В контейнерной среде переход от двух контейнеров к четырём удваивает итог, и никто не трогал ни одной настройки базы. А всё, что запускает по процессу на запрос, - бессерверная платформа, хостинг в стиле CGI, - умножается без предела, и именно для такого случая существует PgBouncer.

Размер пула: формула и отправная точка#

Самое старое и по-прежнему лучшее эмпирическое правило пришло от сообщества PostgreSQL, а прославила его документация HikariCP:

connections = (core_count * 2) + effective_spindle_count

На сервере с 2 vCPU и хранилищем NVMe это выходит около 5 или 6 на всё приложение целиком, а не на процесс. На 4 vCPU - около 9 или 10. Эти числа кажутся абсурдно малыми любому, кто держал пул на 50 и не замечал этого, и в этом суть: он никогда не использовал 50, он стоял в очереди внутри базы данных, а не перед ней.

Практическая отправная точка для небольшого приложения:

Дальше корректируйте по фактам. Если пул никогда не исчерпывается, а запросы быстрые, его хватает: слишком большой пул не показывает никаких симптомов до того дня, когда покажет. Если запросы ждут соединения, а CPU базы простаивает, увеличьте пул. Если запросы ждут, а CPU базы загружен полностью, дело не в пуле: исправляйте запросы с помощью EXPLAIN ANALYZE или добавьте индекс, потому что больший пул лишь размажет тот же CPU тоньше.

На RE:NODE линейки баз данных - это ваш собственный сервер, а не доля общего кластера, с доступом к файлам и консоли, поэтому max_connections - это то, что написано в postgresql.conf на этом сервере, и вы можете его менять. Поднимайте его осторожно на небольшом тарифе: связывающее ограничение здесь память, и когда контейнер достигает её предела, он останавливается и чисто перезапускается, а не уходит в swap. Сто бэкендов на тарифе в 1 ГБ превращают медленный день в перезапуск. Какое число поднимать первым, разобрано в статье настройка Postgres для небольших серверов, и это редко именно это число.

Настройки пула в используемых вами библиотеках#

Каждый пул выражает один и тот же небольшой набор идей под разными именами. Ниже - значения по умолчанию, которые стоит знать, потому что некоторые из них не подходят серверному приложению.

Три из этих значений заслуживают комментария. Из-за max_overflow в SQLAlchemy реальный потолок - 15 на процесс, а не 5, и это удивляет тех, кто ведёт расчёт выше. Django исторически открывал и закрывал соединение на каждый запрос: это безопасно и медленно; CONN_MAX_AGE, выставленный примерно в 60, переиспользует соединение, а свежие версии умеют работать с настоящим пулом на psycopg 3 - сверьтесь с документацией вашей версии, потому что это изменилось недавно. А значение драйверов MongoDB по умолчанию, 100 на процесс, намного больше, чем нужно любому небольшому приложению.

// node-postgres: один пул на процесс, создаётся один разimport { Pool } from "pg";export const pool = new Pool({  connectionString: process.env.DATABASE_URL,  max: 5,  idleTimeoutMillis: 30_000,  connectionTimeoutMillis: 5_000,   // fail fast instead of hanging});

# SQLAlchemy: реальный потолок здесь 8, а не 5engine = create_engine(    os.environ["DATABASE_URL"],    pool_size=5,    max_overflow=3,    pool_timeout=5,       # seconds to wait for a connection    pool_recycle=1800,    # reconnect before anything else drops it    pool_pre_ping=True,   # check liveness before handing one out)

Создавайте пул один раз, на уровне модуля или при запуске приложения, и никогда - внутри обработчика запроса. Пул, созданный на каждый запрос, - это не пул, а соединение с лишними действиями, и это самый частый способ, которым такое ломается в небольших кодовых базах.

Таймауты: быстро падать, а не копиться#

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

• Таймаут захвата (connectionTimeoutMillis, pool_timeout, connectionTimeout). Как долго запрос ждёт свободного соединения, прежде чем сдаться. Задайте несколько секунд. Запрос, который уже ждал пять секунд, - это запрос, за которым никто больше не следит, и его отказ освобождает место для того, что важнее.
• Таймаут запроса. Задаётся на базе или сессии и прерывает слишком долгий запрос. SET statement_timeout = '10s' для веб-трафика, больше для отчётов, выключен для миграций.
• Таймаут простоя внутри транзакции. idle_in_transaction_session_timeout по умолчанию равен 0, то есть никогда, и это умолчание вызвало больше сбоев, чем любое другое. Соединение, которое открыло транзакцию и ушло, бессрочно держит свои блокировки и не даёт работать vacuum. Тридцати секунд с запасом.
• Максимальное время жизни (maxLifetime, pool_recycle). Периодически закрывайте и открывайте соединения заново, чтобы файрвол, прокси или переключение на резерв не оставили вас с сокетами, о которых другая сторона уже забыла.

-- Sensible defaults for an application role, set onceALTER ROLE app_user SET statement_timeout = '15s';ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';ALTER ROLE app_user SET lock_timeout = '3s';

Если задать их на роли, а не в коде приложения, они применяются к каждому соединению, включая то, которое кто-нибудь откроет с ноутбука в два часа ночи. Какую роль использовать и почему это не должен быть суперпользователь, рассказано в статье роли и права в Postgres.

Смысл всех четырёх один: превратить бесконечное ожидание в быструю, заметную ошибку, которую видят ваши проверки состояния и журналы. Приложение, которое возвращает 503 за две секунды, можно восстановить; то, которое держит десять тысяч запросов открытыми, пока не сработает таймаут балансировщика, - нельзя. Вторая половина этого поведения разобрана в статье корректное завершение и проверки состояния.

PgBouncer и когда он нужен#

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

[databases]appdb = host=127.0.0.1 port=5432 dbname=appdb[pgbouncer]listen_port = 6432pool_mode = transactionmax_client_conn = 1000default_pool_size = 10

Три режима пула, и выбор режима - это всё решение:

• session - клиент держит серверное соединение, пока не отключится. Безопасно, совместимо со всем, и почти ничего не экономит.
• transaction - серверное соединение возвращается в конце каждой транзакции. Это режим, который стоит иметь, и именно он мультиплексирует тысячу клиентов на десять бэкендов.
• statement - возвращается после каждого оператора. Ломает многооператорные транзакции. Редко уместен.

У режима transaction есть условия, потому что всё, что живёт в сессии, а не в транзакции, перестаёт быть надёжным: операторы SET, консультативные блокировки уровня сессии, LISTEN/NOTIFY и курсоры, удерживаемые вне транзакции. Серверные подготовленные операторы годами были проблемой; свежие версии PgBouncer поддерживают их на уровне протокола через max_prepared_statements, но проверьте свою версию, прежде чем на это полагаться.

PgBouncer нужен, когда у вас много короткоживущих процессов, каждому из которых нужно соединение: стек PHP с сотнями воркеров, платформа, которая порождает процесс на запрос, или несколько приложений, делящих одну базу. Он не нужен для одного приложения на Node или Python с правильно подобранным пулом, где это был бы второй процесс, который нужно запускать без всякой пользы. Добавлять его «для масштаба» при четырёх процессах приложения - классический случай решения проблемы, которой у вас нет.

Как найти утечку#

Пул, который исчерпывается медленно, за часы, и восстанавливается после перезапуска, - это не нехватка мощности. У него есть путь в коде, который берёт соединение и никогда его не возвращает.

Шаблон всегда один: ранний return, выброшенное исключение или ветка, пропускающая освобождение. Везде, где вы видите ручной connect без освобождения в блоке finally или менеджере контекста, есть кандидат.

// The leak: an exception here never releases the clientconst client = await pool.connect();const rows = await client.query(sql);   // throwsclient.release();// The fixconst client = await pool.connect();try {  return await client.query(sql);} finally {  client.release();}

Ещё лучше вообще не брать соединения вручную. pool.query() в node-postgres, блок with в Python, менеджер контекста или слой репозитория - все они делают освобождение автоматическим, и ни одно из них нельзя забыть в пути кода, добавленном позже.

Чтобы подтвердить увиденное, спросите базу, а не гадайте:

-- Who is connected, and what are they doing?SELECT state, count(*) FROM pg_stat_activity GROUP BY state;-- The dangerous ones: open transactions doing nothingSELECT pid, usename, state,       now() - state_change AS idle_for,       left(query, 60) AS last_queryFROM pg_stat_activityWHERE state = 'idle in transaction'ORDER BY idle_for DESC;

// MongoDB: current, available and total ever createddb.serverStatus().connections

Куча сессий idle in transaction - это утёкшая транзакция, а не утёкшее соединение, и это хуже: она держит блокировки и мешает vacuum убирать за собой. Куча обычных idle-сессий, равная вашему настроенному максимуму, - это здоровый пул в покое. Рост totalCreated в MongoDB или соединения, которые продолжают расти после выравнивания трафика, - признак того, что где-то пул создаётся заново вместо повторного использования.

Выводите из пула три числа и следите за ними: соединения в работе, ожидающие запросы и 99-й процентиль времени ожидания захвата. Одна только загрузка ничего не говорит: пул, загруженный на 100 процентов, когда никто не ждёт, подобран идеально. Значимая метрика - время ожидания, и мониторинг, который что-то вам говорит излагает общие доводы в пользу такого выбора метрик. Если время ожидания велико, база занята или запросы медленные, а когда пора менять тариф начинается с умения отличить одно от другого.

FAQ#

Что означает «sorry, too many clients already»?

Что PostgreSQL достиг max_connections и отказал в новой сессии. Посчитайте процессы приложения, умноженные на размер их пула, плюс воркеры, задачи cron и любые открытые клиенты. Итог выше лимита, и исправление почти всегда - меньший пул, а не более высокий лимит.

Может, просто увеличить max_connections?

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

Какой размер пула хороший?

Начните примерно с удвоенного числа ядер CPU базы, распределённого между всеми процессами вашего приложения, плюс несколько запасных для миграций и администрирования. Для небольшого приложения на базе с 2 vCPU это около 8 соединений в сумме, а не на процесс.

Нужен ли мне PgBouncer?

Только если у вас много короткоживущих процессов, каждому из которых нужно соединение: стек PHP с большим pm.max_children, платформа с процессом на запрос или несколько приложений на одной базе. Одному хорошо настроенному приложению он не даёт выгоды, а пулинг транзакций ограничивает возможности сессии, которыми вы, возможно, пользуетесь.

Почему мой пул заканчивается за ночь, а после перезапуска работает?

Это утечка. Какой-то путь кода берёт соединение и никогда его не освобождает, обычно потому, что исключение пропустило освобождение. Оборачивайте каждый ручной захват в finally или менеджер контекста и предпочитайте собственный вспомогательный метод запросов пула, чтобы об освобождении нельзя было забыть.

Есть ли такая же проблема у MongoDB?

Та же форма, но мягче. Её соединения - потоки, а не процессы, но значение драйвера по умолчанию, maxPoolSize 100 на процесс, всё равно умножается по вашему приложению. Уменьшите его, задайте таймаут очереди ожидания и при сомнениях проверьте db.serverStatus().connections.