io:BuffileRead und io:BuffileWrite - Amazon Aurora
Unterstützte Engine-VersionenContextWahrscheinliche Ursachen für erhöhte WartezeitenAktionen

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

io:BuffileRead und io:BuffileWrite

Die Ereignisse IO:BufFileRead und IO:BufFileWrite treten auf, wenn Aurora PostgreSQL temporäre Dateien erstellt. Wenn Vorgänge mehr Arbeitsspeicher benötigen, als die derzeit definierten Arbeitsspeicherparameter definieren, schreiben sie temporäre Daten in persistenten Speicher. Dieser Vorgang wird manchmal als „Verschütten auf die Festplatte“ bezeichnet.

Unterstützte Engine-Versionen

Diese Warteereignisinformationen werden für alle Versionen von Aurora PostgreSQL unterstützt.

Context

IO:BufFileRead und IO:BufFileWrite beziehen sich auf den Arbeitsspeicherbereich und den Wartungsarbeitsspeicherbereich. Weitere Informationen zu diesen lokalen Speicherbereichen finden Sie unter Arbeitsspeicherbereich und Wartungs-Arbeitsspeicherbereich.

Der Standardwert für work_mem ist 4 MB. Wenn eine Sitzung parallel Vorgänge ausführt, verwendet jeder Worker, der die Parallelität bearbeitet, 4 MB Speicher. Stellen Sie work_mem daher sorgfältig ein. Wenn Sie den Wert zu stark erhöhen, verbraucht eine Datenbank mit vielen Sitzungen möglicherweise zu viel Speicher. Wenn Sie den Wert zu niedrig festlegen, erstellt Aurora PostgreSQL temporäre Dateien im lokalen Speicher. Die Festplatten-I/O für diese temporären Dateien kann die Leistung verringern.

Wenn Sie die folgende Ereignisfolge beobachten, generiert Ihre Datenbank möglicherweise temporäre Dateien:

  1. Plötzlicher und starker Rückgang der Verfügbarkeit

  2. Schnelle Erholung für den freien Speicherplatz

Möglicherweise sehen Sie auch ein „Kettensäge“ -Muster. Dieses Muster kann darauf hinweisen, dass Ihre Datenbank ständig kleine Dateien erstellt.

Wahrscheinliche Ursachen für erhöhte Wartezeiten

Im Allgemeinen werden diese Warteereignisse durch Vorgänge verursacht, die mehr Speicher verbrauchen, als die Parameter work_mem oder maintenance_work_mem zuweisen. Um dies zu kompensieren, schreiben die Vorgänge in temporäre Dateien. Häufige Ursachen für die IO:BufFileRead- und IO:BufFileWrite-Ereignisse sind die folgenden:

Abfragen, die mehr Speicher benötigen als im Arbeitsspeicherbereich vorhanden

Abfragen mit den folgenden Merkmalen verwenden den Arbeitsspeicherbereich:

  • Hash-Verknüpfungen

  • ORDER BY-Klausel

  • GROUP BY-Klausel

  • DISTINCT

  • Fensterfunktionen

  • CREATE TABLE AS SELECT

  • Aktualisierung der materialisierten Ansicht

Anweisungen, die mehr Speicher benötigen als im Arbeitsspeicherbereich für Wartungsarbeiten vorhanden

Die folgenden Anweisungen verwenden den Arbeitsspeicherbereich für Wartungsarbeiten:

  • CREATE INDEX

  • CLUSTER

Aktionen

Abhängig von den Ursachen Ihres Wait-Ereignisses empfehlen wir verschiedene Aktionen.

Identifizieren Sie das Problem

Nehmen Sie an, dass Performance Insights nicht aktiviert ist und Sie vermuten, dass IO:BufFileRead und IO:BufFileWrite häufiger als normal auftreten. Gehen Sie wie folgt vor:

  1. Untersuchen Sie die Metrik FreeLocalStorage in Amazon CloudWatch.

  2. Suchen Sie nach einem Kettensägenmuster, bei dem es sich um eine Reihe von gezackten Stacheln handelt.

Ein Kettensägenmuster weist auf einen schnellen Verbrauch und die Freigabe von Speicher hin, die häufig mit temporären Dateien verbunden sind. Wenn Sie dieses Muster bemerken, aktivieren Sie Performance Insights. Wenn Sie Performance Insights verwenden, können Sie feststellen, wann die Wait-Ereignisse auftreten und welche Abfragen mit ihnen verknüpft sind. Ihre Lösung hängt von der spezifischen Abfrage ab, die die Ereignisse verursacht.

Oder stellen Sie den Parameter log_temp_files ein. Dieser Parameter protokolliert alle Abfragen, die mehr als Schwellenwert für temporäre Dateien erzeugen. Wenn der Wert 0 ist, protokolliert Aurora PostgreSQL alle temporären Dateien. Wenn der Wert 1024 ist, protokolliert Aurora PostgreSQL alle Abfragen, die temporäre Dateien erzeugen, die größer als 1 MB sind. Weitere Informationen zu log_temp_files finden Sie unter Fehlerberichte und -protokollierung in der PostgreSQL-Dokumentation.

Untersuchen Sie Ihre Join-Anfragen

Ihre Anwendung verwendet wahrscheinlich Joins. Die folgende Abfrage verbindet beispielsweise vier Tabellen.

SELECT * 
       FROM order 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Eine mögliche Ursache für Spitzen bei der Verwendung temporärer Dateien ist ein Problem in der Abfrage selbst. Beispielsweise filtert eine defekte Klausel die Joins möglicherweise nicht richtig. Betrachten Sie den zweiten inneren Join im folgenden Beispiel.

SELECT * 
       FROM order
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Die obige Abfrage verknüpft fälschlicherweise customer.id mit customer.id, wodurch zwischen jedem Kunden und jeder Bestellung ein kartesisches Produkt generiert wird. Diese Art des versehentlichen Joins generiert große temporäre Dateien. Abhängig von der Größe der Tabellen kann eine kartesische Abfrage sogar Speicher füllen. Wenn die folgenden Bedingungen erfüllt sind, hat Ihre Anwendung möglicherweise kartesische Joins:

  • Sie sehen einen großen, starken Rückgang der Speicherverfügbarkeit, gefolgt von einer schnellen Wiederherstellung.

  • Es werden keine Indizes erstellt.

  • Es werden keine CREATE TABLE FROM SELECT-Anweisungen ausgegeben.

  • Es werden keine materialisierten Ansichten aktualisiert.

Um festzustellen, ob die Tabellen mit den richtigen Schlüsseln verbunden werden, überprüfen Sie Ihre Abfrage- und objektrelationalen Mapping-Anweisungen. Denken Sie daran, dass bestimmte Abfragen Ihrer Anwendung nicht ständig aufgerufen werden und einige Abfragen dynamisch generiert werden.

Überprüfen Sie Ihre ORDER BY- und GROUP BY Anfragen

In einigen Fällen kann eine ORDER BY-Klausel zu übermäßig vielen temporären Dateien führen. Berücksichtigen Sie die folgenden Hinweise:

  • Schließen Sie Spalten nur dann in eine ORDER BY-Klausel ein, wenn sie sortiert werden müssen. Diese Richtlinie ist besonders wichtig für Abfragen, die Tausende von Zeilen zurückgeben und viele Spalten in der ORDER BY-Klausel angeben.

  • Ziehen Sie in Betracht, Indizes zu erstellen, um ORDER BY-Klauseln zu beschleunigen, wenn sie mit Spalten übereinstimmen, die dieselbe aufsteigende oder absteigende Reihenfolge aufweisen. Partielle Indizes sind vorzuziehen, da sie kleiner sind. Kleinere Indizes werden schneller gelesen und durchquert.

  • Wenn Sie Indizes für Spalten erstellen, die Nullwerte akzeptieren können, überlegen Sie, ob die Nullwerte am Ende oder am Anfang der Indizes gespeichert werden sollen.

    Reduzieren Sie nach Möglichkeit die Anzahl der Zeilen, die sortiert werden müssen, indem Sie die Ergebnismenge filtern. Wenn Sie WITH-Klausel-Anweisungen oder Unterabfragen verwenden, denken Sie daran, dass eine innere Abfrage eine Ergebnismenge generiert und an die äußere Abfrage übergibt. Je mehr Zeilen eine Abfrage herausfiltern kann, desto weniger muss die Abfrage erledigen.

  • Wenn Sie nicht die vollständige Ergebnismenge abrufen müssen, verwenden Sie die LIMIT-Klausel. Wenn Sie beispielsweise nur die obersten fünf Zeilen benötigen, generiert eine Abfrage mit der LIMIT-Klausel keine Ergebnisse. Auf diese Weise benötigt die Abfrage weniger Speicher und temporäre Dateien.

Eine Abfrage, die eine GROUP BY-Klausel verwendet, kann auch temporäre Dateien erfordern. GROUP BY-Abfragen fassen Werte mithilfe von Funktionen wie den folgenden zusammen:

  • COUNT

  • AVG

  • MIN

  • MAX

  • SUM

  • STDDEV

Befolgen Sie zum Optimieren von GROUP BY-Abfragen die Empfehlungen für ORDER BY-Abfragen.

Verwenden Sie den DISTINCT-Vorgang nicht

Vermeiden Sie nach Möglichkeit die Verwendung des DISTINCT-Vorgangs, um doppelte Zeilen zu entfernen. Je mehr unnötige und doppelte Zeilen Ihre Abfrage zurückgibt, desto teurer wird der DISTINCT-Vorgang. Fügen Sie nach Möglichkeit Filter in der WHERE-Klausel hinzu, auch wenn Sie dieselben Filter für verschiedene Tabellen verwenden. Das Filtern der Abfrage und der korrekte Beitritt verbessern Ihre Leistung und reduziert den Ressourcennutzung. Es verhindert auch falsche Berichte und Ergebnisse.

Wenn Sie DISTINCT für mehrere Zeilen derselben Tabelle verwenden müssen, sollten Sie einen zusammengesetzten Index erstellen. Das Gruppieren mehrerer Spalten in einem Index kann die Zeit zum Auswerten verschiedener Zeilen verbessern. Wenn Sie Amazon-Aurora-PostgreSQL-Version 10 oder höher verwenden, können Sie außerdem mithilfe des CREATE STATISTICS-Befehls Statistiken zwischen mehreren Spalten korrelieren.

Erwägen Sie, Fensterfunktionen anstelle von GROUP-BY-Funktionen zu verwenden

Mit GROUP BY ändern Sie die Ergebnismenge und rufen dann das aggregierte Ergebnis ab. Mithilfe von Fensterfunktionen aggregieren Sie Daten, ohne die Ergebnismenge zu ändern. Eine Fensterfunktion verwendet die OVER-Klausel, um Berechnungen über die von der Abfrage definierten Mengen durchzuführen und eine Zeile mit einer anderen zu korrelieren. Sie können alle GROUP BY-Funktionen in Fensterfunktionen verwenden, aber auch Funktionen wie die folgenden verwenden:

  • RANK

  • ARRAY_AGG

  • ROW_NUMBER

  • LAG

  • LEAD

Um die Anzahl der temporären Dateien zu minimieren, die von einer Fensterfunktion generiert werden, entfernen Sie Duplikationen für dieselbe Ergebnismenge, wenn Sie zwei verschiedene Aggregationen benötigen. Betrachten Sie folgende Abfrage.

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

Sie können die Abfrage mit der WINDOW-Klausel wie folgt umschreiben.

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Standardmäßig konsolidiert der Ausführungsplaner von Aurora PostgreSQL ähnliche Knoten, sodass keine Vorgänge dupliziert werden. Durch die Verwendung einer expliziten Deklaration für den Fensterblock können Sie die Abfrage jedoch einfacher pflegen. Sie können die Leistung auch verbessern, indem Sie Doppelarbeit verhindern.

Untersuchen Sie materialisierte Ansichten und CTAS-Aussagen

Wenn eine materialisierte Ansicht aktualisiert wird, wird eine Abfrage ausgeführt. Diese Abfrage kann einen Vorgang wie GROUP BY, ORDER BY oder DISTINCT enthalten. Während einer Aktualisierung können Sie eine große Anzahl temporärer Dateien und die Warteereignisse IO:BufFileWrite und IO:BufFileRead beobachten. Wenn Sie eine Tabelle basierend auf einer SELECT-Anweisung erstellen, führt die CREATE TABLE-Anweisung eine Abfrage aus. Um die benötigten temporären Dateien zu reduzieren, optimieren Sie die Abfrage.

Verwenden Sie pg_repack, wenn Sie Indizes erstellen

Wenn Sie einen Index erstellen, ordnet die Engine die Ergebnismenge an. Wenn Tabellen größer werden und die Werte in der indizierten Spalte vielfältiger werden, benötigen die temporären Dateien mehr Speicherplatz. In den meisten Fällen können Sie die Erstellung temporärer Dateien für große Tabellen nicht verhindern, ohne den Speicherbereich für Wartungsarbeiten zu ändern. Weitere Informationen finden Sie unter Wartungs-Arbeitsspeicherbereich.

Eine mögliche Problemumgehung beim Neuerstellen eines großen Index besteht darin, das pg_repack-Tool zu verwenden. Weitere Informationen finden Sie unter Reorganisieren von Tabellen in PostgreSQL-Datenbanken mit minimalen Sperren in der pg_repack-Dokumentation.

Erhöhen Sie maintenance work_mem, wenn Sie Tabellen clustern

Der Befehl CLUSTER gruppiert die durch table_name angegebene Tabelle basierend auf einem vorhandenen Index, der durch index_name angegeben wird. Aurora PostgreSQL erstellt die Tabelle physisch so neu, dass sie der Reihenfolge eines bestimmten Indexes entspricht.

Als magnetische Speicherung vorherrschend war, war Clustering üblich, da der Speicherdurchsatz begrenzt war. Jetzt, da SSD-basierter Speicher üblich ist, ist Clustering weniger beliebt. Wenn Sie jedoch Tabellen clustern, können Sie die Leistung je nach Tabellengröße, Index, Abfrage usw. immer noch geringfügig steigern.

Wenn Sie den Befehl CLUSTER ausführen und die Warteereignisse IO:BufFileWrite und IO:BufFileRead beobachten, stimmen Sie maintenance_work_mem ab. Erhöhen Sie die Speichergröße auf einen ziemlich großen Betrag. Ein hoher Wert bedeutet, dass die Engine mehr Speicher für den Clustering-Vorgang verwenden kann.

Optimieren Sie den Speicher, um io:BuffileRead und io:BuffileWrite zu verhindern

In irgendeiner Situation müssen Sie den Speicher abstimmen. Ihr Ziel ist es, die folgenden Anforderungen auszugleichen:

  • Der work_mem-Wert (siehe Arbeitsspeicherbereich)

  • Der Speicher, der nach dem Diskontieren desshared_buffersWert (siehePuffer Pool)

  • Die maximale Anzahl geöffneter und verwendeter Verbindungen, die durch max_connections begrenzt ist

Erhöhen Sie die Größe des Arbeitsspeicherbereichs

In einigen Situationen besteht Ihre einzige Möglichkeit darin, den von Ihrer Sitzung verwendeten Speicher zu erhöhen. Wenn Ihre Abfragen richtig geschrieben sind und die richtigen Schlüssel für Joins verwenden, sollten Sie den work_mem-Wert erhöhen. Weitere Informationen finden Sie unter Arbeitsspeicherbereich.

Um herauszufinden, wie viele temporäre Dateien eine Abfrage generiert, setzen Sie log_temp_files auf 0. Wenn Sie den work_mem-Wert auf den in den Protokollen angegebenen Höchstwert erhöhen, verhindern Sie, dass die Abfrage temporäre Dateien generiert. work_mem legt jedoch das Maximum pro Planknoten für jede Verbindung oder jeden parallelen Worker fest. Wenn die Datenbank über 5.000 Verbindungen verfügt und jede 256 MiB-Speicher verwendet, benötigt die Engine 1,2 TiB RAM. Daher kann es sein, dass Ihrer Instance der Speicher knapp wird.

Reservieren Sie ausreichend Speicher für den freigegebenen Pufferpool

Ihre Datenbank verwendet Speicherbereiche wie den freigegebenen Pufferpool, nicht nur den Arbeitsspeicherbereich. Berücksichtigen Sie die Anforderungen dieser zusätzlichen Speicherbereiche, bevor Sie work_mem erhöhen. Weitere Informationen zum Pufferpool finden Sie unter Puffer Pool.

Angenommen, Ihre Aurora PostgreSQL-Instance-Klasse ist db.r5.2xlarge. Diese Klasse hat 64 GiB Speicher. Standardmäßig sind 75 Prozent des Arbeitsspeichers für den freigegebenen Pufferpool reserviert. Nachdem Sie den dem Shared Memory-Bereich zugewiesenen Betrag abgezogen haben, bleiben 16.384 MB übrig. Weisen Sie den verbleibenden Speicher nicht ausschließlich dem Arbeitsspeicherbereich zu, da das Betriebssystem und die Engine ebenfalls Speicher benötigen.

Der Speicher, den Sie work_mem zuordnen können, hängt von der Instance-Klasse ab. Wenn Sie eine größere Instance-Klasse verwenden, ist mehr Speicher verfügbar. Im vorhergehenden Beispiel können Sie jedoch nicht mehr als 16 GiB verwenden. Andernfalls ist Ihre Instance nicht verfügbar, wenn ihr der Speicher ausgeht. Um die Instance aus dem nicht verfügbaren Status wiederherzustellen, werden die Aurora PostgreSQL-Automatisierungsdienste automatisch neu gestartet.

Verwalten der Anzahl der Verbindungen

Angenommen, Ihre Datenbank-Instance hat 5.000 gleichzeitige Verbindungen. Jede Verbindung verwendet mindestens 4 MB work_mem. Der hohe Speicherverbrauch der Verbindungen dürfte die Leistung beeinträchtigen. Als Reaktion darauf haben Sie die folgenden Optionen:

  • Aktualisieren Sie auf eine größere Instance-Klasse.

  • Verringern Sie die Anzahl gleichzeitiger Datenbankverbindungen mit einem Verbindungsproxy oder Pooler.

Berücksichtigen Sie bei Proxys Amazon RDS Proxy, PGBouncer oder einen auf Ihrer Anwendung basierenden Verbindungspooler. Diese Lösung lindert die CPU-Last. Es reduziert auch das Risiko, wenn alle Verbindungen den Arbeitsspeicherbereich benötigen. Wenn weniger Datenbankverbindungen vorhanden sind, können Sie den Wert von work_mem erhöhen. Auf diese Weise reduzieren Sie das Auftreten der IO:BufFileRead- und IO:BufFileWrite-Warteereignisse. Auch die Abfragen, die auf den Arbeitsspeicherbereich warten, beschleunigen sich erheblich.