Как найти причину «Exception: idle transaction timeout» в приложении Flask+psycopg2+postgresql+pgbouncer?

Question

[Алексей]

Была такая проблема. С периодичностью раз в месяц web-приложение останавливалось с ошибкой 502. Причина - postgresql переставал обрабатывать запросы, потому что был превышен лимит количества соединений. Список процессов показывал кучу открытых ожидающих соединений, которые блокировались каким-то запросом. Причём запрос был вполне рядовой, который обычно выполнялся мгновенно. Причину, которая приводила к такой блокировке, выяснить не хватило ума. Но было несколько процессов "idle in transaction", и после убивания одного из них всё восстанавливалось.

В postgresql 9.5 ещё нет опции, устанавливающей таймаут на висячие транзакции. Поэтому было решено установить Pgbouncer.
В итоге есть postgresql, перед ним pgbouncer в pool_mode=transaction.

Пару недель всё работало замечательно. Но в течение последних двух дней вдруг без всякой видимой причины образовалась проблема. Web-приложение возвращает пользователю 500, пользователь удивляется, пробует ещё раз, всё опять работает нормально. Через несколько минут, уже в другом месте приложения, ситуация повторяется. Пользователь нервничает.

В логе приложения (Python, Flask, psycopg2):

Exception on /any-url-of-the-application [POST]
...
...
Exception: idle transaction timeout
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

Смотрю, на каких запросах падает - на любых, которые в обычной ситуации отрабатывают мгновенно.

В pgbouncer.ini:
idle_transaction_timeout = 600

Нагрузка на сервер - 10-20 соединений в секунду. В системных логах, в логах Postgresql и Pgbouncer - нет ничего подозрительного. Список процессов не показывает наличие процессов postgres с висящими транзакциями. Какие системные всплески, приводящие к кратковременному исчерпанию ресурсов, которые могут приводить к такой специфической ошибке - ума не приложу.

Странно вот что. Если бы такая ошибка возникала в обслуживающих скриптах, выполняющих длинные регулярные задачи в cron, было бы понятно, где копать. А тут - простой юзер, работает себе в браузере, и вдруг после очередного клика на кнопке - ошибка 500! idle_transaction_timeout = 600 - это 10 минут. Юзер же получает 500 мгновенно. Если учесть, что он при каждом обновлении страницы создаёт в контексте Flask-приложения своё собственное соединение, как оно может вдруг упасть по причине idle transaction timeout?

Что это может быть?

Answer 1

[Melkij]

и после убивания одного из них всё восстанавливалось.

остаётся надеяться что не kill -9

Начните с проверки, что ваше приложение действительно корректно переоткрывает соединение отстреленное по инициативе базы или баунсера. А не оставляет его в пуле навечно, выдавая сохранённый текст ошибки.

Кстати говоря, вы не назвали версию pgbouncer. Возможно и баунсер вы поставили столь же старый как и базу. Припоминаю возможно релевантный фикс в 1.10

Comments

[Алексей]

Ну вообще-то kill-9. Другого способа на боевом сервере, где нужно быстро соображать, не нашёл.
Версия баунсера 1.7 Похоже, ваша наводка на фикс актуальна, если, конечно, именно из-за этого бага и происходит всё это. Спасибо.
Вообще, баунсер, как я говорил, был установлен с единственной целью - убивать длинные транзакции, которые почему-то блокировали другие запросы, из-за чего происходило превышение количества соединений. Версия postgres 9.5 такой опции не имеет ещё. Был вдохновлён поначалу этим решением. Но оно оказалось нерабочим, потому что в момент, когда постгрес больше не принимает запросы по причине connection_limit, поздно пить боржоми.
Как просто удалить (хотя бы и kill-9) idle транзакции другим способом - не нашёл.

[Melkij]

kill -9 любого процесса базы роняет целиком базу в рестарт.
Снимать процессы вызовом pg_terminate_backend (а для того не работать суперпользователем и тогда superuser_reserved_connections вам возможность административно подключиться даст)

[Алексей]

Поставил версию 1.12. Не помогло.

Вообще, непонятно, как пользователь, который открыл страницу в браузере, может сразу же получить ошибку, которая в логе приложения описывается как idle transaction timeout, если таймаут аж 10 минут? Баунсер использовал для запроса не свободное соединение из пула, а какое-то, в котором уже 10 минут как висит незавершённая транзакция? Как такое может быть??? Или я что-то не понимаю в этой жизни? ))

[Melkij]

потому и

Начните с проверки, что ваше приложение действительно корректно переоткрывает соединение отстреленное по инициативе базы или баунсера. А не оставляет его в пуле навечно, выдавая сохранённый текст ошибки.

Не помню по поводу именно psycopg2, а так встречались такие реализации пулов на приложении которые некорректно реагируют на закрытие коннекта.

[Алексей]

Melkij, Не знаю, как в этом убедиться))
Кстати, ваше замечание по поводу

Снимать процессы вызовом pg_terminate_backend (а для того не работать суперпользователем и тогда superuser_reserved_connections вам возможность административно подключиться даст)

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

Про superuser_reserved_connections не знал. Если правильно понял, нужно создать суперпользователя в postgres, от имени которого и запускать эту обработку?

[Алексей]

Melkij,
И да, ещё

kill -9 любого процесса базы роняет целиком базу в рестарт.

это не так. Реальная история: есть 2 процесса с idle transaction, и много "SELECT waiting", которые блокированы. Убиваю один процесс из двух - не угадал, картина не меняется, но и база не падает в рестарт. Когда убиваю второй процесс, в котором idle transaction, остальные процессы спокойно отрабатывают и ситуация нормализуется. Сам видел))
Спасибо за помощь, разобрался с superuser_reserved_connections, буду копать в этом направлении.

[Melkij]

неа, kill -9 (SIGKILL) уведёт базу в рестарт. SIGTERM'ом попросить процесс завершиться можно и это корректно, его pg_terminate_backend и использует.

[Алексей]

Melkij, ну хорошо, не буду спорить, при случае попробую ещё раз проверить. Спасибо!

Answer 2

[Dimonchik]

тут все изучили?

или к вашей версии Постгре неприменимо?

очевидно, что дело в настойках базы / пула