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.

Optimierung der Speicherparameter für Aurora Postgre SQL Postgre SQL

In Amazon Aurora Postgre SQL können Sie mehrere Parameter verwenden, die den Speicherverbrauch für verschiedene Verarbeitungsaufgaben steuern. Wenn eine Aufgabe mehr Speicher beansprucht als für einen bestimmten Parameter festgelegt, SQL verwendet Aurora Postgre andere Ressourcen für die Verarbeitung, z. B. durch Schreiben auf die Festplatte. Dies kann dazu führen, dass Ihr Aurora SQL Postgre-DB-Cluster langsamer wird oder möglicherweise angehalten wird und ein out-of-memory Fehler angezeigt wird.

Die Standardeinstellung für jeden Speicherparameter kann normalerweise die beabsichtigten Verarbeitungsaufgaben verarbeiten. Sie können Ihre Aurora SQL Postgre-DB-Cluster jedoch auch auf die speicherbezogenen Parameter der RDS abstimmen. Sie führen diese Optimierung durch, um sicherzustellen, dass genügend Speicher für die Verarbeitung Ihrer spezifischen Workload zugewiesen ist.

Nachstehend finden Sie Informationen über Parameter, die die Speicherverwaltung steuern. Sie können auch lernen, wie Sie die Speicherauslastung bewerten.

Überprüfen und Einstellen von Parameterwerten

Zu den Parametern, die Sie zur Speicherverwaltung und zur Bewertung der Speichernutzung Ihres Aurora SQL Postgre-DB-Clusters festlegen können, gehören die folgenden:

Dies sind alles dynamische Parameter, sodass Sie sie für die aktuelle Sitzung ändern können. Stellen Sie dazu mit psql und mithilfe der folgenden SET Anweisung eine Verbindung RDS zum Aurora SQL SQL Postgre-DB-Cluster her.

SET parameter_name TO parameter_value;

Sitzungseinstellungen sind nur für die Dauer der Sitzung gültig. Wenn die Sitzung endet, kehrt der Parameter auf seine Einstellung in der DB-Cluster-Parametergruppe zurück. . Bevor Sie Parameter ändern, überprüfen Sie zunächst die aktuellen Werte, indem Sie die pg_settings-Tabelle wie folgt abfragen.

SELECT unit, setting, max_val
   FROM pg_settings WHERE name='parameter_name';

Um beispielsweise den Wert des work_mem Parameters zu ermitteln, stellen Sie eine Verbindung zur Writer-Instance des Aurora SQL Postgre-DB-Clusters her und führen Sie die folgende Abfrage aus.

SELECT unit, setting, max_val, pg_size_pretty(max_val::numeric)
  FROM pg_settings WHERE name='work_mem';
unit  | setting  | max_val   | pg_size_pretty
------+----------+-----------+----------------
 kB   | 1024     | 2147483647| 2048 MB
(1 row)

Um Parametereinstellungen so zu ändern, dass sie bestehen bleiben, müssen Sie eine benutzerdefinierte DB-Cluster-Parametergruppe verwenden. . Nachdem Sie Ihren Aurora SQL Postgre-DB-Cluster RDS mit unterschiedlichen Werten für diese Parameter mithilfe der SET Anweisung trainiert haben, können Sie eine benutzerdefinierte Parametergruppe erstellen und diese auf Ihren Aurora Postgre-DB-Cluster anwenden. SQL Weitere Informationen finden Sie unter Parametergruppen für Amazon Aurora.

Den Arbeitsspeicherparameter verstehen

Der Arbeitsspeicherparameter (work_mem) gibt die maximale Speichermenge an, die Aurora Postgre zur Verarbeitung komplexer Abfragen verwenden SQL kann. Zu komplexen Abfragen gehören solche, die Sortier- oder Gruppierungsvorgänge beinhalten, also Abfragen, die die folgenden Klauseln verwenden:

Der Abfrageplaner wirkt sich indirekt darauf aus, wie Ihr Aurora SQL Postgre-DB-Cluster den Arbeitsspeicher verwendet. Der Abfrageplaner generiert Ausführungspläne für die Verarbeitung von SQL Anweisungen. Ein bestimmter Plan kann eine komplexe Abfrage in mehrere Arbeitseinheiten aufteilen, die parallel ausgeführt werden können. Wenn möglich, SQL verwendet Aurora Postgre die im work_mem Parameter angegebene Speichermenge für jede Sitzung, bevor für jeden parallel Prozess auf die Festplatte geschrieben wird.

Mehrere Datenbankbenutzer, die mehrere Operationen gleichzeitig ausführen und mehrere Arbeitseinheiten parallel generieren, können den zugewiesenen Arbeitsspeicher Ihres Aurora SQL Postgre-DB-Clusters erschöpfen. Dies kann zu übermäßiger Erstellung temporärer Dateien und Festplatten-I/O führen, oder schlimmer noch, es kann zu einem out-of-memory Fehler führen.

Identifizieren temporärer Dateiverwendung

Immer, wenn der für die Verarbeitung von Abfragen erforderliche Speicher den im work_mem-Parameter angegebenen Wert übersteigt, werden die Arbeitsdaten in einer temporären Datei auf die Festplatte ausgelagert. Sie können sehen, wie oft dies geschieht, indem Sie den log_temp_files-Parameter aktivieren. Standardmäßig ist dieser Parameter deaktiviert (Einstellung auf -1). Um alle temporären Dateiinformationen zu erfassen, setzen Sie diesen Parameter auf 0. Setzen Sie log_temp_files auf eine andere positive Ganzzahl, um temporäre Dateiinformationen für Dateien zu erfassen, die dieser Datenmenge entsprechen oder größer sind (in Kilobyte). In der folgenden Abbildung sehen Sie ein Beispiel von AWS Management Console.

Nachdem Sie die temporäre Dateiprotokollierung konfiguriert haben, können Sie mit Ihrer eigenen Workload testen, ob Ihre Arbeitsspeichereinstellung ausreichend ist. Sie können einen Workload auch simulieren, indem Sie pgbench verwenden, eine einfache Benchmarking-Anwendung aus der SQL Postgre-Community.

Das folgende Beispiel initialisiert (-i)pgbench, indem es die notwendigen Tabellen und Zeilen für die Ausführung der Tests erstellt. In diesem Beispiel erstellt der Skalierungsfaktor (-s 50) 50 Zeilen in der pgbench_branches-Tabelle, 500 Zeilen in pgbench_tellers und 5.000.000 Zeilen in der pgbench_accounts-Tabelle in der labdbDatenbank.

pgbench -U postgres -h your-cluster-instance-1.111122223333.aws-regionrds.amazonaws.com -p 5432 -i -s 50 labdb
Password:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 15.46 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 61.13 s (drop tables 0.08 s, create tables 0.39 s, client-side generate 54.85 s, vacuum 2.30 s, primary keys 3.51 s)

Nach der Initialisierung der Umgebung können Sie den Benchmark für eine bestimmte Zeit (-T) und die Anzahl der Kunden (-c) ausführen. In diesem Beispiel wird auch die -d Option zur Ausgabe von Debugging-Informationen verwendet, während die Transaktionen vom Aurora SQL Postgre-DB-Cluster verarbeitet werden.

pgbench -h -U postgres your-cluster-instance-1.111122223333.aws-regionrds.amazonaws.com -p 5432 -d -T 60 -c 10 labdb
Password:*******
pgbench (14.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 1
duration: 60 s
number of transactions actually processed: 1408
latency average = 398.467 ms
initial connection time = 4280.846 ms
tps = 25.096201 (without initial connection time)

Weitere Informationen zu pgbench finden Sie unter pgbench in der Postgre-Dokumentation. SQL

Sie können den Befehl „psql metacommand“ (\d) verwenden, um die von „pgbench“ erstellten Relationen wie Tabellen, Ansichten und Indizes aufzulisten.

labdb=>  \d pgbench_accounts
 Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null |
 bid      | integer       |           |          |
 abalance | integer       |           |          |
 filler   | character(84) |           |          |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Wie in der Ausgabe gezeigt, wird die pgbench_accounts-Tabelle nach der aid-Spalte indiziert. Um sicherzustellen, dass diese nächste Abfrage Arbeitsspeicher verwendet, fragen Sie eine beliebige nicht indizierte Spalte ab, z. B. die im folgenden Beispiel gezeigte.

postgres=> SELECT * FROM pgbench_accounts ORDER BY bid;

Überprüfen Sie das Protokoll auf die temporären Dateien. Öffnen Sie dazu die AWS Management Console, wählen Sie die Aurora SQL Postgre-DB-Cluster-Instance und dann die Registerkarte Logs & Events aus. Zeigen Sie die Protokolle in der Konsole an oder laden Sie sie zur weiteren Analyse herunter. Wie in der folgenden Abbildung dargestellt, zeigt die Größe der temporären Dateien, die für die Verarbeitung der Abfrage benötigt werden, an, dass Sie erwägen sollten, die für die work_mem-Parameter angegebene Menge zu erhöhen.

Sie können diesen Parameter für Einzelpersonen und Gruppen unterschiedlich konfigurieren, je nach Ihren betrieblichen Anforderungen. Sie können beispielsweise den work_mem-Parameter auf 8 GB für die Rolle mit dem Namen dev_team festlegen.

postgres=> ALTER ROLE dev_team SET work_mem=‘8GB';

Mit dieser Einstellung für work_mem wird jeder Rolle, die ein Mitglied der dev_team-Rolle ist, bis zu 8 GB Arbeitsspeicher zugewiesen.

Verwenden von Indizes für schnellere Reaktionszeit

Wenn Ihre Abfragen zu lange dauern, bis Ergebnisse zurückgegeben werden, können Sie überprüfen, ob Ihre Indizes erwartungsgemäß verwendet werden. Aktivieren Sie zuerst \timing, das psql-Metakommando, wie folgt.

postgres=>  \timing on

Verwenden Sie nach dem Einschalten des Timings eine einfache SELECT Anweisung.

postgres=> SELECT COUNT(*) FROM
  (SELECT * FROM pgbench_accounts
  ORDER BY bid)
  AS accounts;
count 
-------
5000000
(1 row)
Time: 3119.049 ms (00:03.119)

Wie in der Ausgabe gezeigt, dauerte die Ausführung dieser Abfrage etwas mehr als 3 Sekunden. Um die Reaktionszeit zu verbessern, erstellen Sie einen Index für pgbench_accounts wie folgt.

postgres=> CREATE INDEX ON pgbench_accounts(bid);
CREATE INDEX

Führen Sie die Abfrage erneut aus und beachten Sie die kürzere Antwortzeit. In diesem Beispiel wurde die Abfrage etwa fünfmal schneller abgeschlossen, in etwa einer halben Sekunde.

postgres=>  SELECT COUNT(*) FROM (SELECT * FROM pgbench_accounts ORDER BY bid) AS accounts;
 count 
-------
 5000000
(1 row)
Time: 567.095 ms

Arbeitsspeicher für logische Dekodierung anpassen

Logische Replikation ist SQL seit ihrer Einführung in Postgre Version 10 in allen Versionen von Aurora Postgre SQL verfügbar. Wenn Sie die logische Replikation konfigurieren, können Sie auch die logical_decoding_work_mem-Parameter einstellen, um die Speichermenge anzugeben, die der logische Dekodierungsprozess für den Dekodierungs- und Streaming-Prozess verwenden kann.

Während der logischen Dekodierung werden Write-Ahead-Log (WAL) -Datensätze in SQL Anweisungen umgewandelt, die dann zur logischen Replikation oder für eine andere Aufgabe an ein anderes Ziel gesendet werden. Wenn eine Transaktion in das geschrieben WAL und dann konvertiert wird, muss die gesamte Transaktion in den für angegebenen Wert passen. logical_decoding_work_mem Standardmäßig ist dieser Parameter auf 65 536 KB eingestellt. Jeder Überlauf wird auf den Datenträger geschrieben. Dies bedeutet, dass es erneut von der Festplatte gelesen werden muss, bevor es an sein Ziel gesendet werden kann, wodurch der Gesamtprozess verlangsamt wird.

Sie können den Betrag des Transaktionsüberlaufs in Ihre aktuelle Workload zu einem bestimmten Zeitpunkt bewerten, indem Sie die aurora_stat_file-Funktion wie im folgenden Beispiel gezeigt, verwenden.

SELECT split_part (filename, '/', 2)
   AS slot_name, count(1) AS num_spill_files,
   sum(used_bytes) AS slot_total_bytes,
   pg_size_pretty(sum(used_bytes)) AS slot_total_size
   FROM aurora_stat_file()
   WHERE filename like '%spill%'
   GROUP BY 1;
  slot_name | num_spill_files | slot_total_bytes | slot_total_size
------------+-----------------+------------------+-----------------
 slot_name  |       590       |      411600000   | 393 MB
(1 row)

Diese Abfrage gibt die Anzahl und Größe der Spill-Dateien auf Ihrem Aurora SQL Postgre-DB-Cluster zurück, wenn die Abfrage aufgerufen wird. Bei länger laufenden Workloads befinden sich möglicherweise noch keine Spill-Dateien auf der Festplatte. Um ein Profil lang andauernder Workloads zu erstellen, empfehlen wir Ihnen, eine Tabelle zu erstellen, in der die Informationen der Spill-Datei während der Ausführung der Workload erfasst werden. Sie können die Tabelle wie folgt erstellen.

CREATE TABLE spill_file_tracking AS
    SELECT now() AS spill_time,*
    FROM aurora_stat_file()
    WHERE filename LIKE '%spill%';

Um zu sehen, wie Spill-Dateien während der logischen Replikation verwendet werden, richten Sie einen Publisher und einen Abonnenten ein und starten Sie dann eine einfache Replikation. Weitere Informationen finden Sie unter Logische Replikation für Ihren Aurora SQL Postgre-DB-Cluster einrichten. Während der Replikation können Sie einen Auftrag erstellen, der die Ergebnismenge aus der aurora_stat_file()-spill-File-Funktion wie folgt erfasst.

INSERT INTO spill_file_tracking
  SELECT now(),*
  FROM aurora_stat_file()
  WHERE filename LIKE '%spill%';

Verwenden Sie den folgenden psql-Befehl, um den Job einmal pro Sekunde auszuführen.

\watch 0.5

Stellen Sie während der Ausführung des Auftrags eine Verbindung zur Writer-Instance von einer anderen psql-Sitzung her. Verwenden Sie die folgende Reihe von Anweisungen, um eine Arbeitslast auszuführen, die die Speicherkonfiguration überschreitet und Aurora Postgre veranlasst, eine Spill-Datei SQL zu erstellen.

labdb=> CREATE TABLE my_table (a int PRIMARY KEY, b int);
CREATE TABLE
labdb=> INSERT INTO my_table SELECT x,x FROM generate_series(0,10000000) x;
INSERT 0 10000001
labdb=> UPDATE my_table SET b=b+1;
UPDATE 10000001

Diese Anweisungen dauern einige Minuten. Wenn Sie fertig sind, drücken Sie die Strg-Taste und die C-Taste gleichzeitig, um die Überwachungsfunktion zu beenden. Verwenden Sie dann den folgenden Befehl, um eine Tabelle zu erstellen, die die Informationen über die Verwendung der Spill-Datei durch den Aurora SQL Postgre-DB-Cluster enthält.

SELECT spill_time, split_part (filename, '/', 2)
    AS slot_name, count(1)
    AS spills, sum(used_bytes)
    AS slot_total_bytes, pg_size_pretty(sum(used_bytes))
    AS slot_total_size FROM spill_file_tracking
  GROUP BY 1,2 ORDER BY 1;
                   spill_time | slot_name             | spills | slot_total_bytes | slot_total_size
------------------------------+-----------------------+--------+------------------+-----------------
2022-04-15 13:42:52.528272+00 | replication_slot_name | 1      | 142352280        | 136 MB
2022-04-15 14:11:33.962216+00 | replication_slot_name | 4      | 467637996        | 446 MB
2022-04-15 14:12:00.997636+00 | replication_slot_name | 4      | 569409176        | 543 MB
2022-04-15 14:12:03.030245+00 | replication_slot_name | 4      | 569409176        | 543 MB
2022-04-15 14:12:05.059761+00 | replication_slot_name | 5      | 618410996        | 590 MB
2022-04-15 14:12:07.22905+00  | replication_slot_name | 5      | 640585316        | 611 MB
(6 rows)

Die Ausgabe zeigt, dass beim Ausführen des Beispiels fünf Spill-Dateien erstellt wurden, die 611 MB Speicher verbrauchten. Um zu vermeiden, dass auf den Datenträger geschrieben wird, empfehlen wir, den logical_decoding_work_mem-Parameter auf die nächsthöhere Speichergröße, 1024, einzustellen.