Problemen met queryprestaties in Azure Database for MySQL Flexibele server oplossen
VAN TOEPASSING OP: 
Azure Database for MySQL - Enkele server Azure Database for MySQL - Flexibele server
Belangrijk
Azure Database for MySQL enkele server bevindt zich op het buitengebruikstellingspad. We raden u ten zeerste aan een upgrade uit te voeren naar een flexibele Azure Database for MySQL-server. Zie Wat gebeurt er met Azure Database for MySQL Enkele server voor meer informatie over migreren naar Azure Database for MySQL Flexibele server ?
Queryprestaties kunnen worden beïnvloed door meerdere factoren, dus het is belangrijk om te kijken naar het bereik van de symptomen die u ondervindt in uw exemplaar van Azure Database for MySQL Flexible Server. Is queryprestaties bijvoorbeeld traag voor:
- Alle query's die worden uitgevoerd op het azure Database for MySQL Flexible Server-exemplaar?
- Een specifieke set query's?
- Een specifieke query?
Houd er ook rekening mee dat recente wijzigingen in de structuur of onderliggende gegevens van de tabellen waarop u query's uitvoert, van invloed kunnen zijn op de prestaties.
Functionaliteit voor logboekregistratie inschakelen
Voordat u afzonderlijke query's analyseert, moet u querybenchmarks definiëren. Met deze informatie kunt u logboekregistratiefunctionaliteit op de databaseserver implementeren om query's te traceren die een drempelwaarde overschrijden die u opgeeft op basis van de behoeften van de toepassing.
Azure Database for MySQL Flexible Server is het raadzaam om de functie voor langzame querylogboeken te gebruiken om query's te identificeren die langer duren dan N seconden om uit te voeren. Nadat u de query's uit het logboek voor langzame query's hebt geïdentificeerd, kunt u MySQL-diagnostische gegevens gebruiken om problemen met deze query's op te lossen.
Voordat u langlopende query's kunt traceren, moet u de slow_query_log parameter inschakelen met behulp van Azure Portal of Azure CLI. Als deze parameter is ingeschakeld, moet u ook de waarde van de long_query_time parameter configureren om het aantal seconden op te geven dat query's kunnen worden uitgevoerd voordat ze worden geïdentificeerd als 'traag uitgevoerde' query's. De standaardwaarde van de parameter is 10 seconden, maar u kunt de waarde aanpassen aan de behoeften van de SLA van uw toepassing.
Hoewel het logboek voor langzame query's een uitstekend hulpprogramma is voor het traceren van langlopende query's, zijn er bepaalde scenario's waarin het mogelijk niet effectief is. Bijvoorbeeld het logboek voor langzame query's:
- Dit heeft negatieve gevolgen voor de prestaties als het aantal query's erg hoog is of als de query-instructie erg groot is. Pas de waarde van de
long_query_timeparameter dienovereenkomstig aan. - Het is mogelijk niet handig als u ook de
log_queries_not_using_indexparameter hebt ingeschakeld, waarmee wordt opgegeven dat query's moeten worden vastgelegd die naar verwachting alle rijen moeten ophalen. Query's die een volledige indexscan uitvoeren, profiteren van een index, maar ze worden vastgelegd omdat de index het aantal geretourneerde rijen niet beperkt.
Gegevens ophalen uit de logboeken
Logboeken zijn maximaal zeven dagen na het maken beschikbaar. U kunt logboeken voor langzame query's weergeven en downloaden via Azure Portal of Azure CLI. Navigeer in Azure Portal naar uw server, selecteer serverlogboeken onder Bewaking en selecteer vervolgens de pijl-omlaag naast een vermelding om de logboeken te downloaden die zijn gekoppeld aan de datum en tijd die u onderzoekt.
Als uw logboeken voor langzame query's zijn geïntegreerd met Azure Monitor-logboeken via diagnostische logboeken, kunt u query's uitvoeren in een editor om ze verder te analyseren:
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
Notitie
Zie Logboeken analyseren in Azure Monitor-logboeken voor meer voorbeelden om aan de slag te gaan met het diagnosticeren van trage querylogboeken via diagnostische logboeken.
In de volgende momentopname ziet u een trage voorbeeldquery.
# 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%';;
U ziet dat de query in 26 seconden is uitgevoerd, meer dan 443.000 rijen heeft onderzocht en 126 rijen met resultaten heeft geretourneerd.
Meestal moet u zich richten op query's met hoge waarden voor Query_time en Rows_examined. Als u echter query's met een hoge Query_time ziet, maar slechts een paar Rows_examined, duidt dit vaak op de aanwezigheid van een knelpunt voor resources. Voor deze gevallen moet u controleren of er een IO-beperking of CPU-gebruik is.
Een query profileren
Nadat u een specifieke trage query hebt geïdentificeerd, kunt u de opdracht EXPLAIN en profilering gebruiken om meer details te verzamelen.
Voer de volgende opdracht uit om het queryplan te controleren:
EXPLAIN <QUERY>
Notitie
Zie Uitleg gebruiken om queryprestaties in Azure Database for MySQL Flexible Server te profilen voor meer informatie over het gebruik van EXPLAIN-instructies.
Naast het maken van een EXPLAIN-plan voor een query, kunt u de opdracht SHOW PROFILE gebruiken, waarmee u de uitvoering van instructies kunt diagnosticeren die in de huidige sessie zijn uitgevoerd.
Als u profilering en profiel wilt inschakelen voor een specifieke query in een sessie, voert u de volgende set opdrachten uit:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Notitie
Het profileren van afzonderlijke query's is alleen beschikbaar in een sessie en historische instructies kunnen niet worden geprofileerd.
Laten we eens kijken naar het gebruik van deze opdrachten om een query te profilen. Schakel eerst profilering in voor de huidige sessie en voer de SET PROFILING = 1 opdracht uit:
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Voer vervolgens een suboptimale query uit waarmee een volledige tabelscan wordt uitgevoerd:
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)
Geef vervolgens een lijst weer met alle beschikbare queryprofielen door de opdracht uit te SHOW PROFILES voeren:
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)
Voer ten slotte de opdracht uit om het profiel voor query 1 SHOW PROFILE FOR QUERY 1 weer te geven.
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)
De meest gebruikte query's op de databaseserver weergeven
Wanneer u problemen met queryprestaties wilt oplossen, is het handig om te begrijpen welke query's het vaakst worden uitgevoerd op uw Azure Database for MySQL Flexible Server-exemplaar. U kunt deze informatie gebruiken om te meten of een van de belangrijkste query's langer duurt dan normaal. Daarnaast kan een ontwikkelaar of DBA deze informatie gebruiken om te bepalen of een query een plotselinge toename van het aantal queryuitvoeringen en de duur heeft.
Voer de volgende query uit om de tien meest uitgevoerde query's weer te geven voor uw Azure Database for MySQL Flexible Server-exemplaar:
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;
Notitie
Gebruik deze query om de meest uitgevoerde query's in uw databaseserver te benchmarken en te bepalen of er een wijziging is aangebracht in de topquery's of dat bestaande query's in de eerste benchmark zijn toegenomen tijdens de uitvoering.
De 10 duurste query's weergeven op basis van de totale uitvoeringstijd
De uitvoer van de volgende query bevat informatie over de tien belangrijkste query's die worden uitgevoerd op de databaseserver en het aantal uitvoeringen op de databaseserver. Het biedt ook andere nuttige informatie, zoals de latenties van query's, de vergrendelingstijden, het aantal tijdelijke tabellen dat is gemaakt als onderdeel van de queryruntime, enzovoort. Gebruik deze queryuitvoer om de belangrijkste query's in de database bij te houden en wijzigingen in factoren zoals latenties, die kunnen wijzen op een kans om de query verder af te stemmen om toekomstige risico's te voorkomen.
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 garbagecollection bewaken
Wanneer innoDB garbagecollection wordt geblokkeerd of vertraagd, kan de database een aanzienlijke opschoningsvertraging ontwikkelen die een negatieve invloed kan hebben op het opslaggebruik en de queryprestaties.
De lengte van de geschiedenislijst voor innoDB-segmentgeschiedenis (HLL) meet het aantal wijzigingsrecords dat is opgeslagen in het logboek ongedaan maken. Een groeiende HLL-waarde geeft aan dat innoDB's garbagecollection-threads (threads opschonen) niet bijhouden met schrijfworkloads of dat opschonen wordt geblokkeerd door een langlopende query of transactie.
Overmatige vertragingen in garbagecollection kunnen ernstige, negatieve gevolgen hebben:
- De Tabelruimte van het InnoDB-systeem wordt uitgebreid, waardoor de groei van het onderliggende opslagvolume wordt versneld. Soms kan de systeemtabelruimte door verschillende terabytes worden gezwollen als gevolg van een geblokkeerde opschoning.
- Verwijderde records worden niet tijdig verwijderd. Dit kan ertoe leiden dat InnoDB-tabelruimten toenemen en voorkomt dat de engine de opslag die door deze records wordt gebruikt, opnieuw wordt gebruikt.
- De prestaties van alle query's kunnen afnemen en het CPU-gebruik kan toenemen vanwege de groei van InnoDB-opslagstructuren.
Als gevolg hiervan is het belangrijk om HLL-waarden, -patronen en -trends te bewaken.
HLL-waarden zoeken
U kunt de HLL-waarde vinden door de opdracht show engine innodb status uit te voeren. De waarde wordt weergegeven in de uitvoer, onder de kop TRANSACTIES:
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
(...)
U kunt ook de HLL-waarde bepalen door een query uit te voeren op de tabel 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)
HLL-waarden interpreteren
Houd bij het interpreteren van HLL-waarden rekening met de richtlijnen die worden vermeld in de volgende tabel:
| Value | Notes |
|---|---|
| Kleiner dan ~10.000 | Normale waarden, waarmee wordt aangegeven dat garbagecollection niet achterloopt. |
| Tussen ~10.000 en ~1.000.000 | Deze waarden geven een kleine vertraging in garbagecollection aan. Dergelijke waarden kunnen acceptabel zijn als ze stabiel blijven en niet toenemen. |
| Groter dan ~1.000.000 | Deze waarden moeten worden onderzocht en kunnen corrigerende acties vereisen |
Overmatige HLL-waarden aanpakken
Als de HLL grote pieken vertoont of een patroon van periodieke groei vertoont, onderzoekt u de query's en transacties die worden uitgevoerd op uw Azure Database for MySQL Flexible Server-exemplaar onmiddellijk. Vervolgens kunt u eventuele workloadproblemen oplossen die de voortgang van het garbagecollectionproces mogelijk verhinderen. Hoewel het niet wordt verwacht dat de database vrij is van opschoningsvertraging, moet u de vertraging niet onbeheerd laten groeien.
Voer bijvoorbeeld de volgende opdrachten uit om transactiegegevens uit de information_schema.innodb_trx tabel te verkrijgen:
select * from information_schema.innodb_trx
order by trx_started asc\G
De details in de trx_started kolom helpen u bij het berekenen van de transactieduur.
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%';
(…)
Raadpleeg de tabel voor informatie over huidige databasesessies, inclusief de tijd die is besteed aan de huidige status van de information_schema.processlist sessie. In de volgende uitvoer ziet u bijvoorbeeld een sessie die de afgelopen 1462 seconden actief een query heeft uitgevoerd:
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%';
(...)
Aanbevelingen
Zorg ervoor dat uw database voldoende resources heeft toegewezen om uw query's uit te voeren. Soms moet u mogelijk de grootte van het exemplaar omhoog schalen om meer CPU-kernen en extra geheugen te krijgen voor uw workload.
Vermijd grote of langlopende transacties door ze op te delen in kleinere transacties.
Configureer innodb_purge_threads op basis van uw workload om de efficiëntie voor opschoning op de achtergrond te verbeteren.
Notitie
Test eventuele wijzigingen in deze servervariabele voor elke omgeving om de wijziging in het enginegedrag te meten.
Gebruik waarschuwingen voor host-CPU-percentage, hostgeheugenpercentage en totaal aantal verbindingen, zodat u meldingen ontvangt als het systeem een van de opgegeven drempelwaarden overschrijdt.
Gebruik Query Performance Insights of Azure Workbooks om problematische of langzaam uitgevoerde query's te identificeren en deze vervolgens te optimaliseren.
Verzamel voor productiedatabaseservers regelmatig diagnostische gegevens om ervoor te zorgen dat alles soepel verloopt. Als dat niet het geval is, kunt u eventuele problemen oplossen die u identificeert.
Volgende stappen
Als u peerantwoorden wilt vinden op uw belangrijkste vragen of als u een vraag wilt posten of beantwoorden, gaat u naar Stack Overflow.