В этой статье мы будем исследовать некоторые методы сжатия таблицы / базы данных и дефрагментации в 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
Вы можете сжимать таблицы с текстовыми данными / данными 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;
После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.
Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.
Сжатие таблиц MyISAM в MySQL / MariDB
Для сжатия таблиц 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 17 октября, 12:12 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17 октября 13:23 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17 октября, 11:54 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,5 г
# mysqlcheck -o innodb_test -u root -p
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,7 г
Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.
