W tym artykule rozważymy metody kompresji i defragmentacji tabel i baz danych w MySQL / MariaDB, które pozwolą ci zaoszczędzić miejsce na dysku z bazy danych.
W dużych projektach z czasem bazy danych stają się ogromne i zawsze pojawia się pytanie, jak sobie z tym poradzić. Istnieje kilka opcji rozwiązania tego problemu. Możesz zmniejszyć ilość danych w samej bazie danych, usuwając stare informacje, dzieląc bazę danych na kilka, zwiększając ilość miejsca na dysku na serwerze lub kompresując tabele.
Kolejnym ważnym aspektem funkcjonowania bazy danych jest potrzeba okresowej defragmentacji tabel i baz danych, co może znacznie przyspieszyć ich pracę.
Treść
- Kompresja i optymalizacja bazy danych z typem tabeli InnoDB
- Kompresuj tabele MyISAM w MySQL
- Optymalizacja tabel i baz danych w MySQL / MariaDB
Kompresja i optymalizacja bazy danych z typem tabeli InnoDB
Pliki Ibdata1 i ib_log
W wielu projektach z tabelami Innodb występuje problem z dużymi rozmiarami plików ibdata1 i ib_log. Przyczyną w większości przypadków jest nieprawidłowe ustawienie serwera MySQL / MariaDB lub architektura bazy danych. Wszystkie informacje z tabel Innodb przechowywane w pliku ibdata1, którego przestrzeń sama w sobie nie jest zwolniona. Wolę przechowywać dane tabeli w osobnych plikach ibd *. Aby to zrobić, w pliku konfiguracyjnym my.cnf dodaj linię:
innodb_file_per_table
lub
innodb_file_per_table = 1
Jeśli Twój serwer jest już skonfigurowany i masz kilka działających baz danych z tabelami Innodb, musisz wykonać następujące czynności:
- Wykonaj kopię zapasową wszystkich baz danych na serwerze (oprócz mysql i performance_schema). Podstawy zrzutu można usunąć za pomocą następującego polecenia:
# mysqldump -u [nazwa użytkownika] -p [hasło] [nazwa_bazy_danych]> [plik zrzutu] - Po utworzeniu kopii zapasowej bazy danych zatrzymaj serwer mysql / mariadb;
- Zmień ustawienia w pliku my.cfg;
- Usuń pliki ibdata1 i ib_log Pliki
- Uruchom serwer mysql / mariadb;
- Przywróć wszystkie bazy danych z kopii zapasowej:
# mysql -u [nazwa użytkownika] -p [hasło] [nazwa_bazy danych] < [dump_file.sql]
Po zakończeniu tej procedury wszystkie tabele Innodb będą przechowywane w osobnych plikach i plikach ibdata1 nie wzrośnie wykładniczo.
Kompresja tabeli InnoDB
Możesz kompresować tabele danymi typu text / BLOB. Jeśli masz podobne tabele, możesz zaoszczędzić sporo miejsca na dysku.
Mam bazę danych innodb_test z tabelami, które można potencjalnie skompresować i zwolnić miejsce na dysku. Przed rozpoczęciem pracy zdecydowanie zalecamy wykonanie kopii zapasowej wszystkich baz danych. Połącz z serwerem mysql:
# mysql -u root -p
W konsoli mysql zaloguj się do żądanej bazy danych:
# use innodb_test;
Aby wyświetlić listę tabel i ich rozmiar, użyj zapytania:
WYBIERZ nazwę_tabeli AS „Tabela”,
OKRĄGŁY (((długość_danych + długość_indeksu) / 1024/1024), 2) AS „Rozmiar w (MB)”
Z Information_schema.TABLES
GDZIE table_schema = "innodb_test"
ORDER BY (długość_danych + długość_indeksu) DESC;
Gdzie innodb_test to nazwa twojej bazy danych.
Prawdopodobnie niektóre tabele można skompresować. Weźmy jako przykład tabelę b_crm_event_relations. Uruchom żądanie:
mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT = COMPRESSED;
Zapytanie OK, dotyczy 0 wierszy (3,27 s) Rekordy: 0 Duplikaty: 0 Ostrzeżenia: 0
Po wykonaniu widać, że ze względu na kompresję rozmiar tabeli zmniejszył się z 26 do 11 MB.
Dzięki kompresji tabeli możesz zaoszczędzić dużo miejsca na dysku na serwerze. Ale podczas pracy ze skompresowanymi tabelami obciążenie procesora wzrośnie. Kompresji tabel należy używać, jeśli nie występują problemy z zasobami procesora, ale występuje problem z miejscem na dysku.
Kompresuj tabele MyISAM w MySQL
Aby skompresować tabele formatów Myisam, musisz użyć specjalnego żądania z konsoli serwera, a nie w konsoli mysql. Aby skompresować żądany stół, wykonaj:
# myisampack -b / var / lib / mysql / test / modx_session
Gdzie / var / lib / mysql / test / modx_session jest ścieżką do twojej tabeli. Niestety nie miałem rozdętej bazy danych i musiałem przeprowadzić kompresję na małych tabelach, ale wynik jest nadal widoczny (plik został skompresowany z 25 do 18 MB):
# du -sh modx_session.MYD
25M modx_session.MYD
# myisampack -b / var / lib / mysql / test / modx_session
Kompresowanie /var/lib/mysql/test/modx_session.MYD: (4933 rekordy) - Obliczanie statystyk - Kompresowanie pliku 29,84% Pamiętaj, aby uruchomić myisamchk -rq na skompresowanych tabelach
# du -sh modx_session.MYD
18M modx_session.MYD
W żądaniu określiliśmy przełącznik -b, gdy jest dodawany, kopia zapasowa tabeli jest tworzona przed kompresją i oznaczana jako OLD:
# ls -la modx_session.OLD
-rw-r ----- 1 mysql mysql 25550000 17 grudnia 15:20 modx_session.OLD
# du -sh modx_session.OLD
25M modx_session.OLD
Optymalizacja tabel i baz danych w MySQL / MariaDB
Aby zoptymalizować tabele i bazy danych, zaleca się przeprowadzenie defragmentacji. Sprawdź, czy w bazie danych znajdują się tabele wymagające defragmentacji.
Wejdźmy do konsoli MySQL, wybierz potrzebną bazę danych i wykonaj zapytanie:
wybierz nazwa_tabeli, okrągły (data_lue / 1024/1024) jako data_length_mb, okrągły (data_free / 1024/1024) jako data_free_mb z Information_schema.tables gdzie round (data_free / 1024/1024)> 50 sortuj według data_free_mb;
W związku z tym wyświetlimy wszystkie tabele, które mają co najmniej 50 MB nieużywanego miejsca:
+-------------------------------+----------------+--------------+ | 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 - całkowity rozmiar tabeli
data_free_mb - nieużywany obszar tabel
Możemy defragmentować te tabele. Sprawdź miejsce na dysku przed:
# ls -lh / var / lib / mysql / innodb_test / | grep b_
-rw-r ----- 1 mysql mysql 402M 17 grudnia 15:43 b_disk_deleted_log_v2.MYD -rw-r ----- 1 mysql mysql 828M 17 grudnia 14:52 b_crm_timeline_bind.MYD -rw-r ----- 1 mysql mysql 981M 17 grudnia 15:45 b_disk_object_path.MYD
Aby zoptymalizować te tabele, użyj następującego polecenia w konsoli mysql:
# TABELA OPTYMALIZACJI b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;
Po udanej defragmentacji powinieneś mieć coś takiego:
+-------------------------------+----------------+--------------+ | 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 |
Jak widać, data_free_mb wynosi teraz 0, a ogólny rozmiar tabeli znacznie się zmniejszył (3-4 razy).
Możesz także przeprowadzić defragmentację za pomocą narzędzia mysqlcheck z konsoli serwera:
# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file
Gdzie innodb_test jest Twoją bazą danych
A b_workflow_file to nazwa żądanej tabeli
Aby zoptymalizować wszystkie potrzebne tabele bazy danych, uruchom polecenie w konsoli serwera:
# mysqlcheck -o innodb_test -u root -p
Gdzie innodb_test to nazwa żądanej bazy danych.
Lub uruchom optymalizację wszystkich baz danych na serwerze:
# mysqlcheck -o - wszystkie bazy danych -u root -p
Jeśli sprawdzisz rozmiar bazy danych przed i po optymalizacji, rozmiar jako całość zmniejszy się:
# du -sh
2,5G
# mysqlcheck -o innodb_test -u root -p
Wpisz hasło: innodb_test.b_admin_notify uwaga: Tabela nie obsługuje optymalizacji, wykonując ponownie + analizuj zamiast statusu: OK innodb_test.b_admin_notify_lang uwaga: Tabela nie obsługuje optymalizacji, wykonując ponownie + analizuj zamiast statusu: OK innodb_test.b_adv_banner Uwaga: Tabela nie obsługuje optymalizuj, wykonuj ponownie + analizuj zamiast stanu: OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
# du -sh
1,7G
Dlatego, aby zaoszczędzić miejsce na serwerze, możesz okresowo optymalizować i kompresować swoje tabele i bazy danych. Powtarzam, przed rozpoczęciem prac optymalizacyjnych, tworzę kopię zapasową bazy danych.
