Предотвращение простоев при изменении больших таблиц | by Sullyvan Nunes
Представьте, что у вас есть таблица Postgres 9.5 с более чем 30 млн строк и около 30 ГБ, а затем вы случайно добавляете столбец неправильного типа. Наша команда в EBANX наткнулась на эту проблему. В то время самым удобным выходом было запустить таблицу изменений и изменить тип столбца. Почему нет?
Итак, мы запустили что-то вроде этого:
Универсальная команда alter tableИз-за большого количества запросов к этой таблице блокировка, созданная для команды ALTER TABLE, была чрезвычайно опасна для стабильности нашего приложения, но когда мы остановили этот запрос, все вернулось обратно. до нормального — за исключением того, что мы потеряли некоторые запросы, сделанные в этот период времени. Этого было более чем достаточно, чтобы научить нас не запускать ALTER COLUMN TYPE в такой большой таблице.
Все команды ALTER TABLE будут удерживать блокировку ACCESS EXCLUSIVE (документация Postgres по блокировкам на уровне таблиц), чтобы гарантировать, что это единственная транзакция, имеющая доступ к таблице.
Как долго она продлится, зависит от того, что вы хотите изменить. Так что проблема была не в самой команде, а во времени ее выполнения.
ALTER TABLE изменяет определение существующей таблицы. […] Блокировка ACCESS EXCLUSIVE удерживается, если явно не указано иное.
Когда вы просите базу данных Postgres изменить тип столбца, она будет изменять его построчно, и это займет несколько дней, чтобы выполнить это в нашей базе данных — но для этого невозможно заблокировать таблицу. долго в нашем приложении.
Нам нужно было обобщить ALTER COLUMN TYPE, чтобы он работал как можно быстрее и не делал нашу таблицу недоступной. Мы можем сказать, что в области наихудших сценариев у нас был хороший, потому что этот новый столбец не был заполнен какими-либо данными.
Таким образом, изменение типа пустого столбца равносильно его удалению и началу с нуля с правильным типом. И мы сделали это,
Поскольку каждая транзакция требует времени, и все команды ALTER TABLE вызовут блокировку, и действительно эта блокировка существовала, но не более чем на 50 мс.
Эти действия имеют очень умный способ выполнения и позволяют избежать более длительных блокировок. Чтобы справиться с отбрасыванием, база данных только пометит столбец как удаленный, оставив его недоступным для приложения, а не удалив его фактически, что приведет к более быстрой процедуре, как мы можем видеть на снимках примера ниже.
Прежде всего, мы создали таблицу с названием article и заполнили ее 73872 записями, занимающими около 14 МБ дискового пространства.
Итак, мы выполнили команду ALTER TABLE, сбросив атрибуты имя и описание и как мы видим атрибуты были помечены как отброшенные. Однако удаленные атрибуты по-прежнему занимают место на диске.
Только после того, как мы выполним команду ПЫЛЕСОСА на этой таблице, неиспользуемое дисковое пространство освобождается операционной системой.
Теперь, когда столбец удален, мы можем добавить новый, сообщив ему правильный тип. Когда вы делаете это с ненулевым значением по умолчанию, postgresql перезаписывает всю таблицу, что занимает много времени, но этого не произойдет, если вы создадите ее без значения по умолчанию и затем заполните ее.
Добавление столбца со значением по умолчанию требует обновления каждой строки таблицы (для сохранения нового значения столбца). Однако, если значение по умолчанию не указано, PostgreSQL может избежать физического обновления. Поэтому, если вы собираетесь заполнить столбец в основном значениями, отличными от значений по умолчанию, лучше всего добавить столбец без значений по умолчанию, вставив правильные значения с помощью UPDATE.
Работа с изменением столбцов действительно сложна и требует знаний о вашем приложении, поскольку все отношения между таблицами увеличивают время блокировки, а в другом приложении время простоя создания столбца не должно быть приемлемым.
В Postgres есть много интеллектуальных решений, позволяющих поддерживать базу данных в рабочем состоянии, даже если ее размер работает против вас. Разобраться со всеми этими блокировками может быть сложно, но дальнейшее расследование может помочь решить вашу проблему самым безопасным способом.
Эта статья была написана вместе с Марсело Геварой
Темная сторона ALTER TABLE: руководство
Если вы часто погружаетесь в мир MySQL, вероятно, нет необходимости объяснять вам, что такое SQL-запросы и что они делают .
Скорее всего, вы знаете их, как свои пять пальцев. SELECT запросов позволяют нам читать данные, INSERT запросов позволяют нам вставлять данные в заданную базу данных, UPDATE запросов позволяют нам обновлять данные для определенных строк или для всей таблицы, если мы хотим, DELETE запросов позволяют мы удаляем записи из таблицы и т. д. Если вы когда-либо копали глубже в мир MySQL, вы должны знать, что есть еще один запрос, который очень важен как для администраторов баз данных, так и для разработчиков — это запрос ALTER TABLE 9.0046 .
Что такое ALTER TABLE?
Проще говоря, ALTER TABLE — это запрос в MySQL, который позволяет нам изменить (или ALTER ) структуру таблицы. Вещи, которые может сделать ALTER TABLE , многочисленны - ALTER TABLE может использоваться для изменения имен таблиц или столбцов, а также для добавления или изменения столбцов. Однако, в конце концов, ALTER TABLE в основном используется, когда задействованы индексы — когда разработчикам нужно добавить индексы, они изменяют структуру своих таблиц, и именно здесь ALTER TABLE снова вступает в игру.
Скорость выполнения ALTER TABLE
Скорость выполнения операторов ALTER TABLE , очевидно, напрямую зависит от размера таблиц или столбцов, которые мы хотим изменить, однако есть одно предостережение. ALTER TABLE выполняет все свои операции с копией таблицы, а не с самой таблицей . Другими словами, когда MySQL приказано изменить таблицу и выполняется оператор ALTER TABLE , MySQL делает копию изменяемой таблицы, вставляет в нее данные, которые в настоящее время находятся внутри нашей таблицы, выполняет все нужных операций туда, и копирует данные обратно в нашу таблицу — только тогда мы видим результаты.
ALTER TABLE может работать немного по-другому, если мы не используем InnoDB в качестве нашего основного механизма хранения — MyISAM не будет отображать строки, которые вставляются в таблицу, пока не будут завершены все операции, но, тем не менее, основная предпосылка остается такой же. Скорость такого заявления напрямую зависит от факторов, изложенных ниже.
- Структура таблиц нашей базы данных.
- Размер наших столов.
- Наш веб-сервер и сервер базы данных.
- Наша конфигурация MySQL.
- То, как мы выполняем запросы.
Во-первых, важна структура наших таблиц, потому что запросы, включающие столбцы, которые не были должным образом «заботятся» (индексированы и т. д.), будут медленнее, чем должны быть. Кроме того, имейте в виду, что если бы наши таблицы не использовали нормализацию, наши запросы были бы медленнее, чем должны быть, особенно если наши таблицы большие. Представьте, что вы копируете, возможно, миллионы ненужных строк в другую таблицу... Звучит медленно, не так ли?
Во-вторых, размер наших таблиц действительно имеет значение, потому что чем больше наши таблицы, тем больше времени потребуется для их копирования. Второй пункт также тесно связан со всеми остальными пунктами, потому что мы также должны думать о том, как настроен наш веб-сервер и о том, как мы выполняем наши запросы, чтобы они выполнялись без проблем и как можно быстрее: например , если наш сервер базы данных настроен на использование 80% доступной оперативной памяти, скорее всего, наши запросы будут выполняться намного быстрее, чем в конфигурации по умолчанию.
Кроме того, если мы запустим несколько ALTER TABLE запросы один за другим и мало места на диске, мы рискуем полностью его исчерпать. Позвольте нам проиллюстрировать.
Темная сторона ALTER TABLE
Допустим, у нас есть выделенный сервер с 16 ГБ оперативной памяти и 100 ГБ места на жестком диске. Хороший администратор базы данных, вероятно, настроит его так, чтобы он занимал 8 ГБ или более оперативной памяти, и предположим, что таблица, к которой мы хотим выполнять запросы, состоит из 100 миллионов записей и не нормализована. В целом, все, из чего состоит эта таблица, занимает на диске 20 Гб.
На данный момент у нас, возможно, есть пара других таблиц, с которыми мы работаем — скажем, они занимают еще 50 ГБ места на жестком диске, но вы поняли. Это запрос, который мы запускаем: не стесняйтесь адаптировать, затем скопируйте и вставьте его в свой клиент Arctype.
ALTER TABLE demo_table MODIFY demo_column VARCHAR(150) NOT NULL AFTER Another_column;
Сначала MySQL сделает копию таблицы на диске, затем вставит в нее текущие данные, проделает все необходимые операции и только потом поменяет местами старую и новую таблицы вместе: помните, что у нас 30 ГБ диска место осталось? После того, как временная таблица будет создана, у нас останется 10 ГБ.
К тому времени мы, вероятно, начнем молиться о том, чтобы индекс занимал меньше места, чем 10 ГБ, и наши запросы выполнялись успешно. Если у нас заканчивается место на диске, наши запросы прерываются. Действительно ли мы хотим видеть такие результаты? Конечно, нет.
Предотвращение проблем с ALTER TABLE
Как видите, как только ALTER TABLE станет более глубоко вовлеченным в наши процессы базы данных, мы вполне можем столкнуться с многочисленными проблемами. Как мы их решаем?
Одним из способов было бы использование инструментов, которые делают копии таблиц и модифицируют их с помощью триггеров, чтобы они оставались доступными во время подготовки новой таблицы: другими словами, чтобы изменить структуру таблиц, не блокируя их: для полное руководство, загляните сюда.
Другим способом, которым мы можем выполнить такую задачу, будет ручное выполнение, что означает, что мы также можем использовать такой подход: indexes, и наши экземпляры MySQL должны быть правильно настроены.
INSERT INTO demo2 SELECT * FROM demo demo2 — новая таблица, а demo — имя старой таблицы. Прежде чем сделать это, убедитесь, что структура обеих таблиц одинакова (см. изображение ниже пункта № 5). DROP TABLE demo . ALTER TABLE demo2 RENAME TO demo . Если мы предпочитаем выполнять шаги вручную, такой подход подойдет. Однако имейте в виду, что такой подход в значительной степени0045 ALTER TABLE делает в первую очередь, и что в некоторых крайних случаях DROP TABLE может стать ужасно медленным для выполнения — в этом случае, уничтожьте запрос, используя SHOW PROCESSLIST , а затем KILL определенный идентификатор запроса .