Для контроля нагрузки и управления ресурсами памяти у сервера базы данных PostgreSQL есть лимит на одновременно открытые соединения — параметр max_connections. Ошибка too many connections означает, что этот лимит был достигнут и сервер больше не может принимать новые соединения. В этот момент приложение начинает получать ошибки подключения, а часть запросов просто не выполняется.
Чаще всего эта ошибка возникает вследствие неправильных архитектурных или конфигурационных решений в приложении.
Почему возникает ошибка too many connections
- Рост нагрузки — самая очевидная причина. Каждый HTTP-запрос может открывать соединение с базой и держать его пока выполняются необходимые SQL-запросы. Когда количество пользователей возрастает, таких запросов становится слишком много и лимит соединений заканчивается.
- Утечки соединений — иногда в приложении возникает проблема, когда соединение открывается, но по какой-то причине не закрывается. Это может быть из-за того, что приложение упало с исключением, которое не было правильно обработано и соединение не закрылось.
- Неправильно настроенный пул соединений — большие приложения или готовые фреймворки могут использовать пул соединений. Он нужен, чтобы переиспользовать уже открытое соединение, а не создавать новое. При неправильной настройке этого пула возможны ситуации когда создаётся слишком много соединений или они долго не освобождаются.
- Долгие запросы — пока SQL-запросы выполняются, соединение остаётся занятым, при высокой нагрузке системы это приведёт к исчерпанию лимита.
- Фоновые задачи — очереди, cron-скрипты, воркеры событий используют базу данных. Каждый из таких процессов держит отдельное соединение.
Чтобы исправить ошибку too many connections, нужно для начала посмотреть, что происходит с базой. PostgreSQL хранит информацию обо всех подключениях в представлении pg_stat_activity. Важно отметить, что когда max_connections превышен, администратор всё равно может подключиться к базе, т.к. PostgreSQL резервирует несколько соединений для суперпользователя (параметр superuser_reserved_connections)
SELECT pid, -- id процесса, можно использовать для ручного завершения
usename, -- пользователь под которым открыто соединение
application_name, -- название приложения которое подключилось
client_addr, -- ip клиента открывшего соединение
state, -- текущее состояние соединения
query_start, -- время начала выполнения текущего запроса
now() - query_start AS duration, -- сколько времени выполняется текущий запрос
query -- sql текущего или последнего выполненного запроса
FROM pg_stat_activity
ORDER BY query_start;
В этой таблице можно посмотреть:
- все активные соединения
- выполняемые SQL-запросы
- состояние соединения (active, idle, idle in transaction)
- время начала запроса
По этим данным можно понять, кто именно занимает соединения и как долго.
Способы исправления
1. Увеличить лимит
Иногда проблему можно решить, увеличив лимит — max_connections (по умолчанию он равен 100). Этот параметр находится в файле postgresql.conf. После изменения конфигурации сервер обязательно нужно перезапустить (изменения не применятся до перезапуска).
Каждое соединение потребляет память и системные ресурсы, слишком большой лимит может привести к ухудшению производительности, поэтому не стоит «бездумно» увеличивать max_connections.
2. Использовать пул соединений.
Идея достаточно проста: соединения создаются заранее и переиспользуются. Когда запрос заканчивается, соединение возвращается в пул. В зависимости от нагрузки можно регулировать размер пула (количество соединений) на один экземпляр приложения.
При очень высоких нагрузках простого пула может быть недостаточно. Тогда можно использовать PgBouncer — это отдельный пулер, который работает между приложением и PostgreSQL. Он умеет обслуживать тысячи клиентских подключений используя гораздо меньшее количество соединений с базой.
У PgBouncer есть несколько режимов работы:
- session — клиенту выделяется серверное соединение на всё время жизни подключения. Оно возвращается в пул только после закрытия клиентского соединения. Это максимально приближенный к прямому подключению к PostgreSQL режим. Подходит для приложений требующих сохранения состояния сессии, помогает ускорить работу для клиентов с множеством коротких подключений
- transaction — соединение выделяется клиенту только на время транзакции. Позволяет существенно снизить нагрузку на сервер, но накладывает ограничения (не все возможности работы с PostgreSQL поддерживаются)
- statement — соединение возвращается в пул сразу после выполнения каждого SQL-запроса. Больше всего экономит ресурсы, и накладывает большие ограничения. В этом режиме транзакции должны состоять только из одного запроса, транзакции с несколькими операторами вызовут ошибку
Настроить пуллер можно через файл конфигурации pgbouncer.ini. Основные настройки:
- pool_mode — режим работы
- default_pool_size — начальное количество соединений в пуле
- min_pool_size — минимальное количество соединений в пуле
- reserve_pool_size — размер дополнительного пула, который используется после исчерпывания основного
- reserve_pool_timeout — время, по истечении которого будет выделено соединение из резервного пула
- server_idle_timeout — время, по истечении которого простаивающее соединение будет закрыто
- server_lifetime — максимальное время жизни серверного соединения
- query_wait_timeout — максимальное время, которое клиент может ждать свободное серверное соединение в очереди.
3. Найти утечки соединений
Если в ходе исследования выяснилось, что соединения накапливаются в состоянии idle (клиент подключён к серверу, но не выполняет запросов), то вероятнее всего где-то в коде соединение не закрывается. При использовании пула много соединений в состоянии idle будет нормальным, в этом случае надо обращать внимание на idle in transaction (соединение находится внутри транзакции, но не выполняет запросов). Определить точное место утечки поможет добавление логирования открытия и закрытия соединений.
SELECT state, count(*) FROM pg_stat_activity
GROUP BY state;
Также будет не лишним проверить места обработки ошибок. Хорошая практика — всегда закрывать соединение в finally или использовать механизмы автоматического освобождения ресурсов.
4. Оптимизировать медленные запросы
Когда соединение занято несколько секунд, или даже минут, то стоит обратить внимание на SQL-запросы, которые выполняются в данный момент. Оптимизация запросов — это тема для отдельной статьи, а скорее даже нескольких. Тут я приведу просто общие способы:
- Проанализировать долгий запрос с помощью EXPLAIN ANALYZE — можно увидеть план выполнения запроса и где он тратит время
- Добавить индексы
- Проверить и переписать JOIN
- Оптимизировать выборку для уменьшения объёма обрабатываемых данных
SELECT pid,
now() - query_start AS duration,
state,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
Профилактика
Избавиться от риска too many connections можно только при правильной эксплуатации базы. Важно отслеживать текущее количество соединений, и сверять его с max_connections, а также следить за длительностью запросов. С помощью этого мониторинга можно заметить проблему ещё до того, как база начнёт отказывать в подключениях
SELECT connections_count, max_count, round(connections_count::numeric/max_count * 100, 2) as percent
FROM (SELECT count(*) as connections_count,
current_setting('max_connections')::integer as max_count
FROM pg_stat_activity) t1;
При добавлении новых функций нужно проверять систему под нагрузкой. Это называется нагрузочным тестированием и поможет заранее узнать и избавиться от узких мест.
Стоит наладить контроль над зависшими транзакциями. Для этого PostgreSQL имеет параметр idle_in_transaction_session_timeout, который завершает транзакцию, если она не была активной в течение указанного времени.
SELECT pid,
now() - xact_start AS transaction_duration,
state,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY transaction_duration DESC;
Нужно установить лимит для параллельности фоновых задач чтобы они не забирали много соединений у основного приложения.
Грамотная работа с соединениями — один из ключевых факторов стабильности PostgreSQL. Большинство проблем с too many connections решаются правильной настройкой пула, оптимизацией запросов и контролем количества фоновых процессов.