В тази статия ще разгледаме методите на компресия и дефрагментиране на таблици и бази данни в 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). Dump базите могат да бъдат премахнати със следната команда:
# mysqldump -u [потребителско име] -p [парола] [име на база данни]> [dump_file.sql] - След като архивирате базата данни, спрете сървъра mysql / mariadb;
- Променете настройките във файла my.cfg;
- Изтриване на файлове ibdata1 и ib_log файлове;
- Стартирайте сървъра mysql / mariadb;
- Възстановяване на всички бази данни от архивиране:
# mysql -u [потребителско име] -p [парола] [име на база данни] < [dump_file.sql]
След приключване на тази процедура, всички таблици InnoDB ще се съхраняват в отделни файлове и файл ibdata1 няма да расте експоненциално.
Компресиране на таблицата InnoDB
Можете да компресирате таблици с данни от тип текст / BLOB. Ако имате подобни таблици, можете да спестите доста място на диска.
Имам база данни innodb_test с таблици, които потенциално биха могли да бъдат компресирани и освободени дисково пространство. Преди цялата работа, горещо препоръчвам да архивирате всичките си бази данни. Свържете се с mysql сървър:
# mysql -u root -p
В конзолата mysql влезте в желаната база данни:
# употреба innodb_test;
За да изброите таблиците и техния размер, използвайте заявката:
ИЗБРАНИ_името на таблицата AS "Таблица",
КРЪГ (((data_length + index_length) / 1024/1024), 2) AS "Размер в (MB)"
ОТ информация_schema.TABLES
WHERE table_schema = "innodb_test"
ПОРЪЧАЙТЕ (data_length + index_length) DESC;
Къде innodb_test е името на вашата база данни.
Вероятно е някои таблици да бъдат компресирани. Вземете за пример таблицата b_crm_event_relations. Изпълнете заявката:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Заявка ОК, засегнати 0 реда (3.27 сек) Записи: 0 Дублирания: 0 Предупреждения: 0
След изпълнението можете да видите, че поради компресия размерът на таблицата е намалял от 26 на 11 MB.
Благодарение на компресията на таблицата можете да спестите много дисково пространство на сървъра. Но при работа със компресирани таблици натоварването на процесора ще се увеличи. Компресирането на таблица трябва да се използва, ако нямате проблеми с ресурсите на процесора, но има проблем с дисковото пространство.
Компресирайте таблиците на MyISAM в MySQL
За компресиране на таблици с формати MyISAM, трябва да използвате специална заявка от конзолата на сървъра, а не в конзолата mysql. За да компресирате желаната таблица, направете:
# myisampack -b / var / lib / mysql / test / modx_session
Къде / var / lib / mysql / test / modx_session е пътят към вашата таблица. За съжаление нямах раздута база данни и се наложи да извърша компресия на малки таблици, но резултатът все още се вижда (файлът беше компресиран от 25 до 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Компресиране /var/lib/mysql/test/modx_session.MYD: (4933 записа) - Изчисляваща статистика - Компресиране на файл 29,84% Не забравяйте да стартирате myisamchk -rq на компресирани таблици
# du -sh modx_session.MYD
18M modx_session.MYD
В заявката посочихме превключвателя -b, когато се добави, се създава резервно копие на таблицата преди компресията и се маркира като СТАРО:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 Дек 17 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Оптимизация на таблици и бази данни в MySQL / MariaDB
За оптимизиране на таблици и бази данни се препоръчва да се извърши дефрагментация. Проверете дали в базата данни има таблици, които изискват дефрагментиране.
Нека влезем в конзолата MySQL, изберете базата данни, която ви е необходима и изпълнете заявката:
изберете table_name, round (data_length / 1024/1024) като data_length_mb, кръг (data_free / 1024/1024) като data_free_mb от information_schema.tables, където кръг (data_free / 1024/1024)> 50 поръчате от data_free_mb;
Така ще покажем всички таблици, които имат минимум 50 MB неизползвано пространство:
+-------------------------------+----------------+--------------+ | 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 / | греп b_
-rw-r ----- 1 mysql mysql 402M Dec 17 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M Дек 17 14:52 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 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 - всички-бази данни -u root -p
Ако проверите размера на базата данни преди и след оптимизация, размерът като цяло намаля:
# du -sh
2.5G
# mysqlcheck -o innodb_test -u root -p
Въведете парола: innodb_test.b_admin_notify бележка: Таблицата не поддържа оптимизиране, прави пресъздаване + анализ вместо състояние: ОК innodb_test.b_admin_notify_lang бележка: Таблицата не поддържа оптимизиране, прави пресъздаване + анализ вместо състояние: ОК innodb_test.b_adv_banner бележка: Таблицата не поддържа оптимизиране, правене на пресъздаване + анализ вместо състояние: ОК ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
# du -sh
1.7g
По този начин, за да спестите място на сървъра, можете периодично да оптимизирате и компресирате вашите таблици и бази данни. Повтарям, преди да извършите някаква работа по оптимизация, създайте резервно копие на базата данни.
