Сжатие и дефрагментация базы данных в 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, нужно выполнить следующее:

1. Сделайте бэкап всех БД на своем сервере (кроме mysql и performance_schema). Дамп баз можно снять следующей командой:
   # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. После создания резервной копии БД остановите сервер mysql/mariadb;
3. Измените настройки в файле my.cfg;
4. Удалите файлы ibdata1 и ib_log файлы;
5. Запустите сервер mysql/mariadb;
6. Восстановите из бэкапа все БД:
   # 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

Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.