Сжатие, дефрагментация и оптимизация базы данных MariaDB/MySQL

В этой статье мы будем исследовать некоторые методы сжатия таблицы / базы данных и дефрагментации в 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] < [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. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.

Насколько публикация полезна?

Нажмите на звезду, чтобы оценить!

Средняя оценка / 5. Количество оценок:

Оценок пока нет. Поставьте оценку первым.