Czym się różni InnoDB od MyISAM?
Kamil Porembiński
Kamil Porembiński
18.04.2017

Czym się różni InnoDB od MyISAM?

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.

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ównież oprogramowanie Percona XtraBackup.

Kopie zapasowe

Jeżeli dojdzie już do awarii serwera, a domyślne metody naprawienia tabel nie działają, będziemy musieli skorzystać z kopii zapasowej. Z którego silnika prościej jest wykonać taką kopię? Najbardziej popularną metodą jest skorzystanie z programu mysqldump. Zrzucamy w ten sposób dane do formatu SQL, które potem bezproblemowo możemy zaimportować do serwera. Operacje te trwają dość długo w przypadku ogromnych baz danych, jednak import pliku SQL będzie szybszy dla silnika MyISAM. Silnik InnoDB podczas importowania nie potrafi zbudować indeksu przy pomocy sortowania, dlatego operacja importu trwa znaczniej dłużej.

Kolejną metodą jest wykonanie kopii zapasowej plików z danymi. Dla tabel MyISAM, dane przechowywane są w plikach:

  • .frm – definicja tabeli
  • .MYD (MYData) – plik z danymi
  • .MYI (MYIndex) – indeksy tabeli

Silnik InnoDB, w zależności od ustawień serwera, przechoduje dane również w plikach .frm (definicja tabel), natomiast dane trzymane są w jednej lub większej ilości plików, które składają się na tablespace. Za ustawienia odpowiada zmienna innodb_file_per_table. Domyślnie od wersji serwera >= 5.5.7 ustawiona jest na OFF. Dla serwera MySQL w wersjach >= 5.5.0, <= 5.5.6, domyślnie była ustawiona na ON. Włączenie tej opcji oznacza tworzenie osobnego pliku tablespace dla każdej tabeli. Wtedy zostanie również stworzony plik .idb, który zawiera dane i indeksy danej tabeli.

Skopiowanie odpowiednich plików jest proste i dość szybkie. W przypadku MyISAM musimy zablokować dostęp do zapisu dla tabel poleceniem FLUSH TABLES WITH READ LOCK; lub po prostu zatrzymać serwer. Niestety rozwiązanie to nie jest wydajne bo musimy odciąć dostęp do zapisu do serwera. W przypadku silnika InnoDB możemy skorzystać z oprogramowania Percona XtraBackup i wykonać kopię bez zatrzymywania bazy danych.

Wydajność silnika bazodanowego

Bardzo często można spotkać się z opinią, że silnik MyISAM jest szybszy od InnoDB. Jakieś 5 lat temu może tak było. Jednak w ostatnim czasie i z każdą nową wersją serwera MySQL, firma Oracle rozwija silnik InnoDB. Prace przy MyISAM praktycznie stoją w miejscu. Również Percona pracuje nad własną wersją InnoDB o nazwie XtraDB. Średnio co kilka miesięcy pojawia się kolejna wersja serwera. Warto zwrócić uwagę na fakt, że od MySQL 5.4, MyISAM przestał być domyślnym silnikiem. Zatem coś musi być na rzeczy.

Zawsze uważano, że MyISAM jest szybszy, głównie dla zapytań typu SELECT. Jak się jednak okazuje nie jest to takie oczywiste. InnoDB wykorzystuje mechanizm klastrowania indeksów, co w niektórych przypadkach znacząco przyspiesza działanie bazy. Działanie takich indeksów polega na przechowywanie również i danych w obrębie indeksu. Podczas przeszukiwania tablic, przeglądane są posortowane indeksy, a następnie sięga się po dane jakie wskazuje indeks. W takim wypadku baza wykonuje dwie operacje: odczyt indeksu a potem odczyt danych. Klastrowanie indeksów znacząco przyspiesza te operacje. Oszczędzamy na każdym zapytaniu jedną operację odczytu danych.

Ale korzystanie z bazy danych to nie tylko wyszukiwanie i pobieranie rekordów. To również ich dodawanie, kasowanie czy modyfikowanie. Jak tutaj radzi sobie MyISAM a jak InnoDB? W przypadku pierwszego silnika mamy do czynienia z blokowaniem na poziomie tabeli. Dodając rekordy do tabeli, blokujemy ją na czas całej operacji. Inne zapytania jakie odwołują się w tym czasie do tej tabeli, muszą poczekać w kolejce. Sprawia to, że w przypadku dużej ilości zapytań oraz modyfikowanych rekordów – wydajność bazy spada. W przypadku InnoDB mamy do czynienia z blokowaniem na poziomie rekordów, dzięki czemu możliwe jest działanie równoległych zapytań na tej samej tabeli.

MyISAM w wielu sytuacjach potrafi zdecydowanie działać szybciej niż InnoDB, ale dzieje się to kosztem integralności danych. W nim po prostu nie ma takiej funkcjonalności.

Różnicą w obu silnikach jest również zarządzanie pamięcią. MyISAM posiada możliwość buforowania tylko indeksów, gdzie wielkość bufora określa zmienna key_buffer_size. Zalecana wielkość takiego bufora to 25% całej pamięci jaką posiada serwer. W przypadku drugiego silnika mamy możliwość buforowania wszystkich danych a nie tylko indeksów. Odpowiada za to parametr innodb_buffer_pool_size, który domyślnie ustawia wielkość buforów na 128MB. Zalecane jest ustawienie tego bufora na 80% całej pamięci jaką posiada serwer.

Testowanie wydajności silników

Testy były wykonywane na serwerze opartym o system Red Hat Enterprise Linux 5.7 w wersji 64 bitowej. Maszyna posiadała procesory Quad-Core AMD Opteron(tm) Processor 2374 HE oraz 8GB pamięci RAM. Do testów wykorzystano oprogramowanie SysBench. Przed przystąpieniem do testów, należy wypełnić bazę przykładowymi danymi. Do tego celu posłuży nam polecenie:

[root@dbtest thecamels]# sysbench --test=oltp --mysql-user=root --mysql-db=test --oltp-table-size=20000000 --myisam-max-rows=20000000 --mysql-table-engine=myisam --oltp-table-name=myisam prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Creating table 'myisam'...
Creating 20000000 records in table 'myisam'...

Podobnym poleceniem tworzymy tabelę pod silnik InnoDB:

sysbench --test=oltp --mysql-user=root --mysql-db=test --oltp-table-size=20000000 --myisam-max-rows=20000000 --mysql-table-engine=innodb --oltp-table-name=innodb prepare

Teraz przyszedł czas na testy silników. Na początku sprawdzimy jak szybko wykonywane są zapytania typu SELECT (parametr --oltp-read-only=on). Nie będą wykonywane zapytania typu UPDATE, DELETE, INSERT. Odpowiednio wydajemy polecenia najpierw dla MyISAM:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=test --oltp-table-name=myisam --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run
...
OLTP test statistics:
    queries performed:
        read:                            1000111
        write:                           0
        other:                           0
        total:                           1000111
    transactions:                        1000111 (8839.28 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000111 (8839.28 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          113.1439s
    total number of events:              1000111
    total time taken by event execution: 14458.9161
    per-request statistics:
         min:                                  0.06ms
         avg:                                 14.46ms
         max:                                299.95ms
         approx.  95 percentile:              41.53ms

Threads fairness:
    events (avg/stddev):           7813.3672/128.22
    execution time (avg/stddev):   112.9603/0.15

potem dla InnoDB:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run    
... 
OLTP test statistics:
    queries performed:
        read:                            1000103
        write:                           0
        other:                           0
        total:                           1000103
    transactions:                        1000103 (8620.56 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000103 (8620.56 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          116.0136s
    total number of events:              1000103
    total time taken by event execution: 14844.5125
    per-request statistics:
         min:                                  0.05ms
         avg:                                 14.84ms
         max:                                266.96ms
         approx.  95 percentile:              42.62ms

Threads fairness:
    events (avg/stddev):           7813.3047/97.54
    execution time (avg/stddev):   115.9728/0.00

a na końcu dla XtraDB:

[root@dbtest ~]# sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run    
...
OLTP test statistics:
    queries performed:
        read:                            1000637
        write:                           0
        other:                           0
        total:                           1000637
    transactions:                        1000637 (35228.19 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000637 (35228.19 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          28.4044s
    total number of events:              1000637
    total time taken by event execution: 3473.6785
    per-request statistics:
         min:                                  0.09ms
         avg:                                  3.47ms
         max:                               5815.52ms
         approx.  95 percentile:               3.05ms

Threads fairness:
    events (avg/stddev):           7817.4766/2766.84
    execution time (avg/stddev):   27.1381/0.62

Jak widać silnik MyISAM nie jest zdecydowanie szybszy od InnoDB podczas zapytań typu SELECT. Drugi silnik radzi sobie porównywalnie szybko. Również można zauważyć, że XtraDB podczas wykonywania SELECTów jest zdecydowanie szybszy od InnoDB czy MyISAM. Test wykonał się na nim piorunująco szybko.

MyISAM vs InnoDB

A jak to wygląda w przypadku operacji, które modyfikują dane? Będziemy mieli tutaj do czynienia z różnego rodzaju zapytaniami modyfikującymi, kasującymi i dodającymi rekordy. Do tego celu posłużą nam odpowiednie polecenia:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=test --oltp-table-name=myisam --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run
...
OLTP test statistics:
    queries performed:
        read:                            14000000
        write:                           5000000
        other:                           2000000
        total:                           21000000
    transactions:                        1000000 (229.04 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000000 (4351.78 per sec.)
    other operations:                    2000000 (458.08 per sec.)

Test execution summary:
    total time:                          4366.0247s
    total number of events:              1000000
    total time taken by event execution: 558754.8210
    per-request statistics:
         min:                                  4.88ms
         avg:                                558.75ms
         max:                               1240.84ms
         approx.  95 percentile:             792.23ms

Threads fairness:
    events (avg/stddev):           7812.5000/2.61
    execution time (avg/stddev):   4365.2720/0.27

oraz dla silnika InnoDB:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run 
...
OLTP test statistics:
    queries performed:
        read:                            14000784
        write:                           5000280
        other:                           2000112
        total:                           21001176
    transactions:                        1000056 (924.42 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19001064 (17563.92 per sec.)
    other operations:                    2000112 (1848.83 per sec.)

Test execution summary:
    total time:                          1081.8238s
    total number of events:              1000056
    total time taken by event execution: 138408.7900
    per-request statistics:
         min:                                  2.98ms
         avg:                                138.40ms
         max:                               3589.94ms
         approx.  95 percentile:             284.44ms

Threads fairness:
    events (avg/stddev):           7812.9375/46.88
    execution time (avg/stddev):   1081.3187/0.15

a na końcu dla XtraDB:

sysbench --test=oltp --mysql-user=root --oltp-table-size=1000000  --num-threads=128 --max-requests=1000000 --mysql-table-engine=innodb --mysql-db=test --oltp-table-name=innodb --myisam-max-rows=20000000 --oltp-read-only=off --oltp-test-mode=complex run 
...  
OLTP test statistics:
    queries performed:
        read:                            14001148
        write:                           5000410
        other:                           2000164
        total:                           21001722
    transactions:                        1000082 (919.01 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19001558 (17461.13 per sec.)
    other operations:                    2000164 (1838.01 per sec.)

Test execution summary:
    total time:                          1088.2202s
    total number of events:              1000082
    total time taken by event execution: 139270.7414
    per-request statistics:
         min:                                  3.01ms
         avg:                                139.26ms
         max:                              12718.80ms
         approx.  95 percentile:             285.29ms

Threads fairness:
    events (avg/stddev):           7813.1406/50.75
    execution time (avg/stddev):   1088.0527/0.03

Jak widać w tym wypadku InnoDB zdecydowanie lepiej poradziło sobie z testem wydajnościowym. Blokowanie na poziomie tabel bardzo spowalnia działanie bazy danych, co przekłada się na mniejszą liczbę obsługiwanych transakcji. Wydajność XtraDB w tym teście była zbliżona do InnoDB.

MyISAM vs InnoDB

Dla kolejnego testu została przygotowana tabela posiadająca 2 000 000 rekordów, wypełnionych tekstem. Całość zajmowała około 35 GB. Testowanym zapytaniem było przeszukanie tabeli za pomocą operatora LIKE.

Wynik zapytania dla MyISAM:

select count(*) from table_myisam where txt LIKE '%lipsum%';
1 row in set (10 min 44.08 sec)

Wynik zapytania dla InnoDB:

select count(*) from table_innodb where txt LIKE '%lipsum%';
1 row in set (14 min 29.96 sec)

Wynik zapytania dla XtraDB:

select count(*) from table_xtradb where txt LIKE '%lipsum%';
1 row in set (12 min 14.21 sec)

Jak widać w tym wypadku silnik MyISAM zdecydowanie był szybszy od pozostałych. Jednak różnice te nie są aż tak wielkie w porównaniu do XtraDB.

MyISAM vs InnoDB

Jaki silnik wybrać dla bazy danych? MyISAM czy InnoDB?

Odpowiedź na to pytanie nie jest prosta, gdyż wszystko zależy do jakich celów będziemy wykorzystywać bazę danych, czy będzie nam zależało na integralności danych czy raczej na wydajności podczas pobierania rekordów.

MyISAM
Zalety

  • szybki odczyt z tabel
  • prostsze wykonywanie kopii zapasowych
  • odpowiedni dla tabel z małą ilością danych

Wady

  • brak obsługi transakcji
  • brak mechanizmów odpowiedzialnych za integralność danych
  • przy dużych tabelach, długie czasy wykonywania REPAIR TABLE po awarii serwera

InnoDB
Zalety

  • obsługa transakcji
  • gwarantuje integralność danych
  • domyślny silnik od MySQL 5.5
  • lepiej sprawuje się podczas replikacji typu master – slave

Wady

  • wolniejszy odczyt danych
  • trudniejsze wykonywanie backupów

Powiązane wpisy:

  1. Prosty i szybki backup bazy dzięki Percona XtraBackup
  2. Co to jest replikacja MySQL?
  3. Kompendium: Jak zabezpieczyć WordPressa?
  4. Protokół TCP/IP. Co to jest?
  5. Domena a Hosting: Definicja i różnice.
  6. The twelve-factor app – aplikacja dwunastu czynników