Ottimizza l'utilizzo elevato di memoria nelle istanze

MySQL   |  PostgreSQL   |  SQL Server

Questo documento spiega come identificare un utilizzo elevato della memoria utilizzata per le istanze Cloud SQL e fornisce suggerimenti su come risolvere i problemi relativi alla memoria.

Per scoprire come configurare la memoria utilizzata per un'istanza Cloud SQL, consulta Best practice per la gestione della memoria utilizzata.

Identifica la memoria utilizzata elevata

Le sezioni seguenti illustrano gli scenari di utilizzo elevato della memoria.

Utilizza Esplora metriche per identificare la memoria utilizzata

Puoi esaminare la memoria utilizzata dall'istanza con la metrica database/memory/components in Esplora metriche:

• usage: memoria utilizzata attivamente dai processi PostgreSQL, tra cui shared_buffers e altre cache nel database.
• cache: memoria utilizzata dalla cache della pagina del sistema operativo, che in genere è recuperabile. Valori cache elevati non indicano necessariamente una pressione sulla memoria.
• free: memoria inutilizzata disponibile per i processi del database o per la cache di pagina del sistema operativo.

Nota: se la memoria disponibile in database/memory/components.cache e database/memory/components.free combinate è inferiore al 10%, il rischio di un evento OOM è elevato. Per monitorare l'utilizzo della memoria ed evitare eventi OOM, ti consigliamo di configurare una policy di avviso con una condizione di soglia della metrica del 90% in database/memory/components.usage.

Utilizza Query Insights per analizzare il piano di spiegazione delle query che consumano molte risorse

Query Insights consente di rilevare, diagnosticare e prevenire i problemi di prestazioni delle query per i database Cloud SQL. Query Insights fornisce un elenco di query a lunga esecuzione insieme al relativo piano di spiegazione (documentazione di PostgreSQL). Esamina il piano di spiegazione e identifica la parte della query che ha un metodo di scansione con un'elevata memoria utilizzata. Indipendentemente dal tempo di esecuzione della query, Query Insights fornisce il piano di spiegazione per tutte le query. Identifica le query complesse che richiedono più tempo per sapere quali query bloccano la memoria per periodi più lunghi.

I metodi di scansione PostgreSQL comuni che utilizzano molta memoria includono:

• Scansione heap bitmap
• Ordinamento rapido
• Hash join o Hash

Utilizzo elevato della memoria utilizzata e log pertinenti per le istanze abilitate per Gemini

Anteprima — Gemini in Databases

Questa funzionalità è soggetta ai "Termini dell'offerta pre-GA" nella sezione dei Termini di servizio generali dei Termini specifici dei servizi. Puoi trattare i dati personali per questa funzionalità, come indicato nell'Addendum per il trattamento dei dati Cloud, fatti salvi gli obblighi e le restrizioni all'interno dell'accordo che regola l'accesso a Google Cloud. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per ulteriori informazioni, consulta le descrizioni della fase di lancio.

Se hai attivato Gemini, invece di un errore OOM che causa un tempo di inattività del database, viene terminata una connessione che esegue una query con un'elevata memoria utilizzata, evitando così il tempo di inattività del database. Per identificare la query predefinita, puoi controllare i log del database per le seguenti voci:

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

Viene visualizzato il seguente log del database Cloud SQL per PostgreSQL, che acquisisce la query con un utilizzo elevato della memoria utilizzata che è stata terminata per evitare l'OOM. La query è una versione normalizzata della query originale:

  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.

Le notifiche vengono visualizzate anche nella pagina Istanze Cloud SQL per i seguenti eventi:

• Utilizzo della memoria dell'istanza nelle ultime 24 ore.
• Elenco delle query normalizzate che sono state annullate nelle ultime 24 ore.
• Un link alla documentazione di Google sull'ottimizzazione della memoria utilizzata.

Utilizzo elevato della memoria - Suggerimenti

I seguenti suggerimenti riguardano i problemi comuni relativi alla memoria. Se l'istanza continua a utilizzare una quantità elevata di memoria, è molto probabile che alla fine si verifichi un problema di out of memory. Se le richieste di memoria di PostgreSQL o di un altro processo causano l'esaurimento della memoria del sistema, nei log di PostgreSQL viene visualizzato un messaggio del kernel Out of Memory e l'istanza PostgreSQL viene arrestata. Ad esempio:

Out of Memory: Killed process 12345 (postgres)

Il caso più comune in cui si verifica un problema di esaurimento della memoria è un valore più alto di work_mem con un numero elevato di connessioni attive. Pertanto, se ricevi frequenti errori OOM o per evitarli nell'istanza Cloud SQL per PostgreSQL, ti consigliamo di seguire questi suggerimenti:

• Imposta work_mem

  Le query che utilizzano l'algoritmo "quicksort" sono più veloci di quelle che utilizzano l'algoritmo "merge sort" esterno. Tuttavia, la prima opzione potrebbe causare l'esaurimento della memoria. Per risolvere il problema, imposta il valore work_mem in modo che sia sufficientemente ragionevole da bilanciare le operazioni di ordinamento che avvengono nella memoria e sul disco. Puoi anche valutare la possibilità di impostare work_mem a livello di sessione anziché per un'intera istanza.

• Monitora le sessioni attive

  Ogni connessione utilizza una determinata quantità di memoria. Utilizza la seguente query per controllare il numero di connessioni attive:

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

  Se hai un numero elevato di sessioni attive, analizza la causa principale di questo numero elevato, ad esempio i blocchi delle transazioni.

• Imposta shared_buffers

  Se shared_buffers è impostato su un valore più alto, valuta la possibilità di diminuire il valore di shared_buffers in modo che la memoria possa essere utilizzata per altre operazioni, come work_mem, o per stabilire nuove connessioni.

  Percentuale successi cache

  PostgreSQL generalmente tenta di conservare nella cache i dati a cui accedi più spesso. Quando i dati vengono richiesti da un client, se sono già memorizzati nella cache nei buffer condivisi, vengono forniti direttamente al client. Questa situazione viene definita successo della cache. Se i dati non sono presenti nei buffer condivisi, vengono prima recuperati nei buffer condivisi da un disco e poi forniti al client. Questa situazione viene definita fallimento della cache. La percentuale successi cache misura il numero di richieste di contenuti gestite dalla cache rispetto alle richieste ricevute. Esegui la seguente query per controllare la percentuale successi cache per le richieste di tabella nell'istanza PostgreSQL:

  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;
  

  Esegui la seguente query per controllare la percentuale successi cache per le richieste di indice nell'istanza PostgreSQL:

    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;
  

  In genere, una percentuale successi cache compresa tra il 95% e il 99% è considerata un buon valore.

• In Cloud SQL per PostgreSQL, il flag huge_pages è abilitato per impostazione predefinita per una migliore gestione della memoria. Per scoprire di più su huge_pages, consulta la documentazione di PostgreSQL.

• Imposta max_locks_per_transaction

  Il valore max_locks_per_transaction indica il numero di oggetti del database che possono essere bloccati contemporaneamente. Nella maggior parte dei casi, il valore predefinito 64 è sufficiente. Tuttavia, se utilizzi un set di dati di grandi dimensioni, potresti riscontrare errori di memoria insufficiente (OOM). Valuta la possibilità di aumentare il valore di max_locks_per_transaction in modo che sia sufficientemente alto da evitare errori di memoria esaurita.

  Nota: il blocco a livello di oggetto avviene in memoria su una transazione in corso. Un aumento irragionevole del valore max_prepared_transactions può causare la richiesta di più memoria condivisa da parte dell'istanza del database.

  Il valore max_locks_per_transaction deve essere max_locks_per_transaction * (max_connections + max_prepared_transactions) oggetti. Ciò significa che se hai 300.000 oggetti e il valore di max_connections è 200, max_locks_per_transaction deve essere 1500.

• Imposta max_pred_locks_per_transaction

  La transazione potrebbe non riuscire se hai client che toccano molte tabelle diverse in una singola transazione serializzabile. In questo scenario, valuta la possibilità di aumentare max_pred_locks_per_transaction impostando un valore ragionevolmente elevato. Come max_locks_per_transaction, anche max_pred_locks_per_transaction utilizza la memoria condivisa, quindi non impostare un valore eccessivamente elevato.

• Se la memoria utilizzata è ancora elevata e ritieni che queste query siano traffico legittimo, valuta la possibilità di aumentare il numero di risorse di memoria nell'istanza per evitare l'arresto anomalo o il tempo di inattività del database.

Passaggi successivi

• Google Cloud motori per suggerimenti