MySQL – lista zapytań
Od czasu do czasu pojawia się potrzeba sprawdzenia jak sobie radzi jakiś serwis z komunikacją z MySQL. Najprostszym rozwiązaniem jest log wolnych zapytań, który uruchamia się poprzez wpis w my.cnf:
log_slow_queries = /sciezka/slow-mysql.log long_query_time = 1 log-queries-not-using-indexes
Dzięki temu w pliku slow-mysql.log otrzymamy nie tylko zapytania zajmujące więcej niż 1 sekundę (w przypadku wersji z patchem Percony można wpisać liczby poniżej 1), ale także zapytania, które nie korzystają z indeksów. Lista zapytań z pewnością będzie zawierać także takie wpisy, które sprawdzane przy pomocy EXPLAINa dawały pozytywny wynik. Nic w tym dziwnego, ponieważ explain stara się przewidzieć wynik zapytania na podstawie cząstkowych danych, bez korzystania z właściwego silnika szukania.
Moglibyśmy zadowolić się takim rozwiązaniem, ale niestety nie otrzymamy w ten sposób złych zapytań, które korzystają z indeksów i nie kwalifikują się na dodanie do listy powolnych. W tym przypadku użyć możemy polecenia grep, przy pomocy którego będziemy szukać w plikach *.php ciągu zawierającego słowa select i from. Jeśli jest więcej katalogów, to trzeba będzie dodatkowo skorzystać z find. Nie podaję polecenia, ponieważ takie szukanie ma zbyt wiele wad, żeby wykorzystać je w większym serwisie:
1. zapytanie może być składane z szeregu odrębnych kawałków i otrzymamy tylko fragment
2. wartości do where będziemy musieli wpisywać ręcznie
3. jeśli użyte są tabele z prefixem w nazwie, to będziemy musieli podmienić odpowiednie fragmenty tekstu
4. autor mógł zrobić klasę z metodą select, albo wstawić szereg ifów wycinających większość zapytań
5. pliki mogą być pociągnięte ioncube
Jeżeli jednak skorzystamy z tego rozwiązania, to po przetworzeniu pliku otrzymamy listę zapytań, która mniej więcej da nam wgląd do tego co serwis robi z bazą danych.
Kolejnym rozwiązaniem może być logowanie zapytań bezpośrednio przez klasę wykonującą operacje na MySQL. Tak jest np. w przypadku WordPressa, któremu wystarczy powiedzieć, żeby zapisywał zapytania i żeby pokazał je w stopce. Jeśli sami tworzymy taką klasę, to dobrze jest dodać odczytanie miejsca wywołania, dzięki czemu będziemy wiedzieć skąd dane zapytanie się wzięło (można odczytać nie tylko plik, ale także numer linii). Gdy już mamy taką listę, to wystarczy przejść po różnych podstronach serwisu i zanotować sobie wszystkie pojawiające się zapytania.
Na koniec pozostaje rozwiązanie, które wymaga tego, aby MySQL pozwalał łączyć się poprzez tcp/ip (zamiast przez linuksowe gniazdo):
bind-address = 127.0.0.1
Jeśli tak jest, to w konsoli odpalamy polecenie:
tcpdump -i lo port 3306 -s 2000 -x -n -q -tttt > tcpdump.out
i przestawiamy w serwisie połączenie z mysql z localhost na 127.0.0.1. Od tej chwili każde zapytanie będzie szło przez port lokalny, który jest na „podsłuchu”. Po odczekaniu kilku chwil (zależnie od liczby osób online) zatrzymujemy nasłuch przy pomocy kombinacji klawiszy ctrl+c. Jeżeli coś poszło nie tak, to otrzymamy komunikat:
0 packets captured 0 packets received by filter 0 packets dropped by kernel
W przeciwnym wypadku w pliku tcpdump.out będziemy mieli wszystko co potrzeba do dalszej analizy. Listę zapytań otrzymamy przy pomocy pt-query-digest, który można odpalić w następujący sposób:
pt-query-digest --type=tcpdump --noreport --output=slowlog tcpdump.out > slow.log
Przy pomocy przełączników noreport i output=slowlog wymuszamy na programie wygenerowanie pełnej listy zapytań. Jeżeli chcemy otrzymać raport z listą najczęściej pojawiających się zapytań, to odpalamy program następująco:
pt-query-digest --type=tcpdump tcpdump.out > raport.txt
W ten sposób w pliku raport.txt znajdą się zapytania posortowane pod kątem wpływu na serwer MySQL (jeśli serwer jest z patchem Percony, to dane otrzymane przy pomocy tcpdump będą bardziej szczegółowe).
Co teraz można zrobić z listą zapytań? To już zależy od założonego celu. Jeśli chcemy pozbyć się zapytań generujących spory ruch na dysku, czyli tych tworzących tymczasowe tabele, to szukamy distinct lub group by. Są one niemal pewnym gwarantem tego, że będzie utworzona tabela tymczasowa.
Dodaj komentarz