Best Practices für die Verwaltung der Arbeitsspeichernutzung

Durch das Konfigurieren und Überwachen der Arbeitsspeichernutzung für eine Cloud SQL-Instanz können Sie Leistungsengpässe, Probleme mit unzureichendem Arbeitsspeicher und Datenbankausfälle vermeiden. Wenn Sie eine Instanz erstellen, wählen Sie eine anfängliche Arbeitsspeichergröße aus. Sie können speicherbezogene Datenbank-Flags anpassen, um die Instanz fehlerfrei zu halten, wenn Ihre PostgreSQL-Arbeitslast wächst.

PostgreSQL unterteilt Speicherkomponenten in globalen Speicher, der von allen Prozessen gemeinsam verwendet wird, um Abfragen auszuführen (z. B. shared_buffers und max_connections), und lokalen Speicher, der jeder Verbindung zugewiesen ist (z. B. work_mem, maintenance_work_mem und temp_buffers). Weitere Konfigurationsaspekte finden Sie unter Allgemeine Best Practices und Betriebsrichtlinien.

Arbeitsspeichernutzung und Flags

Bei einer hohen Arbeitsspeichernutzung durch Cloud SQL-Instanzen können folgende Fragen auftreten:

  • Welche Abfrage oder welcher Prozess verwendet einen hohen Arbeitsspeicher?
  • Sind die Speichereinstellungen für die Datenbankaktivität geeignet?
  • Wie können die Speichereinstellungen geändert werden?

Bei der Ausführung einer PostgreSQL-Datenbank wird der Großteil der Speichernutzung in einigen wenigen Bereichen genutzt:

  • Gemeinsamer Zwischenspeicher: Dies ist der gemeinsame Arbeitsspeicher, den PostgreSQL für Tabellendaten für read- und write-Vorgänge zuweist. Beim read-Vorgang werden alle vom Laufwerk angeforderten Daten zuerst in den RAM abgerufen und dann an den Client weitergegeben. In ähnlicher Weise werden die Daten in PostgreSQL, wenn sie angefordert werden (z. B. SELECT * from emp), zuerst vom Laufwerk in die shared_buffers zum Zwischenspeichern geholt und dann an den Client übergeben. Das Gleiche passiert mit dem Vorgang write.

    Der gemeinsame Zwischenspeicher ist auch der gemeinsame Arbeitsspeicherbereich für alle Prozesse und Verbindungen für Datenbankaktivitäten wie Daten-Caching, Verbindungs-Caching und DML-Vorgänge (Data Manipulation Language, Datenbearbeitungssprache). Der maximale Wert, der in diesem Bereich zugewiesen werden kann, wird durch das Flag shared_buffers angegeben. Der Standardwert ist 33 % des Arbeitsspeichers der Instanz. Wenn der Wert von shared_buffers hoch ist, ist auch die Größe der im Speicher gespeicherten Daten hoch.

  • Arbeitsspeicher für Abfrage: Während eine Abfrage ausgeführt wird, weist PostgreSQL jedem Vorgang (Sortieren, Hashing usw.) lokalen Arbeitsspeicher zu. Das Maximum, das es jedem Vorgang einer Abfrage zuweisen kann, bevor in temporäre Laufwerksdateien geschrieben wird, wird durch das Flag work_mem konfiguriert. Der Standardwert ist 4 MB. Wenn der Wert von work_mem hoch ist, ist auch die Datenmenge, die im Speicher sortiert werden kann, hoch.
  • Wartungsarbeitsspeicher: Einige Wartungsvorgänge (VACUUM, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY usw.) erfordern einen separaten lokalen Speicher, den PostgreSQL zuweist. Der maximale Betrag für den Backend-Prozess, der von diesen Vorgängen verwendet wird, kann mit dem Flag maintenance_work_mem konfiguriert werden. Der Standardwert ist 64 MB. Beachten Sie, dass Autovacuum-Worker auch Wartungsarbeitsspeicher verwenden und der Höchstwert durch das Flag autovacuum_work_mem überschrieben werden kann. Wenn der Wert von maintenance_work_mem hoch ist, ist die Leistungsgeschwindigkeit des VACUUM-Vorgangs hoch.
  • Temporäre Zwischenspeicher: Wenn eine temporäre Tabelle in einer Datenbanksitzung verwendet wird, weist PostgreSQL temporäre Zwischenspeicher zu, um die sitzungslokale temporäre Tabelle zu speichern. Der Höchstwert kann mit dem Flag temp_buffers angegeben werden und der Standardwert ist 8 MB.
  • Datenbankverbindung: Wenn ein Client eine Verbindung zur Datenbank herstellt, erstellt PostgreSQL einen Backend-Prozess, um die Client-Sitzung zu bedienen. Neben dem Arbeitsspeicher für die Ausführung der Abfrage weist PostgreSQL zusätzlichen Arbeitsspeicher zu, um Informationen wie den Systemkatalog-Cache und vorbereitete Abfragepläne zu verwalten. Die maximale Anzahl gleichzeitig zulässiger Verbindungen zum Datenbankserver kann über das Flag max_connections konfiguriert werden. Jede inaktive Verbindung benötigt etwa 2 MB bis 3 MB gemeinsamen Speicher. Wenn der Wert von max_connections hoch ist, kann die Instanz mehr Verbindungen herstellen, allerdings auf Kosten des Arbeitsspeichers.

Eine vollständige Liste der Speicherkomponenten in PostgreSQL finden Sie in der PostgreSQL-Dokumentation. Informationen zum Ändern oder Bearbeiten der in diesem Abschnitt aufgeführten Flags finden Sie unter Datenbank-Flags konfigurieren.

Arbeitsspeichernutzung überwachen

Beobachten Sie den Speicher der Instanz regelmäßig in Cloud Monitoring und halten Sie ihn unter dem Arbeitsspeicherlimit. Als Best Practice wird empfohlen, in Cloud Monitoring eine Benachrichtigung einzurichten, die gesendet wird, wenn die Nutzung 6 Stunden lang 90 % des Limits überschreitet. Diese Benachrichtigung kann Sie warnen, wenn Ihre Speichernutzung ständig das Limit erreicht.

Überwachen Sie außerdem Vorfälle mit unzureichendem Arbeitsspeicher. Richten Sie dazu einen logbasierten Messwert für die Nachricht server process .* was terminated by signal 9: Killed in Cloud Monitoring ein, um die Ereignisse für fehlenden Speicher zu erfassen und jedes Mal eine Benachrichtigung zu senden, wenn solch ein Ereignis stattfindet.

Wenn Ihre Instanz ständig über das Limit von 90 % des Arbeitsspeichers geht oder ein Ereignis aufgrund fehlenden Speichers auftritt, können Sie den Arbeitsspeicher der Instanz erhöhen. Alternativ können Sie die Arbeitsspeichernutzung reduzieren, indem Sie die Anzahl der Datenbankverbindungen begrenzen oder Datenbank-Flags wie shared_buffers, work_mem oder max_connections verringern. Wenn Sie diese Flags verringern, kann die Leistung Ihrer Instanz eingeschränkt werden.

Nicht genügend Arbeitsspeicher

Wenn nicht genügend Arbeitsspeicher für die Datenbankarbeitslast vorhanden ist, verwendet das zugrunde liegende Linux-Betriebssystem als letzte Möglichkeit den out-of-memory (OOM) killer, um einen Prozess zu beenden und Arbeitsspeicher freizugeben. Cloud SQL ist so konfiguriert, dass die OOM killer nur auf die PostgreSQL-Worker-Prozesse abzielt. Der Postmaster-Prozess bleibt in dieser Situation erhalten, sodass nur alle bestehenden Datenbankverbindungen beendet und eine Wiederherstellung ausgeführt werden müssen, um die Integrität der Datenbank zu schützen. In diesem Fall kommt es zu Dienststörungen und zu Ausfallzeiten der Datenbank. Im PostgreSQL-Datenbanklog werden Meldungen wie die folgenden angezeigt:

2021-10-24 23:34:22.265 UTC [7]: [663-1] db=,user= LOG: server process (PID 1255039) was terminated by signal 9: Killed
2021-10-24 23:34:22.265 UTC [7]: [664-1] db=,user= DETAIL: Failed process was running: SELECT * FROM tab ORDER BY col
2021-10-24 23:34:22.277 UTC [7]: [665-1] db=,user= LOG: terminating any other active server processes
2021-10-24 23:34:22.278 UTC [1255458]: [1-1] db=postgres,user=postgres WARNING: terminating connection because of crash of another server process
2021-10-24 23:34:22.278 UTC [1255458]: [2-1] db=postgres,user=postgres DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-10-24 23:34:22.278 UTC [1255458]: [3-1] db=postgres,user=postgres HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-10-24 23:34:22.278 UTC [1255458]: [4-1] db=postgres,user=postgres CONTEXT: while updating tuple (27,18) in relation "tab"
...
2021-10-24 23:34:22.558 UTC [1255477]: [1-1] db=postgres,user=postgres FATAL: the database system is in recovery mode
...
2021-10-24 23:34:25.579 UTC [7]: [666-1] db=,user= LOG: all server processes terminated; reinitializing
...
2021-10-24 23:34:25.691 UTC [1255482]: [1-1] db=,user= LOG: database system was interrupted; last known up at 2021-10-24 23:31:53 UTC
2021-10-24 23:34:25.776 UTC [1255482]: [2-1] db=,user= LOG: database system was not properly shut down; automatic recovery in progress
2021-10-24 23:34:25.789 UTC [1255482]: [3-1] db=,user= LOG: redo starts at 227/AB359400
2021-10-24 23:34:38.957 UTC [1255482]: [4-1] db=,user= LOG: redo done at 229/4621F508
2021-10-24 23:34:38.959 UTC [1255482]: [5-1] db=,user= LOG: last completed transaction was at log time 2021-10-24 23:34:18.5535+00
2021-10-24 23:34:39.290 UTC [7]: [667-1] db=,user= LOG: database system is ready to accept connections

Abgebrochene Abfragen aufgrund hoher Speichernutzung

Wenn eine Cloud SQL-Datenbankabfrage viel Arbeitsspeicher beansprucht, erkennt Cloud SQL proaktiv die hohe Arbeitsspeichernutzung und beendet die Verbindung, die diese Abfrage ausführt, um einen OOM-Fehler (Out of Memory) zu verhindern. Dadurch werden Datenbankausfälle aufgrund der hohen Speichernutzung verhindert.

Sie können eine Abfrage, die aufgrund einer hohen Speichernutzung abgebrochen wurde, anhand des Logeintrags für eine Abfrage im PostgreSQL-Datenbanklog identifizieren. Im Log einer abgebrochenen Abfrage wird beispielsweise die folgende Meldung angezeigt:

(...timestamp….) [PID]:  db=postgres,user=$user_id LOG: Process [PID] received SIGTERM
(...timestamp….) [PID]:  db=postgres,user=$user_id DETAIL: Cloud SQL cancelled this query owing to its excessive memory usage which can cause the PostgreSQL instance to crash.
(...timestamp….) [PID]:  db=postgres,user=$user_id HINT:  To avoid future interruptions, consider increasing instance memory or optimizing this query.

Wenn Ihre Datenbank Query Insights verwendet, können Sie Abfragedetails für eine bestimmte Abfrage aufrufen, um zusätzliche Informationen zur abgebrochenen Abfrage zu erhalten. Anhand dieser Logs können Sie die abgebrochene Abfrage identifizieren und die hohe Speichernutzung für Ihre Datenbank optimieren, indem Sie die zugrunde liegenden Ursachen für die abgebrochene Abfrage ermitteln und beheben.

Nächste Schritte