List Issues Suggestions1 Discussions Insights

Медленное API: что чинить первым и чего делать не надо

Запросы идут по 4 секунды. Разбор реального плана оптимизации: что в нём было верно, где перепутан порядок и почему одна из «правок» молча ничего не делала.

postgresql performance cache redis devops

v2 0 stars 0 forks 0 watchers 1 branch 0 runs Public

Исправлены сбитые символы ёv22 weeks ago2

Исходная

Реальная ситуация с боевого сервера:

запросы к базе — от 3 до 8 секунд;
PostgreSQL занимает 20 МБ из 3.8 ГБ доступной памяти;
shared_buffers — 128 МБ, то есть значение по умолчанию;
кэш есть, но с коротким временем жизни;
пула подключений нет.

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

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

Сначала измерить

Найти, какой именно запрос медленный

Перед любой правкой надо знать конкретный запрос. Включи лог медленных запросов:

sql

1-- логировать всё, что дольше секунды

2ALTER SYSTEM SET log_min_duration_statement = 1000;

3SELECT pg_reload_conf();

Либо ставь расширение pg_stat_statements и смотри топ по суммарному времени:

sql

1SELECT calls, mean_exec_time, total_exec_time, query

2FROM pg_stat_statements

3ORDER BY total_exec_time DESC

4LIMIT 10;

Колонка total_exec_time важнее, чем mean_exec_time: запрос на 50 мс, выполняемый десять тысяч раз, съедает больше, чем один на четыре секунды.

Why: Оптимизация без измерения — это угадывание. За общим «база медленная» обычно стоит один-два конкретных запроса, а остальное работает нормально.

$SELECT calls, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Check

Известен конкретный текст медленного запроса
Ты смотрел на суммарное время, а не только на среднее

pg_stat_statements — статистика по запросам Чеклист по медленным запросам

Посмотреть план выполнения

sql

1EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Что искать в выводе:

Строка в плане	Что значит
`Seq Scan` по большой таблице	нет нужного индекса
`rows=1` в плане, `actual rows=50000`	статистика устарела, нужен `ANALYZE`
тот же узел с `loops=200`	классическая проблема N+1
`Sort Method: external merge Disk`	не хватает `work_mem`

Вывод удобно разбирать в визуализаторе — ссылка ниже.

Why: Этого шага в исходном плане не было вообще — и это главная его слабость. Запрос на четыре секунды при небольшом объёме данных почти никогда не лечится памятью. Обычно это отсутствующий индекс или N+1 — и тогда одна строка даёт выигрыш в сотни раз, а не на 20%.

$EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Check

Ты знаешь, есть ли Seq Scan по большой таблице
Проверил, не N+1 ли это

Как читать EXPLAIN Визуализатор планов запросов Индексы в PostgreSQL

Почему порядок важен

Исходный план ранжировал работы по соотношению эффекта к затраченному времени — это правильный подход:

Шаг	Время	Заявленный эффект
Поднять время жизни кэша	5 мин	минус 99% нагрузки на базу
Настройки PostgreSQL	15 мин	20–30%
Пул подключений	30 мин	10–20%

Но с двумя оговорками.

Первая. «Минус 99% нагрузки» верно только для повторных чтений редко меняющихся данных: настройки сайта, контакты, баннеры. На корзине, остатках товара или личном кабинете такой цифры не будет никогда. Кэш убирает повторные чтения, а не медленный запрос.

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

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

Правки

Настройки PostgreSQL — и главная ловушка

Значения по умолчанию не зависят от объёма памяти машины: shared_buffers всегда 128 МБ, work_mem — 4 МБ. На сервере с 4 ГБ это мало.

Вот как НЕ работает — именно так было написано в исходном плане:

yaml

1environment:

2 POSTGRES_SHARED_BUFFERS: 1GB # такой переменной не существует

3 POSTGRES_WORK_MEM: 16MB # и такой тоже

Официальный образ postgres знает только POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB, POSTGRES_INITDB_ARGS, POSTGRES_HOST_AUTH_METHOD и PGDATA. Всё остальное он просто игнорирует — без единого предупреждения.

Вот как работает:

yaml

1postgres:

2 image: postgres:16-alpine

3 command: >

4 postgres

5 -c shared_buffers=1GB

6 -c effective_cache_size=3GB

7 -c work_mem=16MB

8 -c maintenance_work_mem=256MB

Отправная точка для расчёта: shared_buffers около 25% от памяти, effective_cache_size — 50–75%.

Why: Это самый опасный вид ошибки: правка внесена, контейнер поднялся, ошибок нет — и ничего не изменилось. Задача закрыта, галочка стоит, проблема на месте.

$docker exec -it <контейнер> psql -U postgres -c "SHOW shared_buffers;"

Check

Настройки заданы через command с -c или файлом конфига
SHOW shared_buffers показывает новое значение, а не 128MB

Какие переменные понимает образ postgres Настройки памяти PostgreSQL

Кэш: поднять время жизни и сразу же продумать сброс

Для редко меняющихся данных время жизни в 10 минут действительно мало:

python

1# было везде 600 секунд

2SEO_TTL = 3600 # час

3ANALYTICS_TTL = 7200 # два часа

4CONTACTS_TTL = 1800 # полчаса

Но вместе с этим обязательно сброс при записи:

python

1async def update_contacts(self, data):

2 result = await self.repo.update(data)

3 await self.cache.delete("site_content:contacts") # без этой строки

4 return result # админ будет

5 # в ярости

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

Проверить, что кэш работает:

bash

1docker exec -it <redis> redis-cli

2> KEYS site_content:*

3> TTL site_content:contacts

Why: Кэш — это размен: скорость в обмен на актуальность. Чем больше время жизни, тем больше платишь. Поднять время жизни без сброса при записи — значит перенести жалобы с медленного сайта на «я всё поменял, а ничего не поменялось».

$docker exec -it <redis> redis-cli TTL site_content:contacts

Check

Время жизни поднято только там, где данные редко меняются
Каждый метод записи сбрасывает свой ключ
Проверено вручную: поменял через админку — сразу видно на сайте

Пул подключений — когда он действительно нуженOptional

Каждое подключение к PostgreSQL — это отдельный процесс на сервере базы. Сотня подключений — сотня процессов.

Но сначала проверь, есть ли проблема:

sql

1SELECT count(*), state FROM pg_stat_activity GROUP BY state;

2SHOW max_connections;

Если подключений десяток из ста возможных — пул не нужен, это не узкое место.

Если нужен — есть два уровня:

Пул в самом приложении. У SQLAlchemy он включён по умолчанию — проверь pool_size и max_overflow прежде, чем ставить что-то ещё.
Внешний пул (PgBouncer). Нужен, когда к базе ходят несколько разных приложений или много копий одного.

У PgBouncer есть важное ограничение: в режиме transaction не работают подготовленные выражения и сеансовые настройки. Для asyncpg это значит, что нужно явно отключать кэш выражений, иначе получишь плавающие ошибки под нагрузкой.

Why: Пул — самый дорогой из трёх шагов и самый слабый по эффекту. Ставить его первым — верный способ потратить день и получить новый источник странных ошибок.

$SELECT count(*), state FROM pg_stat_activity GROUP BY state;

Check

Проверено, что подключения действительно заканчиваются
Сначала посмотрели пул самого приложения

PgBouncer — документация

Правильный порядок целиком

Измерить. Какой запрос, сколько раз, сколько времени суммарно.
EXPLAIN ANALYZE. Индекс, N+1 или устаревшая статистика — самый дешёвый и самый большой выигрыш.
Настройки базы. Через command: postgres -c ..., с проверкой через SHOW.
Кэш. Поверх уже быстрого запроса, со сбросом при записи.
Пул подключений. Только если измерения показали, что он нужен.

После каждого шага — измерить снова. Иначе через неделю ты не сможешь сказать, какая из пяти правок сработала, а какая просто добавила сложности.

И отдельно про цифры в планах. Когда видишь «улучшение на 99.9%» — спроси, на каком сценарии. Обычно это сто одинаковых запросов к одному эндпоинту подряд — то есть идеальный для кэша и не очень похожий на живой трафик.

Запрос идёт 4 секунды на таблице в 50 тысяч строк. С чего начать?

В compose добавили POSTGRES_SHARED_BUFFERS=1GB. Что произойдёт?

Подняли время жизни кэша с 10 минут до двух часов. Что обязательно сделать вместе с этим?