Rozwiązywanie problemów z wysokim użyciem procesora w usłudze Azure Database for MySQL — serwer elastyczny
Usługa Azure Database for MySQL — elastyczny serwer udostępnia szereg metryk, których można użyć do identyfikowania wąskich gardeł zasobów i problemów z wydajnością na serwerze. Aby ustalić, czy na serwerze występuje wysokie wykorzystanie procesora CPU, monitoruj metryki, takie jak "Procent procesora CPU hosta", "Łączne połączenia", "Procent pamięci hosta" i "Procent operacji we/wy". Czasami wyświetlenie kombinacji tych metryk zapewni wgląd w to, co może spowodować zwiększone wykorzystanie procesora CPU w wystąpieniu serwera elastycznego usługi Azure Database for MySQL.
Rozważmy na przykład nagły wzrost liczby połączeń, które inicjują wzrost liczby zapytań bazy danych, które powodują wzrost wykorzystania procesora CPU.
Oprócz przechwytywania metryk ważne jest również śledzenie obciążenia w celu zrozumienia, czy co najmniej jedno zapytanie powoduje wzrost wykorzystania procesora CPU.
Wysokie przyczyny procesora CPU
Skoki procesora CPU mogą wystąpić z różnych powodów, głównie ze względu na wzrost liczby połączeń i słabo napisanych zapytań SQL lub kombinację obu tych typów:
Skok liczby połączeń
Wzrost liczby połączeń może prowadzić do zwiększenia liczby wątków, co z kolei może spowodować wzrost użycia procesora CPU, który musi zarządzać tymi połączeniami wraz z zapytaniami i zasobami. Aby rozwiązać problemy ze wzrostem liczby połączeń, sprawdź metrykę Łączna liczba połączeń i zapoznaj się z następną sekcją, aby uzyskać więcej informacji na temat tych połączeń. Można użyć performance_schema, by zidentyfikować hosty i użytkowników aktualnie połączonych z serwerem za pomocą następujących poleceń:
Aktualnie połączone hosty
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Aktualnie połączeni użytkownicy
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Źle napisane zapytania SQL
Zapytania, które są trudne do wykonania i przeszukują dużą liczbę wierszy bez indeksu lub te, które wykonują tymczasowe sortowanie wraz z innymi nieefektywnymi planami, mogą prowadzić do skoków użycia procesora. Podczas gdy niektóre zapytania mogą być wykonywane szybko w jednej sesji, mogą powodować skoki użycia procesora CPU podczas uruchamiania w wielu sesjach. Dlatego ważne jest, aby zawsze wyjaśniać zapytania przechwycone z listy procesów pokazu i zapewnić, że ich plany wykonania są wydajne. Można to osiągnąć, zapewniając skanowanie minimalnej liczby wierszy przy użyciu filtrów/klauzuli where, korzystać z indeksów i unikać używania dużego tymczasowego sortowania wraz z innymi złymi planami wykonywania. Aby uzyskać więcej informacji na temat planów wykonywania, zobacz WYJAŚNIJ format danych wyjściowych.
Przechwytywanie szczegółów bieżącego obciążenia
Polecenie SHOW (FULL) PROCESSLIST wyświetla listę wszystkich sesji użytkownika aktualnie połączonych z wystąpieniem serwera elastycznego usługi Azure Database for MySQL. Zawiera również szczegółowe informacje o bieżącym stanie i aktywności każdej sesji.
To polecenie tworzy tylko migawkę bieżącego stanu sesji i nie dostarcza informacji o aktywności sesji historycznej.
Przyjrzyjmy się przykładowym danym wyjściowym z uruchomienia tego polecenia.
SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL |
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails; |
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
| +-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+ |
| 5 rows in set (0.00 sec) |
Istnieją dwie sesje należące do użytkownika należącego do klienta "adminuser", oba z tego samego adresu IP:
- Sesja 24835 wykonuje instrukcję SELECT w ciągu ostatnich siedmiu sekund.
- Sesja 24837 wykonuje instrukcję "show full processlist".
W razie potrzeby może być konieczne zakończenie zapytania, takiego jak zapytanie raportowania lub HTAP, które spowodowało wzrost użycia procesora CPU obciążenia produkcyjnego. Jednak zawsze należy wziąć pod uwagę potencjalne konsekwencje zakończenia zapytania przed podjęciem akcji w celu zmniejszenia wykorzystania procesora CPU. Innym razem, gdy istnieją jakiekolwiek długotrwałe zapytania, które prowadzą do skoków użycia procesora CPU, dostosuj te zapytania, aby zasoby były optymalnie wykorzystywane.
Szczegółowa analiza bieżącego obciążenia
Aby uzyskać dokładne informacje o stanie sesji, transakcji i zapytaniu, należy użyć co najmniej dwóch źródeł informacji:
- Lista procesów serwera z INFORMATION_SCHEMA. Tabela PROCESSLIST, do której można również uzyskać dostęp, uruchamiając polecenie SHOW [FULL] PROCESSLIST.
- Metadane transakcji innoDB z INFORMATION_SCHEMA. INNODB_TRX tabeli.
Przy użyciu informacji tylko z jednego z tych źródeł nie można opisać stanu połączenia i transakcji. Na przykład lista procesów nie informuje o tym, czy istnieje otwarta transakcja skojarzona z każdą z sesji. Z drugiej strony metadane transakcji nie wyświetlają stanu sesji i czasu spędzonego w tym stanie.
Poniższe przykładowe zapytanie, które łączy informacje o liście procesów z niektórymi ważnymi elementami metadanych transakcji InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
W poniższym przykładzie przedstawiono dane wyjściowe z tego zapytania:
****************** 1. row ******************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
****************** 2. row ******************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Analiza tych informacji według sesji znajduje się w poniższej tabeli.
| Obszar | Analiza |
|---|---|
| Sesja 11 | Ta sesja jest obecnie w stanie bezczynności (uśpienia) bez uruchomionych zapytań i została uruchomiona przez 636 sekund. W ramach sesji transakcja, która została otwarta przez 2908 sekund, zmodyfikowała 17 825 792 wierszy i używa izolacji powtarzalnego odczytu. |
| Sesja 12 | Sesja wykonuje obecnie instrukcję SELECT, która została uruchomiona przez 15 sekund. W sesji nie jest uruchomione zapytanie, zgodnie z wartościami NULL dla trx_started i trx_age_seconds. Sesja będzie nadal przechowywać granicę odzyskiwania pamięci tak długo, jak to działa, chyba że używa bardziej zrelaksowanej izolacji READ COMMITTED. |
Jeśli sesja jest zgłaszana jako bezczynna, nie wykonuje już żadnych instrukcji. W tym momencie sesja zakończyła poprzednią pracę i oczekuje na nowe instrukcje od klienta. Jednak sesje bezczynności są nadal odpowiedzialne za użycie procesora CPU i użycie pamięci.
Wyświetlanie otwartych transakcji
Dane wyjściowe z poniższego zapytania zawierają listę wszystkich transakcji aktualnie uruchomionych na serwerze bazy danych w kolejności od czasu rozpoczęcia transakcji, dzięki czemu można łatwo określić, czy istnieją długotrwałe i blokujące transakcje przekraczające oczekiwane środowisko uruchomieniowe.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Opis stanów wątków
Transakcje, które przyczyniają się do wyższego wykorzystania procesora CPU podczas wykonywania, mogą mieć wątki w różnych stanach, zgodnie z opisem w poniższych sekcjach. Te informacje umożliwiają lepsze zrozumienie cyklu życia zapytania i różnych stanów wątków.
Sprawdzanie uprawnień/Otwieranie tabel
Ten stan zwykle oznacza, że operacja otwierania tabeli trwa długo. Zwykle można zwiększyć rozmiar pamięci podręcznej tabeli, aby poprawić problem. Jednak otwieranie tabel powoli może wskazywać na inne problemy, takie jak zbyt wiele tabel w tej samej bazie danych.
Wysyłanie danych
Chociaż ten stan może oznaczać, że wątek wysyła dane za pośrednictwem sieci, może również wskazywać, że zapytanie odczytuje dane z dysku lub pamięci. Ten stan może być spowodowany sekwencyjnym skanowaniem tabeli. Należy sprawdzić wartości innodb_buffer_pool_reads i innodb_buffer_pool_read_requests, aby określić, czy duża liczba stron jest obsługiwana z dysku do pamięci. Aby uzyskać więcej informacji, zobacz Rozwiązywanie problemów z małą ilością pamięci w usłudze Azure Database for MySQL — serwer elastyczny.
Aktualizowanie
Ten stan zwykle oznacza, że wątek wykonuje operację zapisu. Sprawdź metrykę związaną z we/wy w monitor wydajności, aby lepiej zrozumieć, co robią bieżące sesje.
Oczekiwanie na <blokadę lock_type>
Ten stan wskazuje, że wątek czeka na drugą blokadę. W większości przypadków może to być blokada metadanych. Należy przejrzeć wszystkie inne wątki i zobaczyć, kto bierze blokadę.
Opis i analizowanie zdarzeń oczekiwania
Ważne jest, aby zrozumieć podstawowe zdarzenia oczekiwania w a aparatu MySQL, ponieważ długie oczekiwania lub duża liczba oczekiwań w bazie danych może prowadzić do zwiększenia wykorzystania procesora CPU. Poniższy przykład przedstawia odpowiednie polecenie i przykładowe dane wyjściowe.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
| +--------------------------------------+-----------------+-----------------+---------------+ |
| 10 rows in set (0.00 sec) |
Ogranicz czas wykonywania instrukcji SELECT
Jeśli nie wiesz o kosztach wykonywania i czasie wykonywania operacji bazy danych obejmujących zapytania SELECT, wszystkie długotrwałe SELEKty mogą prowadzić do nieprzewidywalności lub zmienności na serwerze bazy danych. Rozmiar instrukcji i transakcji, a także powiązane wykorzystanie zasobów, nadal rośnie w zależności od bazowego wzrostu zestawu danych. Ze względu na ten niezwiązany wzrost instrukcje użytkownika końcowego i transakcje zużywają coraz dłużej, zużywając coraz więcej zasobów, dopóki nie przeciążą serwera bazy danych. W przypadku korzystania z niezwiązanych zapytań SELECT zaleca się skonfigurowanie parametru max_execution_time tak, aby wszystkie zapytania przekraczające ten czas trwania zostały przerwane.
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, aby obsłużyć obciążenie.
- Unikaj dużych lub długotrwałych transakcji, dzieląc je na mniejsze transakcje.
- Uruchom instrukcje SELECT na serwerach repliki do odczytu, jeśli to możliwe.
- Użyj alertów w obszarze "Procent procesora CPU hosta", 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.