Ещё лет 5 назад на собеседованиях с backend-разработчиками был популярен вопрос: как переименовать колонку в высоконагруженной таблице (возможны вариации: как сделать любой другой alter table)? Ответ мог быть примерно следующий:
Для начала создаём новую колонку (или даже таблицу с нужной структурой). Далее делаем триггеры на старую колонку, которые вносят все поступающие изменения на новую колонку. Параллельно с этим синхронизируем уже добавленные данные из старой колонки в новую. Через какое-то время данные актуализируются, и задача будет решена.
Вопрос всё ещё часто звучит на собесах. И актуальность его выросла, так как за последние 5 лет стало намного больше масштабных проектов с высокой нагрузкой.
В MySQL существуют специальные инструменты для решения подобных задач: pt-online-schema-change и gh-ost. Принцип их действия практически такой же, как описанный выше.
Хочу рассказать, как мы делали alter table в нашем микросервисе с высоконагруженной таблицей (golang + postgres, ~1500rps, ~15 млн. записей).
Постановка задачи
В одном из проектов, над которым мы работаем в Каруне, задача стояла немного сложнее, чем просто "переименовать колонку": необходимо было захешировать данные одной колонки. Мы решили сделать это через создание новой колонки и перенести функционал на неё.
Шаг 1. Добавляем колонку
Для начала мы создали колонку и повесили на неё индекс:
ALTER TABLE my_table ADD COLUMN IF NOT EXISTS field_encrypted TEXT;
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS field_encrypted_unique_idx
ON my_table (field_encrypted);Выполненные операции не создавали никаких блокировок.
Шаг 2. Переливаем старые данные
Перезалив данных из старой колонки в новую мы реализовали через cron библиотеку языка golang. Такое решение приняли из-за удобного логирования возникающих проблем и возможности быстрого реагирования на них. Да и сторонников делать через логику базы данных у нас не нашлось.
Конечно, сразу же возникли трудности следующего вида. В принципе, аффектить базу данных сильно нельзя — это увеличивает время ответа сервиса (>1 sec — это уже плохо) и количество блокировок. Зависимые сервисы могут не обработать ответы, появится неконсистентность. Или может спровоцироваться ещё более сильная нагрузка на базу данных, и сервис может не выдержать.
Поэтому:
Все изменения для новой колонки делались батчами, а не по одной записи (batch insert, batch update).
У нас запущено несколько инстансов этого микросервиса. Поэтому необходимо было не допустить многократный запуск этого скрипта в одно время. Тут нам помогла advisory lock для postgres (каждый инстанс проверял блокировку и только 1 выполнял джобу):
SELECT pg_try_advisory_xact_lock(1);Периодически для базы запускался достаточно тяжелый VACUUM:
Во время его работы также нежелательно запускать скрипты по описанной выше причине. Поэтому мы должны были проверять, не запущен ли он.
SELECT check_vacuum('my_table');Сама функция check_vacuum:
CREATE FUNCTION check_vacuum(name text) RETURNS boolean
LANGUAGE sql SECURITY DEFINER
AS $_$
SELECT count(*)::int > 0
FROM pg_stat_progress_vacuum
WHERE relid::regclass = $1::regclass OR
relid::regclass::text in (
SELECT reltoastrelid::regclass::text FROM pg_class WHERE relname = $1
);
$_$;После того, как мы перегнали все данные, необходимостью стало переключение на работу с новой колонкой.
ШАГ 3. Вешаем constraint
Для новой колонки требовался constraint not null. Но напрямую сделать этого нельзя, иначе можно надолго заблокировать таблицу, что недопустимо. Поэтому в postgres существует следующий механизм:
ALTER TABLE my_table ADD CONSTRAINT field_ecnrypted_not_null
CHECK (field_encrypted IS NOT NULL) NOT VALID;Он позволяет повесить ограничение not null, но не валидировать существующие записи сразу, а сделать это следующим шагом и без блокировок:
ALTER TABLE my_table VALIDATE CONSTRAINT field_ecnrypted_not_null;Ну и в заключение мы сделали то, к чему шли:
ALTER TABLE my_table DROP COLUMN old_field;Заключение
Данные шаги позволили бесшовно, незаметно для пользователей нашего микросервиса переименовать колонку и добавить хеширование к ней. Данный подход мы реализовали примерно за неделю — у нас не было дедлайна. Но если вдруг вы едете на горящем велосипеде, и всё вокруг горит, то можно справиться и за пару дней.
Интересно узнать, как вы решаете похожие задачи на MySQL. Пишите в комменты, с удовольствием почитаю и подключусь к обсуждению.
Комментарии (29)
egor_nullptr
18.08.2021 12:43+3как переименовать колонку в высоконагруженной таблице.
Интересно узнать, как вы решаете похожие задачи на MySQL.ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;С рядом ограничений, но работает.
Akina
18.08.2021 21:25-1Это зачем так сложно? CHANGE - потенциально, при неправильном задании типа данных, способна принести проблем.
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;... а всё, собственно. Ни тебе блокировок, ни потенциальных проблем, ни чего ещё - операция онлайновая и практически мгновенная. Правда, клиентов ожидают неприятные сюрпризы - ведь они не могут так же на лету перестроиться в тот же момент, и будут обращаться к полю по старому имени. И это - основная проблема, но она как раз находится за рамками обсуждаемой в статье проблемы.
Кстати, в самой статье процесс перехода клиента на новую структуру как-то выпущен. А самое главное - вообще не озвучено, как решалась проблема клиентов, ориентированных на старую структуру в процессе перелива данных. Про триггер ни слова - надо так понимать, что батч-процедуру копирования запускали и запускали, обновляя свежие записи, внесённые старой версией клиента, покуда все клиенты не были обновлены?
slava-a Автор
18.08.2021 21:32Проблема с клиентами решалась через временный костыль - сначала работа сервиса осуществлялась через новый столбик, а в случае эксепшена - через старый.
Про триггер - пункт 2. Вместо триггера был скрипт, который обновлял только старые данные. Свежие данные записывались уже правильно, без необходимости обновления.
p.s. целью статьи было описать сложности с БД. имхо проблемы с кодом это не так интересно и решается тысячью способами довольно просто
GooG2e
18.08.2021 15:01Я может что-то фундаментально не понимаю, но допустим понятно что влечёт за собой добавление столбца, изменение типов данных, добавление ограничений на столбец, но что за собой влечёт изменение названия колонки?
slava-a Автор
18.08.2021 15:37Само переименование в чистом виде наверняка никаких проблем не влечет. ИМХО такая задача не несет ценности и кроме того, вы можете положить прод на ровном месте.
Подобные вещи выполняются с какими-то другими, уже более осмысленными действиями - как вы сказали, например, изменение типов данных.
bgnx
18.08.2021 15:13-3Ещё лет 5 назад на собеседованиях с backend-разработчиками был популярен вопрос: как переименовать колонку в высоконагруженной таблице (возможны вариации: как сделать любой другой alter table)? Ответ мог быть примерно следующий:Зачем такие сложности с создаем новой колонки и постепенной миграцией? Почему бы просто не задать всем колонкам уникальное имя которое никогда не захочется поменять (случайный айдишник) а уже в коде хранить соответствие имя -> id при работе с этой бд. Тогда переименование колонки сведется к простейшей операции замены одного имени на другое в коде вообще без каких-либо затрат со стороны бд
slava-a Автор
18.08.2021 15:31Под переименованием обычно понимается простейшая alter table. Чтобы понять, знает ли собеседуемый про блокировки и имеет ли подобный опыт.
По сути просто переименование колонки является бессмысленной операцией. Это всегда часть какой-то другой задачи. Как, например, добавить новую колонку созависимую со старой. Или, как конкретно нашем случае, нужно было помимо переименования колонки поменять ее тип с uuid на text.
AndyKorg
19.08.2021 06:19+1Возможно так же надо отказаться от осмысленных имен таблиц. Да, что там таблиц! Можно и имена БД чисто произвольными задавать. И еще дальше - имена серверов и т.д. :)
Интересно почему так никто не делает? :)
iwannabeacoder
18.08.2021 15:47+1Интересная тема, но есть пара концептуальных замечаний. Проблема одной таблицы с высокой нагрузкой в том, что приходится искать компромисс между скоростью вставки и выборки. В моем проекте есть таблицы, оптимизированные для вставки и для чтения. Переброска данных осуществляется по крону, в mysql есть для этих целей EVENT. Таким образом, есть возможность конфигурировать для разных таблиц разные диски (ssd/hdd) и теоретически онлайн миграция может быть с небольшим количеством ограничений: сначала миграция таблицы для записи и эвента, после копирование таблицы для чтения и любая по длительности миграция. Теоретически - это потому, что практически у меня kubernetes и при миграции поднимается новый инстанс базы с новой версией базы, на которой копия актуальной базы подвеглась миграции. При старте нового инстанса остается только синхронизировать данные с момента снятия дампа старой базы.
Но, обратно к теме онлайна:
На тестовом сервере есть две таблицы executed_orders_0 и executed_orders_1. №1 содержит 42 милиона записей, №0 какое-то количество сделок на бирже за последние 2 минуты. Это все на диске занимает приблизительно 6Гб, mysql ограничен по ресурсам на 2 ядра и 1.4Гб памяти, чтобы более или менее эмулировать raspberry pi 4, на котором работает релизная сборка.
alter table executed_orders_1 add column test int, ALGORITHM=INSTANT; выполняется 0.218 секунды.
alter table executed_orders_1 drop column test, ALGORITHM=INPLACE, LOCK=NONE; выполняется 679.312 секунд. Графана показывает полную загрузку 1 ядра и 700 iops на диске.
Я так побаловался пару раз с add/drop и телеметрия трех микросервисов, которые пишут в базу не показала никаких проблем со скоростью записи. В логах сервера тоже никаких проблем с эвентом, который перебрасывает записи из одной таблицы в другую. Думаю, что это все будет работать и с прямым чтением/записью единственной таблицы.
slava-a Автор
18.08.2021 15:52Интересно еще было бы попробовать добавить индексы и констрейнты к этой колонке
iwannabeacoder
18.08.2021 16:16+1Судя по документации, foreign key поддерживается с ALGORITHM=INPLACE
MySQL :: MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations
А на
alter table executed_orders_1 add constraint test_c check (test is null) enforced, ALGORITHM=COPY;
сервер крепко задумался, скорее всего будет выполняться те же 10 минут, если не дольше.
iwannabeacoder
18.08.2021 16:51P.S. Время выполнения около 10 минут, сервер достиг ограничения в 860 iops. С более производительным диском запрос должен выполниться быстрее. Блокировки таблиц вроде не наблюдались, но это не 100%, так как снова смотреть логи я не осилил.
korva
19.08.2021 11:25В постгресе есть параллельные индексы:
https://postgrespro.ru/docs/postgresql/9.4/sql-createindex#SQL-CREATEINDEX-CONCURRENTLY
dph
18.08.2021 21:04Но
ALTER TABLE my_table ADD COLUMN"требует блокировки ACCESS EXCLUSIVE. В большинстве случаев это может быть не заметным, но если идет выполнение длинной транзакции, то данное требование приведет к весьма заметной недоступности таблицы.Поэтому указанный подход нужно использовать очень аккуратно и не стоит использовать в ответах на собеседовании.slava-a Автор
18.08.2021 21:25Как иначе тогда без 'alter table add column'? Создавать новую таблицу?
select_artur
18.08.2021 23:16Лучше сначала брать блокировку EXCLUSIVE, который не блокирует читателей. А еще лучше дополнительно использовать lock_timeout и пробовать выполнить операцию в цикле.
dph
22.08.2021 14:49А можно описать, а чем этот подход поможет?
select_artur
22.08.2021 17:06EXCLUSIVE поможет вам подождать все транзакции ROW SHARE и выше, не блокируя читателей.
Когда вы взяли блокировку EXCLUSIVE вам уже нужно будет выполнить ALTER TABLE и ждать, когда завершатся транзакции ACCESS SHARE перед вами. Это конечно может быть не очень полезно, если у вас в основном только ACCESS SHARE, но полезно, если есть долгие записи.
dph
22.08.2021 14:48Просто нужно уметь возможность на момент миграции отключать "длинные транзакции" (например те же отчеты или другие миграции на этой же таблице).
Если есть возможность или необходимость - можно оценивать, какие вообще транзакции идут сейчас по данной таблице и откладывать миграцию DDL на момент "все тихо", но это уже сложное решение.
Или принудительно делать rollback для всех транзакций, а потом уже делать ALTER TABLE
bigtrot
20.08.2021 13:36Rename тоже требует ACCESS EXCLUSIVE. И почему эта операция, как и ADD в большинстве случаев может быть не незаметной?
dph
22.08.2021 14:54Можешь погуглить, это популярная тема.
Если коротко, то при попытке взятия ACCESS EXCLUSIVE во время выполнения другой длинной транзакции все новые транзакции будут выстраиваться в очередь после выполнения AE, а она будет ждать завершения этой длинной транзакции.
В худшем случае можно получить простой сроком в несколько минут (если такие длинные транзакции разрешены конкретными настройками, обычно разрешены).bigtrot
23.08.2021 08:40Так и ADD COLUMN создаст такие же проблемы. Тогда в чем преимущество добавление колонки по сравнению с её переименованием, если и там и там требуется ACCESS EXCLUSIVE, только после переименования таблица готова для работы с новой колонкой, а после добавления требуются дополнительные манипуляции по переносу данных?
LaRN
18.08.2021 21:45-1Можно взять дамп проделать с ним все нужные манипуляции, т.к. без нагрузки, то это вообще не вопрос. Далее поднять новый инстанс сервиса, который будет смотреть на это дамп, а дельту по данным постепенно перегнать со старого прода на дамп, который теперь есть новый прод.
Так и схема на сервере обновиться и сервис, который на новую схему заточен не рухнет, и старый сервис со старой версией БД спокойно доработает.
rrrad
https://habr.com/ru/company/yandex/blog/435880/
https://habr.com/ru/company/miro/blog/540500/
Хотя объективно, идея мониторить vacuum-ы в процессе миграции
- это что-то новое.