Comments 41
Отличная статья! Спасибо, очень познавательно. Больше бы статей про правильную работу с базами данных и их схемами.
Еще очень интересно про репликацию и ее варианты.
Еще очень интересно про репликацию и ее варианты.
Операции, которые изначально быстро работают в PostgreSQL и не требуют длительных блокировок:
…
SET NOT NULL
Эмм. Ну нет.
SET NOT NULL блокировку требует и на время полного seqscan таблицы в один поток. Либо очень грязным хаком напрямую системного каталога.
Уж поверьте человеку, который ради этого вообще впервые полез писать пачт к postgresql. Хотя мой небольшой патч, предоставляющий возможность обойтись штатными средствами уже очевидно не примут.
Хотя дальше пишете про constraint, и вот это именно то, что я в патче предлагал для валидации set not null и таким образом после добавления временного constraint set not null был бы быстрым, а потом спилить лишний constraint. И сообщество вроде как согласно, что да, это лучше чем ничего, но патч висит как есть.
Касательно же add column default, про который много уже где написано как его правильно вносить — уже понемногу теряет актуальность, в postgresql 11 наконец эта штука существует штатно. Зато отдельное спасибо за упоминание очереди блокировок. А то везде в статьях назовут операцию быстрой и безопасной, а потом случайно с какой-нибудь транзакцией пересекаются и лежат, ждут блокировку.
Очень похоже на то, что я писал https://habr.com/post/425063/ + библиотечка https://github.com/tbicr/django-pg-zero-downtime-migrations
Было пару вопросов к библиотеке:
- как поведёт себя
RunSql("UPDATE table SET sql = 'CREATE INDEX' WHERE sql = 'CREATE INDEX CONCURRENTLY'") - обходит ли библиотека описаные проблемы с
SET NOT NULLиRENAME COLUMN - что будет с
CREATE COLUMN SET DEFAULT NOT NULLкогда мы раскатываем миграцию для кластера из нескольких инстансов с django (то есть старый/новый код работает со новой/старой схемой)?
Рискну ответить не полностью разобравшись в вопросе. Первый пункт про возможные проблемы при использовании ключевых слов в строках запросов? — Строки из запросов можно просто вырезать: всё что в одинарных кавычках, а так же все числа не соседствующие с буквами можно заменить на плейсхолдеры, после чего спокойно обрабатывать запрос.
Не уверен насчёт корректности такого метода, — когда-то писал тулзу которая парсит slowlog постгреса, удаляет из запросов все параметры и считает суммарное время исполнения для каждого запроса: идея в том, чтобы найти запросы которые съедают больше всего времени, посмотреть глазами и оптимизровать. И в моём случае это сработало, — по результатам добавил один индекс и нагрузка на сервера упала вдвое… :-) Кстати, может быть есть готовые тулзы?
Не уверен насчёт корректности такого метода, — когда-то писал тулзу которая парсит slowlog постгреса, удаляет из запросов все параметры и считает суммарное время исполнения для каждого запроса: идея в том, чтобы найти запросы которые съедают больше всего времени, посмотреть глазами и оптимизровать. И в моём случае это сработало, — по результатам добавил один индекс и нагрузка на сервера упала вдвое… :-) Кстати, может быть есть готовые тулзы?
удаляет из запросов все параметры и считает суммарное время исполнения для каждого запроса
Есть pg_stat_statements contrib. Давно штатный и не требует логировать запросы. Если парсить лог запросов — то можно пропустить что-нибудь достаточно быстрое, но вызываемое очень часто — а такие вещи могут потреблять ресурсов больше, чем несколько тяжёлых, но редких запросов.
О, спасибо!
Единственный момент, там нет параметров, если при разных параметрах запрос может быть как быстрым, так и медленным, то во первых может немного сказываться на статистике, во вторых с логом проще сразу в explain перейти.
да, pg_stat_statements не отменяет необходимость следить за запросами попадающими в лог по подходящей для проекта отсечке log_min_duration_statement.
Ну и давняя известная беда, pg_stat_statements считает только время выполнения, но не время планирования.
Ну и давняя известная беда, pg_stat_statements считает только время выполнения, но не время планирования.
Можете привести примеры или почитать про проблемы с планированием — вроде никогда не сталкивался?
Из простого — создайте табличку партицированную на тысячи разделов вместо явно рекомендуемых в документации up to perhaps a hundred child tables. Потом посмотрите explain analyze — увидите что planning time может даже превышать время выполнения. Но в pg_stat_statements этого видно не будет, он сейчас считает только execution time.
Так же planning time может быть сильно выше execution time из за bloat таблиц, мониторинг и pg_repack помогает.
Позволю себе предположить, что:
if 'CREATE INDEX' in sql:
sql = sql.replace('CREATE INDEX', 'CREATE INDEX CONCURRENTLY')- хак, я привёл пример при которых можно получить не совсем то, чего хотелось.
Одно дело парсинг логов, который можно перезапустить (источник вряд ли меняется), другое дело потенциальное изменение результирующих данных в базе как сайд эффект.
Есть ли у кого-либо подобный материал по MySQL?
А как вы решили проблему с vacuum full? Там кроме блокировок ещё надо место на жестком диске почти равное данной таблице
Если места на диске не хватает или одним куском табличка не пролезает (например, диски не тянут столько писать разом или сеть до реплики) — есть утилитка pgcompacttable
И на vacuul full места надо не «почти равное», а даже вплоть до двукратного — создать копию таблички и всех индексов, а так же прогнать всё это через WAL.
И на vacuul full места надо не «почти равное», а даже вплоть до двукратного — создать копию таблички и всех индексов, а так же прогнать всё это через WAL.
Спасибо за статью.
А каким образом делаете VACUUM FULL?
А каким образом делаете VACUUM FULL?
Могу ответить за нас — pgcompacttable или pg_repack по ситуации.
Да, как написали выше пользуемся pg_repack
Мыши кололись, но упорно продолжали не есть Монго :(
что-то не понял идеи костыля с новой колонкой. допустим три дня вы транзакциями по паре тысяч строк копируете из старой колонки в новую, а дальше то что? если не блокировать, в старой колонке за три дня данные уже совершенно иные.
Вы про смену типа поля? Пропустили добавление триггера. Соответственно триггеры на before insert и before update поддерживают актуальность нового поля всё время пока идёт обновление.
нет, я про первый кейс с дефолтным значением колонки. с тригерами тоже не понятно. а блокировки кто будет выставлять? копировальщик зачитает на момент t1, запишет в момент t3. между ними гарантирован лост апдейт. постгрес то не блокировочник. в яндексе об этом в курсе?
Дефолтное значение колонки — а зачем там дополнительные блокировки? Дефолт же ещё выставляем, значит приложение с этим работать вообще ещё не умеет и не трогает, не задеплоили же базу ещё для этой фичи. Ну или умеет, но понимает, что null = default, а хотим сделать not null, вычитываем все is null — т.е. которые ещё не обновили, если их уже обновило приложение — мы их и не вычитываем и не меняем. А, пригляделся, для самого update «and some_bool is null» нужен в этом случае для recheck. Тут маленький race condition, либо for update какой-то из select хватать. Но не трёхдневный точно.
Триггеры — зачем какая-то дополнительная блокировка? Триггер вызывается уже после взятия блокировки строки. Мы же разумеется не говорим о тупейшем отдельно select и затем update по одной строке из результата select в миграции? Здесь один update, который нормально берёт для себя блокировки и не гоняет бесполезно данные туда-сюда между приложением и БД (хотя код модуля я не смотрел, не интересно во всяких orm копаться)
Триггеры — зачем какая-то дополнительная блокировка? Триггер вызывается уже после взятия блокировки строки. Мы же разумеется не говорим о тупейшем отдельно select и затем update по одной строке из результата select в миграции? Здесь один update, который нормально берёт для себя блокировки и не гоняет бесполезно данные туда-сюда между приложением и БД (хотя код модуля я не смотрел, не интересно во всяких orm копаться)
после того как вы выставили дефолт вы пишите:
никакие тригеры тут не фигурируют. пока вторая пачка переносит значения, строки первой пачки уже сменили значения и в рассинхроне.
про тригеры ок.
Нам остается обновить все существующие строчки, которые были в таблице на момент выполнения этой команды. Что мы и будем делать пачками по несколько тысяч экземпляров, чтобы не блокировать надолго большой объем данных.
никакие тригеры тут не фигурируют. пока вторая пачка переносит значения, строки первой пачки уже сменили значения и в рассинхроне.
про тригеры ок.
Вы ведь про добавление нового поля? Откуда взялось «пачка переносит значения»? Никто ничего не переносит.
Мы поставили set default — значит все новые сроки будут писаться с этим default. Затем в фоне обновляем все старые строки простым update, который сам корректно беспокоится о взятии row level lock. А приложение использующее это новое поле будет задеплоено уже после завершения миграции. Если вы хотите задеплоить раньше — то у вас и приложение должно ожидать получить NULL из колонки которую вы хотите NOT NULL назвать.
Ну и пишу это не я. Я не автор ни статьи, ни инструмента и вообще не работаю в яндексе. Автор статьи даже в ЛС не отвечает и не правит одну явную опечатку.
Мы поставили set default — значит все новые сроки будут писаться с этим default. Затем в фоне обновляем все старые строки простым update, который сам корректно беспокоится о взятии row level lock. А приложение использующее это новое поле будет задеплоено уже после завершения миграции. Если вы хотите задеплоить раньше — то у вас и приложение должно ожидать получить NULL из колонки которую вы хотите NOT NULL назвать.
Ну и пишу это не я. Я не автор ни статьи, ни инструмента и вообще не работаю в яндексе. Автор статьи даже в ЛС не отвечает и не правит одну явную опечатку.
я процитировал откуда взялась пачка
корректно будет лишь в одном случае — если row level lock ставить на все записи и одной транзакцией перенести. я же доступно расписал: пока вторая пачка переносит значения, строки первой пачки уже сменили значения и в рассинхроне.
Затем в фоне обновляем все старые строки простым update, который сам корректно беспокоится о взятии row level lock.
корректно будет лишь в одном случае — если row level lock ставить на все записи и одной транзакцией перенести. я же доступно расписал: пока вторая пачка переносит значения, строки первой пачки уже сменили значения и в рассинхроне.
я процитировал откуда взялась пачка
Почему пачка — понятно и очевидно. Почему переносит значения? Откуда это взялось? Зачем куда-то что-то переносить?
Если вы про MVCC сущность тапла — то update выполняется под корректной блокировкой строки и никто в это вмешаться не может до конца транзакции.
пока вторая пачка переносит значения, строки первой пачки уже сменили значения
Почему сменили? Кто сменил? Кто разрешил взять блокировку на строку?
взялось из текста. была колонка, там были значения. на них он хочет поставить not null, там где нулл чем-то дефолтным заполнить. в этом суть.
ощущение что у вас буфер лишь на одно предложение. я же два предложения процетировал, прочтите их вместе:
видимо пачка = транзакция. первая пачка прошла, локи отпускает, что бы «не блокировать надолго большой объем данных»
Почему сменили? Кто сменил? Кто разрешил взять блокировку на строку?
ощущение что у вас буфер лишь на одно предложение. я же два предложения процетировал, прочтите их вместе:
Нам остается обновить все существующие строчки, которые были в таблице на момент выполнения этой команды. Что мы и будем делать пачками по несколько тысяч экземпляров, чтобы не блокировать надолго большой объем данных.
видимо пачка = транзакция. первая пачка прошла, локи отпускает, что бы «не блокировать надолго большой объем данных»
Так я дважды уже переспросил «вы про добавление новой колонки?»
Если нет — то откуда у вас «была колонка, там были значения»? Раз колонка новая — то не было там никаких значений. Цитируете вы фрагмент именно добавления нового поля. Нет там никаких старых значений и не было.
Если вы про кейс с изменением типа существующей колонки — то следит триггер, прекрасно упомянутый и вроде даже согласились, что к триггеру у вас вопросов нет.
Пачка — да, отдельная транзакция. В этом и смысл.
Мы один раз обновили пачку строк, заполнив в ней значение нового поля и более к этим строкам не возвращаемся. Потому что в них теперь есть значение и под is null они больше не попадают. И нас вообще не волнует, что с теми строками происходит дальше. Потому что default мы им проставили, а приложение это новое поле ещё не использует и следовательно поле как неизменившаяся часть тапла копируется при обновлениях строки.
Если нет — то откуда у вас «была колонка, там были значения»? Раз колонка новая — то не было там никаких значений. Цитируете вы фрагмент именно добавления нового поля. Нет там никаких старых значений и не было.
Если вы про кейс с изменением типа существующей колонки — то следит триггер, прекрасно упомянутый и вроде даже согласились, что к триггеру у вас вопросов нет.
Пачка — да, отдельная транзакция. В этом и смысл.
Мы один раз обновили пачку строк, заполнив в ней значение нового поля и более к этим строкам не возвращаемся. Потому что в них теперь есть значение и под is null они больше не попадают. И нас вообще не волнует, что с теми строками происходит дальше. Потому что default мы им проставили, а приложение это новое поле ещё не использует и следовательно поле как неизменившаяся часть тапла копируется при обновлениях строки.
попробуйте сосредоточится и вникнуть в текст. я точно процитировал кусок о котором речь, я разжувал что именно аффтор имеет ввиду. у него задача поставить not null на существующую колонку. это важно во тут сосредоточится. колонка существует, значения там есть. то что он это делает через добавление новой колонки и предварительным заполнением дефолтным значением — реализация задачи.
у него задача поставить not null на существующую колонку
эмм. А это вы откуда достали? Попробуйте вы тоже что ли сосредоточиться.
Не имеет ровно никакого смысла для set not null делать новую колонку. Потому что set not null всё равно хочет seqscan всей таблицы под эксклюзивной блокировкой и это единственный code path этого tablecmd.c.
Автор пишет о добавлении новой (!) колонки. Которой раньше никогда не было. И никаких данных в ней тоже не было. Только статичный default, который мы и вносим. После обновления всех строк на этот статичный default нам надо проставить SET NOT NULL (и если надо, о чём тоже написано), как оно было в исходной долгой блокирующей alter table.
Не имеет никакого смысла только для проставления NOT NULL копировать колонку. База всё равно будет валидировать все check и все not null при попытке проставить not null любой колонке.
UFO just landed and posted this here
Sign up to leave a comment.
Изменение схемы таблиц PostgreSQL без долгих блокировок. Лекция Яндекса