10 podstawowych wskazówek dotyczących wydajności MySQL

Podobnie jak w przypadku wszystkich relacyjnych baz danych, MySQL może okazać się skomplikowaną bestią, która w każdej chwili może się zatrzymać, pozostawiając aplikacje w potrzebie, a biznes w sieci.

Prawda jest taka, że ​​typowe błędy są przyczyną większości problemów z wydajnością MySQL. Aby upewnić się, że serwer MySQL pracuje z maksymalną prędkością, zapewniając stabilną i stałą wydajność, ważne jest, aby wyeliminować te błędy, które są często przesłonięte przez subtelności w obciążeniu lub pułapka konfiguracji.

Na szczęście wiele problemów z wydajnością MySQL okazuje się mieć podobne rozwiązania, co sprawia, że ​​rozwiązywanie problemów i dostrajanie MySQL jest zadaniem wykonalnym.

Oto 10 wskazówek, jak uzyskać doskonałą wydajność z MySQL.

Wskazówka dotycząca wydajności MySQL nr 1: Profilowanie obciążenia pracą

Najlepszym sposobem, aby zrozumieć, jak serwer spędza czas, jest profilowanie obciążenia serwera. Profilując obciążenie, można udostępniać najdroższe zapytania do dalszego dostrajania. Tutaj czas jest najważniejszą miarą, ponieważ kiedy wysyłasz zapytanie do serwera, nie obchodzi Cię nic poza tym, jak szybko się zakończy.

Najlepszym sposobem profilowania obciążenia jest użycie narzędzia, takiego jak analizator zapytań MySQL Enterprise Monitor lub zestawienie zapytań pt z zestawu narzędzi Percona. Te narzędzia przechwytują zapytania wykonywane przez serwer i zwracają tabelę zadań posortowanych według zmniejszającego się czasu odpowiedzi, błyskawicznie przenosząc najdroższe i najbardziej czasochłonne zadania do góry, dzięki czemu można zobaczyć, na czym należy się skoncentrować.

Narzędzia do profilowania obciążenia grupują podobne zapytania razem, umożliwiając wyświetlenie zapytań, które są wolne, a także zapytań, które są szybkie, ale wykonywane wielokrotnie.

Wskazówka dotycząca wydajności MySQL nr 2: Zapoznaj się z czterema podstawowymi zasobami

Aby działać, serwer bazy danych potrzebuje czterech podstawowych zasobów: procesora, pamięci, dysku i sieci. Jeśli którykolwiek z nich jest słaby, błędny lub przeciążony, prawdopodobnie serwer bazy danych będzie działał słabo.

Zrozumienie podstawowych zasobów jest ważne w dwóch szczególnych obszarach: doborze sprzętu i rozwiązywaniu problemów.

Wybierając sprzęt dla MySQL, upewnij się, że wszystkie komponenty mają dobrą wydajność. Równie ważne, zrównoważyć je względem siebie. Często organizacje wybierają serwery z szybkimi procesorami i dyskami, ale brakuje im pamięci. W niektórych przypadkach dodanie pamięci jest tanim sposobem na zwiększenie wydajności o rząd wielkości, zwłaszcza w przypadku obciążeń związanych z dyskiem. Może się to wydawać sprzeczne z intuicją, ale w wielu przypadkach dyski są nadmiernie wykorzystywane, ponieważ nie ma wystarczającej ilości pamięci, aby pomieścić działający zestaw danych serwera.

Kolejny dobry przykład tej równowagi dotyczy procesorów. W większości przypadków MySQL będzie działał dobrze z szybkimi procesorami, ponieważ każde zapytanie działa w jednym wątku i nie może być równoległe między procesorami.

Jeśli chodzi o rozwiązywanie problemów, sprawdź wydajność i wykorzystanie wszystkich czterech zasobów, uważnie sprawdzając, czy działają one słabo, czy po prostu są poproszone o wykonanie zbyt dużej ilości pracy. Ta wiedza może pomóc szybko rozwiązać problemy.

Wskazówka dotycząca wydajności MySQL nr 3: Nie używaj MySQL jako kolejki

Kolejki i wzorce dostępu podobne do kolejki mogą przedostać się do Twojej aplikacji bez Twojej wiedzy. Na przykład, jeśli ustawisz status elementu tak, aby określony proces roboczy mógł go odebrać, zanim zacznie na nim działać, nieświadomie tworzysz kolejkę. Oznaczanie e-maili jako niewysłanych, wysyłanie ich, a następnie oznaczanie jako wysłanych to typowy przykład.

Kolejki powodują problemy z dwóch głównych powodów: serializują obciążenie, uniemożliwiając wykonywanie zadań równolegle, i często skutkują tabelą zawierającą pracę w toku, a także dane historyczne z zadań, które zostały przetworzone dawno temu. Oba dodają opóźnienie do aplikacji i ładują do MySQL.

Wskazówka dotycząca wydajności MySQL nr 4: Filtruj wyniki według najtańszych wyników

Świetnym sposobem optymalizacji MySQL jest wykonanie najpierw taniej, nieprecyzyjnej pracy, a następnie ciężkiej, precyzyjnej pracy na mniejszym, wynikowym zestawie danych.

Załóżmy na przykład, że szukasz czegoś w określonym promieniu punktu geograficznego. Pierwszym narzędziem w przyborniku wielu programistów jest wzór na wielkie koło (Haversine) do obliczania odległości wzdłuż powierzchni kuli. Problem z tą techniką polega na tym, że wzór wymaga wielu operacji trygonometrycznych, które bardzo obciążają procesor. Obliczenia wielkiego koła zwykle działają powoli i powodują gwałtowny wzrost wykorzystania procesora przez maszynę.

Przed zastosowaniem wzoru na wielkie koło, zredukuj swoje rekordy do małego podzbioru sumy i przytnij wynikowy zestaw do precyzyjnego koła. Prostym sposobem na zrobienie tego jest kwadrat zawierający okrąg (precyzyjnie lub nieprecyzyjnie). W ten sposób świat poza placem nigdy nie zostanie trafiony wszystkimi kosztownymi funkcjami trygonometrycznymi.

Wskazówka dotycząca wydajności MySQL nr 5: Poznaj dwie śmiertelne pułapki związane ze skalowalnością

Skalowalność nie jest tak ogólnikowa, jak mogłoby się wydawać. W rzeczywistości istnieją precyzyjne matematyczne definicje skalowalności, które są wyrażone w postaci równań. Te równania pokazują, dlaczego systemy nie skalują się tak dobrze, jak powinny.

Weźmy Prawo Uniwersalnej Skalowalności, definicję przydatną przy wyrażaniu i określaniu ilościowym cech skalowalności systemu. Wyjaśnia problemy skalowania w kategoriach dwóch podstawowych kosztów: serializacji i przesłuchu.

Równoległe procesy, które muszą zostać zatrzymane, aby mogło dojść do serializacji, mają z natury ograniczoną skalowalność. Podobnie, jeśli równoległe procesy muszą ze sobą rozmawiać przez cały czas, aby koordynować swoją pracę, ograniczają się wzajemnie.

Unikaj serializacji i przesłuchów, a Twoja aplikacja będzie znacznie lepiej skalować. Co to przekłada się na wnętrze MySQL? Różni się, ale niektórymi przykładami byłoby unikanie wyłącznych blokad w wierszach. Z tego powodu kolejki, punkt 3 powyżej, mają tendencję do słabego skalowania.

Wskazówka dotycząca wydajności MySQL nr 6: Nie skupiaj się zbytnio na konfiguracji

Administratorzy baz danych poświęcają zwykle ogromną ilość czasu na ulepszanie konfiguracji. Rezultat zwykle nie jest dużą poprawą, a czasami może być nawet bardzo szkodliwy. Widziałem wiele „zoptymalizowanych” serwerów, które ciągle ulegały awariom, zabrakło pamięci i działały słabo, gdy obciążenie było trochę bardziej intensywne.

Domyślne ustawienia dostarczane z MySQL są uniwersalne i bardzo przestarzałe, ale nie musisz konfigurować wszystkiego. Lepiej jest dobrze poznać podstawy i zmienić inne ustawienia tylko w razie potrzeby. W większości przypadków 95% maksymalnej wydajności serwera można uzyskać, ustawiając poprawnie około 10 opcji. Kilka sytuacji, w których to nie ma zastosowania, będzie przypadkami skrajnymi, unikalnymi dla twoich okoliczności.

W większości przypadków narzędzia do „dostrajania” serwera nie są zalecane, ponieważ dają wskazówki, które nie mają sensu w określonych przypadkach. Niektóre mają nawet zakodowane niebezpieczne, niedokładne porady - takie jak wskaźniki trafień w pamięci podręcznej i formuły zużycia pamięci. Nigdy nie były one słuszne, a wraz z upływem czasu stały się jeszcze mniej poprawne.

Wskazówka dotycząca wydajności MySQL nr 7: Uważaj na zapytania dotyczące stronicowania

Aplikacje, które paginują, zwykle rzucają serwer na kolana. Pokazując stronę z wynikami, z linkiem prowadzącym do następnej strony, aplikacje te zazwyczaj grupują i sortują w sposób, w którym nie można używać indeksów, i wykorzystują LIMITi offsetpowodują, że serwer generuje dużo pracy, następnie odrzucając wiersze.

Optymalizacje często można znaleźć w samym interfejsie użytkownika. Zamiast pokazywać dokładną liczbę stron w wynikach i linki do każdej strony z osobna, możesz po prostu pokazać link do następnej strony. Możesz także uniemożliwić ludziom przechodzenie do stron znajdujących się zbyt daleko od pierwszej strony.

Po stronie zapytania, zamiast używać LIMITwith offset, możesz wybrać o jeden wiersz więcej niż potrzebujesz, a gdy użytkownik kliknie łącze „następna strona”, możesz wyznaczyć ten ostatni wiersz jako punkt początkowy dla następnego zestawu wyników. Na przykład, jeśli użytkownik wyświetlił stronę z wierszami od 101 do 120, wybierzesz również wiersz 121; aby wyrenderować następną stronę, należałoby zapytać serwer o wiersze większe lub równe 121, limit 21.

Wskazówka dotycząca wydajności MySQL nr 8: Chętnie zapisuj statystyki, niechętnie ostrzegaj

Monitorowanie i ostrzeganie są niezbędne, ale co dzieje się z typowym systemem monitorowania? Zaczyna wysyłać fałszywe alarmy, a administratorzy systemu konfigurują reguły filtrowania wiadomości e-mail, aby zatrzymać hałas. Wkrótce Twój system monitorowania stanie się całkowicie bezużyteczny.

Lubię myśleć o monitorowaniu na dwa sposoby: przechwytywaniu wskaźników i ostrzeganiu. Bardzo ważne jest, aby uchwycić i zapisać wszystkie możliwe metryki, ponieważ będziesz zadowolony z ich posiadania, gdy będziesz próbować dowiedzieć się, co zmieniło się w systemie. Pewnego dnia pojawi się dziwny problem i spodoba ci się możliwość wskazywania wykresu i pokazywania zmiany w obciążeniu serwera.

Z drugiej strony istnieje tendencja do zbytniego ostrzegania. Ludzie często ostrzegają o takich rzeczach, jak współczynnik trafień bufora lub liczba tymczasowych tabel tworzonych na sekundę. Problem w tym, że nie ma dobrego progu dla takiego wskaźnika. Właściwy próg różni się nie tylko od serwera do serwera, ale z godziny na godzinę, gdy zmienia się obciążenie.

W rezultacie ostrzegaj oszczędnie i tylko w warunkach wskazujących na określony problem, który można podjąć. Niski współczynnik trafień bufora nie podlega działaniu ani nie wskazuje na rzeczywisty problem, ale serwer, który nie odpowiada na próbę połączenia, jest rzeczywistym problemem, który należy rozwiązać.

Wskazówka dotycząca wydajności MySQL nr 9: Naucz się trzech zasad indeksowania

Indeksowanie jest prawdopodobnie najbardziej niezrozumianym tematem w bazach danych, ponieważ istnieje wiele sposobów, aby zorientować się, jak działają indeksy i jak je wykorzystuje serwer. Aby naprawdę zrozumieć, co się dzieje, potrzeba dużo wysiłku.

Indeksy, jeśli są odpowiednio zaprojektowane, służą trzem ważnym celom na serwerze bazy danych:

  1. Indeksy pozwalają serwerowi znajdować grupy sąsiednich wierszy zamiast pojedynczych wierszy. Wiele osób uważa, że ​​celem indeksu jest znalezienie pojedynczych wierszy, ale znalezienie pojedynczych wierszy prowadzi do losowych operacji na dysku, które są powolne. O wiele lepiej jest znaleźć grupy wierszy, z których wszystkie lub większość jest interesujących, niż znajdować wiersze pojedynczo.
  2. Indeksy pozwalają serwerowi uniknąć sortowania, odczytując wiersze w żądanej kolejności. Sortowanie jest kosztowne. Czytanie wierszy w żądanej kolejności jest znacznie szybsze.
  3. Indeksy pozwalają serwerowi spełnić całe zapytania z samego indeksu, unikając w ogóle potrzeby dostępu do tabeli. Jest to inaczej nazywane indeksem pokrywającym lub zapytaniem tylko o indeks.

Jeśli możesz zaprojektować swoje indeksy i zapytania, aby wykorzystać te trzy możliwości, możesz przyspieszyć swoje zapytania o kilka rzędów wielkości.

Wskazówka dotycząca wydajności MySQL nr 10: Skorzystaj z wiedzy swoich współpracowników

Nie próbuj iść sam. Jeśli zastanawiasz się nad problemem i robisz to, co wydaje ci się logiczne i rozsądne, to świetnie. To zadziała około 19 razy na 20. Innym razem trafisz do króliczej nory, która będzie bardzo kosztowna i czasochłonna, właśnie dlatego, że rozwiązanie, które próbujesz, wydaje się mieć dużo sensu.

Zbuduj sieć zasobów związanych z MySQL - wykracza to poza zestawy narzędzi i przewodniki rozwiązywania problemów. Na listach mailingowych, forach, w witrynach z pytaniami i odpowiedziami czai się kilka osób o ogromnej wiedzy. Konferencje, targi i imprezy dla lokalnych grup użytkowników zapewniają cenne możliwości zdobywania wiedzy i budowania relacji z rówieśnikami, którzy mogą Ci pomóc.

Osoby poszukujące narzędzi uzupełniających te wskazówki mogą skorzystać z Kreatora konfiguracji Percona dla MySQL, Doradcy zapytań Percona dla MySQL i wtyczek Percona Monitoring. (Uwaga: musisz utworzyć konto Percona, aby uzyskać dostęp do tych dwóch pierwszych linków. To nic nie kosztuje). Kreator konfiguracji może pomóc w wygenerowaniu podstawowego pliku my.cnf dla nowego serwera, który jest lepszy od przykładowych plików dostarczanych z serwer. Doradca zapytań przeanalizuje Twój kod SQL, aby pomóc w wykryciu potencjalnie złych wzorców, takich jak zapytania dotyczące stronicowania (nr 7). Percona Monitoring Plugins to zestaw wtyczek do monitorowania i tworzenia wykresów, które pomagają w szybkim zapisywaniu statystyk i niechętnie ostrzegają (nr 8). Wszystkie te narzędzia są dostępne bezpłatnie.