Соединения PostgreSQL и PgBouncer: цена backend и что ломает режим transaction

Тысяча клиентов — это тысяча процессов postgres: около 1,5 ГБ памяти в простое, а под лимитом 2 ГБ контейнер умирает от OOM. PgBouncer в режиме transaction обходится 20 серверными соединениями, но всё, что жило в сессии, ломается. Снимок транзакции описан в MVCC PostgreSQL, SET LOCAL за пулером — в паттернах мультиарендности.

DBA / 01koddo

1 000 клиентов, 20 backend и момент возврата в пул

Стенд

Отдельная сеть, образ PgBouncer на базе postgres:18, PostgreSQL с max_connections = 1100, сам PgBouncer и клиентский контейнер с pgbench. Пароль для pgbench напрямую передаётся через PGPASSWORD, к PgBouncer клиенты идут без пароля (auth_type = trust в учебном стенде):

N=dba-connections-and-pgbouncer-net; P=dba-connections-and-pgbouncer-pg
printf 'FROM postgres:18\nRUN apt-get update && apt-get install -y --no-install-recommends pgbouncer procps\n' > Dockerfile
docker build -q -t dba-connections-and-pgbouncer-img .
docker network create $N
docker run -d --name $P --network $N --memory 2g --cpus 4 --ulimit nofile=65536:65536 \
  -e POSTGRES_PASSWORD=pg dba-connections-and-pgbouncer-img \
  -c max_connections=1100 -c shared_buffers=256MB
docker run -d --name dba-connections-and-pgbouncer-pgb --network $N --memory 512m --user postgres \
  --ulimit nofile=65536:65536 -v $PWD/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro \
  -v $PWD/userlist.txt:/etc/pgbouncer/userlist.txt:ro \
  --entrypoint pgbouncer dba-connections-and-pgbouncer-img /etc/pgbouncer/pgbouncer.ini
docker run -d --name dba-connections-and-pgbouncer-cli --network $N --memory 3g \
  --ulimit nofile=65536:65536 --entrypoint sleep dba-connections-and-pgbouncer-img infinity
docker exec $P pgbench -U postgres -i -s 10 postgres

В pgbouncer.ini база postgres смотрит на контейнер $P, а pool_mode = transaction, default_pool_size = 20, max_client_conn = 1500, max_prepared_statements = 0; вторая база one с pool_size=1 нужна, чтобы два клиента гарантированно делили одно серверное соединение. SELECT version() вернул PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2), pgbouncer --version — PgBouncer 1.26.1.

Опыт 1: что стоит соединение

Каждое подключение — это postmaster_child_launch в BackendStartup:

	pid = postmaster_child_launch(bn->bkend_type, bn->child_slot,
								  &startup_data, sizeof(startup_data),
								  client_sock);

Код — postmaster.c, строки 3569–3571, тег REL_18_6: порождённый процесс и есть backend клиента. Сумма RSS по процессам вводит в заблуждение, потому что shared_buffers, исполняемый файл и унаследованные от postmaster страницы считаются в каждом. Поэтому память считается по Pss из smaps_rollup (общая страница делится между всеми, кто её держит). Скрипт mem.sh внутри контейнера PostgreSQL суммирует Rss и Pss по клиентским backend:

for pid in $(pgrep -f 'postgres: postgres postgres'); do
  awk -v p=$pid '/^Rss:/{r=$2}/^Pss:/{s=$2}/^Pss_Anon:/{a=$2}/^Pss_Shmem:/{h=$2} END{print p, r, s, a, h}' /proc/$pid/smaps_rollup
done | awk '{n++; r+=$2; s+=$3; a+=$4; h+=$5} END{printf "backends=%d rss_sum=%.0f MB pss_sum=%.0f MB pss_anon_sum=%.0f MB pss_shmem_sum=%.0f MB avg_pss=%.2f MB\n", n, r/1024, s/1024, a/1024, h/1024, s/1024/n}'

Соединения держит pgbench -c N -f idle.sql: \set aid random(1, 1000000), SELECT abalance FROM pgbench_accounts WHERE aid = :aid; и \sleep 40s. Запускать mem.sh нужно от пользователя postgres (docker exec -u postgres), иначе smaps_rollup чужих процессов недоступен. Замер при 500 соединениях под лимитом 2 ГБ и при 1 000 после docker update --memory 4g:

backends=500 rss_sum=9855 MB pss_sum=758 MB pss_anon_sum=714 MB pss_shmem_sum=36 MB avg_pss=1.52 MB
backends=1000 rss_sum=20158 MB pss_sum=1491 MB pss_anon_sum=1427 MB pss_shmem_sum=56 MB avg_pss=1.49 MB
anon 1505505280
pagetables 228085760

Последние две строки — anon и pagetables из memory.stat контейнера при 1 000 соединений. Rss в сумме даёт 20 158 МБ на сервере с 4 ГБ, примерно в 13,5 раза больше Pss (МБ здесь — мебибайты). Реальная цена соединения — около 1,5 МБ Pss и ещё 0,2 МБ таблиц страниц (218 МБ на 1 000; о росте VmPTE при касании shared_buffers — опыт про page tables у 300 backend). При 500 соединениях под лимитом 2 ГБ memory.current контейнера вырос с 226 до 1 101 МБ: 1,75 МБ на соединение. На 1 000 соединениях anon и pagetables дают 1 653 МБ, а shared_buffers добавляет ещё 256 МБ: до лимита в 2 048 МБ остаётся около 140 МБ ещё до первого запроса.

Опыт 2: 1 000 клиентов напрямую и через PgBouncer

Нагрузка запускается в клиентском контейнере; -S — select-only, без него tpcb-like. Прогон на 500 клиентах:

docker exec -e PGPASSWORD=pg dba-connections-and-pgbouncer-cli pgbench -h $P -U postgres -c 500 -j 8 -T 20 -S postgres
docker exec -e PGPASSWORD=pg dba-connections-and-pgbouncer-cli pgbench -h dba-connections-and-pgbouncer-pgb -p 6432 -U postgres -c 500 -j 8 -T 20 -S postgres

number of clients: 500
latency average = 7.425 ms
tps = 67344.074117 (without initial connection time)
number of clients: 500
latency average = 4.946 ms
tps = 101092.328601 (without initial connection time)

Первый блок — прямое подключение к PostgreSQL, второй — к порту 6432. На 1 000 клиентов напрямую под лимитом 2 ГБ ответом стала смерть контейнера:

FATAL:  terminating connection due to unexpected postmaster exit
pgbench: error: client 653 aborted in command 1 (SQL) of script 0; perhaps the backend died while processing
oom=true exit=137

Последняя строка — docker inspect -f 'oom={{.State.OOMKilled}} exit={{.State.ExitCode}}'. OOM-killer убил postmaster, он же PID 1 контейнера; через PgBouncer тот же контейнер с 2 ГБ 1 000 клиентов выдержал. Чтобы сравнить скорость, прямой прогон на 1 000 клиентов выполнен после docker update --memory 4g --memory-swap 4g. Итог (одна серия на общей машине, повторов не было, поэтому разницу в проценты не пересчитывай):

Без пула select-only на четырёх ядрах теряет пропускную способность с ростом числа клиентов: 142 393 TPS на 20 клиентах, 123 343 на 100, 57 957 на 1 000. PgBouncer удерживает 89 тысяч, а SHOW POOLS во время прогона на 1 000 клиентов показывает, как тысяча клиентов делит двадцать соединений (показаны не все столбцы):

database              | postgres
cl_active             | 53
cl_waiting            | 947
sv_active             | 20
maxwait_us            | 10780
pool_mode             | transaction

На tpcb-like пулер не ускорил: при 200 клиентах он медленнее (2 960 против 3 793 TPS), при 1 000 клиентах скорости равны (3 031 и 2 841 TPS). Причину не проверяли (ожидания и блокировки не снимались); все транзакции обновляют одну из десяти строк pgbench_branches, и число процессов тут вряд ли главное. Для select-only во время прогона на 200 клиентах через PgBouncer docker stats показал cpu=66.73% у PgBouncer и cpu=314.88% у PostgreSQL (одна выборка): узким местом был PostgreSQL.

Возврат соединения в пул — этот код PgBouncer 1.26.1 после ReadyForQuery:

					if (server->link->copy_mode)
						break;
				}

				/* retval does not matter here */
				release_server(server);

Код — server.c, строки 908–913: если нет ожидаемых ответов (проверка выше) и клиент не посреди COPY, сервер уходит в пул. Условие стоит под connection_pool_mode(server) != POOL_SESSION (строка 888): в session-режиме этот путь не работает.

Снимок: цена, которой нет

Раз каждый backend виден в ProcArray, GetSnapshotData обходит их все:

		for (int pgxactoff = 0; pgxactoff < numProcs; pgxactoff++)
		{
			/* Fetch xid just once - see GetNewTransactionId */
			TransactionId xid = UINT32_ACCESS_ONCE(other_xids[pgxactoff]);
			uint8		statusFlags;

			Assert(allProcs[arrayP->pgprocnos[pgxactoff]].pgxactoff == pgxactoff);

			/*
			 * If the transaction has no XID assigned, we can skip it; it
			 * won't have sub-XIDs either.
			 */
			if (likely(xid == InvalidTransactionId))
				continue;

Код — procarray.c, строки 2273–2286. Цикл идёт по плотному массиву xids, простаивающий backend без xid пропускается одной проверкой, а перед циклом GetSnapshotDataReuse (строка 2233) возвращает прежний снимок, если никто не завершил транзакцию с xid. Проверка (в каждом прогоне к простаивающим добавлены 20 серверных соединений пула PgBouncer от прошлых опытов): один клиент pgbench -S -c 1, четыре прогона по 8 секунд при 0, 10 и 1 000 простаивающих соединениях, параллельно два писателя -N -R 2000, чтобы повторное использование снимка не срабатывало. Средняя задержка запроса:

0 простаивающих:      0.024 0.024 0.024 0.024 мс
10 простаивающих:     0.023 0.023 0.023 0.023 мс
1000 простаивающих:   0.023 0.024 0.022 0.023 мс
1000 открытых xid, xip в снимке: 1000
                      0.026 0.026 0.024 0.025 мс

Эффект не воспроизвёлся. 1 000 простаивающих соединений не меняют время запроса (0,022–0,024 мс), а 1 000 открытых транзакций с xid, которые попали в xip снимка, дают +0,001–0,002 мс на границе шума. Падение TPS из опыта 2 на 500 и 1 000 клиентах нельзя целиком отнести на цену снимка: на четырёх ядрах там и очередь на процессор, и переключения контекста.

Опыт 3: что ломается в transaction mode

База one с pool_size=1 заставляет двух клиентов делить одно серверное соединение. Клиент A запускает psql -f, а клиента B выполняет \!-командой изнутри скрипта. Сначала SET, потом advisory lock:

== SET напрямую
A видит: A
B видит: (пусто)
A видит: A
== SET через PgBouncer
A видит: A
B видит: A
A видит: B
== advisory lock напрямую
A держит advisory-блокировку 42
B: pg_try_advisory_lock(42) = false
A: pg_advisory_unlock(42) = true
после unlock в A: advisory-блокировок в pg_locks = 0
== advisory lock через PgBouncer
A держит advisory-блокировку 42
B: pg_try_advisory_lock(42) = true
A: pg_advisory_unlock(42) = true
после unlock в A: advisory-блокировок в pg_locks = 1

Обычный SET остался на серверном соединении: B прочитал значение A, а A потом увидел значение B. Это тот случай, из-за которого в мультиарендных схемах пишут SET LOCAL. Сброс сессии не помогает, потому что server_reset_query по умолчанию равен DISCARD ALL, но server_reset_query_always равен 0, и запрос выполняется только в session-режиме (SHOW CONFIG). С advisory lock хуже: PgBouncer отдал B то же серверное соединение, и блокировка 42, которую уже держал A, выдалась снова, потому что владелец у неё один — процесс backend, а документация для уже державшей блокировку сессии гарантирует успех повторного запроса. После unlock в A блокировка осталась занятой B и пережила обоих клиентов: после выхода обоих клиентов: 1 в pg_locks, пока соединение не закрыли (KILL one). Вместо pg_advisory_lock в таком режиме нужен pg_advisory_xact_lock.

LISTEN ломается так же: подписка живёт на серверном соединении.

A: LISTEN jobs выполнен
C: pg_listening_channels() = jobs
listening: jobs

Первая строка — клиент A после LISTEN jobs. Вторая — другой клиент C, который ничего не подписывал, но видит канал jobs. Третья — новый клиент после выхода A. Подписчик, ждущий уведомления, должен подключаться к PostgreSQL напрямую.

Prepared statements на уровне протокола проверены на pgbench -M prepared -S -c 50 -T 10 через PgBouncer. При max_prepared_statements = 0:

     47 prepared statement "P_0" already exists
     50 prepared statement "P_0" does not exist
      1 Run was aborted

При max_prepared_statements = 200 (значение по умолчанию в 1.26.1, SHOW CONFIG; поддержка появилась в 1.21.0) прогон прошёл без ошибок: 100 719 TPS, 0,496 мс против 233 180 TPS, 0,214 мс при прямом подключении. SHOW STATS показывает, как пулер переписывает имена:

total_client_parse_count      | 50
total_server_parse_count      | 20
total_bind_count              | 1006845

50 клиентов отправили Parse по разу, пулер разобрал запрос на 20 серверных соединениях по разу и обслужил миллион Bind. Накладные расходы остались: прямое подключение быстрее в 2,3 раза.

Odyssey решает ту же задачу как многопоточный пулер (README); в опыте его нет.

Что из этого следует

• Лимит max_connections считай по памяти: около 1,5 МБ Pss и 0,2 МБ таблиц страниц на соединение плюс рабочая память запроса. На 1 000 соединений при 2 ГБ контейнер уже не выживает. Алерт на долю max_connections описан в статье про мониторинг.
• Пул в transaction нужен, когда клиентов намного больше, чем ядер. Он не поможет против блокировок строк (tpcb-like не ускорился) и добавляет задержку (2 960 против 3 793 TPS на 200 клиентах).
• Всё, что живёт в сессии, выноси в транзакцию: SET LOCAL, pg_advisory_xact_lock. LISTEN и долгие сессионные блокировки — в отдельное прямое соединение.
• Prepared statements работают при max_prepared_statements > 0; при нуле клиентская библиотека с протокольным prepare упадёт ошибками, как в опыте.

Чек-лист

1. SHOW POOLS: растущий cl_waiting при sv_active, равном default_pool_size, значит, пул мал или транзакции долгие.
2. Память считай по Pss из smaps_rollup, а не по сумме RSS.
3. Перед включением transaction найди в приложении SET без LOCAL, pg_advisory_lock, LISTEN и протокольные prepared statements.
4. Проверь SHOW CONFIG: max_prepared_statements и server_reset_query_always.
5. Убери стенд: docker rm -f -v dba-connections-and-pgbouncer-pg dba-connections-and-pgbouncer-pgb dba-connections-and-pgbouncer-cli; docker network rm dba-connections-and-pgbouncer-net; docker rmi dba-connections-and-pgbouncer-img.