Comments 29
https://habr.com/ru/company/yandex/blog/435880/
https://habr.com/ru/company/miro/blog/540500/
Хотя объективно, идея мониторить vacuum-ы в процессе миграции
- это что-то новое.
как переименовать колонку в высоконагруженной таблице.
Интересно узнать, как вы решаете похожие задачи на MySQL.
ALTER TABLE tbl
CHANGE old_col_name new_col_name data_type,
ALGORITHM=INPLACE, LOCK=NONE;С рядом ограничений, но работает.
Это зачем так сложно? CHANGE - потенциально, при неправильном задании типа данных, способна принести проблем.
ALTER TABLE table_name
RENAME COLUMN old_col_name TO new_col_name;... а всё, собственно. Ни тебе блокировок, ни потенциальных проблем, ни чего ещё - операция онлайновая и практически мгновенная. Правда, клиентов ожидают неприятные сюрпризы - ведь они не могут так же на лету перестроиться в тот же момент, и будут обращаться к полю по старому имени. И это - основная проблема, но она как раз находится за рамками обсуждаемой в статье проблемы.
Кстати, в самой статье процесс перехода клиента на новую структуру как-то выпущен. А самое главное - вообще не озвучено, как решалась проблема клиентов, ориентированных на старую структуру в процессе перелива данных. Про триггер ни слова - надо так понимать, что батч-процедуру копирования запускали и запускали, обновляя свежие записи, внесённые старой версией клиента, покуда все клиенты не были обновлены?
Проблема с клиентами решалась через временный костыль - сначала работа сервиса осуществлялась через новый столбик, а в случае эксепшена - через старый.
Про триггер - пункт 2. Вместо триггера был скрипт, который обновлял только старые данные. Свежие данные записывались уже правильно, без необходимости обновления.
p.s. целью статьи было описать сложности с БД. имхо проблемы с кодом это не так интересно и решается тысячью способами довольно просто
Я может что-то фундаментально не понимаю, но допустим понятно что влечёт за собой добавление столбца, изменение типов данных, добавление ограничений на столбец, но что за собой влечёт изменение названия колонки?
Ещё лет 5 назад на собеседованиях с backend-разработчиками был популярен вопрос: как переименовать колонку в высоконагруженной таблице (возможны вариации: как сделать любой другой alter table)? Ответ мог быть примерно следующий:Зачем такие сложности с создаем новой колонки и постепенной миграцией? Почему бы просто не задать всем колонкам уникальное имя которое никогда не захочется поменять (случайный айдишник) а уже в коде хранить соответствие имя -> id при работе с этой бд. Тогда переименование колонки сведется к простейшей операции замены одного имени на другое в коде вообще без каких-либо затрат со стороны бд
Под переименованием обычно понимается простейшая alter table. Чтобы понять, знает ли собеседуемый про блокировки и имеет ли подобный опыт.
По сути просто переименование колонки является бессмысленной операцией. Это всегда часть какой-то другой задачи. Как, например, добавить новую колонку созависимую со старой. Или, как конкретно нашем случае, нужно было помимо переименования колонки поменять ее тип с uuid на text.
Возможно так же надо отказаться от осмысленных имен таблиц. Да, что там таблиц! Можно и имена БД чисто произвольными задавать. И еще дальше - имена серверов и т.д. :)
Интересно почему так никто не делает? :)
Интересная тема, но есть пара концептуальных замечаний. Проблема одной таблицы с высокой нагрузкой в том, что приходится искать компромисс между скоростью вставки и выборки. В моем проекте есть таблицы, оптимизированные для вставки и для чтения. Переброска данных осуществляется по крону, в 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 и телеметрия трех микросервисов, которые пишут в базу не показала никаких проблем со скоростью записи. В логах сервера тоже никаких проблем с эвентом, который перебрасывает записи из одной таблицы в другую. Думаю, что это все будет работать и с прямым чтением/записью единственной таблицы.
Интересно еще было бы попробовать добавить индексы и констрейнты к этой колонке
Судя по документации, 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 минут, если не дольше.
В постгресе есть параллельные индексы:
https://postgrespro.ru/docs/postgresql/9.4/sql-createindex#SQL-CREATEINDEX-CONCURRENTLY
Но ALTER TABLE my_table ADD COLUMN"требует блокировки ACCESS EXCLUSIVE. В большинстве случаев это может быть не заметным, но если идет выполнение длинной транзакции, то данное требование приведет к весьма заметной недоступности таблицы.Поэтому указанный подход нужно использовать очень аккуратно и не стоит использовать в ответах на собеседовании.
Как иначе тогда без 'alter table add column'? Создавать новую таблицу?
Лучше сначала брать блокировку EXCLUSIVE, который не блокирует читателей. А еще лучше дополнительно использовать lock_timeout и пробовать выполнить операцию в цикле.
А можно описать, а чем этот подход поможет?
EXCLUSIVE поможет вам подождать все транзакции ROW SHARE и выше, не блокируя читателей.
Когда вы взяли блокировку EXCLUSIVE вам уже нужно будет выполнить ALTER TABLE и ждать, когда завершатся транзакции ACCESS SHARE перед вами. Это конечно может быть не очень полезно, если у вас в основном только ACCESS SHARE, но полезно, если есть долгие записи.
Просто нужно уметь возможность на момент миграции отключать "длинные транзакции" (например те же отчеты или другие миграции на этой же таблице).
Если есть возможность или необходимость - можно оценивать, какие вообще транзакции идут сейчас по данной таблице и откладывать миграцию DDL на момент "все тихо", но это уже сложное решение.
Или принудительно делать rollback для всех транзакций, а потом уже делать ALTER TABLE
Rename тоже требует ACCESS EXCLUSIVE. И почему эта операция, как и ADD в большинстве случаев может быть не незаметной?
Можешь погуглить, это популярная тема.
Если коротко, то при попытке взятия ACCESS EXCLUSIVE во время выполнения другой длинной транзакции все новые транзакции будут выстраиваться в очередь после выполнения AE, а она будет ждать завершения этой длинной транзакции.
В худшем случае можно получить простой сроком в несколько минут (если такие длинные транзакции разрешены конкретными настройками, обычно разрешены).
Так и ADD COLUMN создаст такие же проблемы. Тогда в чем преимущество добавление колонки по сравнению с её переименованием, если и там и там требуется ACCESS EXCLUSIVE, только после переименования таблица готова для работы с новой колонкой, а после добавления требуются дополнительные манипуляции по переносу данных?
Можно взять дамп проделать с ним все нужные манипуляции, т.к. без нагрузки, то это вообще не вопрос. Далее поднять новый инстанс сервиса, который будет смотреть на это дамп, а дельту по данным постепенно перегнать со старого прода на дамп, который теперь есть новый прод.
Так и схема на сервере обновиться и сервис, который на новую схему заточен не рухнет, и старый сервис со старой версией БД спокойно доработает.
Чисто ради обсуждения спрашиваю. Повесить табличку "ведутся технические работы" и без нагрузки быстро-быстро сделать альтер. Чем плохо?
Как сделать alter table в высоконагруженной таблице