Как я могу предотвратить агрессивную автоматическую очистку от блокировки таблицы только для вставки в Postgres 13?
Я запускаю экземпляр Postgres 13 с таблицей только для вставки. Когда приложение выполняет запись в эту таблицу, оно вручную получает блокировку «SHARE ROW EXCLUSIVE» (как форму управления параллелизмом, гарантирующую отсутствие вставок в таблицу между чтением и записью клиента в таблицу). В большинстве случаев это нормально, но когда Postgres запускает агрессивную автоочистку, чтобы предотвратить зацикливание XID, эта автоочистка блокирует запись в эту таблицу. Автоочистка может занять несколько минут, что является неприемлемо долгим временем для невозможности записи в эту таблицу.
Основываясь на этом сообщении в блоге , я подумал, что использование Postgres 13 предотвратит эту проблему:
В отличие от других запусков автоочистки, автоочистка с защитой от цикла не прекращает работу, когда блокирует параллельную транзакцию. Это заблокирует даже короткие операции, требующие блокировки ACCESS EXCLUSIVE (например, операторы DDL в таблице). Такая заблокированная операция заблокирует весь остальной доступ к таблице, и обработка остановится.
Начиная с PostgreSQL v13, настройки по умолчанию уже должны защитить вас от этой проблемы.
Моя текущая теория заключается в том, что обычный процесс автоочистки на самом деле не просматривает большинство страниц — из документации :
[VACUUM] будет пропускать страницы, на которых нет версий мертвых строк, даже если на этих страницах все еще могут быть версии строк со старыми значениями XID.
Поскольку это таблица только для вставки, предположительно большинство/все страницы не имеют версий мертвых строк, поэтому процесс автоочистки будет пропущен. Поскольку автоочистка никогда не посещает эти страницы, строки никогда не замораживаются, поэтому в конечном итоге приходится выполнять агрессивную автоочистку, чтобы предотвратить перенос.
Я хотел бы запретить автоочистке блокировать запись в эту таблицу.
Один из подходов — установить для autovacuum_freeze_max_age очень низкое значение для этой таблицы, чтобы агрессивная автоочистка запускалась чаще (и должна блокироваться на более короткое время, поскольку с каждым разом должно выполняться меньше работы). Но это все равно заблокирует на некоторое время, чего я бы хотел избежать, если это возможно.
Я надеюсь, что есть способ вообще избежать агрессивной автоматической очистки этой таблицы. Я не решаюсь использовать что-то вроде ручного пылесоса с ОТКЛЮЧЕНИЕМ ПРОПУСКА СТРАНИЦ, поскольку оно предупреждает, что:
предназначен для использования только в случае... возникновения аппаратной или программной проблемы, приводящей к повреждению базы данных.
Мне кажется странным, что посещение всех страниц сопряжено с предотвращением переноса XID и «агрессивной» настройкой, поэтому, пожалуйста, дайте мне знать, если я неправильно понимаю, как здесь работает система! Я надеялся, что есть способ сделать более «тщательную» неблокирующую автоочистку, которая заморозит все эти строки.
Как я могу избежать блокировки записи в эту таблицу?
Почему автоочистка так занята? Сколько транзакций в секунду/час/день вы обрабатываете? А не могли бы вы поделиться своей текущей конфигурацией? У нас есть база данных, которая выполняет почти 400 миллионов транзакций в день, и у нас нет таких проблем.
Спасибо @jjanes — я отредактировал вопрос, чтобы подробнее описать блокировку SHARE ROW EXCLUSIVE. Цель состоит в том, чтобы гарантировать отсутствие каких-либо операций записи в таблицу между моментом чтения данных клиентом и их записью.
@FrankHeikens, это хороший вопрос, я думаю, проблема в том, что не имеет значения, насколько загружена база данных. Поскольку автоочистка не будет просматривать большинство страниц этой таблицы, она может работать нормально, и мы все равно будем сталкиваться с агрессивными автоочистками, чтобы предотвратить перенос. Эта база данных выполняет 400-500 миллионов транзакций в день.
Это не проблема программирования. Публикация на дочернем сайте dba.stackexchange.com
Ответы 1
Я думаю, что сообщение в блоге, которое вы цитируете, действительно искажает ситуацию в своем «сценарии использования 1». Хотя новые параметры в версии 13 усиливают очистку таблиц, предназначенных только для вставки, эта повышенная очистка не сильно смягчает эффект предотвращения перетекания. На самом деле это полезно только для карты видимости и битов подсказок. Я думаю, что основной мотивацией для введения этой функции было влияние на карту видимости, а не на защиту от зацикливания, хотя, насколько я помню, многие люди в списке рассылки разработчиков в то время тоже неправильно поняли это.
Начиная с версии 13, был достигнут некоторый прогресс в улучшении проблем предотвращения обхода, но, насколько мне известно, они все еще не так хороши, как должны быть на самом деле.
Если все ваши процессы надежно взаимодействуют друг с другом, вы можете заменить блокировку «SHARE ROW EXCLUSIVE» рекомендательной блокировкой, которая имеет тот же эффект в пользовательском пространстве, но без случайного включения вакуума в схему блокировки. Но я предполагаю, что вы выбрали «SHARE ROW EXCLUSIVE», потому что база данных обеспечивает это сама, а это означает, что только один процесс, берущий блокировку, должен знать о своих намерениях. это сделает рекомендательную блокировку ненадежной, поскольку каждый фрагмент вашего пользовательского кода необходимо будет проверять, чтобы убедиться, что он был изменен для его правильного использования.
Агрессивная очистка должна запускаться только один раз за вакуум_freeze_table_age минус вакуум_freeze_min_age, что является большим числом и поэтому должно занять много времени. Если у вас есть окно обслуживания (ночью, выходные), в течение которого вы можете запускать VACUUM FREEZE вручную на столе хотя бы так часто, возможно, с выключенным INDEX_CLEANUP и установленным PARALLEL, вам следует избегать любой незапланированной агрессивной уборки за пределами окна обслуживания.
Я, вероятно, преувеличил, насколько эффективен автоочистка, управляемая вставкой, для предотвращения огромных запусков автоочистки с предотвращением цикла. Но, по крайней мере, он сработает раньше, когда будет достигнута опция «vacuum_freeze_table_age». Тем не менее, ваш ответ правильный.
Спасибо за ответ здесь @jjanes — мы действительно стреляем себе в ногу, вручную взяв замок, я отредактировал вопрос, чтобы прояснить это. Есть идеи, как избежать этой проблемы, учитывая, что мы получаем эту агрессивную блокировку?
@Паркер, посмотри изменения в моем ответе.
Почему приложение приобретает SHARE ROW EXCLUSIVE? Это не та блокировка, которую можно было бы ожидать от приложения. Только «Получено с помощью CREATE TRIGGER и некоторых форм ALTER TABLE», которые принимают это автоматически.