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.

 

Pisząc poprzedni post dotyczący wydajności wykonałem serię testów wydajności obu silników. Poniżej zamieszczam wyniki tych testów, wykonywane były one przy pomocy sysbencha na tabeli wielkości 10 milionów rekordów. Dało to ok. 2GB danych dla MyISAM i 2,3GB danych dla InnoDB. Serwer MySQL, na którym uruchamiane były zapytania działał z następującą wersją silnika XtraDB (czyli InnoDB na sterydach, prosto od Percony):

mysql> SHOW VARIABLES LIKE 'innodb_version';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| innodb_version | 1.0.8-11.2 |
+----------------+------------+
1 row in set (0.00 sec)

Pierwsza seria testów wykonana była z następującymi parametrami:

sysbench --test=oltp --mysql-user=root --oltp-table-size=10000000 --num-threads=128 --max-requests=1000000 --mysql-table-engine=myisam --mysql-db=sbtest1 --myisam-max-rows=20000000 --oltp-read-only=on --oltp-test-mode=simple run


Przekładało się to na serię prostych SELECTów, a wyniki wyglądają następująco:

MyISAM

OLTP test statistics:
queries performed:
read:                            1000038
write:                           0
other:                           0
total:                           1000038
transactions:                        1000038 (11709.51 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1000038 (11709.51 per sec.)
other operations:                    0      (0.00 per sec.)

Test execution summary:
total time:                          85.4039s
total number of events:              1000038
total time taken by event execution: 10928.3403
per-request statistics:
min:                                  0.03ms
avg:                                 10.93ms
max:                                 95.07ms
approx.  95 percentile:              27.57ms

InnoDB

OLTP test statistics:
queries performed:
read:                            1000036
write:                           0
other:                           0
total:                           1000036
transactions:                        1000036 (11490.19 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1000036 (11490.19 per sec.)
other operations:                    0      (0.00 per sec.)

Test execution summary:
total time:                          87.0339s
total number of events:              1000036
total time taken by event execution: 11136.9699
per-request statistics:
min:                                  0.04ms
avg:                                 11.14ms
max:                                 90.66ms
approx.  95 percentile:              27.99ms

Jak widać, minimalnie szybszy jest silnik MyISAM, ale różnica nie jest znacząca. Kolejna seria testów odbyła się w środowisku z minimalną ilością pamięci. Wielkość innodb_bufer_pool i buforu indeksów dla MyISAM została ustalona na 1M, po uruchomieniu MySQL wolnej pamięci zostało ok. 80M. Ponownie uruchamiany był test w trybie read-only i simple. Wyniki są następujące:

MyISAM

OLTP test statistics:
queries performed:
read:                            1000026
write:                           0
other:                           0
total:                           1000026
transactions:                        1000026 (9178.32 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1000026 (9178.32 per sec.)
other operations:                    0      (0.00 per sec.)

Test execution summary:
total time:                          108.9552s
total number of events:              1000026
total time taken by event execution: 13942.7316
per-request statistics:
min:                                  0.04ms
avg:                                 13.94ms
max:                                 83.50ms
approx.  95 percentile:              28.24ms

InnoDB

OLTP test statistics:
queries performed:
read:                            1000047
write:                           0
other:                           0
total:                           1000047
transactions:                        1000047 (7523.30 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1000047 (7523.30 per sec.)
other operations:                    0      (0.00 per sec.)

Test execution summary:
total time:                          132.9266s
total number of events:              1000047
total time taken by event execution: 17010.8143
per-request statistics:
min:                                  0.04ms
avg:                                 17.01ms
max:                                122.26ms
approx.  95 percentile:              35.36ms

Wyraźnie widać szybsze działanie MyISAM – różnica ponad 20%.

Pozostały dwa testy. Oba wykonane z parametrami:

--oltp-read-only=off --oltp-test-mode=complex

które przekładają się na mieszankę odczytu i zapisu. Pierwszy test wykonany został przy ograniczonej ilości pamięci – innodb_buffer_pool 100M, ok. 150M wolnej pamięci na cache dyskowy.

MyISAM:

OLTP test statistics:
queries performed:
read:                            1400000
write:                           500000
other:                           200000
total:                           2100000
transactions:                        100000 (390.42 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1900000 (7417.98 per sec.)
other operations:                    200000 (780.84 per sec.)

Test execution summary:
total time:                          256.1343s
total number of events:              100000
total time taken by event execution: 32762.8704
per-request statistics:
min:                                  4.06ms
avg:                                327.63ms
max:                                777.21ms
approx.  95 percentile:             330.36ms

InnoDB

OLTP test statistics:
queries performed:
read:                            1400896
write:                           500320
other:                           200128
total:                           2101344
transactions:                        100064 (571.73 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1901216 (10862.85 per sec.)
other operations:                    200128 (1143.46 per sec.)

Test execution summary:
total time:                          175.0199s
total number of events:              100064
total time taken by event execution: 22359.2100
per-request statistics:
min:                                  2.96ms
avg:                                223.45ms
max:                               2809.59ms
approx.  95 percentile:             541.20ms

Drugi test – wszystkie dane mieszczą się w buforach i cache dyskowym.

MyISAM

OLTP test statistics:
queries performed:
read:                            1400000
write:                           500000
other:                           200000
total:                           2100000
transactions:                        100000 (397.46 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1900000 (7551.81 per sec.)
other operations:                    200000 (794.93 per sec.)

Test execution summary:
total time:                          251.5951s
total number of events:              100000
total time taken by event execution: 32163.5540
per-request statistics:
min:                                 10.07ms
avg:                                321.64ms
max:                                734.92ms
approx.  95 percentile:             324.48ms

InnoDB

OLTP test statistics:
queries performed:
read:                            1400910
write:                           500325
other:                           200130
total:                           2101365
transactions:                        100065 (912.90 per sec.)
deadlocks:                           0      (0.00 per sec.)
read/write requests:                 1901235 (17345.06 per sec.)
other operations:                    200130 (1825.80 per sec.)

Test execution summary:
total time:                          109.6125s
total number of events:              100065
total time taken by event execution: 13762.0113
per-request statistics:
min:                                  3.04ms
avg:                                137.53ms
max:                               7903.30ms
approx.  95 percentile:             333.94ms

Z powyższych wyników można wyciągnąć kilka wniosków. Na pewno, InnoDB jest szybsze jeśli chodzi o obciążenie obejmujące spory procent zapytań modyfikujących dane. W testach sysbech zapis to prawie 25%, a to przełożyło się na spadek wydajności MyISAM z ok. 9100 qps do 7500 qps. W przypadku zapytań typu SELECT MyISAM jest szybszy, aczkolwiek różnica ta nie jest już tak duża, jak było to kilka lat temu. Widać także wyraźnie, że wraz ze wzrostem wielkości bufora wzrasta znacznie wydajność InnoDB. W przypadku MyISAM wzrost wielkości pamięci nie przełożył się na wzrost wydajności, gdyż wąskim gardłem pozostało blokowanie się tabeli dla operacji zapisu.

Powyższe testy z pewnością nie mają zacięcia naukowo – statystycznego, ale pokazują, że w zależności od rodzaju obciążenia i zasobów serwera wydajność obu silników może być różna. Nawet jeśli nie planujemy wykorzystania dodatkowych właściwości InnoDB jak klucze obce czy transakcje, dobrze jest przetestować, czy zastosowanie tego silnika nie będzie korzystne z punktu widzenia samej wydajności. Na koniec jeszcze raz przypominam, że InnoDB, jakie było testowane to najnowsza wersja XtraDB (11.2), oparta na InnoDB Plugin 1.0.8. Wydajność InnoDB wbudowanego w MySQL 5.1 będzie inna.