Udostępnij za pośrednictwem

Rozwiązywanie problemów z wydajnością zapytań w usłudze Azure Database for MySQL — serwer elastyczny

  • Artykuł

Na wydajność zapytań może mieć wpływ wiele czynników, dlatego najpierw należy przyjrzeć się zakresowi objawów, które występują w wystąpieniu serwera elastycznego usługi Azure Database for MySQL. Na przykład wydajność zapytań jest niska dla:

  • Wszystkie zapytania uruchomione w wystąpieniu serwera elastycznego usługi Azure Database for MySQL?
  • Konkretny zestaw zapytań?
  • Konkretne zapytanie?

Należy również pamiętać, że wszelkie ostatnie zmiany struktury lub danych bazowych tabel, które wysyłasz zapytania, mogą mieć wpływ na wydajność.

Włączanie funkcji rejestrowania

Przed przeanalizowaniem poszczególnych zapytań należy zdefiniować testy porównawcze zapytań. Dzięki tym informacjom można zaimplementować funkcję rejestrowania na serwerze bazy danych w celu śledzenia zapytań przekraczających określony próg na podstawie potrzeb aplikacji.

Usługa Azure Database for MySQL — serwer elastyczny, zaleca się używanie funkcji dziennika wolnych zapytań do identyfikowania zapytań, które trwa dłużej niż N sekund. Po zidentyfikowaniu zapytań z dziennika wolnych zapytań możesz użyć diagnostyki MySQL do rozwiązywania problemów z tymi zapytaniami.

Przed rozpoczęciem śledzenia długotrwałych zapytań należy włączyć slow_query_log parametr przy użyciu witryny Azure Portal lub interfejsu wiersza polecenia platformy Azure. Po włączeniu tego parametru należy również skonfigurować wartość parametru long_query_time , aby określić liczbę sekund, które zapytania mogą być uruchamiane przed zidentyfikowaniem jako "wolno działające" zapytania. Wartość domyślna parametru to 10 sekund, ale możesz dostosować wartość, aby zaspokoić potrzeby umowy SLA aplikacji.

Zrzut ekranu przedstawiający interfejs dziennika wolnych zapytań serwera usługi Azure Database for MySQL.

Dziennik wolnych zapytań jest doskonałym narzędziem do śledzenia długotrwałych zapytań, ale istnieją pewne scenariusze, w których może nie być skuteczne. Na przykład dziennik wolnych zapytań:

  • Negatywnie wpływa na wydajność, jeśli liczba zapytań jest bardzo wysoka lub jeśli instrukcja zapytania jest bardzo duża. Odpowiednio dostosuj wartość parametru long_query_time .
  • Może nie być pomocne, jeśli włączono log_queries_not_using_index również parametr , który określa, że zapytania dziennika mają pobierać wszystkie wiersze. Zapytania wykonujące pełne skanowanie indeksu korzystają z indeksu, ale zostałyby zarejestrowane, ponieważ indeks nie ogranicza liczby zwracanych wierszy.

Pobieranie informacji z dzienników

Dzienniki są dostępne przez maksymalnie siedem dni od ich utworzenia. Dzienniki wolnych zapytań można wyświetlić i pobrać za pośrednictwem witryny Azure Portal lub interfejsu wiersza polecenia platformy Azure. W witrynie Azure Portal przejdź do serwera, w obszarze Monitorowanie wybierz pozycję Dzienniki serwera, a następnie wybierz strzałkę w dół obok wpisu, aby pobrać dzienniki skojarzone z datą i godziną badania.

Zrzut ekranu przedstawiający usługę Azure Database for MySQL — elastyczny serwer pobierania danych z dzienników.

Ponadto, jeśli dzienniki wolnych zapytań są zintegrowane z dziennikami usługi Azure Monitor za pośrednictwem dzienników diagnostycznych, możesz uruchamiać zapytania w edytorze, aby je dokładniej analizować:

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

Uwaga

Aby uzyskać więcej przykładów, aby rozpocząć diagnozowanie dzienników wolnych zapytań za pomocą dzienników diagnostycznych, zobacz Analizowanie dzienników w dziennikach usługi Azure Monitor.

Poniższa migawka przedstawia przykładowe powolne zapytanie.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

Zapytanie uruchomiono w ciągu 26 sekund, przeanalizowano ponad 443 tys. wierszy i zwróciło 126 wierszy wyników.

Zazwyczaj należy skoncentrować się na zapytaniach o wysokich wartościach dla Query_time i Rows_examined. Jeśli jednak zauważysz zapytania o wysokim Query_time, ale tylko kilka Rows_examined, często wskazuje to na obecność wąskiego gardła zasobów. W takich przypadkach należy sprawdzić, czy występuje ograniczenie operacji we/wy lub użycie procesora CPU.

Profilowanie zapytania

Po zidentyfikowaniu określonego wolno działającego zapytania możesz użyć polecenia EXPLAIN i profilowania, aby zebrać więcej szczegółów.

Aby sprawdzić plan zapytania, uruchom następujące polecenie:

EXPLAIN <QUERY>

Uwaga

Aby uzyskać więcej informacji na temat używania instrukcji EXPLAIN, zobacz Wydajność zapytań profilu w usłudze Azure Database for MySQL — serwer elastyczny przy użyciu funkcji WYJAŚNIJ.

Oprócz utworzenia planu EXPLAIN dla zapytania można użyć polecenia SHOW PROFILE, które umożliwia diagnozowanie wykonywania instrukcji, które zostały uruchomione w bieżącej sesji.

Aby włączyć profilowanie i profilowanie określonego zapytania w sesji, uruchom następujący zestaw poleceń:

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

Uwaga

Profilowanie poszczególnych zapytań jest dostępne tylko w sesji, a instrukcje historyczne nie mogą być profilowane.

Przyjrzyjmy się bliżej użyciu tych poleceń do profilowania zapytania. Najpierw włącz profilowanie dla bieżącej sesji, uruchom SET PROFILING = 1 polecenie:

SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Następnie wykonaj nieoptymalne zapytanie, które wykonuje pełne skanowanie tabeli:

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 1 row in set (27.60 sec) |

Następnie wyświetl listę wszystkich dostępnych profilów zapytań, uruchamiając SHOW PROFILES polecenie :

SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
| +----------+-------------+----------------------------------------------------+ |
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
| +----------+-------------+----------------------------------------------------+ |
| 1 row in set, 1 warning (0.00 sec) |

Na koniec, aby wyświetlić profil zapytania 1, uruchom SHOW PROFILE FOR QUERY 1 polecenie .

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
| +----------------------+-----------+ |
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
| +----------------------+-----------+ |
| 15 rows in set, 1 warning (0.00 sec) |

Wyświetlanie listy najczęściej używanych zapytań na serwerze bazy danych

Za każdym razem, gdy rozwiązujesz problemy z wydajnością zapytań, warto zrozumieć, które zapytania są najczęściej uruchamiane w wystąpieniu serwera elastycznego usługi Azure Database for MySQL. Możesz użyć tych informacji, aby ocenić, czy którekolwiek z najważniejszych zapytań trwa dłużej niż zwykle. Ponadto deweloper lub administrator baz danych może użyć tych informacji, aby określić, czy jakiekolwiek zapytanie ma nagły wzrost liczby i czasu trwania wykonywania zapytań.

Aby wyświetlić listę 10 najczęściej wykonywanych zapytań względem wystąpienia serwera elastycznego usługi Azure Database for MySQL, uruchom następujące zapytanie:

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Uwaga

Użyj tego zapytania, aby przeprowadzić test porównawczy zapytań wykonywanych na serwerze bazy danych i określić, czy w pierwszych zapytaniach nastąpiła zmiana lub czy w początkowym tezie zapytania wzrosły czas trwania uruchamiania.

Wyświetlanie listy 10 najdroższych zapytań według łącznego czasu wykonywania

Dane wyjściowe z poniższego zapytania zawierają informacje o 10 pierwszych zapytaniach uruchomionych na serwerze bazy danych i ich liczbie wykonań na serwerze bazy danych. Udostępnia również inne przydatne informacje, takie jak opóźnienia zapytań, czasy ich blokady, liczba tabel tymczasowych utworzonych w ramach środowiska uruchomieniowego zapytań itp. Użyj tych danych wyjściowych zapytania, aby śledzić najważniejsze zapytania w bazie danych i zmiany czynników, takich jak opóźnienia, co może wskazywać na szansę dalszego dostosowania zapytania, aby uniknąć wszelkich przyszłych zagrożeń.

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
 count_star AS all_occurrences ,
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  ,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS  sum_rows_selected,
 sum_rows_examined AS  sum_rows_scanned,
 sum_created_tmp_tables,  sum_created_tmp_disk_tables,
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
 sum_created_tmp_tables * 100, 0))) AS
 tmp_disk_tables_percent,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
 AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;

Monitorowanie odzyskiwania pamięci w usłudze InnoDB

Gdy odzyskiwanie pamięci w usłudze InnoDB jest zablokowane lub opóźnione, baza danych może opracować znaczne opóźnienie czyszczenia, które może negatywnie wpłynąć na wykorzystanie magazynu i wydajność zapytań.

Długość listy historii wycofywania w usłudze InnoDB (HLL) mierzy liczbę rekordów zmian przechowywanych w dzienniku cofania. Rosnąca wartość HLL wskazuje, że wątki odzyskiwania pamięci w usłudze InnoDB (wątki przeczyszczania) nie nadążają za obciążeniem zapisu lub że czyszczenie jest blokowane przez długotrwałe zapytanie lub transakcję.

Nadmierne opóźnienia w wyrzucaniu pamięci mogą mieć poważne, negatywne konsekwencje:

  • Przestrzeń tabel systemu InnoDB zostanie rozwinięta, co przyspiesza wzrost bazowego woluminu magazynu. Czasami przestrzeń tabel systemowych może pęcznieć o kilka terabajtów w wyniku zablokowanego przeczyszczenia.
  • Rekordy oznaczone znacznikami usuwania nie zostaną usunięte w odpowiednim czasie. Może to spowodować, że przestrzenie tabel InnoDB rosną i uniemożliwiają ponowne wykorzystanie magazynu zajmowanego przez te rekordy przez aparat.
  • Wydajność wszystkich zapytań może ulec pogorszeniu, a wykorzystanie procesora CPU może wzrosnąć z powodu wzrostu struktur magazynu InnoDB.

W rezultacie ważne jest monitorowanie wartości, wzorców i trendów HLL.

Znajdowanie wartości HLL

Wartość HLL można znaleźć, uruchamiając polecenie stanu innodb aparatu show. Wartość zostanie wyświetlona w danych wyjściowych w obszarze nagłówka TRANSAKCJE:

show engine innodb status\G

****************** 1. row ******************

(...)

------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300

(...)

Możesz również określić wartość HLL, wykonując zapytanie względem tabeli information_schema.innodb_metrics:

mysql> select count from information_schema.innodb_metrics
    -> where name = 'trx_rseg_history_len';

+---------+
| count |
| +---------+ |
| 2964300 |
| +---------+ |
| 1 row in set (0.00 sec) |

Interpretowanie wartości HLL

Podczas interpretowania wartości HLL należy wziąć pod uwagę wytyczne wymienione w poniższej tabeli:

Wartość Uwagi
Mniej niż ~10 000 Normalne wartości wskazujące, że odzyskiwanie pamięci nie spada z tyłu.
Od ~10 000 do ~1 000 000 Te wartości wskazują niewielkie opóźnienie w wyrzucaniu pamięci. Takie wartości mogą być dopuszczalne, jeśli pozostają stałe i nie zwiększają się.
Większe niż ~1000 000 Te wartości należy zbadać i mogą wymagać akcji naprawczych

Rozwiązywanie problemów z nadmierną wartością HLL

Jeśli usługa HLL pokazuje duże skoki lub wykazuje wzorzec okresowego wzrostu, natychmiast zbadaj zapytania i transakcje uruchomione w wystąpieniu serwera elastycznego usługi Azure Database for MySQL. Następnie możesz rozwiązać wszelkie problemy z obciążeniem, które mogą uniemożliwiać postęp procesu odzyskiwania pamięci. Chociaż nie oczekuje się, że baza danych będzie wolna od opóźnienia przeczyszczania, nie można pozwolić, aby opóźnienie rosło niekontrolowanie.

Aby uzyskać informacje o transakcji z information_schema.innodb_trx tabeli, na przykład uruchom następujące polecenia:

select * from information_schema.innodb_trx
order by trx_started asc\G

Szczegóły w kolumnie trx_started ułatwią obliczenie wieku transakcji.

mysql> select * from information_schema.innodb_trx
    -> order by trx_started asc\G

****************** 1. row ******************
                    trx_id: 8150550
                 trx_state: RUNNING
               trx_started: 2021-11-13 20:50:11
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 19
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)

Aby uzyskać informacje na temat bieżących sesji bazy danych, w tym czasu spędzonego w bieżącym stanie sesji, sprawdź tabelę information_schema.processlist . Na przykład następujące dane wyjściowe pokazują sesję, która aktywnie wykonuje zapytanie w ciągu ostatnich 1462 sekund:

mysql> select user, host, db, command, time, info
    -> from information_schema.processlist
    -> order by time desc\G

****************** 1. row ******************
   user: test
   host: 172.31.19.159:38004
     db: employees
command: Query
   time: 1462
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';

(...)

Zalecenia

  • Upewnij się, że baza danych ma wystarczającą ilość zasobów przydzielonych do uruchamiania zapytań. Czasami może być konieczne skalowanie w górę rozmiaru wystąpienia, aby uzyskać więcej rdzeni procesora CPU i dodatkowej pamięci, aby obsłużyć obciążenie.

  • Unikaj dużych lub długotrwałych transakcji, dzieląc je na mniejsze transakcje.

  • Skonfiguruj innodb_purge_threads zgodnie z obciążeniem, aby zwiększyć wydajność operacji przeczyszczania w tle.

    Uwaga

    Przetestuj wszelkie zmiany w tej zmiennej serwera dla każdego środowiska, aby ocenić zmianę zachowania aparatu.

  • Użyj alertów dotyczących "Procent procesora CPU hosta", "Procent pamięci hosta" i "Łączne połączenia", aby otrzymywać powiadomienia, jeśli system przekroczy którykolwiek z określonych progów.

  • Użyj szczegółowych informacji o wydajności zapytań lub skoroszytów platformy Azure, aby zidentyfikować wszelkie problematyczne lub powoli uruchomione zapytania, a następnie je zoptymalizować.

  • W przypadku serwerów produkcyjnych baz danych zbierz diagnostykę w regularnych odstępach czasu, aby upewnić się, że wszystko działa bezproblemowo. Jeśli nie, rozwiąż wszelkie zidentyfikowane problemy i rozwiąż je.

Następny krok

Stack Overflow