Ребята, привет. Такой вопрос: - Есть таблица размером в 1Tb в

таблице есть поле url TEXT ( PRIMARY KEY ).
В эту таблицу регулярно идет порядка 1k RPS. Запрос один и тот же INSERT INTO (url) VALUES (...) ON CONFLICT DO UPDATE
Периодически вне зависимости от кол-ва RPS запросы начинают долго висеть на LWLock, BufferContent.
Как то можно определить что вызывает эти блокировки?

1) "Долго" -- это сколько? Как вы это определили? Какие средства использовались? 2) Сколько одновременно при этом запросов? 3) Что ещё делает в тот момент база (что в pg_stat_activity), кроме этих запросов? 4) Что делает остальная система? Какое потребление iops/CPU?

1) Минуты. Заглянул в pg_stats_activity. И наблюдаю со стороны приложения по метрикам в месте SQL запроса. 2) от 1k до 5k в секунду 3) Еще делает SELECT из этой же таблицы, но эти запросы очень быстро проходят. Собственно в pg_stat_activity висят только эти INSERT в состоянии LWLock, BufferContent 4) Ничего, на сервере больше ничего нету. На сервер стучится только 1 процесс под одним и тем же пользователем. И достоверно известно что именно этот процесс своими запросами вызывает блокировки

1) Минуты выполняется один запрос? Вы уверены? 2) Это вообще не ответ на мой вопрос. 3) Этого не можэт быть. У постгреса всегда есть несколько служэбных процэссов, которые вы не сможэт выключить. 4) Это тожэ не ответ на мой вопрос.

1) now() - pg_stat_activity.query_start AS duration, говорит минуты. 2) Вы спросили сколько одновременно запросов. Я ответил от 1 до 5 тысяч запросов в секунду 3) Я не наблюдаю их в pg_stat_activity в моменты когда происходит просадка производительности. Где их посмотреть если в pg_stat_activity их нет? 4) Что имеется ввиду под системой? iops в районе 5k в секунду. при лимите на железе в 10к. CPU не выше 80%.

1) Неслабо. Вижу два варианта -- или это пришёл checkpoint, и выполняется очень долго. В логе в этом случае должно быть сообщение о начале checkpoint (по-моему, оно включено по умолчанию). Или у запроса сломался план. Второе несложно посмотреть, включив лог планов всех запросов, которые выполняются большэ, допустим, 20 секунд. 2) Количество запросов в секунду -- ничего не говорит о том, сколько одновременно запросов выполняются. 3) Хм. Это что-то очень сломанное, если это так. Но, скорее всего, это неверно. Можэт кинуть pg_stat_activity, например, текущий, например, на pastebin. В 14, например, там 'autovacuum launcher', 'logical replication launcher', 'background writer', 'checkpointer', 'walwriter'

А на каких хардах это крутится всё у тебя?

4) iops явно приблизились к своему лимиту. Так что вариант, что bgwriter неуспевает, и запускается checkpointer -- начинает обретать некоторые косвенные подтверждения.

Спасибо за подсказку с чекпоинтом. Проверю. И план тоже гляну. 2) Блин, да. В pg_stats_activity в районе 100-300 висят на этой блокировке. Выходит они одновременно висят? 3) Сброшу как снова случится. Происходит это иногда. Периодичность плавает. Помогает перезагрузка сервера. После нее проблема пропадает 4) Пробовали раздувать БД до больших значений. Случается даже когда по CPU 50% запаса а по IOPS все 70% запаса. Ну важная (не знаю деталь). Речь о CloudSQL for PostgresSQL в Google Cloud

> Выходит они одновременно висят? Наверняка. >3) Сброшу как снова случится. Я предлагал посмотреть ужэ сейчас -- поскольку там в любом случае будет несколько служэбных процэссов, которые вы почему-то пропустили. >Ну важная (не знаю деталь). Речь о CloudSQL for PostgresSQL в Google Cloud Важная. В таком случае имеет прямой смысл задавать вопрос "почему у меня примитивный запрос висит две минуты" в поддержку Google. Во-первых, они берут деньги за то, чтобы таких факапов не было. Это и есть часть работы DBA, притом конкретно та, которую можно возложыть на массовый сервис. Сообщение о проблемах с производительностью базовой функцыональности. Во-вторых, эти внутри ужэ не совсем Postgres, и у вас к тому жэ нет прав его детально ковырять.

Кстати, 300 процэссов -- это в любом случае некоторый перебор. И это только "на блокировке висят", сколько у вас там одновременно вы так и не признались.

Если убрать лимит вообще из запроса выше, прямо сейчас 461 строка в pg_stats_activity

К тому жэ гугл, вроде, имеет какие-то лимиты по суммарным IO -- после которых рубит iops до каких-то базовых значений. Возможно, вы упёрлись во что-то такое. (Мониторинг и средства поддержания информированности у них, кстати, отвратительные совершэнно).

Многовато, лучшэ на нагружэнном сервере большэ 150-200 не иметь.

Это не зависит от кол-ва ресурсов на сервере? Т.е. почему именно 150-200 ? Ну можно наверное этого добиться снизив кол-во max_connections. Но это отразится на клиентах сервиса который данные пишет/читает. Нагрузку то я не могу снизить. Могу скейлить вертикально инстанс. Но это не помогает. Пробовал скейлить до 16vCPU 100GB RAM (Там 20000 IOPS доступно, но выше 8k IOPS не поднимается). Все равно затыкается на этих блокировках. При чем если смотреть метрики. то там запаса достаточно и по CPU и по IOPS. А запросы все равно иногда затыкаются.

>Это не зависит от кол-ва ресурсов на сервере? Довольно умеренно. Обычно, на средне-приличном сервере доступ к базе в памяти имеет пик rps в районе 100-200 одновременных запросов. Основное узкое место при это ужэ -- да, на блокировках важных ресурсов в памяти. Понятно, что этот лимит насыщения зависит от количества ядер -- но, на самом деле, не очень критично. Далее, если база активно работает с диском, то количество параллельных клиентов в точке максимума производительности несколько увеличится -- но, на самом деле, обычно диску не так много запросов надо для насыщения. Так что тут или измерять, где у вас реальный максимум rps -- или положыться на число 150 как много раз измеренное и плюс-минус справделивое.

Anton Kucherov
Вот такая картина сейчас. Хотя сейчас несмотря на ...

с fsync игрались? каждый insert - это маленькая транзакция же по сути...

>Нагрузку то я не могу снизить. 1) Можэте. Количество воркеров в том жэ nginx ограничить. 2) Или можно пулер перед сервером поставить. 3) И, главное -- от увеличения max_connections после некоторого количества -- максимальный rps перестанет расти. Так что нагрузку всё равно вы таким способом не разгребёте.

CloudSQL Альберт ))

ОК, понял...

Вряд ли гугль это разрешает. Впрочем, при таких нагрузках это ужэ неактуально.

1) Ну технически да, но клиенты недовольны будут. У нас наоборот запрос: 1) Починить просадки. 2) Сделать x2 возможный RPS к сервису который и пишет/читает собственно в БД. 2) ну там на стороне сервиса и так пулер. Там как раз пулл на 350 соединений 3) Да, это я замечал. Короче надо судя по всему идти на что то другое и/или менять схему данных. 😕

1) Я вам ещё раз пытаюсь объяснить -- если у сервера будет меньшэ rps, то клиенты не станут от этого довольнее.

Кстати, 10k iops на 1k rps -- это нормально. Не очень много, без особого запаса... Но нормально. На 5k rps -- это вы пролетаете.

>Короче надо судя по всему идти на что то другое Взять, например, дедик, а не непонятночто от гугля. Ну, или яндэкс/амазон хотя бы, если принцыпиально в managed.

🙂 Так я же не спорю. Я согласен. Не станут. В этом то моя проблема и есть. Мне надо больше RPS.

Кстати, а какая версия постгрес? Потому, что если 12 -- то опускать количество max_connections можэт помочь прямо вот очень сильно. В 14 -- многое поправили, всё равно лучшэ не превышать, особенно если много обновлений одной записи... Но в общем на 14 я бы роста в разы не ожыдал.

14. Спасибо в общем за советы. Кстати а где вот про эти магические 150-200 в ОЗУ почитать можно?

В 14 наконец-то добавили "деревянный" ORDER в рекурсивных CTE )) Перевел один проект на днях - счастлив!

Примерно любая история борьбы за максимальный rps с графиками с конференцый последней пятилетки.