У даній статті ми розглянемо методики стиснення і дефрагментації таблиць і баз даних в 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

Ви можете стискати таблиці з даними типу text / BLOB. Якщо у вас є подібні таблиці, ви можете заощадити задоволеному багато дискового простору.

У мене є БД innodb_test з таблицями, які потенційно можна стиснути і вивільнити дисковий простір. Перед початком всіх робіт я настійно рекомендую виконати резервне копіювання всіх ваших БД. Підключаємося до сервера mysql:

# mysql -u root -p

В консолі mysql авторізуемся в потрібній БД:

# use innodb_test;

підключення до бази даних mqsql

Щоб вивести список таблиць і їх розмір, використовуйте запит:

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 – це ім’я вашої БД.

розмір таблиць на диску в БД mysql / mariadb

Є ймовірність, що деякі таблиці можна стиснути. Візьмемо для прикладу таблицю 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 Мб.

стиснення таблиць mysql ALTER TABLE ROW_FORMAT = COMPRESSED

Завдяки стиску таблиць ви можете заощадити багато дискового простору на сервері. Але при роботі із стисненими таблицями виросте навантаження на процесор. Стиснення в таблицях потрібно використовувати, якщо у вас немає проблем з процесорними ресурсами, але є проблема з місцем на диску.

стиснення таблиць MyISAM в MySQL

Для стиснення таблиць формату Майсам, Потрібно використовувати спеціальний запит з консолі сервера, а не в консолі 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

стиснення файлів бази даних за допомогою myisampack

Оптимізація таблиць і баз даних в 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;

mysql OPTIMIZE TABLE з консолі

Після успішної дефрагментації, у вас повинен бути приблизно такий висновок результату:

+-------------------------------+----------------+--------------+
| 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 - утиліта дефрагментації таблиць innodb в mysql

Щоб оптимізувати всі таблиці потрібної вам БД, запустіть команду в консолі сервера:

# 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

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

Leave a Reply

Your email address will not be published. Required fields are marked *