Бывают ситуации, когда файлы базы данных занимают практически все место на сервере и при попытке удалить не нужные данные из таблицы, место не освобождается. Это обусловлено тем, что логически размер БД может как уменьшаться так и увеличиваться, при этом физический файл данных на диске только растет.
Объекты базы данных, такие как таблицы и индексы, хранятся в табличных пространствах, так называемых логических структурах, а физически это файл в ОС на диске.
Дефрагментацией базы данных MySQL называют процесс, который позволяет уменьшить размер физического файла на диске, за счет освобождения не используемого логического пространства.
Есть несколько вариантов дефрагментации баз данных в том числе и таблиц INNODB в MySQL:
Первый вариант так называемых бэкап-восстановление:
- выполняется резервное копирование базы данных с помощью mysqldump
- удаление БД, drop database
- создание новой БД с таким же именем
- восстановление из бэкапа данные
Данный вариант не плох, когда у вас не большая база, что же делать если размер БД, огромный? Использовать второй метод.
Выполнить дефрагментацию базы данных MySQL.
По умолчанию MyIsam применима процедура дефрагментации, а вот для InnoDB должна быть включена опция innodb_file_per_table, после чего мы с легкостью можем использовать данную команду
OPTIMIZE TABLE <table_name>
Данная команда позволяет реорганизовать не только логическое, но и физичекое место на диске. Кроме уменьшения размера, занимаемого файла на диске, данная операция позволяет улучшить скорость ввода/вывода, что позволяет также оптимизировать работу базы данных MySQL.
OPTIMIZE TABLE, рекомендовано использовать при удалении, обновлении и добавлении большого количества записей в таблицы баз.
Во время выполнения процедуры оптимизации, таблица будет заблокирована.
Третий вариант использовать команду mysqlcheck, можно использовать с такими параметрами
mysqlcheck -A -o
Данная команда позволит дефрагментировать все таблицы в БД. Например, выполнив данную команду после крупного удаления данных из таблицы, получили следующие результаты:
Размер одной таблицы в базе ДО — 212,6 Гб. ПОСЛЕ — 113,8 Гб.
Нужна помощь в поддержке баз данных и оптимизации производительности сервера, обращайтесь [email protected]