Сжатие и дефрагментация базы данных в MySQL и MariaDB
В данной статье мы рассмотрим методики сжатия и дефрагментации таблиц и баз данных в 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]
После выполнения этой процедуры, все таблицы 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
Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.
Qiziqarli malumotlar
Сжатие и дефрагментация базы данных в MySQL и MariaDB