Пулы соединений и масштабирование чтения

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

Официант на каждый столик

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

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

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

Сколько подключений выдержит сервер

Число подключений ограничено настройкой max_connections:

SELECT name, setting
FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections', 'work_mem')
ORDER BY name;

              name              | setting
--------------------------------+---------
 max_connections                | 100
 superuser_reserved_connections | 3
 work_mem                       | 4096
(3 rows)

По умолчанию сто подключений, из них три приберегаются для суперпользователя, чтобы администратор мог зайти даже на переполненный сервер. Когда места кончаются, новый клиент получает отказ со словами sorry, too many clients already.

Почему бы не поставить max_connections = 5000? Потому что каждое подключение стоит денег:

• Память. Каждый процесс занимает мегабайты памяти сам по себе. А каждая сортировка или хеш в его запросе может взять ещё work_mem, у нас это 4096 kB, то есть 4 МБ. Тысяча подключений с тяжёлыми запросами съедят память сервера.
• Процессор. Ядер на сервере десятки, а не тысячи. Если одновременно выполняются тысяча запросов, они не ускоряются, а толкаются: ядро переключается между ними, и все идут медленнее.
• Блокировки и уборка. Чем больше одновременных транзакций, тем больше они ждут друг друга, как в уроке про блокировки.

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

SELECT pid, state, backend_type FROM pg_stat_activity;

В песочнице подключение одно, твоё:

 pid | state  |    backend_type
-----+--------+--------------------
  42 | active | standalone backend
(1 row)

На живом сервере здесь строка на каждое подключение, и у большинства в колонке state будет idle: подключение открыто, но запроса нет. Это и есть официанты, которые стоят у столиков без дела.

Пул в приложении

Первый уровень пула живёт в самом приложении. Почти любая библиотека для работы с базой умеет держать пул. В Node.js с библиотекой pg это выглядит так:

import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

const { rows } = await pool.query('SELECT title FROM books WHERE id = $1', [7]);

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

Но у пула в приложении есть предел. Он один на копию сервиса. Двадцать копий по десять подключений это уже двести, а max_connections всего сто. Добавил копий, и база отказала.