Tak to już bywa, że ludzie są leniwi. Mało kto decyduje się na podjęcie działań w sytuacji, gdy nie wydają się one niezbędne. To przekłada się też na kwestię konfiguracji. Jeśli działa, to nie ruszamy, no bo przecież działa. Jeszcze się popsuje i co będzie? Dodatkowo, do domyślnych ustawień po prostu człowiek się przyzwyczaja. Dobrze mu z nimi. O co mi chodzi w tym wstępie? Ano, o to, że począwszy od MySQL 5.5 mamy zmianę domyślnego silnika z MyISAM na InnoDB. Dlaczego to jest dobre, przykład poniżej.

Zwykła, nie tak duża baza danych. W sumie jakieś 400MB danych. Ruch także niewielki – ok. 30 – 40 zapytań na sekundę w tym zapytania modyfikujące dane to 1 – 2 zapytania na sekundę. Problem w tym, że ruch koncentruje się na jednej tabeli. Teraz obrazki – przewińcie je sobie na sam koniec, drodzy Czytelnicy, aby porównać rozkład czasu wykonywania zapytań.

Zapytanie 1, prosty UPDATE z warunkiem WHERE wykorzystującym klucz główny:

# Query 1: 0.48 QPS, 0.46x concurrency, ID 0xEAC73DE450FCE3B7 at byte 7608321
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.93 [1.0], V/M = 9.39
# Query_time sparkline: |  ^__^__|
# Time range: 2011-04-22 09:53:51 to 10:03:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     279
# Exec time     26    268s   134us     20s   962ms      4s      3s    87ms
# Lock time     68    268s    22us     20s   961ms      4s      3s    87ms
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0     278       0       1    1.00    0.99    0.06    0.99
# Rows affecte  23     278       0       1    1.00    0.99    0.06    0.99
# Rows read      0     295       1       3    1.06    0.99    0.32    0.99
# Bytes sent     0  14.17k      52      52      52      52       0      52
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     1  16.62k      61      61      61      61       0      61
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ##
#  10ms  ###########
# 100ms  ###########################################################
#    1s  #########
#  10s+  #####

Zapytanie drugie, prosty SELECT z kilkoma warunkami i sortowaniem przy pomocy indeksu:

# Query 3: 0.14 QPS, 0.16x concurrency, ID 0x09B14B167F54F2F6 at byte 907224
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.83 [1.0]*, V/M = 0.17
# Query_time sparkline: |  _  ^- |
# Time range: 2011-04-22 09:54:12 to 10:03:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      81
# Exec time      8     88s   119us      3s      1s      2s   431ms   945ms
# Lock time      1      5s       0      1s    56ms   241ms   191ms    80us
# Rows sent      0      79       0       1    0.98    0.99    0.15    0.99
# Rows examine  26  24.85M       0 322.08k 314.12k 312.96k  48.57k 312.96k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read     38  24.85M       1 322.08k 314.10k 312.96k  48.57k 312.96k
# Bytes sent     0   7.04k      89      89      89      89       0      89
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0   9.85k     123     123     123     123       0     123
# InnoDB:
# Boolean:
# QC Hit         2% yes,  97% no
# Query_time distribution
#   1us
#  10us
# 100us  ##
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #############################################
#  10s+

Teraz kolejne dwa obrazki, po zmianie silnika z MyISAM na InnoDB. Dotyczą dokładnie tych samych zapytań.

Zapytanie 1:

# Query 3: 0.46 QPS, 0.00x concurrency, ID 0xEAC73DE450FCE3B7 at byte 11082166
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.06
# Query_time sparkline: |  .^__  |
# Time range: 2011-04-22 10:31:55 to 10:44:47
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         20     353
# Exec time     22      1s   186us   218ms     3ms     5ms    14ms     1ms
# Lock time      1    14ms    18us   124us    39us    73us    15us    33us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0     352       0       1    1.00    0.99    0.05    0.99
# Rows affecte  22     352       0       1    1.00    0.99    0.05    0.99
# Rows read      0     353       1       1       1       1       0       1
# Bytes sent    20  17.93k      52      52      52      52       0      52
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size    11  21.03k      61      61      61      61       0      61
# InnoDB:
# IO r bytes     0       0       0       0       0       0       0       0
# IO r ops       0       0       0       0       0       0       0       0
# IO r wait      0       0       0       0       0       0       0       0
# pages distin   1   1.41k       3       7    4.10    4.96    0.41    3.89
# queue wait     0       0       0       0       0       0       0       0
# rec lock wai   0       0       0       0       0       0       0       0
# Query_time distribution
#   1us
#  10us
# 100us  ############################
#   1ms  ################################################################
#  10ms  #
# 100ms  #
#    1s
#  10s+

Zapytanie 2:

# Query 18: 0.14 QPS, 0.00x concurrency, ID 0x09B14B167F54F2F6 at byte 16391093
# This item is included in the report because it matches --limit.
# Scores: Apdex = 1.00 [1.0], V/M = 0.00
# Query_time sparkline: |  _ ^   |
# Time range: 2011-04-22 10:32:03 to 10:53:44
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     177
# Exec time      0      4s   102us    45ms    25ms    30ms     5ms    23ms
# Lock time      0    16ms       0   159us    88us   119us    19us    84us
# Rows sent      0     174       0       1    0.98    0.99    0.13    0.99
# Rows examine   0   1.48M       0   8.71k   8.57k   8.46k   1.09k   8.46k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      5   1.48M       1   8.71k   8.54k   8.46k   1.14k   8.46k
# Bytes sent     0  15.38k      89      89      89      89       0      89
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0  21.26k     123     123     123     123       0     123
# InnoDB:
# IO r bytes     0       0       0       0       0       0       0       0
# IO r ops       0       0       0       0       0       0       0       0
# IO r wait      0       0       0       0       0       0       0       0
# pages distin   0 924.72k   5.31k   5.32k   5.31k   5.20k       0   5.20k
# queue wait     0       0       0       0       0       0       0       0
# rec lock wai   0       0       0       0       0       0       0       0
# Boolean:
# QC Hit         1% yes,  98% no
# Query_time distribution
#   1us
#  10us
# 100us  #
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+

Różnica, mam nadzieję, jest wyraźnie widoczna. W przypadku MyISAM UPDATE wykonywało się od 100 mikrosekund do ponad 10 sekund. Rozrzut był znaczny (odchylenie standardowe 3 sekundy!), użytkownicy odczuwali to jako “zamulenie” serwera. Podobnie jest w przypadku zapytania SELECT – rozpiętość od 100 mikrosekund do ponad 1 sekundy. W sumie daje to odczucie przycinania serwera – raz wszystko leci szybko, raz trzeba czekać kilka sekund.

Zmiana silnika na InnoDB znacznie zmniejszyło rozrzut – nadal występuje, ale są to już granice rzędu 100 mikrosekund – 100 milisekund, dodatkowo znakomita większość zapytań wykonywana jest w przedziale 10 – 100 milisekund dla SELECT’a i 1 – 10 milisekund dla UPDATE. Odchylenie standardowe to maksymalnie 14 milisekund. Różnice tego rzędu wielkości nie są już odczuwalne przez użytkownika, serwis sprawia wrażenie płynnie działającego.

Jak widać, nawet w przypadku baz, które nie są zbytnio obciążone, zmiana rodzaju lock’ów z tabeli na rekord daje znaczną poprawę jakości działania bazy danych. Do powyższego można jeszcze dorzucić zmniejszenie obciążenia serwera fizycznego o kilka procent, ale już samo zlikwidowanie rozrzutu czasu wykonywania zapytań jest warte zmiany silnika bazowanowego. Dzięki MySQL 5.5 i temu, że domyślnym silnikiem staje się InnoDB, mniej będzie tego typu problemów.