Troubleshoot query performance in Azure Database for MySQL - Flexible Server

A következőkre vonatkozik: Azure Database for MySQL – Egykiszolgálós Azure Database for MySQL – Rugalmas kiszolgáló

Fontos

Az önálló Azure Database for MySQL-kiszolgáló a kivonási útvonalon van. Határozottan javasoljuk, hogy frissítsen rugalmas Azure Database for MySQL-kiszolgálóra. További információ a rugalmas Azure Database for MySQL-kiszolgálóra való migrálásról: Mi történik az önálló Azure Database for MySQL-kiszolgálóval?

A lekérdezési teljesítményt több tényező is befolyásolhatja, ezért először is fontos megvizsgálni a rugalmas Azure Database for MySQL-kiszolgálópéldányban tapasztalt tünetek hatókörét. A lekérdezési teljesítmény például lassú a következőhöz:

• Az Azure Database for MySQL rugalmas kiszolgálópéldányán futó összes lekérdezés?
• Egy adott lekérdezéskészlet?
• Egy adott lekérdezés?

Ne feledje azt is, hogy a lekérdezett táblák szerkezetének vagy alapjául szolgáló adatoknak a legutóbbi változásai hatással lehetnek a teljesítményre.

Naplózási funkciók engedélyezése

Az egyes lekérdezések elemzése előtt meg kell határoznia a lekérdezési teljesítményteszteket. Ezekkel az információkkal az adatbázis-kiszolgálón naplózási funkciókat valósíthat meg az alkalmazás igényeinek megfelelően megadott küszöbértéket meghaladó lekérdezések nyomon követéséhez.

Rugalmas Azure Database for MySQL-kiszolgáló esetén ajánlott a lassú lekérdezésnapló-funkció használata az N másodpercnél hosszabb ideig futó lekérdezések azonosításához. Miután azonosította a lassú lekérdezési napló lekérdezéseit, a MySQL-diagnosztika segítségével elháríthatja ezeket a lekérdezéseket.

Mielőtt elkezdené nyomon követni a hosszú ideig futó lekérdezéseket, engedélyeznie kell a slow_query_log paramétert az Azure Portal vagy az Azure CLI használatával. Ha ez a paraméter engedélyezve van, a paraméter értékét long_query_time úgy is konfigurálnia kell, hogy megadja a lekérdezések által futtatható másodpercek számát, mielőtt "lassú futtatású" lekérdezésként azonosítanák őket. A paraméter alapértelmezett értéke 10 másodperc, de az értéket az alkalmazás SLA-jának igényeihez igazíthatja.

Bár a lassú lekérdezési napló nagyszerű eszköz a hosszú ideig futó lekérdezések nyomon követésére, bizonyos helyzetekben előfordulhat, hogy nem hatékony. Például a lassú lekérdezési napló:

• Negatívan befolyásolja a teljesítményt, ha a lekérdezések száma nagyon magas, vagy ha a lekérdezési utasítás nagyon nagy. Ennek megfelelően állítsa be a long_query_time paraméter értékét.
• Nem feltétlenül hasznos, ha engedélyezte a log_queries_not_using_index paramétert is, amely az összes sor lekérésére várt lekérdezések naplózását határozza meg. A teljes indexvizsgálatot végző lekérdezések kihasználják az index előnyeit, de a rendszer naplózza őket, mert az index nem korlátozza a visszaadott sorok számát.

Adatok lekérése a naplókból

A naplók a létrehozásuktól számított hét napig érhetők el. A lassú lekérdezési naplókat az Azure Portalon vagy az Azure CLI-vel listázhatja és letöltheti. Az Azure Portalon lépjen a kiszolgálóra a Figyelés területen válassza a Kiszolgálónaplók lehetőséget, majd a bejegyzés melletti lefelé mutató nyílra kattintva töltse le a vizsgálandó dátumhoz és időponthoz tartozó naplókat.

Ha a lassú lekérdezési naplók diagnosztikai naplókon keresztül integrálva vannak az Azure Monitor-naplókkal, lekérdezéseket futtathat egy szerkesztőben, hogy tovább elemezze őket:

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

Megjegyzés:

További példák a lassú lekérdezési naplók diagnosztikai naplókon keresztüli diagnosztizálására: Naplók elemzése az Azure Monitor-naplókban.

Az alábbi pillanatkép egy lassú lekérdezésmintát ábrázol.

# 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%';;

Figyelje meg, hogy a lekérdezés 26 másodperc alatt futott, több mint 443 ezer sort vizsgált meg, és 126 sornyi eredményt adott vissza.

Általában az Query_time és Rows_examined magas értékeket tartalmazó lekérdezésekre kell összpontosítania. Ha azonban magas Query_time, de csak néhány Rows_examined lekérdezést észlel, ez gyakran az erőforrás szűk keresztmetszetét jelzi. Ezekben az esetekben ellenőriznie kell, hogy van-e I/O-szabályozás vagy PROCESSZORhasználat.

Lekérdezés profilozása

Miután azonosított egy adott lassú futtatású lekérdezést, a EXPLAIN paranccsal és a profilkészítéssel részletesebb információkat gyűjthet.

A lekérdezési terv ellenőrzéséhez futtassa a következő parancsot:

EXPLAIN <QUERY>

Megjegyzés:

A EXPLAIN utasítások használatával kapcsolatos további információkért lásd : A EXPLAIN használata a lekérdezési teljesítmény profilkészítéséhez a rugalmas Azure Database for MySQL-kiszolgálón.

A lekérdezéshez a EXPLAIN-terv létrehozása mellett a SHOW PROFILE parancsot is használhatja, amely lehetővé teszi az aktuális munkamenetben futtatott utasítások végrehajtásának diagnosztizálását.

A profilkészítés engedélyezéséhez és egy adott lekérdezés profilozásához futtassa a következő parancsokat:

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

Megjegyzés:

Az egyes lekérdezések profilozása csak munkamenetekben érhető el, és az előzménykivonatok nem profilhatók.

Nézzük meg közelebbről, hogy ezekkel a parancsokkal profilozza a lekérdezést. Először engedélyezze a profilkészítést az aktuális munkamenethez, és futtassa a SET PROFILING = 1 következő parancsot:

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

Ezután hajtsa végre az optimálisnál rosszabb lekérdezést, amely teljes táblázatvizsgálatot hajt végre:

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)

Ezután megjelenítheti az összes elérhető lekérdezési profil listáját a SHOW PROFILES parancs futtatásával:

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)

Végül az 1. lekérdezés profiljának megjelenítéséhez futtassa a SHOW PROFILE FOR QUERY 1 parancsot.

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)

A leggyakrabban használt lekérdezések listázása az adatbázis-kiszolgálón

A lekérdezési teljesítmény hibaelhárításakor hasznos tudni, hogy mely lekérdezések futnak leggyakrabban a rugalmas Azure Database for MySQL-kiszolgálópéldányon. Ezekkel az információkkal felmérheti, hogy a leggyakoribb lekérdezések futtatása a szokásosnál tovább tart-e. Emellett a fejlesztő vagy a DBA felhasználhatja ezeket az információkat annak azonosítására, hogy egy lekérdezés hirtelen megnövekedett-e a lekérdezések végrehajtási számában és időtartamában.

A rugalmas Azure Database for MySQL-kiszolgálópéldány 10 legvégzettebb lekérdezésének listájához futtassa a következő lekérdezést:

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;

Megjegyzés:

Ezzel a lekérdezéssel mérheti fel az adatbázis-kiszolgálón a legvégső lekérdezéseket, és megállapíthatja, hogy történt-e változás a legfelső lekérdezésekben, vagy a kezdeti teljesítménytesztben lévő meglévő lekérdezések futásideje megnőtt.

A 10 legdrágább lekérdezés felsorolása a teljes végrehajtási idő alapján

Az alábbi lekérdezés kimenete az adatbázis-kiszolgálón futó első 10 lekérdezésről és az adatbázis-kiszolgálón végrehajtott végrehajtások számáról nyújt információt. Egyéb hasznos információkat is tartalmaz, például a lekérdezés késéseit, zárolási idejét, a lekérdezési futtatókörnyezet részeként létrehozott ideiglenes táblák számát stb. Ezzel a lekérdezéskimenettel nyomon követheti az adatbázis legfontosabb lekérdezéseit, és módosíthatja az olyan tényezőket, mint a késések, ami arra utalhat, hogy a jövőbeni kockázatok elkerülése érdekében a lekérdezés tovább finomhangolható.

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;

InnoDB-szemétgyűjtés figyelése

Ha az InnoDB szemétgyűjtés le van tiltva vagy késleltetve van, az adatbázis jelentős törlési késést okozhat, amely negatívan befolyásolhatja a tárterület kihasználtságát és a lekérdezési teljesítményt.

Az InnoDB visszaállítási szegmensek előzménylistájának hossza (HLL) a visszavonási naplóban tárolt változásrekordok számát méri. A növekvő HLL-érték azt jelzi, hogy az InnoDB szemétgyűjtési szálai (törlési szálak) nem tartják lépést az írási számítási feladatokkal, vagy hogy a törlést egy hosszú ideig futó lekérdezés vagy tranzakció blokkolja.

A szemétgyűjtés túlzott késése súlyos, negatív következményekkel járhat:

• Az InnoDB rendszer tablespace bővül, így felgyorsítja a mögöttes tárterület növekedését. Időnként a rendszer táblatere több terabájttal megduzzadhat egy blokkolt kiürítés következtében.
• A törléssel megjelölt rekordok nem lesznek időben eltávolítva. Ez az InnoDB-táblaterek növekedését okozhatja, és megakadályozhatja, hogy a motor újrahasználja a rekordok által foglalt tárolót.
• Az összes lekérdezés teljesítménye csökkenhet, és a processzorhasználat az InnoDB storage-struktúrák növekedése miatt növekedhet.

Ennek eredményeképpen fontos figyelni a HLL-értékeket, mintákat és trendeket.

HLL-értékek keresése

A HLL-értéket a show engine innodb status parancs futtatásával találja meg. Az érték a kimenetben, a TRANSACTIONS fejléc alatt jelenik meg:

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 
 
(...) 

A HLL-értéket a information_schema.innodb_metrics tábla lekérdezésével is meghatározhatja:

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

HLL-értékek értelmezése

A HLL-értékek értelmezésekor vegye figyelembe az alábbi táblázatban felsorolt irányelveket:

Érték	Megjegyzések
Kevesebb mint ~10 000	Normál értékek, amelyek azt jelzik, hogy a szemétgyűjtés nem esik le.
~10 000 és ~1 000 000 között	Ezek az értékek kisebb késést jeleznek a szemétgyűjtésben. Az ilyen értékek elfogadhatók lehetnek, ha állandóak maradnak, és nem növekednek.
~1 000 000-nél nagyobb	Ezeket az értékeket meg kell vizsgálni, és szükség lehet korrekciós műveletekre

Túlzott HLL-értékek kezelése

Ha a HLL nagy kiugró értékeket vagy időszakos növekedést mutat, azonnal vizsgálja meg a rugalmas Azure Database for MySQL-kiszolgálópéldányon futó lekérdezéseket és tranzakciókat. Ezután megoldhatja azokat a számítási feladatokkal kapcsolatos problémákat, amelyek megakadályozhatják a szemétgyűjtési folyamat előrehaladását. Bár nem várható, hogy az adatbázis mentes legyen a törlési késéstől, nem hagyhatja, hogy a késés ellenőrizetlenül növekedjen.

Ha például a information_schema.innodb_trx tábla tranzakciós adatait szeretné lekérte, futtassa a következő parancsokat:

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

Az oszlopban található trx_started részletek segítenek a tranzakció korának kiszámításában.

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%';
(…) 

Az aktuális adatbázis-munkamenetekről , beleértve a munkamenet aktuális állapotában töltött időt is, tekintse meg a táblát information_schema.processlist . Az alábbi kimenet például egy munkamenetet jelenít meg, amely aktívan végrehajt egy lekérdezést az elmúlt 1462 másodpercben:

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%';
 
(...) 

Javaslatok

• Győződjön meg arról, hogy az adatbázis elegendő erőforrással rendelkezik a lekérdezések futtatásához. Előfordulhat, hogy időnként fel kell skáláznia a példány méretét, hogy több processzormagot és további memóriát kapjon a számítási feladat ellátásához.

• Kerülje a nagy vagy hosszú ideig futó tranzakciókat, ha kisebb tranzakciókra bontja őket.

• A innodb_purge_threads a számítási feladatnak megfelelően konfigurálja a háttértisztítási műveletek hatékonyságának növelése érdekében.

  Megjegyzés:

  Tesztelje az egyes környezetek kiszolgálóváltozójának módosításait, hogy felmérje a motor viselkedésének változását.

• A "Gazdagép CPU-százaléka", a "Gazdagép memória százaléka" és a "Teljes Csatlakozás ions" riasztások használatával értesítéseket kaphat, ha a rendszer túllépi a megadott küszöbértékeket.

• A lekérdezési teljesítmény Elemzések vagy Azure-munkafüzetek használatával azonosíthatja a problémás vagy lassan futó lekérdezéseket, majd optimalizálhatja őket.

• Éles adatbázis-kiszolgálók esetén rendszeres időközönként gyűjtse össze a diagnosztikát, hogy minden zökkenőmentesen fusson. Ha nem, elháríthatja és megoldhatja az ön által azonosított problémákat.

További lépések

Ha társválaszt szeretne keresni a legfontosabb kérdésekre, vagy fel szeretne tenni vagy megválaszolni egy kérdést, látogasson el a Stack Overflow webhelyre.