Felsöka frågeprestanda i Azure Database for MySQL – flexibel server
Frågeprestanda kan påverkas av flera faktorer, så det är först viktigt att titta på omfattningen av de symptom som du upplever i din Azure Database for MySQL – flexibel serverinstans. Är till exempel frågeprestandan långsam för:
- Alla frågor som körs på Azure Database for MySQL – flexibel serverinstans?
- En specifik uppsättning frågor?
- En specifik fråga?
Tänk också på att eventuella ändringar av strukturen eller underliggande data i tabellerna som du frågar efter kan påverka prestandan.
Aktivera loggningsfunktioner
Innan du analyserar enskilda frågor måste du definiera frågemått. Med den här informationen kan du implementera loggningsfunktioner på databasservern för att spåra frågor som överskrider ett tröskelvärde som du anger baserat på programmets behov.
Azure Database for MySQL – flexibel server, vi rekommenderar att du använder funktionen för långsam frågelogg för att identifiera frågor som tar längre tid än N sekunder att köra. När du har identifierat frågorna från loggen för långsamma frågor kan du använda MySQL-diagnostik för att felsöka dessa frågor.
Innan du kan börja spåra tidskrävande frågor behöver du aktivera parametern slow_query_log med hjälp av Azure Portal eller Azure CLI. Med den här parametern aktiverad bör du också konfigurera värdet för parametern long_query_time för att ange hur många sekunder som frågor kan köra innan de identifieras som "långsamma" frågor. Standardvärdet för parametern är 10 sekunder, men du kan justera värdet för att uppfylla behoven i programmets serviceavtal.
Loggen för långsamma frågor är ett bra verktyg för att spåra tidskrävande frågor, men det finns vissa scenarier där det kanske inte är effektivt. Till exempel loggen för långsamma frågor:
- Påverkar prestanda negativt om antalet frågor är mycket högt eller om frågeuttrycket är mycket stort. Justera värdet för parametern i enlighet med detta
long_query_time. - Kanske inte är till hjälp om du också har aktiverat parametern
log_queries_not_using_index, som anger att loggfrågor förväntas hämta alla rader. Frågor som utför en fullständig indexgenomsökning drar nytta av ett index, men de loggas eftersom indexet inte begränsar antalet rader som returneras.
Hämtar information från loggarna
Loggar är tillgängliga i upp till sju dagar från det att de har skapats. Du kan lista och ladda ned långsamma frågeloggar via Azure Portal eller Azure CLI. I Azure Portal navigerar du till servern under Övervakning, väljer Serverloggar och väljer sedan nedåtpilen bredvid en post för att ladda ned loggarna som är associerade med det datum och den tid du undersöker.
Om dina långsamma frågeloggar dessutom är integrerade med Azure Monitor-loggar via diagnostikloggar kan du köra frågor i ett redigeringsprogram för att analysera dem ytterligare:
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
Kommentar
Fler exempel för att komma igång med att diagnostisera långsamma frågeloggar via diagnostikloggar finns i Analysera loggar i Azure Monitor-loggar.
Följande ögonblicksbild visar ett exempel på en långsam fråga.
# 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%';;
Frågan kördes på 26 sekunder, undersökte över 443 000 rader och returnerade 126 rader med resultat.
Vanligtvis bör du fokusera på frågor med höga värden för Query_time och Rows_examined. Men om du märker frågor med hög Query_time men bara några få Rows_examined, indikerar detta ofta förekomsten av en resursflaskhals. I dessa fall bör du kontrollera om det finns någon I/O-begränsning eller CPU-användning.
Profilera en fråga
När du har identifierat en specifik fråga som körs långsamt kan du använda kommandot EXPLAIN och profilering för att samla in mer information.
Kontrollera frågeplanen genom att köra följande kommando:
EXPLAIN <QUERY>
Kommentar
Mer information om hur du använder EXPLAIN-instruktioner finns i Profilfrågeprestanda i Azure Database for MySQL – flexibel server med hjälp av EXPLAIN.
Förutom att skapa en EXPLAIN-plan för en fråga kan du använda kommandot SHOW PROFILE, som gör att du kan diagnostisera körningen av instruktioner som har körts under den aktuella sessionen.
Om du vill aktivera profilering och profilera en specifik fråga i en session kör du följande uppsättning kommandon:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Kommentar
Profilering av enskilda frågor är endast tillgängligt i en session och historiska instruktioner kan inte profileras.
Nu ska vi titta närmare på hur du använder dessa kommandon för att profilera en fråga. Först aktiverar du profilering för den aktuella sessionen och kör SET PROFILING = 1 kommandot:
SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Kör sedan en suboptimal fråga som utför en fullständig tabellgenomsökning:
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) |
Visa sedan en lista över alla tillgängliga frågeprofiler genom att SHOW PROFILES köra kommandot:
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) |
Kör slutligen kommandot för att visa profilen för fråga 1 SHOW PROFILE FOR QUERY 1 .
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) |
Lista de mest använda frågorna på databasservern
När du felsöker frågeprestanda är det bra att förstå vilka frågor som oftast körs på din Azure Database for MySQL – flexibel serverinstans. Du kan använda den här informationen för att mäta om någon av de viktigaste frågorna tar längre tid än vanligt att köra. Dessutom kan en utvecklare eller DBA använda den här informationen för att identifiera om någon fråga har en plötslig ökning av antalet frågekörningar och varaktighet.
Kör följande fråga för att lista de 10 vanligaste frågorna mot din Azure Database for MySQL – flexibel serverinstans:
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;
Kommentar
Använd den här frågan för att jämföra de vanligaste frågorna på databasservern och avgöra om de vanligaste frågorna har ändrats eller om några befintliga frågor i det ursprungliga riktmärket har ökat under körningstiden.
Lista de 10 dyraste frågorna efter total körningstid
Utdata från följande fråga innehåller information om de 10 vanligaste frågorna som körs mot databasservern och deras antal körningar på databasservern. Den innehåller också annan användbar information, till exempel svarstider för frågor, deras låstider, antalet temporära tabeller som skapats som en del av frågekörningen osv. Använd det här frågeutdata för att hålla reda på de viktigaste frågorna i databasen och ändringar i faktorer som svarstider, vilket kan tyda på en chans att finjustera frågan ytterligare för att undvika framtida risker.
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;
Övervaka InnoDB-skräpinsamling
När InnoDB-skräpinsamling blockeras eller fördröjs kan databasen utveckla en betydande rensningsfördröjning som kan påverka lagringsanvändningen och frågeprestanda negativt.
HLL (InnoDB Rollback Segment History List Length) mäter antalet ändringsposter som lagras i ångra-loggen. Ett växande HLL-värde anger att InnoDB:s skräpinsamlingstrådar (rensningstrådar) inte håller jämna steg med skrivarbetsbelastningen eller att rensning blockeras av en tidskrävande fråga eller transaktion.
För långa fördröjningar i skräpinsamling kan få allvarliga, negativa konsekvenser:
- InnoDB-systemtabellområdet expanderar, vilket påskyndar tillväxten av den underliggande lagringsvolymen. Ibland kan systemtabellområdet svälla med flera terabyte till följd av en blockerad rensning.
- Borttagna poster tas inte bort i tid. Detta kan göra att InnoDB-tabellytor växer och förhindrar att motorn återanvänder lagringen som används av dessa poster.
- Prestandan för alla frågor kan försämras och processoranvändningen kan öka på grund av tillväxten av InnoDB-lagringsstrukturer.
Därför är det viktigt att övervaka HLL-värden, mönster och trender.
Hitta HLL-värden
Du hittar HLL-värdet genom att köra kommandot show engine innodb status. Värdet visas i utdata under rubriken TRANSAKTIONER:
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
(...)
Du kan också fastställa HLL-värdet genom att fråga tabellen 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) |
Tolka HLL-värden
När du tolkar HLL-värden bör du överväga riktlinjerna som anges i följande tabell:
| Värde | Anteckningar |
|---|---|
| Mindre än ~10 000 | Normalvärden som anger att skräpinsamlingen inte hamnar efter. |
| Mellan ~10 000 och ~1 000 000 | Dessa värden anger en mindre fördröjning i skräpinsamlingen. Sådana värden kan vara acceptabla om de förblir stabila och inte ökar. |
| Större än ~1 000 000 | Dessa värden bör undersökas och kan kräva korrigerande åtgärder |
Hantera överdrivna HLL-värden
Om HLL visar stora toppar eller uppvisar ett mönster av periodisk tillväxt undersöker du frågorna och transaktionerna som körs på din Azure Database for MySQL – flexibel serverinstans omedelbart. Sedan kan du lösa eventuella arbetsbelastningsproblem som kan förhindra förloppet för skräpinsamlingsprocessen. Databasen förväntas inte vara fri från rensningsfördröjning, men du får inte låta fördröjningen växa okontrollerat.
Om du till exempel vill hämta transaktionsinformation från information_schema.innodb_trx tabellen kör du följande kommandon:
select * from information_schema.innodb_trx
order by trx_started asc\G
Informationen i trx_started kolumnen hjälper dig att beräkna transaktionsåldern.
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%';
(...)
Information om aktuella databassessioner, inklusive den tid som spenderas i sessionens aktuella tillstånd, finns information_schema.processlist i tabellen. Följande utdata visar till exempel en session som aktivt har kört en fråga under de senaste 1462 sekunderna:
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%';
(...)
Rekommendationer
Kontrollera att databasen har tillräckligt med resurser allokerade för att köra dina frågor. Ibland kan du behöva skala upp instansstorleken för att få fler CPU-kärnor och ytterligare minne för din arbetsbelastning.
Undvik stora eller långvariga transaktioner genom att dela upp dem i mindre transaktioner.
Konfigurera innodb_purge_threads enligt din arbetsbelastning för att förbättra effektiviteten för bakgrundsrensningsåtgärder.
Kommentar
Testa eventuella ändringar i den här servervariabeln för varje miljö för att mäta ändringen av motorbeteendet.
Använd aviseringar på "Host CPU Percent", "Host Memory Percent" och "Total Connections" så att du får meddelanden om systemet överskrider något av de angivna tröskelvärdena.
Använd Query Performance Insights eller Azure-arbetsböcker för att identifiera eventuella problematiska eller långsamt körande frågor och sedan optimera dem.
För produktionsdatabasservrar samlar du in diagnostik med jämna mellanrum för att säkerställa att allt fungerar smidigt. Annars kan du felsöka och lösa eventuella problem som du identifierar.