MySQL – optymalizacja i wydajność

W poprzednim poście opisywałem znaczenie poszczególnych wartości zbieranych przez MySQL w parametrach Handler_read_*. Dziś parę słów dlaczego te dane są tak przydatne?

To co dziś napiszę, raczej nie powinno być żadnym zaskoczeniem, szczególnie po poprzednim poście. Dane te przekazują nam informację o różnych rodzajach działalności, jakie serwer MySQL wykonuje. Dowiadujemy się, czy wykonywane są sortowania, dowiadujemy się czy wykonywane są pełne skany indeksów bądź tabel. W oparciu o te informacje możemy sobie wygenerować pewien obraz działalności serwera – jak duża część zapytań nie korzysta z indeksów? Jak duża ilość zapytań wykonuje sortowanie przy pomocy algorytmu filesort?

W przypadku MySQL niepoindeksowane zapytania i zapytania, które wymagają wykonania filesort to często większa część nieoptymalnych zapytań. Z takich najbardziej popularnych przypadków pozostają jeszcze zapytania, które generują tabele tymczasowe, w szczególności tabele tymczasowe na dysku. Jeśli jesteśmy w stanie ocenić, jaki procent zapytań stanowią te dwa typy, w sporej części przypadków możemy stwierdzić, jaki rodzaj problemów dotyka dany serwer MySQL. Możemy to stwierdzić bez zbierania slowlogów i ich dalszej analizy – wystarczy uruchomienie zapytania SHOW GLOBAL STATUS;.

Z tych powodów zmienne te są często wykorzystywane do analizy stanu serwera. Jeśli ktoś z czytelników miał styczność z pt-mysql-summary, narzędziem z zestawu Percona Toolkit, które generuje zbiorczą informację o serwerze MySQL, prawdopodobnie kojarzy, że narzędzie to prezentuje także te dane:

Kolejne kolumny to (w zaokrągleniu) łączna wartość od resetu statystyk, przyrost w ciągu sekundy i przyrost w przeciągu 10 sekund. Na powyższym przykładzie widać, że WordPress (bo taka strona korzysta z tego serwera MySQL) powinien popracować nad strukturą bazy danych. Należałoby dokładnie przeanalizować zapytania i prawdopodobnie dałoby się dodać kilka przydatnych indeksów – na to może wskazywać wysoka wartość Handler_read_rnd_next, czyli fakt, że sporo zapytań wykonuje pełny skan tabeli.

Jeśli korzystamy z Cacti i template przygotowanego przez Perconę, w domyślnej konfiguracji rysowany jest wykres zbiorczy dla wszystkich powyższych parametrów. Dzięki temu możemy sprawdzić, jak wyglądała zmiana rodzaju ruchu w czasie. Jeśli na wykresach widać jakieś anomalie, chwilowe skoki któregoś z niekorzystnych parametrów, można na tej podstawie zlokalizować przedział czasowy, w którymś coś się działo, i z którego należałoby przeglądnąć zapytania w slowlogu.

Jeżeli nie mamy pod ręką dostępnych żadnych narzędzi, nadal możemy sprawdzić co w chwili obecnej dzieje się na serwerze – możemy wykorzystać polecenie mysqladmin:

W powyższy sposób co sekundę otrzymujemy informacje o przyroście każdego parametru z grupy Handler_read_*

Tego typu informacje są bardzo przydatne dla administratora. W szczególności wykresy Cacti, gdzie mamy możliwość przeanalizowania zmian w strukturze ruchu na serwerze MySQL.