Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Bulk insert (COPY FROM...) большого кол-ва строк блокирует последующие update и выборки  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 141
Добрый день.

Столкнулись с странной проблемой и похоже без помощи вселенского разума - не справиться.

Делаем так:
- с помощью COPY FROM (отдельной соединение + транзакция) грузим в таблицу temp_table в базе (она используется только для пре-обработки данных) много строк (200 тыс....2 млн. и более)
- далее вызываем хранимку, которая эти загруженные данные проверяет, меняя значения в некоторых строках (по факту проставляется поле is_duplicate = true для строк, которые нам не нужны)
- после обновления части строк эта же хранимка делает insert into ... select from temp_table тех строк, которые нам нужны, в уже рабочие таблицы
- все строки из temp_table удаляются

Это упрощенное описание. В реальности в temp_table есть batch_id и прочее, т.к. загрузки могут идти параллельно и независимо, и обрабатываются хранимкой только строки для своего batch_id.

Так вот это всё работало прекрасно, очень быстро и красиво до момента, когда начали загружать именно огромные пачки строк.
В итоге хранимка останавливается (на неопределенное время, дождаться не смогли ни разу) на выполнении delete from temp_table where... тех строк, которые мы в ней же обновили. Либо на insert into ... select ... from temp_table. Причем всё это лочится ТОЛЬКО если хранимка изменила хоть одну строку в обрабатываемом далее наборе. Если изменений не было - то всё прекрасно проходит.

Перепробовали уже кучу всего и оказалось, если между bulk insert (который COPY FROM) и вызовом хранимки для обработки - поставить задержку в 1 минуту, то проблемы нет. Всё проходит как положено. Если поставить 10 секунд - лочится. Если грузим не 200-1000 тыс.строк, а например всего 10 тыс. - то и без задержек между bulk insert и хранимкой всё пролетает на ура.

В чем причина? Куда копать? Как корректно дождаться момента, когда вставленное bulk insert можно уже обрабатывать полноценно?

Чувствую что не успевает что-то где-то после bulk insert "долететь" и в результате идущие сразу обновления добавленных строк - что-то могут залочить, но - блокировки смотрели, нет ничего. Автовакуум да, срабатывает... но не понимаю чем это может тут мешать.

PostgreSQL v13.
3 фев 21, 10:20    [22273660]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert (COPY FROM...) большого кол-ва строк блокирует последующие update и выборки  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Alexey Trizno,

автор
если между bulk insert (который COPY FROM) и вызовом хранимки для обработки - поставить задержку в 1 минуту, то проблемы нет.


потому что после bulk insert подобного надо перед тем как начинать работать с таблицей выполнить analyze ее
потому что иначе не будет статистики даже сколько там строк у базы (не говоря о куче прочей инфы) и планы будут кривые.

1 минута помогает потому что за это время успевает autoanalyze сработать (а он может и не успеть просто обычно успевает).

autovacuum как раз вам там помогает просто у него нет времени отработать если сразу начать обработку после заливки и не сделать analyze руками.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
3 фев 21, 11:05    [22273692]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert (COPY FROM...) большого кол-ва строк блокирует последующие update и выборки  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 141
Maxim Boguk,

Спасибо. Да, помог вызов ANALYZE для этих временных таблиц перед обработкой загруженных данных.

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

Сразу до идеи с ANALYZE не дошла мысль из-за того, что без него всё работает... если вставленные данные в эти temp таблицы as is переносятся в постоянные. А вот если там что-то сначала попробовать обновить - тогда всё останавливается на последующей выборке.
3 фев 21, 16:03    [22274065]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert (COPY FROM...) большого кол-ва строк блокирует последующие update и выборки  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Alexey Trizno
Maxim Boguk,

Спасибо. Да, помог вызов ANALYZE для этих временных таблиц перед обработкой загруженных данных.

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

Сразу до идеи с ANALYZE не дошла мысль из-за того, что без него всё работает... если вставленные данные в эти temp таблицы as is переносятся в постоянные. А вот если там что-то сначала попробовать обновить - тогда всё останавливается на последующей выборке.


временные таблицы тем более analyze делать надо ОБЯЗАТЕЛЬНО (а если вы их в процессе обсчета активно меняете так еще и периодически ему vacuum analyze делать по мере работы) потому что autovacuum их НЕ ОБРАБАТЫВАЕТ.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
3 фев 21, 19:10    [22274279]     Ответить | Цитировать Сообщить модератору
 Re: Bulk insert (COPY FROM...) большого кол-ва строк блокирует последующие update и выборки  [new]
Alexey Trizno
Member

Откуда: Санкт-Петербург
Сообщений: 141
Maxim Boguk,

Да, ANALYZE для временных сделан, а vacuum analyze вероятно смысла нет, т.к. обработка - это 1-2 update, а дальше уже insert into ... select ... from временная таблица, и всё - соединение закрывается, таблица удаляется.

Спасибо!
3 фев 21, 20:32    [22274332]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить