Как вы справляетесь с задачей изменения схемы производственной базы данных MySQL?
Одна из самых больших жалоб, которые я слышал о MySQL, заключается в том, что он блокирует таблицу, если вы пытаетесь изменить ее схему, например, добавив столбец или добавив индекс.
Под "блокировкой таблицы" это означает, что я не могу ни читать, ни писать в таблицу? Иногда часами?
Это кажется довольно серьезным ограничением. Я собирался использовать MySQL для моего нового проекта, но это заставляет меня задуматься.
Есть ли обходной путь для этого? Как вы справляетесь с задачей изменения схемы вашей производственной базы данных MySQL?
Кстати, кто-то сказал мне, что у Postgresql такой проблемы нет. Это правда - я могу как читать, так и писать в таблицу Postgresql при изменении ее схемы? Есть ли какие-либо потери производительности?
Хотелось бы услышать ваш опыт.
2 ответа
Да, MySQL полностью блокирует таблицу, пока выполняет инструкцию ALTER TABLE. Большая часть этого времени уходит на физическое копирование таблицы, поэтому рекомендуется объединить все необходимые изменения в одном операторе ALTER TABLE.
Существует несколько подходов к решению этой проблемы в действующей базе данных, если вы не можете получить приличное окно обслуживания.
Во-первых, во многих средах время ожидания составляет несколько минут, пока таблица не станет доступной для их запроса, и они не знают (и это действительно трудно проверить), почему таблица заблокирована. Я использовал эту причуду живого сайта, чтобы внести изменения в таблицы. На сайте, за которым я обычно ухаживал, я полагал, что у нас было около 7 минут, прежде чем кто-то начал замечать.:-) Это помогает убедиться, что ваш босс на вашей стороне.
Другой способ сделать это - выполнить трюк select-insert-rename. Это хорошо работает, если таблица имеет достаточно низкий UPDATE частота, или является просто целью INSERTs. Основные шаги - скопировать схему таблицы, внести необходимые изменения, составить инструкцию для выполнения INSERT...SELECT от старого к новому и переименуйте таблицы (сделайте переименование одним оператором). Вам также необходимо заранее подготовить оператор для копирования любых "новых" записей, которые были добавлены или обновлены между SELECT и RENAME, Я также делал это несколько раз на прошлой работе.
Однако есть предостережения:
- У вас почти наверняка возникнут проблемы, если исходной таблицей является MyISAM, если, возможно, таблица почти никогда не записывается. Это из-за способа блокировки таблиц MyISAM. Он лучше работает с таблицами InnoDB, потому что он все еще может быть прочитан, пока ваш большой
INSERT...SELECTбежит. - Вам нужен надежный способ выяснить записи, которые были добавлены или изменены между
SELECTиRENAME, Для таблиц, которые используются только дляINSERT, используйте столбец auto_increment. Для таблиц, которые получаютUPDATEs, вам понадобится надежный последний измененный столбец.
Другие способы решения этой проблемы включают в себя изменение подчиненных устройств и сбой приложения. Это более тесно связано с тем, как реплицируются ваши базы данных. Я также не сделал этого сам, поэтому я не могу описать точные шаги.
Наконец, есть дюжина настроек сервера, которые вы можете изменить, и еще несколько, которые гораздо сложнее изменить, что повлияет на время копирования таблицы. Буфер сортировки один, но также и то, сколько памяти MySQL разрешено использовать, другое. (Помните, что вы также можете установить множество таких соединений для каждого соединения, вместо того, чтобы устанавливать некоторые из них на высоком уровне глобально.) При работе с большим количеством данных MySQL имеет эффект "переломного момента", когда вещи довольно линейны вплоть до определенного уровня. размер, а затем пойти в ад внезапно. Часто возникают сложные запросы, работающие с большим количеством данных, и они связаны с внутренними временными размерами таблиц и объемом используемой памяти, но могут приводить к изменениям таблиц, поскольку они включают повторную индексацию данных. Это одна из причин, почему предоставление базы данных большего объема памяти - это почти всегда хорошо.
Это самая большая жалоба, которую вы слышали о MySQL? Черт возьми, у меня есть ведро с большими, чем это... (возможно, история на другой день)
Да, MySQL полностью блокирует таблицу, когда вы запускаете ALTER TABLE; нет чтения или записи в течение продолжительности, а запросы, которые пытаются это сделать, приостанавливаются до тех пор, пока не будут завершены. В нечетном случае мне приходится изменять схему большой таблицы MySQL (маленькие завершают свое изменение достаточно быстро, чтобы это не вызывало заметных проблем). Обычно я просто планирую окно технического обслуживания и делаю это тогда. Нетрудно точно определить, сколько времени займет такое изменение для данной таблицы на реплике.
Если у вас есть одно из тех идиотских менеджеров, которое отказывается предоставить вам разумные окна обслуживания (и если это так, бегите, как ад на другую работу), то я слышал о людях, делающих такие вещи, как создание схемы таблицы, изменение пустую таблицу, затем выполнить копирование-выбор в новую таблицу (с блокировкой записи, чтобы предотвратить изменения), затем переименовать таблицы. Похоже, слишком большой риск для моего вкуса. У mat -table-sync от Maatkit есть способ сделать это, если вы предпочитаете обвинять кого-то еще, когда он умирает.
Хотя в PostgreSQL нет "гигантской блокировки судьбы", вы все равно подвергаетесь значительному снижению производительности при изменении схемы таблицы в PgSQL - это много дискового ввода-вывода. Я не могу представить, как любая СУБД сможет этого избежать.