Na temat slowlogów dostępnych w MySQL pisałem jakiś czas temu – mowa wtedy była o logach, które są dostępne w dystrybucji MySQL udostępnianej przez twórców tego serwera. Przykładowy wpis w takim logu może wyglądać następująco:
# User@Host: root[root] @ localhost []
# Query_time: 0.183091 Lock_time: 0.000101 Rows_sent: 4 Rows_examined: 3034
SET timestamp=1273057031;
SELECT SQL_NO_CACHE * FROM film LEFT OUTER JOIN film_text USING (film_id) ORDER BY film.title, film_text.description LIMIT 30,4;
Wpis w logu dla tego samego zapytania w przypadku stosowanej przeze mnie wersji MySQL wygląda następująco:
# User@Host: root[root] @ localhost []
# Thread_id: 4249 Schema: sakila
# Query_time: 0.165546 Lock_time: 0.000081 Rows_sent: 4 Rows_examined: 3034 Rows_affected: 0 Rows_read: 34
# Bytes_sent: 2262 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 1750700
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 12
SET timestamp=1273056853;
SELECT SQL_NO_CACHE * FROM film LEFT OUTER JOIN film_text USING (film_id) ORDER BY film.title, film_text.description LIMIT 30,4;
Jak widać na pierwszy rzut oka, informacji jest znacznie więcej. Oprócz podstawowych danych, które pojawiają się także we wcześniejszym przykładzie, można dowiedzieć się że:
– w wyniku zapytania do klienta przesłano 2262 bajtów
– utworzona została jedna tabela tymczasowa, utworzona została na dysku i zajęła ok. 1,7MB
– zapytanie nie zostało pobrane z cache zapytań
– wykonany został full scan tabeli, wyniki były sortowane przy pomocy algorytmu filesort
Dodatkowo, jeśli tabele działają na silniku InnoDB, otrzymujemy dane dotyczące tego silnika – ilość operacji odczytu i zapisu, jak długo trwał odczyt danych z dysku, jak długo zapytanie czekało na uzyskanie locka na odpowiednie rekordy, jak długo zapytanie czekało w kolejce na realizację, ile stron zostało odczytanych w trakcie realizacji zapytania.
Tego typu informacje pozwalają administratorowi już na etapie analizy logów wyłapać potencjalne przyczyny problemów i ułatwiają wybór zapytań, które należy poddać optymalizacji. Przykładowo, gdyby okazało się, że to konkretne zapytanie działa wolno, dzięki rozszerzonym informacjom w logu możemy przypuszczać, że przyczyną jest generowanie tabela tymczasowej na dysku. Być może skutecznym rozwiązaniem będzie zwiększenie rozmiaru tabela tymczasowej możliwej do utworzenia, a jeśli to nie rozwiąże sprawy (a nie rozwiąże, gdyż kolumna `film_text`.`description` jest typu TEXT i musi być utworzona na dysku) to trzeba będzie się przyglądnąć indeksowaniu, ewentualnie zrezygnować z jednego z sortowań.
Tego typu informacje możemy uzyskać w przypadku MySQL z nałożonymi patchami slow_extended i slow_innodb. Patche te są dostępne do pobrania na przykład z repozytorium Percony na Launchpadzie. Patche te można pobrać, a następnie nałożyć je na kod źródłowy MySQL w odpowiedniej wersji i całość skompilować. Jeśli poprzednie zdanie brzmi zbyt przerażająco, to istnieje inna opcja. Na stronie Percony (ale prawdopodobnie nie tylko – MariaDB czy też OurDelta także powinny udostępniać taką wersję – przyznam że nie sprawdzałem) możemy pobrać skompilowaną wersję MySQL z wbudowanymi już patchami rozszerzającymi funkcjonalność. Tego typu binarne dystrybucje po prostu pobieramy na serwer, rozpakowujemy i są gotowe do działania.
Naprawdę bardzo, ale to bardzo polecam skorzystanie z MySQL w wersji z opisywanymi przeze mnie patchami. Jeśli samodzielnie administrujemy serwerem MySQL, grzechem jest nie skorzystać z takiej możliwości. Jeśli samemu nie mamy możliwości zainstalować rozszerzonej wersji MySQL, to dobrze byłoby spróbować przekonać do tego administratora. Dodatkowe informacje znacznie ułatwiają optymalizację zapytań i analizę tego, co się dzieje w bazie danych. Jasne, że nie zawsze będzie to możliwe – może być tak, że w danej organizacji do produkcji dopuszczone są tylko oficjalne wydania. W takiej sytuacji dobrze mieć rozszerzoną wersję choćby na serwerach testowych – to przyspieszy debugging problemów.
Komentarze