MySQL – optymalizacja i wydajność

Gdy widać, że serwer bazodanowy zaczyna zwalniać, obciążenie procesora jest co raz większe, miło by było aby móc zlokalizować przyczynę takiego stanu rzeczy. W przypadku, gdy stosujemy standardową dystrybucję MySQL a użytkowników w bazie jest więcej niż kilku (i analizowanie bieżących wyników mytop’a czy SHOW PROCESSLIST; już nie wystarcza), w zasadzie jedynym rozwiązaniem jest włączenie pełnego logowania zapytań do slowlogów (long_query_time=0) i a potem wykorzystać magię awk’a/sed’a/perl’a aby posumować czas wykonywania zapytań dla każdego użytkownika. Problem w tym, że po pierwsze, tego typu statystyki są nieprecyzyjne. Jeśli chwilowo serwer został przeciążony, wydłuży się czas wykonywania wszystkich zapytań – nie tylko tych, które przeciążenie spowodowały. Po drugie, logowanie wszystkiego do slowlogu wpływa negatywnie na wydajność serwera – w końcu trzeba te wszystkie logi zapisać na dysku. Po trzecie, jeśli na serwerze jest spory ruch, to i logi będą długie – czas potrzebny na ich obróbkę, a także obciążenie przez ten proces generowane, może być znaczące i w praktyce uniemożliwić wykonywanie tego typu operacji na żądanie – da się je wykonać tylko w zaplanowanym okienku, w nocy, gdy dodatkowe obciążenie serwera nie będzie takim problemem. Czy jest jakieś inne rozwiązanie?

Kilka lat temu Google uszczęśliwiło administratorów baz danych MySQL na całym świecie, szczególnie tych, którzy pracowali w firmach hostingowych – tam problem był najbardziej widoczny. Wypuściło jeden wielki patch, który stosowany był na serwerach MySQL w Google, a który między innymi zawierał dodatkowe statystyki użycia serwera na poziomie poszczególnych użytkowników. Przez pewien czas problemem było to, że patch ten (który był przez lata rozbudowywany i zyskał ostatnio czwartą wersję) jest przygotowany pod konkretną wersję MySQL – 5.0.37. Jeśli chciałeś stosować inną wersję, trzeba było samodzielnie patch przepisać. Szczególnie problematyczne było to w przypadku MySQL 5.1. Na szczęście pojawił się sponsor i programiści Percony zabrali się za tą sprawę. W tym momencie każdy użytkownik Percona Server może się cieszyć ładnymi statystykami obciążenia generowanego przez poszczególnych użytkowników. Statystyki te są przechowywane w następujących tabelach w information_schema:

Dostępne są przez polecenie SHOW tabela;:

Można także wykorzystać składnię SELECT:

Jak widać powyżej, w prosty sposób możemy wyciągnąć informacje na temat ruchu generowanego przez poszczególnych użytkowników. Wartość busy_time to łączny czas w sekundach, przez który dany użytkownik był połączony z serwerem MySQL i wykonywał jakieś zapytania – to jest tak z grubsza odpowiednik podsumowania czasu trwania wszystkich zapytań ze slowlogów. Wartość cpu_time to z kolei czas procesora (także w sekundach), który został poświęcony na wykonywanie zapytań danego użytkownika. Wartość ta jest znacznie niższa, bo na czas wykonania zapytania składa się nie tylko czas procesora, ale też czas oczekiwania na locki, oczekiwania na dysk, oczekiwania na zwolnienie mutexów i tak dalej. Pozostałe kolumny z tej tabeli są dosyć jasno opisane, nie będę się więc o nich rozpisywać.

Dane te są zbierane na okrągło i są dostępne w czasie rzeczywistym. Liczniki kasują się w trakcie restartu serwera albo przez wykonanie zapytania FLUSH tabela; Można sobie podefiniować zadania do crona i np. raz dziennie zrzucać sobie dane z całego dnia do archiwum, a potem wyczyścić statystyki na kolejny dzień. Daje to ciekawy przegląd obciążenia serwera MySQL w pewnym przedziale czasowym.