Freigeben über

Beheben von Problemen mit der Abfrageleistung in Azure Database for MySQL Flexible Server

  • Artikel

GILT FÜR:Azure Database for MySQL Single Server Azure Database for MySQL Flexible Server

Wichtig

Azure Database for MySQL Single Server wird eingestellt. Es wird dringend empfohlen, ein Upgrade auf Azure Database for MySQL Flexible Server auszuführen. Weitere Informationen zum Migrieren zu Azure Database for MySQL Flexible Server finden Sie unter Was geschieht mit Azure Database for MySQL Single Server?

Die Abfrageleistung kann durch mehrere Faktoren beeinflusst werden. Daher sollten Sie sich zunächst das Ausmaß bzw. den Bereich der Symptome ansehen, die in Ihrer Instanz von Azure Database for MySQL-Server – Flexibler Server auftreten. Wann ist die Abfrageleistung beispielsweise gering?

  • Für alle Abfragen, die auf der Instanz von Azure Database for MySQL – Flexibler Server ausgeführt werden
  • Für eine bestimmte Gruppe von Abfragen
  • Für eine bestimmte Abfrage

Beachten Sie auch, dass alle kürzlich vorgenommenen Änderungen an der Struktur oder den zugrunde liegenden Daten der abgefragten Tabellen die Leistung beeinträchtigen können.

Aktivieren der Protokollierungsfunktionalität

Bevor Sie einzelne Abfragen analysieren, müssen Sie Abfragebenchmarks definieren. Anhand dieser Informationen können Sie Protokollierungsfunktionen auf dem Datenbankserver implementieren, um Abfragen zu verfolgen, die einen Schwellenwert überschreiten, den Sie basierend auf den Anforderungen der Anwendung angegeben haben.

Für Azure Database for MySQL – Flexibler Server wird empfohlen, das Protokollfeature für langsame Abfragen zu verwenden, um Abfragen zu ermitteln, deren Ausführung länger als N Sekunden dauert. Nachdem Sie die Abfragen mit dem Protokoll für langsame Abfragen ermittelt haben, können Sie die MySQL-Diagnose verwenden, um Probleme mit diesen Abfragen zu behandeln.

Bevor Sie mit der Ablaufverfolgung von zeitintensiven Abfragen beginnen können, müssen Sie den slow_query_log-Parameter im Azure-Portal oder mit der Azure CLI aktivieren. Wenn dieser Parameter aktiviert ist, sollten Sie auch mit dem Wert des long_query_time-Parameters die Anzahl der Sekunden für die Ausführung von Abfragen angeben, bevor die Anfrage als „langsam ausgeführte“ Abfrage festgelegt wird. Der Standardwert des Parameters beträgt 10 Sekunden. Sie können jedoch den Wert anpassen, damit die Anforderungen der SLA Ihrer Anwendung erfüllt werden.

Azure Database for MySQL – Flexibler Server: Protokollschnittstelle für langsame Abfragen

Während das Protokoll für langsame Abfragen ein gut geeignetes Tool für die Ablaufverfolgung von zeitintensiven Abfragen ist, gibt es bestimmte Szenarien, in denen das Tool möglicherweise nicht effektiv ist. Beachten Sie folgende Punkte beim Protokoll für langsame Abfragen:

  • Wenn die Anzahl der Abfragen sehr groß oder die Abfrageanweisung sehr umfangreich ist, wirkt sich dies negativ auf die Leistung aus. Passen Sie den Wert des long_query_time-Parameters entsprechend an.
  • Es ist u. U. nicht hilfreich, wenn Sie auch den log_queries_not_using_index-Parameter aktiviert haben, der angibt, dass Abfragen protokolliert werden, die alle Zeilen abrufen sollen. Abfragen, die eine vollständige Indexprüfung ausführen, nutzen einen Index, werden jedoch protokolliert, da der Index die Anzahl der zurückgegebenen Zeilen nicht beschränkt.

Abrufen von Informationen aus den Protokollen

Protokolle sind für bis zu sieben Tage nach ihrer Erstellung verfügbar. Sie können Protokolle für langsame Abfragen im Azure-Portal oder in der Azure CLI auflisten und herunterladen. Navigieren Sie im Azure-Portal zu Ihrem Server, wählen Sie unter Überwachung die Option Serverprotokolle und dann den Abwärtspfeil neben einem Eintrag aus, um die Protokolle für den zu untersuchenden Zeitraum herunterzuladen.

Azure Database for MySQL – Flexibler Server: Abrufen von Daten aus den Protokollen

Wenn Ihre Protokolle für langsamen Abfrage über Diagnoseprotokolle in Azure Monitor-Protokolle integriert sind, können Sie Abfragen in einem Editor ausführen, um sie weiter zu analysieren:

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

Hinweis

Weitere Beispiele für die ersten Schritte bei der Diagnose von Protokollen für langsame Abfragen über Diagnoseprotokolle finden Sie unter Analysieren von Protokollen in Azure Monitor-Protokollen.

Die folgende Momentaufnahme stellt ein Beispiel für eine langsame Abfrage dar.

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

Die Ausführung der Abfrage hat 26 Sekunden gedauert, und es wurden mehr als 443.000 Zeilen untersucht und 126 Ergebniszeilen zurückgegeben.

Normalerweise sollten Sie sich auf Abfragen mit hohen Werten für Query_time und Rows_examined konzentrieren. Wenn Sie jedoch Abfragen mit einem hohen Query_time-Wert, aber einem relativ kleinen Rows_examined-Wert bemerken, weist dies häufig auf das Vorhandensein von Ressourcenengpässen hin. In diesen Fällen sollten Sie überprüfen, ob E/A-Vorgänge gedrosselt werden oder die CPU ausgelastet ist.

Erstellen eines Abfrageprofils

Nachdem Sie eine bestimmte langsam ausgeführte Abfrage identifiziert haben, können Sie mit dem EXPLAIN-Befehl und der Erstellen eines Profils weitere Details erfassen.

Führen Sie den folgenden Befehl aus, um den Abfrageplan zu überprüfen:

EXPLAIN <QUERY>

Hinweis

Weitere Informationen zur Verwendung von EXPLAIN-Anweisungen finden Sie im Artikel Analysieren der Abfrageleistung in Azure Database for MySQL – Flexibler Server mit EXPLAIN.

Zusätzlich zum Erstellen eines EXPLAIN-Plans für eine Abfrage können Sie den Befehl SHOW PROFILE verwenden, um eine Diagnose der Ausführung von Anweisungen durchzuführen, die in der aktuellen Sitzung ausgeführt wurden.

Führen Sie zum Aktivieren der Profilerstellung und zum Profilieren einer bestimmten Abfrage in einer Sitzung die folgenden Befehle aus:

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

Hinweis

Die Profilerstellung für einzelne Abfragen ist nur in einer Sitzung verfügbar. Für frühere Anweisungen kann kein Profil erstellt werden.

Lassen Sie uns einen genaueren Blick auf die Verwendung dieser Befehle zum Erstellen eines Abfrageprofils werfen. Aktivieren Sie zunächst die Profilerstellung für die aktuelle Sitzung, indem Sie den SET PROFILING = 1-Befehl ausführen:

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

Führen Sie als Nächstes eine suboptimale Abfrage aus, die eine vollständige Tabellenüberprüfung ausführt:

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)

Zeigen Sie dann eine Liste aller verfügbaren Abfrageprofile an, indem Sie den SHOW PROFILES-Befehl ausführen:

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)

Führen Sie schließlich den SHOW PROFILE FOR QUERY 1-Befehl aus, um das Profil für die erste Abfrage anzuzeigen.

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)

Auflisten der am häufigsten verwendeten Abfragen auf dem Datenbankserver

Beim Behandeln von Problemen mit der Abfrageleistung ist es hilfreich, zu wissen, welche Abfragen am häufigsten auf Ihrer Instanz von Azure Database for MySQL – Flexibler Server ausgeführt werden. Sie können diese Informationen verwenden, um festzustellen, ob eine der am häufigsten verwendeten Abfragen länger dauert als üblich. Darüber hinaus kann ein Entwickler oder DBA diese Informationen verwenden, um festzustellen, ob eine Abfrage eine plötzliche Zunahme der Anzahl und Dauer der ausgeführten Abfrage bewirkt.

Führen Sie die folgende Abfrage aus, um die zehn Abfragen aufzulisten, die am häufigsten für Ihre Instanz von Azure Database for MySQL – Flexibler Server ausgeführt werden:

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;

Hinweis

Verwenden Sie diese Abfrage, um eine Benchmark für die am häufigsten ausgeführten Abfragen auf Ihrem Datenbankserver zu erstellen und festzustellen, ob eine Änderung bei den am häufigsten ausgeführten Abfragen aufgetreten ist oder ob die Ausführungsdauer von vorhandene Abfragen in der anfänglichen Benchmark größer geworden ist.

Auflisten der 10 teuersten Abfragen nach Gesamtausführungszeit

Die Ausgabe aus der folgenden Abfrage enthält Informationen zu den 10 wichtigsten Abfragen, die für den Datenbankserver ausgeführt werden, und die Anzahl ihrer Ausführungen auf dem Datenbankserver. Außerdem werden weitere nützliche Informationen bereitgestellt, z. B. die Abfragelatenz, die Sperrzeiten, die Anzahl der temporären Tabellen, die als Teil der Abfragelaufzeit erstellt wurden usw. Verwenden Sie diese Abfrageausgabe, um die wichtigsten Abfragen in der Datenbank nachzuverfolgen und Änderungen an Faktoren wie Latenzen zu berücksichtigen, welche darauf hindeuten, die Abfrage möglicherweise weiter zu optimieren wäre, um zukünftige Risiken zu vermeiden.

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;

Überwachen der automatische Speicherbereinigung von InnoDB

Wenn die automatische Speicherbereinigung von InnoDB blockiert oder verzögert wird, kann es in der Datenbank zu erheblichen Löschverzögerungen kommen, die sich negativ auf die Speicherauslastung und die Abfrageleistung auswirken können.

Die Länge der Verlaufsliste (History List Length, HLL) des InnoDB-Rollbacksegments ist ein Maß für die Anzahl der im Rollbackprotokoll gespeicherten Änderungsdatensätze. Ein wachsender HLL-Wert gibt an, dass die Threads für die automatische Speicherbereinigung von InnoDB (Löschthreads) nicht mit der Schreibworkload Schritt hält oder dass die Bereinigung durch eine zeitintensive Abfrage oder Transaktion blockiert wird.

Übermäßige Verzögerungen bei der automatischen Speicherbereinigung können erhebliche, negative Folgen haben:

  • Der InnoDB-Systemtabellenbereich wird größer, sodass das zugrunde liegende Speichervolumen schneller wächst. Bisweilen kann der Systemtabellenbereich durch eine blockierte Bereinigung um mehrere Terabyte anwachsen.
  • Zum Löschen markierte Datensätze werden nicht zeitgerecht entfernt. Dies kann dazu führen, dass InnoDB-Tabellenbereiche größer werden, und verhindert wird, dass das Modul den von diesen Datensätzen belegten Speicher wiederverwenden kann.
  • Durch das Anwachsen der InnoDB-Speicherstrukturen kann die Leistung aller Abfragen beeinträchtigt werden und die CPU-Auslastung steigen.

Daher ist es wichtig, HLL-Werte, Muster und Trends zu überwachen.

Ermitteln von HLL-Werten

Sie können den HLL-Wert ermitteln, indem Sie den Befehl „show engine innodb status“ ausführen. Der Wert wird in der Ausgabe unter der TRANSACTIONS-Überschrift aufgeführt:

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

Sie können den HLL-Wert auch ermitteln, indem Sie die information_schema.innodb_metrics-Tabelle abfragen:

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

Interpretieren von HLL-Werten

Beachten Sie beim Interpretieren von HLL-Werten die in der folgenden Tabelle aufgeführten Richtlinien:

Wert Hinweise
Kleiner als ~10.000 Normale Werte, die angeben, dass die automatische Speicherbereinigung nicht in Rückstand gerät.
Zwischen 10.000 und 1.000.000 Diese Werte weisen auf eine geringfügige Verzögerung bei der automatischen Speicherbereinigung hin. Solche Werte können akzeptabel sein, wenn sie stabil bleiben und nicht steigen.
Größer als ~1.000.000 Diese Werte sollten untersucht werden und können Korrekturmaßnahmen erforderlich machen

Behandeln übermäßig großer HLL-Werte

Wenn der HLL-Wert große Spitzen oder periodische Zunahmen aufweist, sollten Sie sofort die Abfragen und Transaktionen untersuchen, die in Ihrer Instanz von Azure Database for MySQL – Flexibler Server ausgeführt werden. Anschließend können Sie Workloadprobleme beheben, die möglicherweise die Durchführung der automatischen Speicherbereinigung verhindern. Obwohl nicht erwartet werden kann, dass die Datenbank frei von Löschverzögerungen ist, darf die Verzögerung nicht unkontrolliert anwachsen.

Führen Sie z. B. die folgenden Befehle aus, um Transaktionsinformationen aus der information_schema.innodb_trx-Tabelle abzurufen:

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

Die Details in der trx_started-Spalte helfen Ihnen bei der Berechnung des Transaktionsalters.

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

Informationen zu aktuellen Datenbanksitzungen, einschließlich des Zeitraum im aktuellen Sitzungszustands, finden Sie in der information_schema.processlist-Tabelle. Die folgende Ausgabe zeigt z. B. eine Sitzung, die für die letzten 1462 Sekunden aktiv eine Abfrage ausgeführt hat:

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

Empfehlungen

  • Stellen Sie sicher, dass Ihre Datenbank über genügend Ressourcen verfügt, um Ihre Abfragen auszuführen. Manchmal müssen Sie die Instanzgröße hochskalieren, um mehr CPU-Kerne und zusätzlichen Arbeitsspeicher für Ihre Workload zu erhalten.

  • Vermeiden Sie umfangreiche oder lange Transaktionen, indem Sie sie in kleinere Transaktionen aufteilen.

  • Konfigurieren Sie innodb_purge_threads Ihrer Workload entsprechend, um die Effizienz für Löschvorgänge im Hintergrund zu verbessern.

    Hinweis

    Testen Sie alle Änderungen an dieser Servervariable für jede Umgebung, um die Änderung des Modulverhaltens zu messen.

  • Verwenden Sie Benachrichtigungen für die prozentuale Host-CPU-Auslastung, die prozentuale Arbeitsspeicherauslastung und die Gesamtanzahl der Verbindungen, sodass Sie benachrichtigt werden, wenn das System einen der angegebenen Schwellenwerte überschreitet.

  • Verwenden Sie Query Performance Insights oder Azure Workbooks, um problematische oder langsam ausgeführte Abfragen zu ermitteln und dann zu optimieren.

  • Erfassen Sie für Datenbankserver in der Produktion in regelmäßigen Abständen Diagnosedaten, um sicherzustellen, dass alles reibungslos ausgeführt wird. Beheben Sie andernfalls die festgestellten Probleme.

Nächste Schritte

Um Antworten anderer Benutzer auf Ihre wichtigsten Fragen zu erhalten oder eine Frage zu veröffentlichen oder zu beantworten, besuchen Sie Stack Overflow.