MySQL – optymalizacja i wydajność

Czasami zdarza się, że zamiast oczekiwanego efektu wykonanie SELECT’a kończy się poniższym błędem:

Dzieje się tak wtedy, gdy optimizer MySQL uzna, że dane zapytanie musi sprawdzić większą ilość rekordów niż wartość zmiennej max_join_size. Zmienna ta ma na celu zabezpieczenie serwera przed wykonaniem zapytań, które trwałyby kosmicznie długo. Domyślnie przyjmuje wartość 4G (4*1024*1024*1024). Wydaje się to dużo, ale w praktyce okazuje się, że taki limit prosto przekroczyć.

Tworzymy następujące tabele:

czytaj dalej…

Jak na razie, ruch na blogu jest na tyle niski, że jest możliwość przeanalizowania tego, o co się pytają poszczególni goście, którzy trafili na tę stronę za pomocą Google. Dziś odpowiedź, mam nadzieję że przydatna, na jedno z takich pytań. Gdyby ktoś potrzebował informacji na jakiś temat, zapraszam bezpośrednio do mnie, do zakładki Kontakt. Chętnie się dowiem, na jakiego typu posty jest zapotrzebowanie.

Co pewien czas w wyniku zapytania EXPLAIN, w kolumnie Extra pojawia się wpis: „Using filesort”. Co to dokładnie znaczy?

Nazwa filesort jest, mówiąc delikatnie, niefortunna. W praktyce, filesort nie ma nic wspólnego z sortowaniem na dysku. Jest to po prostu sortowanie wyników, które może odbywać się z wykorzystaniem dysku, ale wcale nie musi. MySQL, jeśli tylko jest w stanie, podczas sortowania wyników zapytania stara się wykorzystać indeksy. Jeśli nie jest w stanie skorzystać z żadnego – stosowany jest właśnie filesort. Filesort to w praktyce dwa różne algorytmy, oparte na algorytmie quicksort. Starszy, dwuprzebiegowy z nich ma mniejsze zapotrzebowanie na pamięć, przez co większa ilość rekordów mieści się w buforze sortowania, generuje natomiast znacznie większe obciążenie dysku losowymi operacjami odczytu i zapisu. Nowszy, dostępny od MySQL 4.1, ma większe zapotrzebowanie na pamięć, natomiast nie wymaga takiej ilości operacji dyskowych. Administrator MySQL ma pewne możliwości sterowania tym, jaki algorytm ma być stosowany. Jeśli wielkość zestawu danych do sortowania przekracza wartość zmiennej konfiguracyjnej max_length_for_sort_data, stosowany jest filesort dwuprzebiegowy, tak więc zwiększając wartość tej zmiennej zwiększamy preferencję stosowania nowszego, jednoprzebiegowego algorytmu.

Jeśli zestaw danych do sortowania jest większy niż wartość zmiennej sort_buffer_size, dane do sortowania dzielone są na mniejsze części, a następnie łączone na dysku. Nie jest to jednak powodem (powtarzam, nie jest to powodem!) aby zwiększać wartość tej zmiennej. Akurat ta zmienna jest doskonałym przykładem na to, że więcej nie znaczy lepiej. Są przypadki, w których zwiększenie buforu zwiększa wydajność. Są też przypadki, kiedy dzieje się zupełnie na odwrót, a w których najlepszym rozwiązaniem jest zmniejszenie bufora. Wiąże się to po części ze sposobem alokacji pamięci przez bibliotekę libc – przy pewnej wielkości (pewien czas temu było to 256KB) zmienia się funkcja służąca do alokacji pamięci z malloc() na mmap(). Każdej zmianie konfiguracji musi towarzyszyć dokładne przetestowanie szybkości wykonywania zapytań, tak aby mieć pewność, że faktycznie zmiany przyniosły oczekiwany efekt.
czytaj dalej…

MySQL udostępnia administratorowi kilka rodzajów logów – error log, slowlog, general log, binlog. W sytuacji, gdy pojawiają się problemy wydajnościowe i konieczne jest przeanalizowanie obciążenia i znalezienie przyczyny kłopotów, przydatny staje się głównie jeden z nich – slowlog. Log ten zawiera informacje na temat zapytań, które wykonywały się dłużej niż wartość zmiennej ‚long_query_time’ w sekundach. Domyślnie jest to 10 sekund. Zazwyczaj domyślna wartość jest dużo za duża – kilkadziesiąt zapytań, z których każde wykonuje się po kilka sekund, może spokojnie zatkać serwer bazodanowy. Z tego powodu, jak również z pewnych historycznych względów, często ustawia się ten parametr na jedną sekundę.
W przypadku MySQL w wersji 5.1 przykładowy wpis ze slowloga może wyglądać następująco:

Co widzimy?
– czas, kiedy zapytanie zostało wykonane: 27 kwietnia 2010 roku o godzinie 20:55:52
– jaki użytkownik i z jakiego hosta wykonał zapytanie: użytkownik root logujący się lokalnie, po sockecie (localhost)
– ile trwało wykonanie zapytania: 0.000274 sekundy
– jak długo tabela była blokowana: 0.000075 sekundy
– ile rekordów zostało przesłanych do klienta: 5
– ile rekordów zostało sprawdzonych w bazie: 5
– zapytanie, jakie zostało wykonane

czytaj dalej…

Zastosowanie ORDER BY RAND() generuje tabelę tymczasową. Zawsze. Administratorzy baz danych nie lubią tabel tymczasowych, bo powodują że zapytanie działa wolno. Niestety, osoby piszące aplikacje korzystające z bazy MySQL często są nieświadome tego, jak dużym problemem może być zastosowanie tego typu sortowania wyników.
Użycie ORDER BY RAND() wymaga od bazy danych utworzenia tabeli tymczasowej z wynikiem zapytania, przydzielenia wszystkim rekordom losowych współczynników, po których są one następnie sortowane. Jeśli tabela tymczasowa miałaby mieć wielkość do kilkuset rekordów, nie stanowi to jeszcze problemu. Problem pojawia się natomiast w momencie, gdy ilość rekordów przekracza kilka tysięcy i pogłębia się wraz ze wzrostem wielkości tabeli.

Jak można zastąpić zastosowanie ORDER BY RAND()? Zależy to od tego, o jakie konkretnie zapytanie chodzi. Jeśli chcemy wyciągnąć pojedynczy, losowy rekord, to względnie  optymalnym rozwiązaniem, ale wymagającym istnienia w tabeli jakiejś kolumny z unikalnymi identyfikatorami rekordów będzie:

Z przedziału od 0 do wyniku powyższego zapytania generujemy losową liczbę (X), a następnie próbujemy znaleźć jakiś rekord o id do niej zbliżonym.

czytaj dalej…

Tabele tymczasowe są nie raz bardzo przydatnym narzędziem dla programisty – udostępniają dodatkowe możliwości przetwarzania danych jeszcze po stronie serwera bazy danych. W tabeli tymczasowej można sobie założyć indeks, można wykonać serię rożnych zapytań na tym samym zestawie danych, już bez konieczności pisania skomplikowanych JOIN’ów itp. Problemem jest to, że tabele tymczasowe tworzone są także automatycznie, jeśli tylko MySQL uzna, że jest to konieczne do zrealizowania danego zapytania. Dlaczego jest to problem? Dlatego, że często programista lub świeżo upieczony administrator MySQL, piszący zapytania, nie jest świadomy tego, kiedy i dlaczego są one tworzone. Jeśli nie jest świadomy, nie jest też w stanie kontrolować zachowania serwera baz danych – prowadzi to często do sytuacji, w której baza danych, z nieznanych przyczyn, zaczyna poważnie zwalniać. Co gorsza, często zdarza się to po pewnym czasie od wdrożenia danego serwisu.

Co to są te automatycznie tworzone tabele tymczasowe? Są to tabele tworzone w oparciu o silnik MEMORY lub MyISAM, które zakładane i zapełniane danymi są przez serwer MySQL. Tworzone są na potrzeby danego, konkretnego zapytania i usuwane są w momencie, gdy przestają być potrzebne. Jak widać, mamy dwa rodzaje tabel – tworzone w pamięci (silnik MEMORY) i na dysku (silnik MyISAM). Z oczywistych względów, szybsze i mniej obciążające serwer są te pierwsze i z tego też względu, jeśli to tylko jest możliwe, to tabela zakładana jest właśnie w pamięci.

Tabela tymczasowa tworzona jest na dysku jeśli:
– jej wielkość przekracza wartości zmiennej max_heap_table_size lub tmp_table_size
– jej zawartość uniemożliwia utworzenie jej przy pomocy silnika MEMORY – w szczególności jeśli zawiera kolumny typu TEXT lub BLOB
czytaj dalej…

Podczas tworzenia nowych, bądź modyfikowania starych zapytań kluczową kwestią jest sprawdzenie, jak dane zapytanie zachowuje się w różnych warunkach. Dlaczego jest to tak ważne? Weźmy pod uwagę następujące zapytanie:

Podczas przeprowadzania jego analizy (http://blog.ksiazek.info/2010/04/09/benchmark-i-profiling/) okazało się, że przydatny może się okazać indeks nałożony na kolumny first_name i last_name w tabeli `actor`.
Po sprawdzeniu wyników okazuje się, że dodanie tego indeksu nieznacznie zmniejszyło czas wykonywania zapytania (tabela `actor1` zawiera dodatkowy indeks):

To samo okazuje się, gdy wykonamy prosty benchmark przy pomocy skryptów php:
– indeks – 3834.18 zapytań na sekundę
– bez indeksu – 3923.48 zapytań na sekundę
czytaj dalej…

Jeśli stosujemy funkcję CONCAT() jako jeden z parametrów JOIN’u, należy pamiętać o pewnych jej cechach. Jeśli wszystkie jej parametry są niebinarnymi ciągami znaków, wynik jej działania także jest niebinarnym ciągiem znaków. Jeśli jakiś paramert jest w postaci binarnej, lub jest to jakaś wartość liczbowa, która z automatu jest konwertowana na postać binarną – wynikiem działania funkcji jest binarny ciąg znaków.

Jakie to ma znaczenie? Załóżmy następującą strukturę bazy danych:

czytaj dalej…

Kolejnym mechanizmem udostępnianym przez MySQL, a który można wykorzystać do przeanalizowania zapytania pod kątem wydajności i zrozumienia co się w czasie jego wykonywania faktycznie dzieje, jest mechanizm statystyk, które dostępne są dzięki zapytaniu:

Wynikiem tego zapytania jest długa lista zmiennych obrazujących stan serwera MySQL w ramach danej sesji – ilość wykonanych zapytań różnych typów, ilość utworzonych tablic tymczasowych, jakiego rodzaju JOIN’y były wykonywane, ile danych zostało przesłanych i tak dalej. Pełna lista tych parametrów wraz z opisem znajduje się w dokumentacji MySQL – http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html

czytaj dalej…

Wykorzystanie zapytania EXPLAIN i przeanalizowanie planu naszego SELECT’a to pierwszy krok, który należy wykonać aby sprawdzić wydajność zapytania. Nie jest to jednak wszystko. MySQL udostępnia dodatkowe mechanizmy, które pokazują, co dokładnie dzieje się z zapytaniem – jak jest wykonywane, jakie operacje są przeprowadzane na tabelach itp.
Pierwszy z tych mechanizmów to profiler. Włączamy go wykonując zapytanie

czytaj dalej…

Podstawową zasadą pisania zapytań jest to, że każde z nich gruntownie testujemy pod względem wydajności. Do koniecznego minimum należy sprawdzenie planu, jaki MySQL stworzył dla danego zapytania. Służy do tego polecenie EXPLAIN.

Przykładowy wynik działania takiego zapytania może wyglądać na przykład tak.

czytaj dalej…