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

Die Abfrageleistung kann von mehreren Faktoren beeinflusst werden. Daher ist es zunächst wichtig, den Umfang der Symptome zu ermitteln, die in Ihrer Instanz von Azure Database for MySQL – 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 durchgeführten Änderungen an der Struktur oder den zugrunde liegenden Daten der von Ihnen 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 den Wert des long_query_time-Parameters konfigurieren, um die Anzahl der Sekunden für die Ausführung von Abfragen anzugeben, bevor die Anfragen als „langsam ausgeführt“ bezeichnet werden. Der Standardwert des Parameters beträgt 10 Sekunden. Sie können jedoch den Wert anpassen, um den Anforderungen des SLA Ihrer Anwendung gerecht zu werden.

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.
• Dies ist möglicherweise nicht hilfreich, wenn Sie auch den log_queries_not_using_index-Parameter aktiviert haben, der angibt, dass Abfragen protokolliert werden sollen, von denen erwartet wird, dass sie alle Zeilen abrufen. Abfragen, die eine vollständige Indexüberprüfung ausführen, nutzen einen Index, würden 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 Zeitraum herunterzuladen, den Sie untersuchen.

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

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 den EXPLAIN-Befehl und die Profilerstellung verwenden, um weitere Details sammeln.

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

EXPLAIN <QUERY>

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>;

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

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:

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.

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 verstehen, 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;

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 GC-Threads (Bereinigungshreads) von InnoDB 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:

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:

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 Bereinigungsverzö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 der im aktuellen Sitzungszustand verbrachten Zeit, finden Sie in der Tabelle information_schema.processlist. 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 „Host-CPU-Prozent“, „Host-Arbeitsspeicherprozent“ und „Verbindungen insgesamt“, 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ächster Schritt