Który silnik bazodanowy wybrać podczas tworzenia nowej bazy danych? To pytanie pada bardzo często na etapie projektowania nowej aplikacji czy systemu, który będzie korzystał z bazy MySQL.
Do wyboru zazwyczaj są dwa silniki: MyISAM oraz InnoDB. Pierwszy z nich jest domyślnym mechanizmem składowania danych w starszych seriach MySQL. Dane zapisywane są w plikach (frm - definicja tabeli, .MYD (MYData) - plik z danymi, .MYI (MYIndex) - indeksy tabeli). Drugim silnikiem (który jest domyślny od MySQL 5.5) jest InnoDB. Posiada on takie mechanizmy jak obsługa transakcji czy klucze obce.
Przed wybraniem silnika należy rozważyć jakie wady i zalety prezentuje każdy z nich. Inaczej na bazę danych będzie patrzył klient, któremu zależy na jej wydajności, a inaczej programista czy administrator, który będzie martwił się o kopie zapasowe danych czy stabilność całego serwera.
Zawsze uważano silnik MyISAM jako taki, który szybciej wykonuje zapytania SELECT, prościej się nim zarządza, wykonuje kopie zapasowe czy odtwarza dane. Natomiast InnoDB uważany był za wolniejszy, jednak wspierający transakcje i klucze obce.
Jak wpływa na dane awaria serwera?
Nie ma systemów idealnych i czasem zdarzają się usterki lub większe awarie. Nawet najlepiej zabezpieczone systemy lubią się psuć, czy to z powodu wyciągniętej wtyczki zasilania czy poważniejszych problemów. Jak z awariami radzi sobie InnoDB a jak to robi MyISAM? Który z nich lepiej radzi sobie z awariami i czy po uszkodzeniu serwera będziemy w stanie odzyskać dane lub chociaż ich część?
Zacznijmy od silnika MyISAM. Nie posiada on obsługi transakcji, dlatego może się zdarzyć że podczas awarii serwera część danych zostanie w bazie dopisana, skasowana lub zmieniona a część nie. Wyobraźmy sobie, że podczas wykonywania zapytania typu UPDATE doszło do wyłączenia serwera. Podczas startowania serwera, uruchomi się proces naprawy tabel, który może trwać bardzo długo w przypadku obszernych baz danych. Może się zdarzyć, że będzie to trwała nawet kilka godzin. W tym czasie będzie bardzo obciążony dysk oraz procesor serwera. Następnie uruchomi się nam baza, z częściowo zmienioną zawartością rekordów. Dane w bazie będą niespójne.
A jak to wygląda w przypadku InnoDB? Zazwyczaj po awarii serwer wstanie bardzo szybko i elegancko, chyba że zostanie uszkodzony plik logu transakcji. Wtedy sytuacja się troszkę kompilikuje. Silnik InnoDB jest silnikiem transakcyjnym, który wspiera ACID. ACID jest skrótem od angielskich słów: atomicity - atomowość, consistency - spójność, isolation - izolacja, durability - trwałość.
Atomowość transakcji oznacza, że albo wykonujemy ją w całości albo wcale. Nie może dojść do sytuacji, w której wykona się część zapytań jak w przypadku MyISAM. Jeżeli chodzi o spójność oznacza to, że po wykonaniu transakcji system będzie spójny, czyli nie zostaną naruszone żadne zasady integralności. Izolacja transakcji oznacza, iż jeżeli dwie transakcje wykonują się współbieżnie, to zazwyczaj (zależnie od poziomu izolacji) nie widzą zmian przez siebie wprowadzanych. Trwałość danych oznacza, że system potrafi uruchomić się i udostępnić spójne, nienaruszone i aktualne dane zapisane w ramach zatwierdzonych transakcji, na przykład po nagłej awarii zasilania.
Wszystko wygląda pięknie, ale jak jest naprawdę? Domyślnie silnik powinien działać w pełnym trybie zgodności z ACID (parametr innodb_flush_log_at_trx_commit ma wartość 1), czyli po każdym poleceniu COMMIT, dane z buforów zapisywane są na dysku i wywoływana jest funkcja flush(). Niestety taka opcja zmniejsza maksymalną ilość obsługiwanych transakcji, oraz wpływa na wydajność bazy. Jednak możemy być pewnie, że dane są zapisywane na dysku.
Jeżeli parametr ten przyjmuje wartość 0, dane z buforów są zapisywane raz na sekundę do pliku logów. W przypadku wartości 2, dane z buforów są zapisywane po każdym commicie, lecz nie jest wykonywany flush. Funkcja flush wykonywana jest raz na sekundę. Nie daje to jednak 100% pewności, że nie utracimy danych. Aby uzyskać maksymalną trwałość i spójność danych, można w pliku my.cnf ustawić opcje: innodb_flush_log_at_trx_commit = 1 i sync_binlog = 1.
Cytat : Uwaga Wiele systemów operacyjnych oraz kontrolerów dysków twardych udaje operacje flush(). Zgłaszają one informację, że dane zostały zapisane na dysku, podczas gdy dalej znajdują się w buforach urządzenia. Jeżeli dojdzie do przerwy w zasilaniu możemy stracić dane (o ile kontroler nie posiada podtrzymania zasilania przez baterie). W systemie Linux, można wyłączyć cachowanie danych za pomocą polecenia hdparm -W0 /dev/sda
A co jeśli już dojdzie do uszkodzenia serwera z silnikiem InnoDB? Podczas uruchamiania serwera jest przeglądany log transakcji. Wszystkie operacje jakie nie zostały zakończone commitem są cofane, a pozostałe nanoszone na tabele. Proces ten trwa zazwyczaj bardzo krótko. Z takiego mechanizmu korzysta równeiż oprogramowanie Percona XtraBackup.
Ukryj panel
