Hohe Arbeitsspeichernutzung in Instanzen optimieren

In diesem Dokument wird erläutert, wie Sie eine hohe Arbeitsspeichernutzung für Cloud SQL-Instanzen ermitteln und gibt Empfehlungen zur Behebung von Arbeitsspeicherproblemen.

Informationen zum Konfigurieren der Arbeits-Speichernutzung für eine Cloud SQL-Instanz finden Sie unter Best Practices für die Verwaltung der Arbeits-Speichernutzung.

Hohe Speichernutzung ermitteln

Verwenden Sie den Metrics Explorer, um die Speichernutzung zu ermitteln

Sie können die Speichernutzung der Instanz mit dem Messwert database/memory/components.usage im Metrics Explorer prüfen.

Query Insights verwenden, um den Plan für Abfragen zu analysieren, die viele Ressourcen verbrauchen

Mit Query Insights können Sie Probleme bei der Abfrageleistung in Cloud SQL-Datenbanken ermitteln, diagnostizieren und verhindern. Query Insights bietet eine Liste lang andauernder Abfragen zusammen mit ihrem Erläuterungsplan (PostgreSQL-Dokumentation). Prüfen Sie den Erläuterungsplan und identifizieren Sie den Teil der Abfrage, der eine Scanmethode mit hoher Speichernutzung enthält. Unabhängig von der Ausführungszeit der Abfrage bietet Query Insights den Erklärungsplan für alle Abfragen. Ermitteln Sie komplexe Abfragen, die mehr Zeit in Anspruch nehmen, sodass Sie wissen, welche Abfragen den Speicher für längere Zeit blockieren.

Zu den gängigen PostgreSQL-Scanmethoden, die viel Arbeitsspeicher verwenden, gehören:

• Bitmap Heap Scan
• Schnelles Sortieren
• Hash Join oder Hash

Hohe Arbeitsspeichernutzung und relevante Protokolle für Gemini-kompatible Instanzen

Wenn Gemini aktiviert ist, wird anstelle eines OOM-Fehlers (Out-of-Memory), der zu einer Datenbankausfallzeit führt, eine Verbindung beendet, die eine Abfrage mit hoher Speichernutzung ausführt. So wird eine Datenbankausfallzeit verhindert. Um die Abfrage zu identifizieren, die zu einer Standardabfrage führt, können Sie in den Datenbankprotokollen nach den folgenden Einträgen suchen:

  (...timestamp….) db=postgres, user=customer FATAL: terminating connection due to administrator command

Im folgenden Cloud SQL for PostgreSQL-Datenbankprotokoll wird die Abfrage mit hoher Speichernutzung erfasst, die beendet wurde, um einen OOM zu verhindern. Die Abfrage ist eine normalisierte Version der ursprünglichen Abfrage:

  db=postgres,user=customer LOG:  postgres process with PID 1734 for the query "SELECT COUNT(product) AS item_count FROM test_table WHERE product_type = $1 AND product LIKE $2 AND c6_2 IN ($3,$4,$5,$6,$7)" has been cancelled.

Auf der Seite Cloud SQL-Instanzen werden auch Benachrichtigungen zu den folgenden Ereignissen angezeigt:

• Arbeitsspeicherauslastung der Instanz in den letzten 24 Stunden.
• Liste der normalisierten Abfragen, die in den letzten 24 Stunden abgebrochen wurden.
• Ein Link zur Google-Dokumentation zur Optimierung der Arbeitsspeichernutzung.

Große Arbeitsspeichernutzung – Empfehlungen

Die folgenden Empfehlungen beheben die häufigsten speicherbezogenen Probleme. Wenn die Instanz weiterhin viel Arbeitsspeicher beansprucht, ist die Wahrscheinlichkeit hoch, dass sie irgendwann ein out of memory-Problem bekommt. Wenn die Arbeitsspeicheranforderungen von PostgreSQL oder einem anderen Prozess dazu führen, dass das System nicht mehr über genügend Arbeitsspeicher verfügt, wird in den PostgreSQL-Logs eine Kernel-Nachricht vom Typ Out of Memory angezeigt. Die PostgreSQL-Instanz wird schließlich beendet. Beispiel:

Out of Memory: Killed process 12345 (postgres)

Am häufigsten tritt ein OOM-Problem bei einem höheren work_mem-Wert und einer hohen Anzahl aktiver Verbindungen auf. Wenn Sie also häufig OOMs erhalten oder OOMs in Ihrer Cloud SQL for PostgreSQL-Instanz vermeiden möchten, sollten Sie die folgenden Empfehlungen beachten:

• work_mem festlegen

  Abfragen, die die schnelle Sortierung verwenden, sind schneller als Abfragen, die eine externe Zusammenführung und Sortierung verwenden. Erstere kann jedoch zu einer Speicherausschöpfung führen. Um dieses Problem zu lösen, setzen Sie den Wert für work_mem so, dass ein Gleichgewicht zwischen den Sortiervorgängen im Arbeitsspeicher und auf dem Laufwerk hergestellt wird. Sie können work_mem auch auf Sitzungsebene festlegen, anstatt es für eine gesamte Instanz festzulegen.

• Aktive Sitzungen überwachen

  Jede Verbindung verwendet eine bestimmte Menge an Arbeitsspeicher. Verwenden Sie die folgende Abfrage, um die Anzahl der aktiven Verbindungen zu prüfen:

    SELECT
      state,
      usename,
      count(1)
    FROM
      pg_stat_activity
    WHERE
      pid <> pg_backend_pid()
    GROUP BY
      state,
      usename
    ORDER BY
      1;
  

  Wenn Sie eine große Anzahl aktiver Sitzungen haben, analysieren Sie die Ursache für eine hohe Anzahl aktiver Sitzungen, z. B. Transaktionssperren.

• shared_buffers festlegen

  Wenn shared_buffers auf einen höheren Wert gesetzt ist, sollten Sie den Wert shared_buffers verringern, damit der Arbeitsspeicher für andere Vorgänge wie work_mem oder für die Herstellung neuer Verbindungen verwendet werden kann.

  Cache-Trefferquote

  PostgreSQL versucht normalerweise, die Daten, auf die Sie am häufigsten zugreifen, im Cache zu halten. Wenn die Daten von einem Client angefordert werden und bereits in freigegebenen Zwischenspeichern gespeichert sind, werden sie direkt dem Client zugewiesen. Dies wird als Cache-Treffer bezeichnet. Sind die Daten nicht in freigegebenen Puffern vorhanden, werden sie zuerst von einem Laufwerk in freigegebene Puffer geholt und dann an den Client übergeben. Dies wird als Cache-Fehler bezeichnet. Die Cache-Trefferquote gibt an, wie viele Inhaltsanfragen der Cache im Vergleich zu den empfangenen Anfragen verarbeitet hat. Führen Sie die folgende Abfrage aus, um die Cache-Trefferquote für die Tabellenanfragen in der PostgreSQL-Instanz zu prüfen:

  SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
  FROM
    pg_statio_user_tables;
  
  

  Führen Sie die folgende Abfrage aus, um die Cache-Trefferquote für die Indexanfragen in der PostgreSQL-Instanz zu prüfen:

    SELECT
      sum(idx_blks_read) as idx_read,
      sum(idx_blks_hit)  as idx_hit,
      (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM
      pg_statio_user_indexes;
  

  Im Allgemeinen werden 95 bis 99 % der Cache-Trefferquote als guter Wert angesehen.

• Große Seiten aktivieren Cloud SQL for PostgreSQL hat standardmäßig huge_pages für eine bessere Speicherverwaltung aktiviert. Wir empfehlen, dass Sie diese Funktion aktivieren. Weitere Informationen zu huge_pages finden Sie in der PostreSQL-Dokumentation.

• max_locks_per_transaction festlegen

  Der Wert max_locks_per_transaction gibt die Anzahl der Datenbankobjekte an, die gleichzeitig gesperrt werden können. In den meisten Fällen ist der Standardwert von 64 ausreichend. Wenn Sie jedoch mit einem großen Dataset arbeiten, kann es zu OOMs kommen. Sie sollten den Wert von max_locks_per_transaction hoch genug einstellen, um OOMs zu vermeiden.

  Der Wert max_locks_per_transaction sollte max_locks_per_transaction * (max_connections + max_prepared_transactions) Objekte sein. Wenn Sie also 300.000 Objekte haben und der Wert von max_connections 200 ist, sollte max_locks_per_transaction 1.500 sein.

• max_pred_locks_per_transaction festlegen

  Die Transaktion kann fehlschlagen, wenn Sie Clients haben, die viele verschiedene Tabellen in einer einzelnen serialisierbaren Transaktion betreffen. In diesem Szenario sollten Sie max_pred_locks_per_transaction auf einen relativ hohen Wert erhöhen. Wie max_locks_per_transaction verwendet auch max_pred_locks_per_transaction freigegebenen Arbeitsspeicher, daher sollte der Wert nicht unangemessen hoch sein.

• Wenn die Speichernutzung weiterhin hoch ist und Sie glauben, dass diese Abfragen legitimer Traffic sind, sollten Sie die Anzahl der Arbeitsspeicherressourcen in Ihrer Instanz erhöhen, um Datenbankabstürze oder Ausfallzeiten zu vermeiden.