Witam drogich Czytelników w jubileuszowym, setnym poście na blogu. Dziś parę słów o rzeczy, która wydawałaby się niczym tajemniczym. W ostatnim czasie miałem jednak okazję kilka razy się przekonać, że sprawa nie jest tak oczywista, jakby się to wydawało. Chodzi konkretnie o sposób, w jaki InnoDB zachowuje się w trakcie kasowania danych.

O co chodzi? Sytuacja jest następująca – kończy się powoli miejsce na dysku, trzeba usunąć jakieś dane. Kasujemy część rekordów z tabeli dzałającej w oparciu o silnik InnoDB. Ilość rekordów zmniejszyła się, ale co się okazuje? Plik tablespace zajmuje dokładnie tyle samo miejsca co poprzednio. Nie da się odzyskać miejsca?

Cóż, to zależy. Dać się da, choć nie zawsze jest to łatwe i przyjemne. Ogólnie, zasada jest taka, że InnoDB nie zmniejsza pliku tablespace. Plik taki może tylko rosnąć. Usuwana jest natomiast jego zawartość. Zobaczmy to na przykładzie. Tworzymy tabelę i wrzucamy tam 500 tysięcy rekordów.

mysql> CREATE TABLE tabback LIKE tab;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tabback SELECT * FROM tab LIMIT 1000000;
Query OK, 1000000 rows affected (29.97 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

Sprawdźmy jej status i jak wygląda to na dysku:

mysql> SHOW TABLE STATUS LIKE 'tabback'\G
*************************** 1. row ***************************
Name: tabback
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1015689
Avg_row_length: 136
Data_length: 139100160
Max_data_length: 0
Index_length: 212615168
Data_free: 5242880
Auto_increment: 1000001
Create_time: 2011-11-10 03:03:02
Update_time: NULL
Check_time: NULL
Collation: utf8_polish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
ls -al | grep tabback
-rw-rw---- 1 mysql mysql        8644 2011-11-10 03:03 tabback.frm
-rw-rw---- 1 mysql mysql   364904448 2011-11-10 03:03 tabback.ibd

Jak widać, na dysku tabela zajmuje ok. 360MB, co mniej więcej przekłada się na wielkość indeksu i ilość danych, którą widać w statusie tabeli. Co ważne, na ten moment w tablespace mamy ok. 5 megabajtów wolnego miejsca. Usuńmy teraz wszystkie dane z tej tabeli i sprawdźmy co się stanie:

mysql> DELETE FROM tabback;
Query OK, 1000000 rows affected (15.81 sec)

mysql> SHOW TABLE STATUS LIKE 'tabback'\G
*************************** 1. row ***************************
Name: tabback
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 32768
Data_free: 356515840
Auto_increment: 1000001
Create_time: 2011-11-10 03:03:02
Update_time: NULL
Check_time: NULL
Collation: utf8_polish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ls -al | grep tabback
-rw-rw---- 1 mysql mysql        8644 2011-11-10 03:03 tabback.frm
-rw-rw---- 1 mysql mysql   364904448 2011-11-10 03:09 tabback.ibd

Widzimy, że wielkość pliku na dysku pozostała bez zmian, natomiast InnoDB twierdzi, że tablespace jest puste – Data_free to 356515840 bajtów. Co ciekawe, zawartość tabeli nie jest usuwana od razu po zakończeniu DELETE. Przez kilkanaście sekund na kolejnych SHOW TABLE STATUS będziemy widzieć jak zmienia się zajętość tablespace.

Ok, to w jaki sposób możemy odzyskać miejsce? To zależy. Jeśli MySQL jest skonfigurowany z innodb_file_per_table, tak jak w powyższym przypadku, to można uruchomić np. OPTIMIZE TABLE bądź też jakąkolwiek inną operację, która będzie skutkowała przebudową struktury tabeli. Może to też być zwykłe ALTER TABLE tabela ENGINE=InnoDB; które nic nie zmienia, ale tabelę przebudowuje. Oczywiście, taka opcja jest możliwa tylko wtedy, gdy mamy jeszcze na tyle miejsca aby tabelę przebudować – podczas tej procedury tworzona jest tabela tymczasowa. Podczas całej takiej procedury przebudowywania tabeli dostęp do danych jest zapewniony.

Jeśli nie mamy już miejsca aby stworzyć tabelę tymczasową, to pozostaje opcja wymagająca przerwania działania tabeli – dump i restore. Zrzucamy dane z tabeli, dropujemy ją i wgrywamy dane ponownie. Plik tablespace zostanie utworzony od zera i zajmie tyle miejsca, ile jest niezbędne aby pomieścić wszystkie dane. Czyli mniej niż miał, no bo przecież część danych pokasowaliśmy.

Sprawdźmy, czy przebudowa tabeli faktycznie rozwiązuje problem:

mysql> ALTER TABLE tabback ENGINE=InnoDB;
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'tabback'\G
*************************** 1. row ***************************
Name: tabback
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 32768
Data_free: 0
Auto_increment: 1000001
Create_time: 2011-11-10 03:28:53
Update_time: NULL
Check_time: NULL
Collation: utf8_polish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ls -al | grep tabback
-rw-rw---- 1 mysql mysql        8644 2011-11-10 03:28 tabback.frm
-rw-rw---- 1 mysql mysql      131072 2011-11-10 03:28 tabback.ibd

Jak widać, tak.

Znacznie poważniejszy problem mamy w momencie, gdy tablespace jest współdzielony przez różne tabele. W takiej sytuacji przebudowa czy zdropowanie tabeli nic nam nie da – taka operacja nie skutkuje przebudową pliku tablespace. Jeśli jesteśmy w takiej sytuacji, to jedynym rozwiązaniem jest po prostu zrzucenie kopii wszystkich baz danych, usunięcie ich z serwera, wyłączenie serwera MySQL, usunięcie plików tablespace, włączenie ponowne serwera (podczas startu pliki tablespace zostaną utworzone od zera) i wgranie wszystkich danych. Jeśli już jesteśmy w takiej sytuacji, to dobrze przy okazji włączyć opcję tworzenia tablespace per tabela – nie będziemy musieli już więcej powtarzać tej procedury.

W tej kwestii InnoDB zachowuje się zupełnie inaczej niż MyISAM. To może powodować zdziwienie u administratorów, którzy do tej pory nie mieli większej styczności z InnoDB. Takich osób będzie zaś przybywać, jako że obecnie to właśnie InnoDB jest domyślnym silnikiem. Mam nadzieję, że udało mi się wyjaśnić jak oswoić InnoDB przynajmniej w tym zakresie.