Aktualizacja

Od roku 2010 trochę się już zmieniło w tej kwestii. Stan obecny (5.6-rc1) najlepiej opisuje poniższy post:
http://dimitrik.free.fr/blog/archives/11-01-2012_11-30-2012.html
W skrócie, InnoDB jest szybsze praktycznie w każdym elemencie. Pora najwyższa zapomnieć o MyISAM. A teraz wracamy do oryginalnego wpisu.

 

MySQL posiada modułową budowę, dzięki czemu może udostępnić użytkownikom dużą ilość silników bazodanowych. Znakomita większość z nich powstała dla konkretnego zastosowania i służy do realizowania wyspecjalizowanych zadań. Wśród silników można jednak znaleźć dwa, które wykorzystywane są powszechnie. Chodzi oczywiście o MyISAM i InnoDB. Administrator MySQL często staje przed dylematem – który z tych dwóch silników wybrać dla własnych celów? Na przestrzeni lat przez internet przewaliły się dziesiątki zaciekłych dyskusji na temat tego, który z tych silników jest lepszym rozwiązaniem. MyISAM oferuje znacznie szybszą obsługę SELECTów, jest też łatwiejszy w backupowaniu i szybciej da się odtworzyć z backupu tabelę działającą na silniku MyISAM. InnoDB obsługuje transakcje, umożliwia stosowanie kluczy obcych, nie da się go łatwo backupować przez kopiowanie plików, ma przeciętną wydajność. Mniej więcej do takich wniosków doszedłem bazując na szybkim przeglądnięciu wyników z Google. Jak to się ma do realiów w jakich pracuje administrator MySQL z 2010 roku?

Realia te są trochę inne, niż cztery – pięć lat temu. Od tego czasu MyISAM praktycznie się nie zmienił – prace nad tym silnikiem być może są prowadzone, natomiast nie jest to w praktyce widoczne. W ciągu ostatnich kilku lat rozwój InnoDB ruszył natomiast gwałtownie do przodu. Zarówno programiści z Innobase, którzy obecnie udostępniają do ściągnięcia InnoDB Plugin w wersji 1.0.7 dla MySQL 5.1, a także InnoDB Plugin w wersji 1.1.0 dla MySQL 5.5, jak też programiści z Percony, pracujący nad swoim silnikiem, XtraDB, będącym tuninngowaną wersją InnoDB, praktycznie co kilka miesięcy wydają kolejną, ulepszoną wersję silnika. Spróbuję porównać oba silniki bazodanowe i zaznaczyć słabe i mocne strony każdego z nich. Temat jest o tyle aktualny, że wraz z MySQL w wersji 5.4 MyISAM przestanie być domyślnym silnikiem – stanie się nim InnoDB. O ile do tej pory sporo osób decydowało się na MyISAM w sposób nieświadomy – silnik ten był domyślny, a ustawienia domyślne traktowane są często jako optymalne, to po wprowadzeniu tej zmiany decyzja o stosowaniu MyISAM będzie musiała zostać podjęta bardziej świadomie.

W tym poście zajmę się porównaniem tych cech obu silników, które mają wpływ na wydajność.

MyISAM to blokowanie na poziomie tabel. Jeśli do tabeli `tabela` idzie INSERT, który długo się wykonuje, wszystkie inne zapytania idące do tej tabeli muszą czekać aż to zapytanie się
skończy. Odstępstwem od tej reguły jest mechanizm „concurent inserts”, który dokładnie opisuję w tym poście. W praktyce jednak zazwyczaj, jeśli tylko wzrasta ilość jednoczesnych operacji modyfikujących dane w jednej tabeli (czyli oprócz INSERTów pojawiają się także UPDATE/REPLACE/DELETE),  MyISAM nie daje rady. InnoDB stosuje blokowanie na poziomie rekordów, umożliwia jednoczesne działanie większej ilości zapytań modyfikujących dane. Ta cecha była od zawsze jedną z głównych przyczyn, dla których rezygnowano z MyISAM a stosowano InnoDB.

W przypadku zapytań typu SELECT powszechnie twierdzi się, że MyISAM jest szybszy. Nie jest to tak oczywiste. InnoDB posiada specyficzną cechę, która w pewnych sytuacjach znacznie przyspiesza działanie SELECTów – klastrowane indeksy. Ogólna idea działania indeksu jest taka, że zamiast przeglądać tabelę w poszukiwaniu rekordów spełniających dane warunki, przegląda się posortowany indeks – z niego pobierane są informacje, gdzie fizycznie znajduje się potrzebny rekord. Rekordy są odczytywane i wyświetlane. Są to przynajmniej dwie operacje dyskowe – odczyt indeksu i odczyt danych. Indeksy klastrowane cechują się tym, że dane przechowywane są w obrębie indeksu. Dzięki temu oszczędzana jest jedna operacja odczytu – odczyt indeksu łączy się z odczytem potrzebnych danych. Tyle teoria. W praktyce bywa różnie, ale w niektórych sytuacjach zastosowanie klastrowanych indeksów daje spore przyspieszenie działania.

Kolejną różnicą pomiędzy tymi silnikami jest wykorzystanie pamięci. MyISAM buforuje tylko indeksy – wielkość tego buforu określa zmienna ‚key_buffer’. Dane nie są trzymane w pamięci, a przynajmniej nie na poziomie MySQL. Wykorzystywane są do tego standardowe mechanizmy cache dyskowego używanego w danym systemie operacyjnym. W przypadku InnoDB MySQL obsługuje buforowanie wszystkich danych – wielkość tego buforu określa zmienna ‚innodb_buffer_pool_size’. Różnica jest o tyle istotna, że odwołanie się do systemowego cache jest bardziej kosztowne, niż odwołanie się do buforów w obrębie samego MySQL.

Wolne odzyskiwanie danych z backupu – MyISAM historycznie patrząc był znacznie szybszy jeśli chodzi o tworzenie tabel ze zrzutów. Zestaw mysqldump + cat dump.sql | mysql baza zazwyczaj szybciej funkcjonował na silniku MyISAM. MySQL odtwarza indeksy na dwa sposoby – poprzez sortowanie (szybszy) i z wykorzystaniem cache indeksów (key cache – wolniejszy). W MySQL 5.0 i 5.1 InnoDB nie potrafi zastosować szybszego algorytmu, przez co odzyskanie z backupu jest wyraźnie wolniejsze, szczególnie gdy ilość danych to kilkadziesiąt GB. Problem ten został rozwiązany przez programistów Innobase, a rozwiązanie to zostało wykorzystane też przez programistów Percony. W tym momencie użytkownicy InnoDB plugin oraz XtraDB także mogą korzystać z szybkiego tworzenia indeksów. Dotyczy to tak zwanych secondary indexes – indeks główny, ze względu na to, że jest klastrowany, musi zostać wygenerowany przez kopiowanie danych, a nie sortowanie.

MyISAM w niektórych sytuacjach jest szybszy jeśli chodzi o operacje dotyczące modyfikacji danych. Oczywiście, dzieje się to głównie wtedy, gdy ilość tabel jest na tyle duża, że blokowanie na poziomie tabel nie stanowi znacznego ograniczenia. InnoDB jest silnikiem transakcyjnym, zapewniającym integralność danych. Spełnienie tych wymagań wiąże się ze sporym narzutem, co przekłada się na niższą wydajność silnika. Można jednak to zmienić – jeśli w danych zastosowaniach nie konieczne jest zachowanie integralności danych (a tylko w takich sytuacjach sens ma porównywanie z MyISAM, które integralności nie zachowuje w ogóle), można zluzować część wymagań. Służy do tego zmienna innodb_flush_logs_at_trx_commit. Zmiana jej na ustawienie mniej bezpieczne (np. możliwa jest strata 1 sekundy transakcji w przypadku padu serwera fizycznego) skutkować może nawet dziesięciokrotnym wzrostem wydajności.

W kolejnych postach postaram się porównać inne cechy obu silników. Jeśli ktoś z czytelników chciałby dodać swoje uwagi i przemyślenia, ewentualnie jeśli pominąłem coś istotnego, zapraszam do komentowania. Zawsze też można skontaktować się bezpośrednio ze mną.