Kilka dni temu miałem okazję zetknąć się z ciekawą sytuacją. Monitoring serwera fizycznego, na którym znajdowała się baza MySQL, wskazywał na duże obciążenie procesora typu system. Kilka minut obserwacji “organoleptycznej” przez mytopa pokazało, że co kilkanaście sekund pojawiają się gwałtowne skoki ilości zapytań – z stu – dwustu do kilku tysięcy na sekundę. Co się działo na tej maszynie?

Tego typu obciążenie zazwyczaj wiąże się z wywołaniami systemowymi obsługującymi dysk twardy. W tym wypadku akurat iowait nie skoczył w górę, ale to można wytłumaczyć faktem, iż potrzebna zawartość znajdowała się w cache systemu operacyjnego. Natomiast narzut na operacje otwarcia pliku, odczytu z cache i zamknięcia pliku nadal pozostaje.

Po włączeniu bardziej precyzyjnego logowania zapytań (long_query_time można ustawiać także na ułamki sekundy) do slowlogów trafiła duża ilość zapytań typu:

SELECT COUNT(id) AS ile FROM tabela WHERE data >= '2011-10-24 20:46:22' AND test=3

Nie było to oczywiście dokładnie to, ale idea jest zachowana. Zapytanie było stosunkowo dobrze poindeksowane – nałożony był indeks na kolumny test i data. Kolejność oczywiście nie przypadkowa – WHERE w przypadku kolumny ‘data’ sprawdza nierówność, czyli zgodnie z działaniem indeksów w MySQL, indeks odwrotny (data, test) nie mógłby wykorzystać kolumny test. EXPLAIN wyglądał następująco:

mysql> EXPLAIN SELECT COUNT(id) AS ile FROM tabela FORCE INDEX (idx_test_data)  WHERE data >= '2011-10-24 20:46:22' AND test=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tabela
type: range
possible_keys: idx_test_data
key: idx_test_data
key_len: 9
ref: NULL
rows: 214
Extra: Using where
1 row in set (0,00 sec)

Słowem wyjaśnienia, FORCE INDEX oczywiście nie występował w oryginalnym zapytaniu. Na potrzeby tego postu stworzyłem tabelę o podobnej strukturze, która posiada jednak inny rozkład danych i muszę się posiłkować wymuszeniem zastosowania indeksu aby optimizer zdecydował że jest sens go używać.

Wracając do rzeczy, zapytanie nie wygląda tragicznie. Efekt jego działania jednak jest niefajny. Co można zrobić żeby ulżyć serwerowi? Cóż, jedną z potencjalnych opcji jest przesiadka na InnoDB. Tam dane także będą przetrzymywane w pamięci, ilość odwołań do plików na dysku powinna się zmniejszyć. Inna rzecz czy taka przesiadka nie pogorszy wydajności ze względu na inne zapytania, które akurat dobrze sobie na MyISAM radziły, a na InnoDB miałyby pod górkę. Jako że zmiana silnika i tak nie wchodziła w grę, opcja z InnoDB odpadła od razu.

Drugim rozwiązaniem jest upchnięcie tych danych w pamięci, pomimo tego, że silnikiem jest MyISAM. Niby się nie da, przecież każdy wie że MyISAM w pamięci przechowuje tylko indeksy. No to wrzućmy te dane do indeksu. Dodany został indeks na trzy kolumny – (test, data, id). Co prawda kolumna ‘data’ wykorzystywana jest w nierówności, ale to nie przeszkadza MySQL w wykorzystaniu faktu iż trzecia część indeksu, id, przechowuje informację o zawartości kolumny id. Zawartość ta w zupełności wystarczy do podania wyniku zapytania, czyli wykonania operacji COUNT(id). Widać to na następującym obrazku:

mysql> EXPLAIN SELECT COUNT(id) AS ile FROM tabela FORCE INDEX (idx_test_data_id)  WHERE data >= '2011-10-24 20:46:22' AND test=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tabela
type: range
possible_keys: idx_test_data_id
key: idx_test_data_id
key_len: 9
ref: NULL
rows: 204
Extra: Using where; Using index
1 row in set (0,00 sec)

Kluczowym jest drobny dodatek do kolumny ‘Extra’: ‘Using index’. Świadczy on o tym, że dane potrzebne do wykonania zapytania pobrane zostały z indeksu – tabela nie musiała być odczytywana. Efekt był zauważalny od razu – obciążenie systemowe spadło o kilkadziesiąt procent. MySQL wszystkie potrzebne dane przechowywał w swoich buforach, nie miał potrzeby aby odwoływać się do czegokolwiek na dysku.

Tego typu optymalizacja to tak zwany “covering index” – indeks, który zawiera wszystkie dane potrzebne do wykonania zapytania. Oczywiście, sens zastosowania takiego indeksu jest wtedy, gdy SELECT potrzebuje parę kolumn na krzyż. Raczej bez celu jest tworzenie indeksu na 9 kolumn z 10, jakie są w ogóle w tabeli. Koszt utrzymywania takiego indeksu prawie na pewno przeważy zalety z przechowywania tych danych w pamięci. Dobrze jest jednak znać tego typu rozwiązanie. Czasami jest to bardzo przydatna sprawa i potrafi zniwelować znaczne obciążenie.