Как избавиться от блокировок уровня записи и повысить производительность больших таблиц в PostgreSQL?

Question

[Александр]

Есть две ситуации:

1. Таблица с ~20k записей, часть из которых часто (до нескольких раз в секунду) обновляется запросами вида "UPDATE table_1 SET counter = counter + 1 WHERE id = 123". При этом запись может заблокироваться на два часа, и все последующие запросы повиснут в воздухе до разблокировки.
   
2. Таблица с ~2.5M записей, данные добавлются скопом (INSERT SELECT), и потом по одной строке удаляются. Здесь просто производительность ниже плинтуса.
   

Индексы по частоиспользуемым полям добавлены. Как с этими проблемами бороться? Я грешу на autovacuum, по крайней мере во втором случае, но толковых рекомендаций, как настраивать его, не нашел.
Окружение: сервер PostgreSQL 9.5 с 16ГБ оперативки, ssd диском и pgbouncer в качестве пул-менеджера.

Заранее спасибо.

Comments

[Артём Клименко]

эм, у вас случайно не дефолтные настройки БД?

просто "Таблица с ~20k записей, часть из которых часто (до нескольких раз в секунду)" - это ниразу не нагрузка, тем более для указанной вами конфигурации сервера

[Александр]

Артём Клименко: Тюнинг в основном касался размера буферов и репликации, ну и про нагрузку не говорил, в данном пункте проблема в блокировке на уровне строки.

[ingerbird]

1) Я так понял, что запись блокируется каким-то еще запросом, который взаимодействует с другими полями упомянутой таблицы? Может вынести счетчик в другую таблицу, чтобы распараллелить эти процессы?

[Артём Клименко]

А причину блокировки смотрели? pgadmin вполне способен отобразить из -за какого процесса заблокировались другие процессы. Или у вас ситуация, что в одном из потоков реально выполняется нужный запрос, который и должен блокировать запись на два часа, а вы хотите чтоб при этом работали и другие потоки?

[Александр]

ingerbird, Артём Клименко: да, есть еще один запрос, который зануляет счетчик. И нет, оно не должно висеть 2 часа.

[Артём Клименко]

Александр: тогда вопрос, а корректно ли у вас этот запрос завершается?
Сейчас оно выглядит что как будто стартует транзакция, происходят изменения поля, что создаёт блокировку, а вот коммита транзакции не происходит и всё в таком состоянии остаётся

[ingerbird]

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

[Александр]

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

Answer 1

[Александр]

Как оказалось, первая проблема связана с особенностями работы pgbouncer. Суть в том, что pgbouncer не блокирует соединение, в котором есть активная транзакция, для других клиентов, а у нас в одном месте происходит довольно длительная (до 15 минут может висеть) транзакция на совершенно левой группе таблиц. В общем получалось так, что проходила куча update на одну строку из одной таблицы, и часть из них попадала в транзакцию, которая предназначалась для других таблиц, и потом, после комита, сервер еще долго разбирался, в каком порядке это все выполнять. Решение: оборачивать в отдельную транзакцию важные запросы на изменение данных (в нашем случае - обновление счетчика), даже если они ходят поодиночке.
А вторую проблему решили изменением структуры таблицы.

Answer 2

[Сергей]

Как минимум не удалять, а помечать что удалено. Потом удалять скопом.

Comments

[Александр]

Что вы подразумеваете под "помечать"? Если доп. поле, то update генерирует по сути новую физическую запись со всеми вытекающими, а старая как раз таки помечается для удаления. Или я что-то путаю?

[airatikus]

Александр, у нас блокировки "уменьшились" после замены delete на update