Řešení potíží s vysokým využitím procesoru v Azure Database for MySQL – flexibilní server

PLATÍ PRO: Flexibilní server Azure Database for MySQL – Jednoúčelový server Azure Database for MySQL

Flexibilní server Azure Database for MySQL poskytuje řadu metrik, které můžete použít k identifikaci kritických bodů prostředků a problémů s výkonem na serveru. Pokud chcete zjistit, jestli u vašeho serveru dochází k vysokému využití procesoru, monitorujte metriky, jako je Například Procento procesoru hostitele, Celková připojení, Procento paměti hostitele a Vstupně-výstupní operace. Zobrazení kombinace těchto metrik někdy poskytne přehled o tom, co může způsobit zvýšené využití procesoru ve vaší instanci flexibilního serveru Azure Database for MySQL.

Představte si například náhlé zvýšení počtu připojení, která iniciují nárůst databázových dotazů, které způsobují, že využití procesoru se zastřelí.

Kromě zachytávání metrik je důležité také sledovat zatížení, abyste pochopili, jestli jeden nebo více dotazů způsobuje špičku využití procesoru.

Vysoké příčiny procesoru

Špičky procesoru můžou nastat z různých důvodů, především kvůli špičkám připojení a špatně napsaným dotazům SQL nebo kombinaci obou:

Špička v připojeních

Zvýšení počtu připojení může vést ke zvýšení počtu vláken, což zase může způsobit nárůst využití procesoru, protože musí spravovat tato připojení spolu s jejich dotazy a prostředky. Pokud chcete řešit potíže se špičkou připojení, měli byste zkontrolovat metriku Total Connections a další podrobnosti o těchto připojeních najdete v další části. Pomocí tabulky performance_schema můžete identifikovat hostitele a uživatele aktuálně připojené k serveru pomocí následujících příkazů:

Aktuálně připojení hostitelé

   select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
   where CURRENT_CONNECTIONS > 0
   and host not in ('NULL','localhost');

Aktuálně připojení uživatelé

   select USER,CURRENT_CONNECTIONS from performance_schema.users
   where CURRENT_CONNECTIONS >0
   and USER not in ('NULL','azure_superuser');

Špatně napsané dotazy SQL

Dotazy, které jsou náročné na provedení a prohledávání velkého počtu řádků bez indexu, nebo ty, které provádějí dočasné řazení spolu s jinými neefektivními plány, mohou vést ke špičkám ve využití procesoru. Zatímco některé dotazy mohou být provedeny rychle v jedné relaci, při spuštění ve více relacích mohou způsobit špičky ve využití procesoru. Proto je důležité vždy vysvětlit dotazy, které zaznamenáte ze seznamu procesů show, a zajistit, aby jejich plány provádění byly efektivní. Toho lze dosáhnout zajištěním, že prohledávají minimální počet řádků pomocí klauzule filters/where, využívají indexy a vyhněte se použití velkých dočasných řazení spolu s dalšími plány chybného spuštění. Další informace o plánech provádění naleznete v tématu EXPLAIN Output Format.

Zachycení podrobností o aktuální úloze

Příkaz SHOW (FULL) PROCESSLIST zobrazí seznam všech uživatelských relací aktuálně připojených k instanci flexibilního serveru Azure Database for MySQL. Poskytuje také podrobnosti o aktuálním stavu a aktivitě každé relace.

Tento příkaz vytvoří jenom snímek aktuálního stavu relace a neposkytuje informace o historické aktivitě relace.

Pojďme se podívat na ukázkový výstup spuštění tohoto příkazu.

mysql> 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)

Všimněte si, že existují dvě relace vlastněné uživatelem "adminuser" vlastněným zákazníkem, a to jak ze stejné IP adresy, tak i ze stejné IP adresy:

• Relace 24835 spouští příkaz SELECT za posledních 7 sekund.
• Relace 24837 spouští příkaz zobrazit úplný seznam procesů.

V případě potřeby může být potřeba ukončit dotaz, například dotaz na generování sestav nebo dotaz HTAP, který způsobil špičku využití procesoru produkčního zatížení. Před provedením akce při pokusu o snížení využití procesoru ale vždy zvažte potenciální důsledky ukončení dotazu. Jindy, pokud se zjistily nějaké dlouhotrvající dotazy, které vedou ke špičkám procesoru, vylaďte tyto dotazy, aby se prostředky optimálně využívaly.

Podrobná aktuální analýza úloh

Abyste získali přesné informace o stavu relace, transakce a dotazu, musíte použít alespoň dva zdroje informací:

• Seznam procesů serveru ze INFORMATION_SCHEMA. Tabulka PROCESSLIST, ke které můžete přistupovat také spuštěním příkazu SHOW [FULL] PROCESSLIST.
• Metadata transakcí InnoDB z INFORMATION_SCHEMA. INNODB_TRX tabulku.

S informacemi z jednoho z těchto zdrojů není možné popsat stav připojení a transakce. Například seznam procesů vás neinformuje, jestli je otevřená transakce přidružená k některé z relací. Na druhou stranu metadata transakcí nezobrazují stav relace a čas strávený v daném stavu.

Následující příklad dotazu, který kombinuje informace o seznamu procesů s některými důležitými částmi metadat transakcí 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

Následující příklad ukazuje výstup z tohoto dotazu:

*************************** 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

Analýza těchto informací podle relace je uvedena v následující tabulce.

Upozorňujeme, že pokud je relace hlášena jako nečinná, už nespouštějí žádné příkazy. V tomto okamžiku relace dokončila veškerou předchozí práci a čeká na nové příkazy z klienta. Nečinné relace jsou ale stále zodpovědné za určité využití procesoru a využití paměti.

Výpis otevřených transakcí

Výstup z následujícího dotazu poskytuje seznam všech transakcí aktuálně spuštěných na databázovém serveru v pořadí od spuštění transakce, abyste mohli snadno zjistit, jestli existují nějaké dlouhotrvající a blokující transakce překračující jejich očekávaný běh.

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;

Principy stavů vláken

Transakce, které při provádění přispívají k vyššímu využití procesoru, můžou mít vlákna v různých stavech, jak je popsáno v následujících částech. Pomocí těchto informací lépe porozumíte životnímu cyklu dotazu a různým stavům vláken.

Kontrola oprávnění /Otevírání tabulek

Tento stav obvykle znamená, že operace otevřené tabulky trvá dlouhou dobu. Obvykle můžete velikost mezipaměti tabulky zvětšit, aby se problém zlepšil. Pomalé otevírání tabulek ale může indidikovat i jiné problémy, jako je příliš mnoho tabulek ve stejné databázi.

Odesílání dat

I když tento stav může znamenat, že vlákno odesílá data přes síť, může také znamenat, že dotaz čte data z disku nebo paměti. Tento stav může být způsoben kontrolou sekvenční tabulky. Měli byste zkontrolovat hodnoty innodb_buffer_pool_reads a innodb_buffer_pool_read_requests a zjistit, jestli se z disku do paměti obsluhuje velký počet stránek. Další informace najdete v tématu Řešení potíží s nedostatkem paměti na flexibilním serveru Azure Database for MySQL.

Aktualizace

Tento stav obvykle znamená, že vlákno provádí operaci zápisu. Projděte si metriku související se vstupně-výstupními operacemi v Sledování výkonu, abyste lépe pochopili, co aktuální relace dělají.

Čekání na <zámek lock_type>

Tento stav označuje, že vlákno čeká na druhý zámek. Ve většině případů se může jednat o zámek metadat. Měli byste zkontrolovat všechna ostatní vlákna a zjistit, kdo zámek přebírá.

Principy a analýza událostí čekání

Je důležité pochopit základní události čekání v modulu MySQL, protože dlouhé čekání nebo velký počet čekání v databázi můžou vést ke zvýšení využití procesoru. Následující příklad ukazuje příslušný příkaz a ukázkový výstup.

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)

Omezení doby provádění příkazů SELECT

Pokud nevíte o nákladech na spuštění a době provádění databázových operací zahrnujících dotazy SELECT, můžou jakékoli dlouhotrvající operace SELECT vést k nepredikovatelnosti nebo nestálosti databázového serveru. Velikost příkazů a transakcí a přidruženého využití prostředků stále roste v závislosti na růstu podkladové sady dat. Vzhledem k tomuto nevázaného růstu trvá příkazy koncových uživatelů a transakce delší a delší dobu a zabírají stále více prostředků, dokud nezahltí databázový server. Při použití nevázaných dotazů SELECT se doporučuje nakonfigurovat parametr max_execution_time tak, aby se všechny dotazy překračující tuto dobu přerušily.

Doporučení

• Ujistěte se, že má vaše databáze přidělených dostatek prostředků ke spouštění dotazů. V některých případech možná budete muset vertikálně navýšit kapacitu instance, abyste získali více jader procesoru, aby vyhovovala vašim úlohám.
• Vyhněte se velkým nebo dlouhotrvajícím transakcím tím, že je rozdělíte na menší transakce.
• Pokud je to možné, spusťte příkazy SELECT na serverech replik pro čtení.
• Používejte upozornění na "Host CPU Percent" (Procento procesoru hostitele), abyste dostávali oznámení, pokud systém překročí některou ze zadaných prahových hodnot.
• Pomocí Nástroje Query Performance Insights nebo Azure Workbooks identifikujte problematické nebo pomalé dotazy a pak je optimalizujte.
• V případě produkčních databázových serverů shromážděte diagnostiku v pravidelných intervalech, abyste měli jistotu, že vše funguje hladce. Pokud ne, vyřešte případné problémy, které identifikujete, a vyřešte je.

Další kroky

Pokud chcete najít partnerské odpovědi na nejdůležitější otázky nebo publikovat nebo odpovědět na otázku, navštivte web Stack Overflow.