У даній статті ми розглянемо методики стиснення і дефрагментації таблиць і баз даних в MySQL / MariaDB, які дозволять вам заощадити місце на диску з БД.
У великих проектах з часом бази даних розростаються до величезних розмірів і завжди виникає питання, як же з цим боротися. Є кілька варіантів для вирішення подібної проблеми. Ви можете зменшити кількість даних в самій базі, шляхом видалення старої інформації, розділити базу на кілька, збільшити обсяг дискового простору на сервері або стиснути таблиці.
Інший важливий аспект функціонування БД - необхідність періодичної дефрагментації таблиць і баз даних, що дозволяє істотно прискорити їх роботу.
зміст:
- Стиснення і оптимізація БД з типом таблиць InnoDB
- Стиснення таблиць MyISAM в 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, потрібно виконати наступне:
- Зробіть бекап всіх БД на своєму сервері (крім 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
Ви можете стискати таблиці з даними типу 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
Таким чином для економії місця на сервері, ви можете періодично оптимізувати і стискати ваші таблиці та БД. Повторюся, перед проведенням будь-яких робіт з оптимізації, створюйте резервну копію БД.
