Řešení potíží s výkonem dotazů na flexibilním serveru Azure Database for MySQL
PLATÍ PRO:
Flexibilní server Azure Database for MySQL – Jednoúčelový server Azure Database for MySQL
Důležité
Jednoúčelový server Azure Database for MySQL je na cestě vyřazení. Důrazně doporučujeme upgradovat na flexibilní server Azure Database for MySQL. Další informace o migraci na flexibilní server Azure Database for MySQL najdete v tématu Co se děje s jednoúčelovým serverem Azure Database for MySQL?
Výkon dotazů může mít vliv na několik faktorů, takže je nejprve důležité se podívat na rozsah příznaků, které máte v instanci flexibilního serveru Azure Database for MySQL. Například je nízký výkon dotazů pro:
- Všechny dotazy spuštěné na instanci flexibilního serveru Azure Database for MySQL?
- Konkrétní sada dotazů?
- Konkrétní dotaz?
Mějte také na paměti, že všechny nedávné změny struktury nebo podkladových dat tabulek, které dotazujete, můžou ovlivnit výkon.
Povolení funkcí protokolování
Před analýzou jednotlivých dotazů je potřeba definovat srovnávací testy dotazů. Pomocí těchto informací můžete implementovat funkce protokolování na databázovém serveru pro trasování dotazů, které překračují zadaná prahová hodnota na základě potřeb aplikace.
Flexibilní server Azure Database for MySQL se doporučuje použít funkci protokolu pomalých dotazů k identifikaci dotazů, které poběží déle než N sekund. Po identifikaci dotazů z protokolu pomalých dotazů můžete k řešení těchto dotazů použít diagnostiku MySQL.
Než začnete trasovat dlouhotrvající dotazy, musíte parametr povolit slow_query_log pomocí webu Azure Portal nebo Azure CLI. Pokud je tento parametr povolený, měli byste také nakonfigurovat hodnotu parametru long_query_time tak, aby určila počet sekund, po které se dotazy můžou spustit, než se označí jako pomalé dotazy. Výchozí hodnota parametru je 10 sekund, ale můžete ji upravit tak, aby vyhovovala potřebám smlouvy SLA vaší aplikace.
I když je protokol pomalých dotazů skvělým nástrojem pro trasování dlouhotrvajících dotazů, existují určité scénáře, ve kterých nemusí být efektivní. Například protokol pomalých dotazů:
- Negativní vliv na výkon, pokud je počet dotazů velmi vysoký nebo pokud je příkaz dotazu velmi velký. Odpovídajícím způsobem upravte hodnotu parametru
long_query_time. - Nemusí být užitečné, pokud jste parametr povolili
log_queries_not_using_index, což určuje, že se mají protokolovat dotazy, u kterých se očekává načtení všech řádků. Dotazy provádějící úplnou kontrolu indexu využívají index, ale zaprotokolují se, protože index neomezuje počet vrácených řádků.
Načítání informací z protokolů
Protokoly jsou k dispozici až sedm dnů od jejich vytvoření. Protokoly pomalých dotazů můžete vypsat a stáhnout prostřednictvím webu Azure Portal nebo Azure CLI. Na webu Azure Portal přejděte na svůj server v části Monitorování, vyberte Protokoly serveru a pak vedle položky vyberte šipku dolů a stáhněte protokoly přidružené k datu a času, který prošetřujete.
Kromě toho, pokud jsou protokoly pomalých dotazů integrované s protokoly služby Azure Monitor prostřednictvím diagnostických protokolů, můžete spouštět dotazy v editoru a analyzovat je dále:
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
Poznámka:
Další příklady, které vám pomůžou začít s diagnostikou protokolů pomalých dotazů prostřednictvím diagnostických protokolů, najdete v tématu Analýza protokolů v protokolech služby Azure Monitor.
Následující snímek znázorňuje ukázkový pomalý dotaz.
# 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%';;
Všimněte si, že dotaz běžel za 26 sekund, prozkoumal více než 443 tisíc řádků a vrátil 126 řádků výsledků.
Obvykle byste se měli zaměřit na dotazy s vysokými hodnotami pro Query_time a Rows_examined. Pokud si ale všimnete dotazů s vysokou Query_time, ale jenom několik Rows_examined, často to značí přítomnost kritických bodů prostředku. V těchto případech byste měli zkontrolovat, jestli nedošlo k omezování vstupně-výstupních operací nebo využití procesoru.
Profilace dotazu
Jakmile identifikujete konkrétní pomalý spuštěný dotaz, můžete k získání dalších podrobností použít příkaz EXPLAIN a profilaci.
Pokud chcete zkontrolovat plán dotazu, spusťte následující příkaz:
EXPLAIN <QUERY>
Poznámka:
Další informace o použití příkazů EXPLAIN najdete v tématu Použití funkce EXPLAIN k profilování výkonu dotazů na flexibilním serveru Azure Database for MySQL.
Kromě vytvoření plánu EXPLAIN pro dotaz můžete použít příkaz SHOW PROFILE, který umožňuje diagnostikovat provádění příkazů, které byly spuštěny v aktuální relaci.
Pokud chcete povolit profilaci a profilaci konkrétního dotazu v relaci, spusťte následující sadu příkazů:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Poznámka:
Profilace jednotlivých dotazů je k dispozici pouze v relaci a historické příkazy nelze profilovat.
Pojďme se podrobněji podívat na použití těchto příkazů k profilování dotazu. Nejprve povolte profilaci pro aktuální relaci, spusťte SET PROFILING = 1 příkaz:
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Pak spusťte neoptimální dotaz, který provede úplnou kontrolu tabulky:
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)
Potom spuštěním SHOW PROFILES příkazu zobrazte seznam všech dostupných profilů dotazů:
mysql> 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)
Nakonec spuštěním příkazu zobrazte SHOW PROFILE FOR QUERY 1 profil pro dotaz 1.
mysql> 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)
Výpis nejčastěji používaných dotazů na databázovém serveru
Pokaždé, když řešíte potíže s výkonem dotazů, je užitečné pochopit, které dotazy se nejčastěji spouští na instanci flexibilního serveru Azure Database for MySQL. Tyto informace můžete použít k měření, jestli spuštění některého z nejčastějších dotazů trvá déle než obvykle. Vývojář nebo DBA navíc můžou tyto informace použít k identifikaci, jestli některý dotaz náhle zvýší počet provádění dotazů a dobu trvání.
Pokud chcete zobrazit seznam 10 nejčastějších dotazů na instanci flexibilního serveru Azure Database for MySQL, spusťte následující dotaz:
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;
Poznámka:
Pomocí tohoto dotazu proveďte srovnávací testy nejčastějších spuštěných dotazů na databázovém serveru a zjistěte, jestli došlo ke změně v hlavních dotazech nebo jestli se během běhu zvýšily nějaké existující dotazy v počátečním srovnávacím testu.
Výpis 10 nejnákladnějších dotazů podle celkové doby provádění
Výstup z následujícího dotazu poskytuje informace o 10 nejčastějších dotazech spuštěných na databázovém serveru a jejich počtu spuštění na databázovém serveru. Poskytuje také další užitečné informace, jako jsou latence dotazů, doby jejich uzamčení, počet dočasných tabulek vytvořených v rámci modulu runtime dotazů atd. Pomocí tohoto výstupu dotazu můžete sledovat hlavní dotazy v databázi a měnit faktory, jako jsou latence, což může znamenat možnost dotaz dále vyladit, aby se zabránilo budoucím rizikům.
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;
Monitorování uvolňování paměti InnoDB
Pokud je uvolňování paměti InnoDB zablokované nebo zpožděné, může databáze vyvinout podstatnou prodlevu vyprázdnění, která může negativně ovlivnit využití úložiště a výkon dotazů.
Délka seznamu historie segmentů vrácení zpět innoDB (HLL) měří počet záznamů změn uložených v protokolu vrácení zpět. Rostoucí hodnota HLL značí, že vlákna uvolňování paměti InnoDB (vyprázdnění vláken) nedrží krok s úlohou zápisu nebo že mazání je blokováno dlouhotrvajícím dotazem nebo transakcí.
Nadměrné zpoždění v uvolňování paměti mohou mít závažné, negativní důsledky:
- Systémový tabulkový prostor InnoDB se rozšíří, čímž se urychlí růst základního svazku úložiště. V některých případech může systémový tabulkový prostor v důsledku blokovaného vyprázdnění zvětšit o několik terabajtů.
- Záznamy označené odstraněním se neodeberou včas. To může způsobit, že se zvětšují tabulkové prostory InnoDB a zabrání modulu v opakovaném použitím úložiště obsazeného těmito záznamy.
- Výkon všech dotazů může snížit a využití procesoru se může zvýšit kvůli růstu struktur úložiště InnoDB.
Proto je důležité monitorovat hodnoty, vzory a trendy HLL.
Vyhledání hodnot HLL
Hodnotu HLL najdete spuštěním příkazu stavu innodb modulu show. Hodnota bude uvedena ve výstupu pod nadpisem TRANSACTIONS:
mysql> 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
(...)
Hodnotu HLL můžete také určit dotazem na tabulku 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)
Interpretace hodnot HLL
Při interpretaci hodnot HLL zvažte pokyny uvedené v následující tabulce:
| Hodnota | Poznámky |
|---|---|
| Méně než ~10 000 | Normální hodnoty označující, že uvolňování paměti nezapadá. |
| Mezi ~10 000 a ~1 000 000 | Tyto hodnoty označují menší prodlevu v uvolňování paměti. Tyto hodnoty mohou být přijatelné, pokud zůstanou stabilní a nezvyšují se. |
| Větší než ~1 000 000 | Tyto hodnoty by se měly prošetřit a můžou vyžadovat nápravné akce. |
Řešení nadměrných hodnot HLL
Pokud HLL ukazuje velké špičky nebo vykazuje vzor pravidelného růstu, prozkoumejte dotazy a transakce spuštěné na instanci flexibilního serveru Azure Database for MySQL okamžitě. Pak můžete vyřešit všechny problémy s úlohami, které by mohly bránit průběhu procesu uvolňování paměti. I když se neočekává, že databáze nebude bez vyprázdnění, nesmíte nechat prodlevu nekontrolovatelně.
Pokud chcete získat informace o transakcích information_schema.innodb_trx z tabulky, spusťte například následující příkazy:
select * from information_schema.innodb_trx
order by trx_started asc\G
Podrobnosti ve trx_started sloupci vám pomůžou vypočítat věk transakcí.
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%';
(…)
Informace o aktuálních databázových relacích, včetně času stráveného v aktuálním stavu relace, najdete v information_schema.processlist tabulce. Následující výstup například ukazuje relaci, která aktivně spouští dotaz za posledních 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%';
(...)
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 a další paměť, 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.
Nakonfigurujte innodb_purge_threads podle úloh, aby se zlepšila efektivita operací mazání na pozadí.
Poznámka:
Otestujte všechny změny této proměnné serveru pro každé prostředí, abyste mohli posoudit změnu chování modulu.
Používejte upozornění na "Procento procesoru hostitele", "Procento paměti hostitele" a "Celkový počet Připojení ionů", abyste dostávali oznámení, pokud systém překročí některou ze zadaných prahových hodnot.
Pomocí Přehledy výkonu dotazů nebo sešitů Azure můžete identifikovat problematické nebo pomalé dotazy a pak je optimalizovat.
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.