MySQL InnoDB nie zwalnia miejsca na dysku po usunięciu wierszy danych z tabeli

140

Mam jedną tabelę MySQL korzystającą z mechanizmu przechowywania InnoDB; zawiera około 2 mln wierszy danych. Kiedy usuwałem wiersze danych z tabeli, nie zwalniało to przydzielonego miejsca na dysku. Również rozmiar pliku ibdata1 nie zmniejszył się po uruchomieniu optimize tablepolecenia.

Czy istnieje sposób na odzyskanie miejsca na dysku z MySQL?

Jestem w złej sytuacji; ta aplikacja działa w około 50 różnych lokalizacjach i obecnie problem braku miejsca na dysku pojawia się prawie we wszystkich z nich.

mysql innodb delete-row

— Sumit Deo
źródło

po uruchomieniu polecenia Optimize również rozmiar pliku ibdata1 nie zmniejszył się.

— Sumit Deo

4

Myślę, że ten komentarz byłby lepiej dodany do Twojej odpowiedzi, a następnie usunięty

— Ben Millwood

możliwy duplikat stackoverflow.com/q/11751792/82114 (ale ten był tutaj pierwszy)

— FlipMcF

1

„Również rozmiar pliku ibdata1 nie zmniejszył się po uruchomieniu polecenia optymalizacji tabeli” , czyli dlatego, że innodb_file_per_tablejest wyłączony. Dobra wiadomość jest taka, że ta opcja jest ondomyślna w najnowszych wersjach MySQL.

— Księgowy م

Uruchamiam „optymalizuj tabelę xxxx” i otrzymuję komunikat „Tabela nie obsługuje optymalizacji, zamiast tego wykonuję odtworzenie + analiza”. Po uruchomieniu "du -h / var / lib / mysql" w powłoce, zauważyłem, że baza danych się skurczyła.

— user1097111

137

MySQL nie zmniejsza rozmiaru ibdata1. Zawsze. Nawet jeśli użyjesz optimize tabledo zwolnienia zajmowanego miejsca z usuniętych rekordów, wykorzysta je później.

Alternatywą jest skonfigurowanie serwera do użycia innodb_file_per_table, ale będzie to wymagało utworzenia kopii zapasowej, usunięcia bazy danych i przywrócenia. Pozytywną stroną jest to, że plik .ibd tabeli jest zmniejszany po rozszerzeniu optimize table.

— Leonel Martins
źródło

4

MySQL 5.5 docs o stanie trybu InnoDB plik na tabelę „Aby skorzystać z funkcji [InnoDB plik na tabelę] dla istniejącej tabeli, możesz włączyć ustawienie pliku na tabelę i uruchomić ALTER TABLE t ENGINE=INNODBna istniejącej tabeli. " Oznacza to, że można włączyć tę funkcję, „przekonwertować” istniejące tabele na osobny plik InnoDB za pomocą polecenia ALTER TABLE, a następnie ZOPTYMALIZOWAĆ tabelę, aby zmniejszyć jej rozmiar. Jednak kiedy skończysz, musisz dowiedzieć się, jak usunąć (ogromny) źródłowy plik InnoDB ...

— Josh

9

Wydaje mi się, że to technicznie odpowiada na pytanie, ale spodziewałbym się, że większość osób poszukujących tego tematu szuka faktycznego procesu zmniejszania / odzyskiwania przestrzeni, czego ta odpowiedź nie zapewnia.

— Manachi,

@Manachi Proces polega na „skonfigurowaniu serwera do korzystania z innodb_file_per_table”, „utworzeniu kopii zapasowej” serwera, „usunięciu bazy danych”, zatrzymaniu mysql, usunięciu pliku .ibd, uruchomieniu serwera i przywróceniu bazy danych. Z MySQL 5.5+ możesz skorzystać z tego, co powiedział Josh, a po zmianie wszystkich tabel zatrzymać serwer, usunąć ogromny plik .ibd i uruchomić go ponownie.

— Leonel Martins,

39

Po prostu miałem ten sam problem.

Dzieje się tak, że nawet jeśli upuścisz bazę danych, innodb nadal nie zwolni miejsca na dysku. Musiałem wyeksportować, zatrzymać mysql, usunąć pliki ręcznie, uruchomić mysql, utworzyć bazę danych i użytkowników, a następnie zaimportować. Dzięki Bogu miałem tylko 200 MB wierszy, ale zaoszczędziłem 250 GB pliku innodb.

Niepowodzenie z założenia.

— gilm
źródło

10

tak, to zdecydowanie porażka.

— trusktr

1

MySql 5.5 ma ten sam problem: uruchomiłem "optymalizację tabeli", aby zmniejszyć użycie dysku w tabeli 28 GB. Operacja prawdopodobnie próbowała utworzyć zoptymalizowany klon oryginalnej, w związku z czym zajęła całe miejsce na partycji. Teraz "optymalizacja tabeli" nie powiodła się i nie mam miejsca na partycji, nawet po usunięciu całej bazy danych ... bardzo rozczarowujące.

— basilikode

1

Ponad 4 lata później napotkałem ten sam problem z MySQL. MS SQL jest podobny: dba.stackexchange.com/questions/47310/ ...

— Csaba Toth

24

Jeśli nie używasz innodb_file_per_table , odzyskanie miejsca na dysku jest możliwe, ale dość żmudne i wymaga znacznej ilości przestojów.

Jak jest dość dogłębne - ale wklejony odpowiednią część poniżej.

Pamiętaj, aby zachować również kopię schematu w swoim zrzucie.

Obecnie nie można usunąć pliku danych z systemowego obszaru tabel. Aby zmniejszyć rozmiar systemowego obszaru tabel, użyj następującej procedury:

Użyj mysqldump, aby zrzucić wszystkie swoje tabele InnoDB.

Zatrzymaj serwer.

Usuń wszystkie istniejące pliki obszarów tabel, w tym pliki ibdata i ib_log. Jeśli chcesz zachować kopię zapasową informacji, skopiuj wszystkie pliki ib * do innej lokalizacji przed usunięciem plików z instalacji MySQL.

Usuń wszystkie pliki .frm dla tabel InnoDB.

Skonfiguruj nowy obszar tabel.

Zrestartuj serwer.

Zaimportuj pliki zrzutu.

— FlipMcF
źródło

Dziękujemy za dołączenie instrukcji - aby link „jak” nie zawiera już tych informacji

— aland

3

Dziesięć lat później miałem ten sam problem. Rozwiązałem to w następujący sposób:

Zoptymalizowałem wszystkie bazy danych.
Zrestartowałem komputer i MySQL w usługach (Windows + r -> services.msc)

To wszystko :)

— Matheus Douglas
źródło

1

Innym sposobem rozwiązania problemu odzyskiwania miejsca jest utworzenie wielu partycji w ramach partycji opartych na tabelach i wartościach i po prostu upuszczenie / obcięcie partycji, aby odzyskać miejsce, co zwolni miejsce używane przez całe dane przechowywane w określonej partycji.

Będą potrzebne pewne zmiany w schemacie tabeli, gdy wprowadzisz partycjonowanie tabeli, takie jak - unikalne klucze, indeksy, aby uwzględnić kolumnę partycji itp.

— Anand Immannavar
źródło

0

Rok temu napotkałem ten sam problem w wersji mysql5.7, a ibdata1 zajmował 150 GB. więc dodałem cofnij obszary tabel

Wykonaj kopię zapasową Mysqldump
Zatrzymaj usługę mysql
Usuń wszystkie dane z katalogu danych
Dodaj poniżej parametr cofnij przestrzeń tabel w bieżącym my.cnf

 #undo tablespace
  innodb_undo_directory =  /var/lib/mysql/
  innodb_rollback_segments = 128 
  innodb_undo_tablespaces = 3
  innodb_undo_logs = 128  
  innodb_max_undo_log_size=1G
  innodb_undo_log_truncate = ON

Uruchom mysql service
store mysqldump backup

Problem rozwiązany !!

— Gajendra
źródło

-1

Istnieje kilka sposobów na odzyskanie miejsca na dysku po usunięciu danych z tabeli dla silnika MySQL Inodb

Jeśli nie używasz innodb_file_per_table od początku, zrzucenie wszystkich danych, usunięcie całego pliku, ponowne utworzenie bazy danych i ponowne zaimportowanie danych jest jedyną drogą (sprawdź odpowiedzi FlipMcF powyżej)

Jeśli używasz innodb_file_per_table, możesz spróbować

Jeśli możesz usunąć wszystkie dane, polecenie obcięcia danych spowoduje usunięcie danych i odzyskanie miejsca na dysku.
Polecenie Alter table usunie i ponownie utworzy tabelę, aby mogła odzyskać miejsce na dysku. Dlatego po usunięciu danych uruchom alter table, które nic nie zmieniają, aby zwolnić dysk twardy (tj: tabela TBL_A ma zestaw znaków uf8, po usunięciu danych uruchom ALTER TABLE TBL_A charset utf8 -> to polecenie nie zmienia nic z tabeli, ale sprawia, że mysql ponownie tworzy tabelę i odzyskuje miejsca na dysku
Utwórz TBL_B jak TBL_A. Wstaw wybrane dane, które chcesz zachować z TBL_A do TBL_B. Usuń TBL_A i zmień nazwę TBL_B na TBL_A. Ten sposób jest bardzo skuteczny, jeśli TBL_A i dane, które trzeba usunąć, są duże (polecenie delete w MySQL innodb ma bardzo złą wydajność)

— Bùi Đức Khánh
źródło