monitorowanie wskaźników wydajności MySQL

author
16 minutes, 34 seconds Read

ten post jest częścią 1 z 3-częściowej serii o monitorowaniu MySQL. Część 2 jest o zbieraniu metryk z MySQL, a część 3 wyjaśnia, jak monitorować MySQL za pomocą Datadog.

co to jest MySQL?

MySQL jest najpopularniejszym serwerem relacyjnej bazy danych open source na świecie. Należący do Oracle, MySQL jest dostępny w darmowej edycji społecznościowej do pobrania, a także w wersjach komercyjnych z dodatkowymi funkcjami i obsługą. Początkowo wydany w 1995 roku, MySQL od tego czasu zrodził głośne widelce dla konkurencyjnych technologii, takich jak MariaDB i Percona.

kluczowe statystyki MySQL

jeśli twoja baza danych działa powoli lub nie obsługuje zapytań z jakiegokolwiek powodu, każda część stosu, która zależy od tej bazy danych, również będzie miała problemy z wydajnością. Aby zapewnić płynne działanie bazy danych, możesz aktywnie monitorować wskaźniki obejmujące cztery obszary wydajności i wykorzystania zasobów:

  • przepływność zapytań
  • wydajność wykonywania zapytań
  • połączenia
  • wykorzystanie puli buforów

użytkownicy MySQL mogą uzyskać dostęp do setek metryk z bazy danych, więc w tym artykule skupimy się na kilku kluczowych metrykach, które pozwolą ci uzyskać w czasie rzeczywistym wgląd w kondycję i wydajność bazy danych. W drugiej części tej serii pokażemy Ci, jak uzyskać dostęp i zebrać wszystkie te dane.

Ten artykuł odwołuje się do terminologii metrycznej wprowadzonej w naszej serii monitorowania 101, która zapewnia ramy dla gromadzenia i alarmowania metrycznego.

kompatybilność między wersjami i technologiami

niektóre strategie monitorowania omówione w tej serii są specyficzne dla wersji MySQL 5.6 i 5.7. Różnice między tymi wersjami zostaną wskazane Po drodze.

Większość przedstawionych tu wskaźników i strategii monitorowania dotyczy również technologii zgodnych z MySQL, takich jak MariaDB i Percona Server, z pewnymi zauważalnymi różnicami. Na przykład niektóre funkcje programu MySQL Workbench, opisane w części 2 tej serii, nie są kompatybilne z aktualnie dostępnymi wersjami MariaDB.

użytkownicy Amazon RDS powinni zapoznać się z naszymi specjalistycznymi przewodnikami monitorowania dla MySQL na RDS i dla Amazon Aurora kompatybilnego z MySQL.

przepustowość zapytań

zapytania MySQL
Nazwa opis Typ metryczny dostępność
pytania ilość wykonanych poleceń (wysłanych przez Klienta) praca: przepływność zmienna stanu serwera
Com_select polecenia SELECT praca: przepustowość zmienna stanu serwera
zapisuje wstawia, aktualizuje lub usuwa praca: Przepustowość obliczona ze zmiennych stanu serwera

Twoim głównym problemem w monitorowaniu każdego systemu jest upewnienie się, że jego praca jest wykonywana skutecznie. Praca bazy danych uruchamia zapytania, więc pierwszym priorytetem monitorowania powinno być upewnienie się, że MySQL wykonuje zapytania zgodnie z oczekiwaniami.

MySQL ma wewnętrzny licznik („zmienna stanu serwera”, w języku MySQL) o nazwie Questions, który jest zwiększany dla wszystkich instrukcji wysyłanych przez aplikacje klienckie. Widok zorientowany na klienta dostarczany przez metrykę Questions często ułatwia interpretację niż powiązany licznik Queries, który liczy również polecenia wykonywane jako część przechowywanych programów, a także polecenia takie jak PREPARE i DEALLOCATE PREPARE uruchamiane jako część przygotowanych instrukcji po stronie serwera.

aby odpytywać zmienną stanu serwera, taką jak Questions lub Com_select:

SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+

możesz także monitorować podział poleceń odczytu i zapisu,aby lepiej zrozumieć obciążenie bazy danych i zidentyfikować potencjalne wąskie gardła. Zapytania odczytu są zazwyczaj przechwytywane przez metrykę Com_select. Zapisuje przyrost jednej z trzech zmiennych stanu, w zależności od polecenia:

zapisuje = Com_insert + Com_update + Com_delete

Metryka do ostrzegania: pytania

obecna liczba zapytań będzie naturalnie rosła i spadała, i jako taka nie zawsze jest to metryka wykonalna w oparciu o ustalone progi. Warto jednak zwracać uwagę na nagłe zmiany objętości zapytań—szczególnie drastyczne spadki przepustowości mogą wskazywać na poważny problem.

wykonanie zapytania

Wykres opóźnień MySQL
Nazwa opis Typ metryczny dostępność
czas wykonania zapytania średni czas wykonania według schematu praca: wydajność Zapytanie o schemat wydajności
błędy zapytań Liczba instrukcji SQL, które wygenerowały błędy praca: Błąd Zapytanie o schemat wydajności
Slow_queries liczba zapytań przekraczająca konfigurowalnylong_query_time limit praca: wydajność zmienna stanu serwera

użytkownicy MySQL mają wiele opcji monitorowania opóźnień zapytań, zarówno poprzez wykorzystanie wbudowanych metryk MySQL, jak i zapytania o schemat wydajności. Domyślnie włączone od wersji MySQL 5.6.6, tabele bazy danych performance_schema w MySQL przechowują statystyki niskopoziomowe dotyczące zdarzeń serwera i wykonywania zapytań.

podsumowanie instrukcji schematu wydajności

wiele kluczowych wskaźników znajduje się w tabeli events_statements_summary_by_digest schematu wydajności, która przechwytuje informacje o opóźnieniach, błędach i woluminie zapytań dla każdej znormalizowanej instrukcji. Przykładowy wiersz z tabeli pokazuje polecenie, które zostało uruchomione dwa razy i które zajęło średnio milisekundy 325 (wszystkie pomiary czasowe są w pikosekundach):

*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55

tabela digest normalizuje wszystkie polecenia (jak widać w polu DIGEST_TEXT powyżej), ignorując wartości danych i standaryzując spacje i wielkie litery, tak aby następujące dwa zapytania były uważane za takie same:

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

aby wyodrębnić średni czas pracy danego schematu w mikrosekundach, możesz odpytywać schemat wydajności:

SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+

podobnie, aby policzyć całkowitą liczbę instrukcji na schemat, który generował błędy:

SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+

schemat sys

odpytywanie schematu wydajności, jak pokazano powyżej, działa świetnie do programowego pobierania metryk z bazy danych. Jednak w przypadku zapytań ad hoc i badań zwykle łatwiej jest użyć schematu sys MySQL. Schemat sys zapewnia uporządkowany zestaw metryk w formacie bardziej czytelnym dla człowieka, dzięki czemu odpowiednie zapytania są znacznie prostsze. Na przykład, aby znaleźć najwolniejsze polecenia (te w 95. percentylu przez runtime):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

lub sprawdzić, które znormalizowane Oświadczenia wygenerowały błędy:

SELECT * FROM sys.statements_with_errors_or_warnings;

wiele innych przydatnych przykładów znajduje się w dokumentacji schematu sys. Schemat sys jest zawarty w MySQL począwszy od wersji 5.7.7, ale użytkownicy MySQL 5.6 mogą go zainstalować za pomocą zaledwie kilku poleceń. Patrz część 2 tej serii, aby uzyskać instrukcje.

powolne zapytania

oprócz bogactwa danych wydajności dostępnych w schemacie wydajności i schemacie sys, MySQL posiada licznik Slow_queries, który zwiększa się za każdym razem, gdy czas wykonania zapytania przekroczy liczbę sekund określoną przez parametr long_query_time. Próg jest domyślnie ustawiony na 10 sekund:

SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

parametr long_query_time można dostosować za pomocą jednego polecenia. Na przykład, aby ustawić próg wolnego zapytania na pięć sekund:

SET GLOBAL long_query_time = 5;

(należy pamiętać, że konieczne może być zamknięcie sesji i ponowne połączenie z bazą danych, aby zmiana została zastosowana na poziomie sesji.)

badanie problemów z wydajnością zapytań

jeśli Twoje zapytania wykonują się wolniej niż oczekiwano, często jest tak, że winowajcą jest ostatnio zmienione zapytanie. Jeśli żadne zapytanie nie zostanie uznane za zbyt wolne, następne do oceny są mierniki na poziomie systemu, aby szukać ograniczeń w podstawowych zasobach (CPU, We/Wy dysku, pamięci i sieci). Nasycenie procesora i wąskie gardła We / Wy są powszechnymi winowajcami. Możesz również sprawdzić metrykę Innodb_row_lock_waits, która liczy, jak często silnik pamięci InnoDB musiał czekać, aby uzyskać blokadę w danym wierszu. InnoDB jest domyślnym silnikiem pamięci masowej od wersji MySQL 5.5, a MySQL używa blokowania na poziomie wiersza dla tabel InnoDB.

aby zwiększyć szybkość operacji odczytu i zapisu, wielu użytkowników będzie chciało dostosować rozmiar puli buforów używanej przez InnoDB do buforowania danych tabeli i indeksu. Więcej na temat monitorowania i zmiany rozmiaru puli buforów poniżej.

metryki do alertu o

  • czas wykonywania zapytania: Zarządzanie opóźnieniami dla kluczowych baz danych ma kluczowe znaczenie. Jeśli średni czas wykonywania zapytań w produkcyjnej bazie danych zaczyna wzrastać, poszukaj ograniczeń zasobów w wystąpieniach bazy danych, możliwych zastrzeżeń do blokad wierszy lub tabel i zmian wzorców zapytań po stronie klienta.
  • błędy zapytań: Nagły wzrost liczby błędów zapytań może wskazywać na problem z aplikacją kliencką lub samą bazą danych. Możesz użyć schematu sys, aby szybko zbadać, które zapytania mogą powodować problemy. Na przykład, aby wymienić 10 znormalizowanych instrukcji, które zwróciły najwięcej błędów:
      SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;  
  • Slow_queries: sposób definiowania powolnego zapytania (a zatem sposób konfigurowania parametru long_query_time) zależy od przypadku użycia. Niezależnie od twojej definicji „wolnego”, prawdopodobnie będziesz chciał zbadać, czy liczba wolnych zapytań wzrośnie powyżej poziomów bazowych. Aby zidentyfikować rzeczywiste zapytania wykonywane powoli, możesz odpytywać schemat sys lub zanurzyć się w opcjonalnym dzienniku wolnych zapytań MySQL, który jest domyślnie wyłączony. Więcej informacji na temat włączania i uzyskiwania dostępu do dziennika wolnych zapytań można znaleźć w dokumentacji MySQL.

połączenia MySQL
Nazwa opis Typ metryczny dostępność
Threads_connected aktualnie otwarte połączenia zasób: Wykorzystanie zmienna stanu serwera
Threads_running aktualnie uruchomione połączenia zasób: wykorzystanie zmienna stanu serwera
Connection_errors_ internal liczba połączeń odrzuconych z powodu błędu serwera Resource: Error zmienna stanu serwera
Aborted_connects liczba nieudanych prób połączenia z serwerem zasób: Błąd zmienna stanu serwera
Connection_errors_ max_connections liczba połączeń odrzuconych ze względu na max_connections limit zasób: błąd zmienna stanu serwera

sprawdzanie i ustawianie limitu połączeń

monitorowanie połączeń klienckich ma kluczowe znaczenie, ponieważ po wyczerpaniu dostępnych połączeń nowe połączenia klienckie zostaną odrzucone. Limit połączenia MySQL domyślnie wynosi 151, ale można go zweryfikować za pomocą zapytania:

SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+

dokumentacja MySQL sugeruje, że solidne serwery powinny być w stanie obsłużyć połączenia w wysokich setkach lub tysiącach:

„Linux lub Solaris powinny być w stanie obsługiwać rutynowo od 500 do 1000 jednoczesnych połączeń i aż 10 000 połączeń, Jeśli masz wiele gigabajtów pamięci RAM i obciążenie każdego z nich jest niskie lub docelowy czas odpowiedzi jest niewymagający. Windows jest ograniczony do (Otwarte tabele × 2 + otwarte połączenia) < 2048 ze względu na warstwę zgodności z Posix używaną na tej platformie.”

limit połączeń można regulować w locie:

SET GLOBAL max_connections = 200;

to ustawienie powróci do wartości domyślnej po ponownym uruchomieniu serwera. Aby na stałe ustawić limit połączenia, dodaj linię taką jak ta do pliku konfiguracyjnego my.cnf (zobacz ten post, aby uzyskać pomoc w zlokalizowaniu pliku konfiguracyjnego):

max_connections = 200

Monitorowanie wykorzystania połączenia

MySQL ujawnia Threads_connected metryczne liczenie wątków połączenia – jeden wątek na połączenie. Monitorując ten wskaźnik wraz z skonfigurowanym limitem połączenia, możesz upewnić się, że masz wystarczającą pojemność do obsługi nowych połączeń. MySQL ujawnia również metrykę Threads_running, aby wyizolować, który z tych wątków aktywnie przetwarza zapytania w danym momencie, w przeciwieństwie do połączeń, które są otwarte, ale są obecnie bezczynne.

jeśli twój serwer osiągnie limit max_connections, zacznie odrzucać połączenia. W takim przypadku metryka Connection_errors_max_connections zostanie zwiększona, podobnie jak metryka Aborted_connects śledząca wszystkie nieudane próby połączenia.

MySQL udostępnia wiele innych wskaźników dotyczących błędów połączenia, które mogą pomóc w zbadaniu problemów z połączeniem. Metryka Connection_errors_internal jest dobra do oglądania, ponieważ jest zwiększana tylko wtedy, gdy błąd pochodzi z samego serwera. Błędy wewnętrzne mogą odzwierciedlać stan braku pamięci lub niemożność uruchomienia nowego wątku przez serwer.

metryki do wpisu na

  • Threads_connected: jeśli klient spróbuje połączyć się z MySQL, gdy wszystkie dostępne połączenia są w użyciu, MySQL zwróci błąd „zbyt wiele połączeń” i zwiększy wartość Connection_errors_max_connections. Aby zapobiec temu scenariuszowi, należy monitorować liczbę otwartych połączeń i upewnić się, że pozostaje ona bezpiecznie poniżej skonfigurowanego limitu max_connections.
  • Aborted_connects: jeśli licznik rośnie, twoi klienci próbują i nie łączą się z bazą danych. Zbadaj źródło problemu dzięki drobnoziarnistym metrykom połączeń, takim jak Connection_errors_max_connectionsi Connection_errors_internal.

wykorzystanie puli buforowej

wykorzystanie puli buforów MySQL
Nazwa opis Typ metryczny dostępność
Innodb_buffer_pool_pages_total całkowita liczba stron w puli bufora zasób: Wykorzystanie zmienna stanu serwera
wykorzystanie puli buforów stosunek użytych do wszystkich stron w puli buforów zasoby: wykorzystanie obliczone ze zmiennych stanu serwera
Innodb_buffer_pool_read_requests zapytania do puli buforów zasoby: wykorzystanie zmienna stanu serwera
Innodb_buffer_pool_reads Requests the buffer pool could not fulfill Resource: Nasycenie zmienna stanu serwera

domyślny silnik pamięci MySQL, InnoDB, wykorzystuje obszar pamięci zwany pulą buforów do buforowania danych dla tabel i indeksów. Wskaźniki puli buforów są wskaźnikami zasobów w przeciwieństwie do wskaźników pracy i jako takie są przede wszystkim przydatne do badania (zamiast wykrywania) problemów z wydajnością. Jeśli wydajność bazy danych zaczyna spadać podczas wzrostu We/Wy dysku, rozszerzenie puli buforów często może przynieść korzyści.

rozmiar puli buforów

pula buforów domyślnie wynosi 128 mebibajtów, ale MySQL zaleca zwiększenie jej do 80 procent fizycznej pamięci na dedykowanym serwerze bazy danych. MySQL dodaje również kilka uwag o ostrożności, jednak, ponieważ obciążenie pamięci InnoDB może zwiększyć rozmiar pamięci o około 10 procent powyżej przydzielonego rozmiaru puli buforów. A jeśli zabraknie pamięci fizycznej, system będzie uciekać się do stronicowania i wydajność będzie znacznie ucierpieć.

pulę buforów można również podzielić na oddzielne regiony, znane jako instancje. Użycie wielu instancji może poprawić współbieżność dla pul buforów w zakresie wielu GiB.

operacje zmiany rozmiaru puli buforów są wykonywane w kawałkach, a rozmiar puli buforów musi być ustawiony na wielokrotność rozmiaru fragmentu razy liczba wystąpień:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

Rozmiar fragmentu domyślnie wynosi 128 MB, ale jest konfigurowalny od MySQL 5.7.5. Wartość obu parametrów można sprawdzić w następujący sposób:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

jeśli zapytanie innodb_buffer_pool_chunk_size nie zwraca wyników, parametr nie jest dostrajalny w Twojej wersji MySQL i można założyć, że wynosi 128 MiB.

aby ustawić rozmiar puli buforów i liczbę wystąpień podczas uruchamiania serwera:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

począwszy od MySQL 5.7.5, można również zmieniać rozmiar puli buforów w locie za pomocą polecenia SET określającego żądany rozmiar w bajtach. Na przykład w przypadku dwóch instancji puli buforów można ustawić każdy rozmiar na 4 GiB, ustawiając Całkowity rozmiar na 8 GiB:

SET GLOBAL innodb_buffer_pool_size=8589934592;

kluczowe wskaźniki puli buforów InnoDB

MySQL udostępnia garść wskaźników dotyczących puli buforów i jej wykorzystania. Niektóre z najbardziej przydatnych są metryki śledzące Całkowity rozmiar puli buforów, ile jest w użyciu i jak skutecznie pula buforów służy odczytom.

metryki Innodb_buffer_pool_read_requests i Innodb_buffer_pool_reads są kluczem do zrozumienia wykorzystania puli buforów. Innodb_buffer_pool_read_requests śledzi liczbę logicznych żądań odczytu, podczas gdy Innodb_buffer_pool_reads śledzi liczbę żądań, których pula buforów nie mogła zaspokoić i dlatego musiała zostać odczytana z dysku. Biorąc pod uwagę, że odczyt z pamięci jest na ogół o rząd wielkości szybszy niż odczyt z dysku, wydajność ucierpi, jeśli Innodb_buffer_pool_reads zacznie rosnąć.

wykorzystanie puli buforów jest przydatnym wskaźnikiem do sprawdzenia przed rozważeniem zmiany rozmiaru puli buforów. Metryka wykorzystania nie jest dostępna po wyjęciu z pudełka, ale można ją łatwo obliczyć w następujący sposób:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

jeśli baza danych obsługuje dużą liczbę odczytów z dysku, ale pula buforów jest daleka od pełnej, może się zdarzyć, że pamięć podręczna została niedawno wyczyszczona i nadal się rozgrzewa. Jeśli pula buforów nie zapełnia się, ale skutecznie obsługuje odczyty, działający zestaw danych prawdopodobnie wygodnie mieści się w pamięci.

wysokie wykorzystanie puli buforów, z drugiej strony, niekoniecznie jest złe w izolacji, ponieważ stare lub nieużywane dane są automatycznie starzone poza pamięcią podręczną za pomocą zasady LRU. Ale jeśli pula buforów nie służy efektywnie obciążeniu odczytem, może to być czas na skalowanie pamięci podręcznej.

Konwersja metryk puli buforów na bajty

Większość metryk puli buforów jest zgłaszana jako liczba stron pamięci, ale te metryki mogą być konwertowane na bajty, co ułatwia łączenie tych metryk z rzeczywistym rozmiarem puli buforów. Na przykład, aby znaleźć całkowity rozmiar puli buforów w bajtach za pomocą zmiennej Status serwera śledzenie wszystkich stron w puli buforów:

Innodb_buffer_pool_pages_total * innodb_page_size

Rozmiar strony InnoDB jest regulowany, ale domyślnie wynosi 16 KiB, czyli 16,384 bajtów. Jego aktualną wartość można sprawdzić za pomocą zapytania SHOW VARIABLES :

SHOW VARIABLES LIKE "innodb_page_size";

podsumowanie

W tym poście zbadaliśmy kilka najważniejszych wskaźników, które powinieneś monitorować, aby mieć oko na aktywność i wydajność MySQL. Jeśli budujesz swój monitoring MySQL, przechwytywanie metryk przedstawionych poniżej pozwoli Ci na zrozumienie wzorców użytkowania bazy danych i potencjalnych ograniczeń. Pomogą one również określić, kiedy konieczne jest skalowanie lub przeniesienie instancji bazy danych do bardziej wydajnych hostów w celu utrzymania dobrej wydajności aplikacji.

  • przepustowość zapytań
  • opóźnienia i błędy zapytań
  • połączenia i błędy klientów
  • wykorzystanie puli buforów

część 2 tej serii zawiera instrukcje dotyczące zbierania i monitorowania wszystkich potrzebnych metryk z MySQL.

podziękowania

podziękowania dla Dave Stokes z Oracle i Ewen Fortune z VividCortex za dostarczenie cennych opinii na temat tego artykułu przed publikacją.

Similar Posts

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.