Вы можете столкнуться с ситуацией, когда вы хотите переместить одну таблицу базы данных MySQL в другую (местоположение на) диск, например, освободить дисковое пространство. Оказывается, этот процесс далеко не просто. В этом посте я опишу несколько наших неудачных подходов (поскольку неудачи — отличная возможность для обучения), а также решение Мы в конечном итоге придумали.
Зачем перемещать таблицы на диске?
Некоторое время назад мы получили предупреждение о том, что дисковое пространство на одном из наших серверов на Moxio постепенно работает низко. Мы постоянно отслеживаем наши серверы для факторов, которые могут угрожать нормальной работе и получать первое предупреждение, когда свободное место дискового пространства снизутся ниже 30% от общего размера диска. Таким образом, у нас все еще есть много времени, чтобы действовать на нем до того, как диск на самом деле будет заполнен.
График свободного дискового пространства перед перемещением таблиц
Обычно в таких ситуациях мы могли бы освободить достаточное количество дискового пространства, удаляя старые резервные копии и временные файлы или перемещая определенные каталоги хранения файлов от главного жесткого диска к внешнему массиву хранения. На этот раз почти все дисковое пространство было принято сервером базы данных MySQL. Некоторые таблицы неуклонно растут со временем, едят все больше и больше дискового пространства. Поскольку это были данные, которые мы должны были сохранить, наше единственное устойчивое решение было перемещение этих таблиц на другой диск.
Желаемая ситуация
Наше идеальное решение будет перемещать только большие отдельные таблицы на внешний массив хранения. С таким подходом мы могли бы сохранить другие таблицы в базе данных на (намного быстрее) SSD-диске, не жертвуя производительностью без необходимости. Так что это даже возможно с MySQL?
Исторически MySQL используется для хранения всех данных таблицы и индексов в Системное табличное пространство , представленный одним или несколькими файлы ibdata на диске. Это означает, что данные из нескольких баз данных и таблиц были сохранены в одном файле, что делает невозможным перемещение одного из них в другое место. Тогда MySQL 4.1.1 представил Файл-за столовые табличные пространства Для innodb с innodb_file_per_table Настройка, которая будет хранить данные и индексы для вновь созданных таблиц в отдельном .ibd файл за стол. Этот параметр включен по умолчанию в MySQL 5.6.6.
При использовании табличных пространств Pile-Per-Table можно использовать предложение данных с созданием таблицы на Поместите данные для таблицы за пределами основного каталога данных MySQL как MySQL 5.6. Это означает, что настройка по желанию была бы технически возможной.
Неудачные подходы
В нашем случае мы имели дело с существующими таблицами. Мы попробовали несколько неудачных подходов, чтобы переместить их в другой каталог, прежде чем в конечном итоге найти удовлетворительное решение.
Изменение каталога данных после создания
Поскольку каталог данных может быть указан для изменения местоположения хранения таблицы при создании таблицы, кажется логичным, что также можно было бы изменить это местоположение после создания, используя эту же опцию с ALTER Table. Действительно, альтернатин синтаксически Поддерживает каталог данных как параметр таблицы. Документация явно заявляет, что этот вариант игнорируется (кроме при разделении). Таким образом, кажется невозможным изменить каталог данных для существующей таблицы.
Симчистие файлов данных
Другой подход, который мы придумали, было вручную перемещение файлов данных таблицы на другой диск, симЛриатив их обратно в исходное местоположение внутри основного каталога данных MySQL. В соответствии с документацией, однако, хотя MySQL поддерживает Symlinking Все каталоги базы данных или Индивидуальные таблицы MyIsam , используя символические ссылки на таблицы InnoDB не поддерживается и может вызвать странные проблемы.
Создание копии в нужном месте
Поскольку каталог данных может быть указан только при создании таблицы, мы также пытались создать копию таблицы в нужном месте, копируя все данные со старой таблицы на новую копию и переименовая новую таблицу, чтобы занять место старого один. Это будет выглядеть несколько нравится:
SHOW CREATE TABLE `table_name`;
Это возвращает оператор CREATE TABLE, который создал бы структуру таблицы таблицы_NAME. Теперь мы выполняем это точное утверждение, но заменив новое имя таблицы и добавив предложение каталога данных:
CREATE TABLE `table_name_new` /* ... */ DATA DIRECTORY='/path/to/desired/location';
Теперь у нас есть пустая копия table_name, в схеме, которая есть. Затем мы скопируем все данные из существующей таблицы на новый и выпустите атомное переименование Чтобы поменять две таблицы:
INSERT INTO `table_name_new` SELECT * FROM `table_name`; RENAME TABLE `table_name` TO `table_name_old`, `table_name_new` TO `table_name`;
Здесь мы столкнулись с проблемами, однако. При запуске вставки в … Выберите Query, MySQL не удался с сообщением об ошибке
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
Оказывается, при использовании вставки в … Выбрать, MySQL должен Установить замки на строках читать Из исходной таблицы для обеспечения надлежащей репликации. Эти замки занимают пространство в Буферный бассейн InnoDB Отказ Размер этого бассейна настраивается с использованием innodb_buffer_pool_size. , но установите 128 МБ по умолчанию. Поскольку таблица мы пытаемся переместить, довольно большая (более 50 миллионов строк), MySQL обязан выходить из его пространства для удержания замков.
Даже если бы мы могли достаточно увеличить InnoDB_BUFFER_POOL_SIZE, чтобы сделать эту работу, этот подход довольно неэффективен, когда вы об этом подумаете. При вставке данных в новую копию MySQL должен сериализовать данные на диск и построить индексы для него, что занимает много времени. У нас уже есть сериализованные данные и индексы: они прямо там для оригинальной таблицы! Вместо того, чтобы MySQL воссоздать весь индекс и файл данных с нуля, мы должны искать способ повторно использовать данные таблицы, которые уже есть.
Решение
В конце концов мы нашли решение для плавно движущихся таблиц на диске, основанном на руководстве для копирования табличных пространств на другой экземпляр MySQL В руководстве MySQL Отказ Решение сводится к перемещению существующего табличного пространства на диске, падение и восстановление таблицы с помощью желаемого каталога данных, а затем повторно подключить сохраненное табличное пространство на новую таблицу.
Пошаговый, это выглядит следующим образом. Сначала мы гарантируем, что все данные покрасны от кэшей MySQL и буферов в табличное пространство на диске:
FLUSH TABLES `table_name` FOR EXPORT;
Внутренние столы также блокируют таблицу на протяжении длительности соединения или пока не разблокируем его. Блокировка гарантирует, что данные в таблице не могут измениться, пока мы перемещаем файлы. Чтобы поддерживать эти замки, мы должны откройте соединение MySQL, в котором мы управляли скрытыми столами …. В новом окне терминала мы перемещаем файлы табличных пространств во временное местоположение. В этом случае мы используем наш домашний каталог:
$ mv /var/lib/mysql/database_name/table_name.{ibd,cfg} ~
Теперь возвращаясь к нашему открытому сеансу MySQL, мы можем отпустить блокировки (табличное пространство было безопасно убрано в согласованном состоянии), падение и повторно создать таблицу в желаемом месте:
UNLOCK TABLES; SHOW CREATE TABLE `table_name`; DROP TABLE `table_name`; CREATE TABLE `table_name` /* ... */ DATA DIRECTORY='/path/to/desired/location';
Это также создаст файлы свежего табличного пространства на диске (в нужном месте) для вновь созданной таблицы. Мы не хотим этого (потому что мы хотим вернуть наши старые таблицы таблиц таблиц), поэтому мы отказываемся от этого нового табличного пространства:
ALTER TABLE `table_name` DISCARD TABLESPACE;
В этот момент мы можем скопировать сохраненные исходные файлы таблиц таблиц в место, где теперь MySQL теперь ожидает, что сохраняет собственность и разрешения:
$ cp -a ~/table_name.{ibd,cfg} /path/to/desired/location
Последний шаг — позволить MySQL импортировать исходные файлы таблиц таблиц обратно с диска:
ALTER TABLE `table_name` IMPORT TABLESPACE;
За исключением копирования файлов табличных пространств на другой диск, весь этот процесс очень быстрый, так как MySQL просто принимает оригинальные данные и индексы таблицы и не должны восстановить их.
Реконструировать
После нескольких неудачных попыток (в которых мы много узнали о внутренних органах MySQL), мы в конечном итоге нашли решение для эффективного перемещения таблиц в другое место на диске. Используя этот подход, мы переместили некоторые из наших самых больших и самых быстрорастущих таблиц для внешнего массива хранения, освобождая дисков, необходимое для хранения нашего сервера.
График свободного дискового пространства после перемещения таблиц
Еще одна вещь, которую нужно помнить: если вы знаете, что таблица базы данных станет большим, заранее планируйте ее местоположение на диске. При создании таблицы указание пользовательского местоположения на диске (с использованием каталога данных) прост и сохраняет проблему процесса, описанного в этом сообщении по дороге.
Наслаждался читать этот пост?
Мои коллеги и я регулярно пишем сообщение, связанные с WebDev, связанными со сообщениями, как это, чтобы поделиться тем, что мы изучаем или обнаруживаем. Вы можете найти все наши посты на Moxio Blog Отказ Получить уведомление о новых сообщениях, следуя я или Moxio в Twitter или подписавшись на нашу RSS Feed Отказ
Оригинал: «https://dev.to/moxio/moving-individual-mysql-tables-on-disk-2j0a»