W poprzednich postach (Różnice pomiędzy MyISAM i InnoDB – wydajność i Co jest szybsze – MyISAM czy InnoDB? ) porównywałem wydajność obu silników. Dziś przyglądniemy się innemu, także ważnemu aspektowi jakim jest bezpieczeństwo danych w trakcie awarii. Administrator MySQL, wybierając silnik bazodanowy, musi uwzględnić to, że awarie się zdarzają. Nawet w najlepiej przetestowanym środowisku produkcyjnym prędzej czy później dojdzie do padu jakiegoś elementu. Systemy operacyjne segfaultują, dyski padają, zasilaniu zdarza się zaniknąć, a UPSy się wyczerpują. Który z silników będzie lepszym wyborem, jeśli chodzi o odporność na tego typu sytuacje?

InnoDB jest silnikiem transakcyjnym, który zapewnia pełny ACID (atomicity, consistency, isolation, durability). Po polsku oznacza to z grubsza tyle, że:
A. zachowana jest ciągłość transakcji – w obrębie jednej transakcji wykonywane są albo wszystkie zapytania, albo żadne. Nie ma takiej możliwości, aby część zapytań została zrealizowana, a część nie – przykładowo, podczas realizowania przelewu jednej osobie odjęto z konta środki a drugiej nie dodano. Co ważne, w przypadku InnoDB, jeśli explicite nie rozpoczniemy transakcji przez BEGIN, to każde pojedyncze zapytanie to jedna transakcja.
C. zachowana jest spójność bazy danych – respektowane są wszelkie więzy integralności (np. ON DELETE CASCADE, czy też ON UPDATE CASCADE)
I. poszczególne transakcje są od siebie izolowane – każda operuje na swojego rodzaju snapshocie zawartości bazy i nie widzi modyfikacji, które zostały wprowadzone do bazy przez inne, wykonywane jednocześnie, ale jeszcze nie zakończone transakcje. InnoDB oferuje kilka poziomów izolacji.
D. zachowane są wszelkie zmiany w bazie, które były efektem poprawnie zakończonej transakcji, nie zależnie co się z bazą stanie po tym fakcie. Udany COMMIT; skutkuje zmianami w bazie, nawet jeśli zaraz po nim serwer padł.

Tyle teoria. W praktyce, z tym pełnym ACIDem bywa różnie, szczególnie jeśli kontroler podsystemu dyskowego, na którym przechowywane są logi transakcji InnoDB, ma zwyczaj udawania flush’a – zgłasza systemowi, że dane są zapisane na dysku podczas gdy w praktyce znajdują się tylko w cache kontrolera (a baterii podtrzymującej ten kontroler nie ma, lub też bateria nie działa). W takiej sytuacji brak zasilania będzie skutkował utratą danych.

Wszelkie informacje o wprowadzonych zmianach przechowywane są przez InnoDB w pamięci, w buforze logu. Dane te są następnie zapisywane do pliku logu transakcji, a później dokonywany jest zapis zmian na dysku (w przypadku systemów typu Linux fakt wykonania zapisu do pliku nie świadczy o tym, że zmiany te zostały fizycznie zapisane na dysku – aby być tego pewnym, pomijając przypadek opisywany paragraf wyżej, konieczne jest wywołanie funkcji systemowej flush() ).

Podczas zapisywania danych na dysku w trybie zgodności z ACID (czyli parametr innodb_flush_log_at_trx_commit ma wartość 1) po każdym COMMIT dane z bufora zapisywane są do logu i wykonywany jest flush(). Przekłada się to na to, że każdy COMMIT to operacja zapisu na dysk, co skutecznie zmniejsza maksymalną liczbę transakcji, którą można wykonać w danej jednostce czasu. W momencie zapisania logu transakcji fizycznie na dysku, dane są bezpieczne. Po pewnym czasie modyfikacje zostaną naniesione na plik z danymi z tabel, a potem usunięte z logu.

Jeśli dojdzie do nagłego przerwania działania serwera MySQL, podczas ponownego uruchomienia wykonywany jest proces odtwarzania zmian w InnoDB. W skrócie ujmując, przeglądany jest log transakcji i wszelkie modyfikacje nanoszone są na tabele. Wszelkie transakcje, które nie zostały zakończone przed awarią zostają cofnięte. Proces ten za każdym razem trwa mniej więcej tyle samo czasu i zależy od wielkości logu transakcji (jego maksymalną wielkość definiuje się w konfiguracji).

MyISAM nie gwarantuje niczego. Jeśli przerwiemy w połowie jakieś duże zapytanie typu UPDATE, to część danych pozostanie zmodyfikowana, a część nie. Nie ma możliwości cofnąć takich modyfikacji. Nie ma wsparcia kluczy obcych, nie ma możliwości nakładania ograniczeń typu ON DELETE czy ON UPDATE. Jeśli dochodzi do awarii serwera, to po ponownym uruchomieniu wykonywana jest procedura naprawy tabel MyISAM. Naprawa taka może trwać długo (osobiście miałem styczność z naprawą kilkugodzinną, ale czytałem także o kilkudniowych), czas zależy od ilości danych w tabeli. Tabele są uszkadzane zazwyczaj wtedy, gdy któraś z nich była modyfikowana w czasie awarii serwera.

Z własnych doświadczeń wiem, że jeśli chodzi o MyISAM, to praktycznie każda awaria serwera wiąże się z koniecznością naprawy części tabel. Proces ten trwa długo i pożera spore zasoby, szczególnie jeśli chodzi o dysk. InnoDB po awarii wstaje szybko i elegancko, chyba że nie wstanie. Jeśli doszło do uszkodzenia logu transakcji, to sprawa jest znacznie poważniejsza i poprawienie tego jest bardzo czasochłonne. Tak bardzo, że czasem szybciej jest przywrócić dane z kopii zapasowej i binlogów. Na szczęście, tego typu sytuacje to ułamek procenta całości – zazwyczaj wszystko odbywa się automatycznie.

Tak jak poprzednio, jeśli któryś z Czytelników ma uwagi czy pytania do powyższego postu, proszę o komentarze.