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:
# 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:
# 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:
# 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:
# 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.
Komentarze