Анализ производства Postgres UPDATE - медлительность запросов RETURNING

Question

Мы запускаем веб-приложение, в котором в среднем 10 000 активных пользователей, обслуживаемое 6 веб-узлами и поддерживаемое Postgres 9.4.6.

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

Это небольшая таблица, внутренняя реализация последовательности, в основном (унаследованное приложение), отслеживающая уникальные идентификаторы для других таблиц:

CREATE TABLE ids_for_records
(
  tableid integer NOT NULL,
  id bigint NOT NULL,
  CONSTRAINT ids_for_records_pk PRIMARY KEY (tableid)
)
WITH (
  OIDS=FALSE
);

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

UPDATE ids_for_records
SET id = id + <batchsize>
WHERE tableid = <unique-internal-table-id>
RETURNING id;

Мне нужно выяснить, почему производительность этого запроса значительно снизилась за последние дни: в среднем он составляет около 1 секунды, но иногда также занимает 30–60 секунд. Во время высокой нагрузки все узлы выполняют одни и те же запросы параллельно для нескольких соединений.

ОБНОВЛЕНИЕ: запрос информации о блокировках (из pg_lock, pg_class и pg_stat_activity), удерживаемых одним (медленным) запросом, показал, что это точно такой же запрос из других транзакций, которые ожидаются. Таким образом, у нас есть одновременные транзакции, пытающиеся обновить (увеличить значение идентификатора) одну и ту же строку, таким образом, одна активная со всеми необходимыми блокировками блокирует все остальные.

В противном случае база данных исправна, наша операционная команда не обнаружила никаких проблем с хранилищем, памятью или соединениями; Размер другой таблицы недавно достиг 64 ГБ, что может быть связано.

Кто-нибудь знает, что может вызвать такое снижение производительности? Такая же загрузка, как и раньше, но этот запрос на узкое место работает примерно в 5 раз медленнее, чем раньше.

Answer 1

Ниже приведены некоторые догадки, пожалуйста, поправьте меня, где я угадал.

Сам запрос будет быстрым, если не происходит что-то очень странное. Что занимает много времени, так это ожидание блокировки строк.

Блокировки удерживаются на протяжении всей транзакции, поэтому, вероятно, обработка пакета выполняется в той же транзакции, что и UPDATE утверждение, которое блокирует одновременные сеансы.

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

Задача состоит в том, чтобы получить целые партии значений последовательности. Вы можете сделать это безопасным способом, защищая setval с консультативными блокировками, которые могут быть сняты до завершения транзакции. Смотрите мой блог для примера, как это можно сделать.