В данной статье мы рассмотрим методики сжатия и дефрагментации таблиц и баз данных в MySQL/MariaDB, которые позволят вам сэкономить место на диске с БД.
В крупных проектах со временем базы данных разрастаются до огромных размеров и всегда возникает вопрос, как же с этим бороться. Есть несколько вариантов для решения подобной проблемы. Вы можете уменьшить количество данных в самой базе, путем удаления старой информации, разделить базу на несколько, увеличить объем дискового пространства на сервере или сжать таблицы.
Другой важный аспект функционирование БД – необходимость периодической дефрагментации таблиц и баз данных, что позволяет существенно ускорить их работу.
Сжатие и оптимизация БД с типом таблиц InnoDB
Файлы ibdata1 и ib_log
На многих проектах с таблицами InnoDB встречается проблема с огромными размерами файлов ibdata1 и ib_log. Причина в большинвсте случае связан с неправильными настройками сервера MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого не высвобождается само по себе. Я предпочитаю хранить данные таблиц в отдельных файлах ibd*. Для этого нужно в конфигурационном файле my.cnf добавить строку:
innodb_file_per_table
или
innodb_file_per_table=1
Если же ваш сервер уже настроен и у вас есть несколько рабочих БД с таблицами InnoDB, нужно выполнить следующее:
- Сделайте бэкап всех БД на своем сервере (кроме mysql и performance_schema). Дамп баз можно снять следующей командой:
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
- После создания резервной копии БД остановите сервер mysql/mariadb;
- Измените настройки в файле my.cfg;
- Удалите файлы ibdata1 и ib_log файлы;
- Запустите сервер mysql/mariadb;
- Восстановите из бэкапа все БД:
# mysql -u [username] –p[password] [database_name] < [dump_file.sql]
После выполнения этой процедуры, все таблицы InnoDB будут хранится в отдельных файлах и файл ibdata1 не будет расти в геометрической прогрессии.
Сжатие таблиц InnoDB
Вы можете сжимать таблицы с данными типа text/BLOB. Если у вас есть подобные таблицы, вы можете сэкономить довольном много дискового пространства.
У меня имеется БД innodb_test с таблицами, которые потенциально можно сжать и высвободить дисковое пространство. Перед всеми работами я настоятельно рекомендую выполнить резервное копирование всех ваших БД. Подключаемся к серверу mysql:
# mysql -u root -p
В консоли mysql авторизуемся в нужной БД:
# use innodb_test;
Чтобы вывести список таблиц и их размер, используйте запрос:
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;
Где innodb_test — это имя вашей БД.
Есть вероятность, что некоторые таблицы можно сжать. Возьмём для примера таблицу b_crm_event_relations. Выполните запрос:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (3.27 sec) Records: 0 Duplicates: 0 Warnings: 0
После выполнения, можно увидеть что за счет сжатия размер таблицы уменьшился с 26 до 11 Мб.
Благодаря сжатию таблиц вы можете сэкономить много дискового пространства на сервере. Но при работе со сжатыми таблицами вырастет нагрузка на процессор. Сжатие в таблицах нужно использовать, если у вас нет проблем с процессорными ресурсами, но есть проблема с местом на диске.
Сжатие таблиц MyISAM в MySQL
Для сжатия таблиц формата Myisam, нужно использовать специальный запрос с консоли сервера, а не в консоли mysql. Чтобы сжать нужную таблицу выполните:
# myisampack -b /var/lib/mysql/test/modx_session
Где /var/lib/mysql/test/modx_session — путь до вашей таблицы. К сожалению, у меня не было раздутой БД и пришлось выполнять сжатие на небольших таблицах, но результат все равно виден (файл сжался с 25 до 18 Мб):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b /var/lib/mysql/test/modx_session
Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records) - Calculating statistics - Compressing file 29.84% Remember to run myisamchk -rq on compressed tables
# du -sh modx_session.MYD
18M modx_session.MYD
В запросе, мы указали ключ -b, при его добавлении, перед сжатием создается бэкап таблицы и помечается как OLD:
# ls -la modx_session.OLD
-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Оптимизация таблиц и баз данных в MySQL/MariaDB
Для отптимизации таблиц и базы данных рекомендуется выполнять дефрагментацию. Проверим, есть ли в базе данных таблицы, которые требуют дефрагментации.
Войдем в консоль MySQL, выберем нужную БД и выполним запрос:
select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;
Таким образом мы выведем все таблицы, которые имеют минимум 50 Мб неиспользуемого пространства:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 402 | 64 | | b_crm_timeline_bind | 827 | 150 | | b_disk_object_path | 980 | 72 |
data_length_mb — общий размер таблицы
data_free_mb — неиспользуемое пространство таблицы
Эти таблицы мы можем дефрагментировать. Проверим занимаемое место на диске до:
# ls -lh /var/lib/mysql/innodb_test/ | grep b_
-rw-r----- 1 mysql mysql 402M Dec 17 15:43 b_disk_deleted_log_v2.MYD -rw-r----- 1 mysql mysql 828M Dec 17 14:52 b_crm_timeline_bind.MYD -rw-r----- 1 mysql mysql 981M Dec 17 15:45 b_disk_object_path.MYD
Чтобы оптимизировать эти таблицы, используйте следующую команду в консоли mysql:
# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
После успешной дефрагментации, у вас должен быть примерно такой вывод результата:
+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 74 | 0 | | b_crm_timeline_bind | 115 | 0 | | b_disk_object_path | 201 | 0 |
Как видите, data_free_mb теперь равен 0 и в целом размеры таблицы значительно уменьшились (в 3-4 раза).
Также можно выполнить дефрагментацию с помощью утилиты mysqlcheck из консоли сервера:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Где innodb_test — это ваша БД
А b_workflow_file — имя нужной таблицы
Чтобы оптимизировать все таблицы нужной вам БД, запустите команду в консоли сервера:
# mysqlcheck -o innodb_test -u root -p
Где innodb_test — имя желаемой БД.
Или запустите оптимизацию всех БД на сервере:
# mysqlcheck -o --all-databases -u root -p
Если проверить размеры базы до и после оптимизации, то размер в целом уменьшился:
# du -sh
2.5G
# mysqlcheck -o innodb_test -u root -p
Enter password: innodb_test.b_admin_notify note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_admin_notify_lang note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_adv_banner note : Table does not support optimize, doing recreate + analyze instead status : OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# du -sh
1.7G
Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.
SSL cертификат на сайте кончился. 🙁
Ругаются FF и Chrome.
Дошел до пункта
Удалите файлы ibdata1 и ib_log файлы;
потом mysql не стартует и ругается на отсутствие ibdata1
какого хера выкладываешь в инет советы которые либо не проверял либо с ошибками…. пиздец какой
Добрый день. Вероятно вы не выполнили все действия описанные выше, нужно выполнить резервное копирование БД, добавить строку innodb_file_per_table в конфигурационный файл my.cnf, чтобы таблицы innodb хранились в самой базе пользователя, а не в файле ibdata1, после чего нужно удалить лог файлы и сам ibdata1, mysql при правильной настройке должен запуститься без ошибок. Перед размещением инструкции, все обкатывается на тестовом стенде. Так же хочу заметить, какая бы не была инструкция, всегда нужно выполнять резервную копию, так как можно в процессе выполнения, допустить ошибку и после этого уже не разобраться где вы ее допустили.
Подскажите, пожалуйста, у меня mysql используется под заббикс и съедает все 300Гб выделенного места именно БД.
Поможет ли мне изменение конфигурации для хранения данных таблиц в отдельных файлах ibd* и изменения в конфигурационном файле my.cnf . Можно ли переполнении БД, потом просто удалить файл ibd1, место освободиться, mysql создаст следующий файл ibd3 и все будет работать по кругу. Или я кардинально ошибаюсь?
Ни в коем случае не трогайте глобальные файлы idbdata* не сделав бекап. Статья описывает ситуацию возникающую в основном со старыми инсталляциями mysql и в ней отдельно упоминается необходимость делать бекап.
Можно попробовать сжать только таблицу history_str или в новых версиях zabbix пытается стать хранилищем логов. В остальных случаях — скорее нет.
Не хватает информирования что сжатие подразумевает преобразование в таблицу только для чтения
Неверно. Это только в случае использования myisam.
Почему разработчики mysql не сделали по умолчанию innodb_file_per_table=1 ??
Они любители больших пухнущих файлов?
Неверно.
Если вы не используете какой-нибудь покрытый пылью Centos или XAMPP, то не включено.
Эта переменная изменилась уже примерно в mysql 5.6 — 9 лет назад.