Ottimizza l'utilizzo elevato della memoria nelle istanze

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

Per scoprire come configurare l'utilizzo della memoria per un'istanza Cloud SQL, vedi Best practice per la gestione dell'utilizzo della memoria.

Identificare l'utilizzo elevato della memoria

Utilizza Metrics Explorer per identificare l'utilizzo della memoria

Puoi esaminare l'utilizzo della memoria dell'istanza con la metrica database/memory/components.usage in Metrics Explorer.

Utilizza Query Insights per analizzare il piano e spiegare il consumo di risorse elevate

Query Insights consente di rilevare, diagnosticare e prevenire 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 esplicativo e identifica la parte della query con un metodo di scansione con un'elevata memoria utilizzata. Indipendentemente dal tempo di esecuzione delle query, Query Insights offre un piano di spiegazione per tutte le query. Identifica le query complesse che richiedono più tempo, in modo da sapere quali query bloccano la memoria per durate più lunghe.

I metodi di scansione più comuni di PostgreSQL che utilizzano un'elevata memoria includono i seguenti:

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

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

Se Gemini è abilitato, invece di un errore di esaurimento della memoria (OOM) che causa un tempo di inattività del database, viene terminata una connessione che esegue una query con un utilizzo elevato della memoria, evitando così tempi di inattività del database. Per identificare la query predefinita, puoi controllare le seguenti voci nei log del database:

  (...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 per l'utilizzo elevato della memoria che è stata terminata per evitare lo 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 di query normalizzate che sono state annullate nelle ultime 24 ore.
  • Un link alla documentazione di Google sull'ottimizzazione dell'utilizzo della memoria.

Utilizzo elevato della memoria - Suggerimenti

I seguenti suggerimenti risolvono i problemi più comuni relativi alla memoria. Se l'istanza continua a utilizzare un'elevata quantità di memoria, è probabile che alla fine venga riscontrato un problema di tipo out of memory. Se le esigenze di memoria di PostgreSQL o di un altro processo causano l'esaurimento della memoria del sistema, vedrai un messaggio del kernel Out of Memory nei log di PostgreSQL e l'istanza di PostgreSQL viene arrestata. Ad esempio:

Out of Memory: Killed process 12345 (postgres)

L'istanza più comune in cui riscontri un problema OOM è con un valore più alto, work_mem, con un numero elevato di connessioni attive. Di conseguenza, se riscontri problemi di OOM frequenti o se eviti questi errori nell'istanza Cloud SQL per PostgreSQL, ti consigliamo di seguire questi suggerimenti:

  • Imposta work_mem

    Le query che utilizzano l'ordinamento rapido sono più veloci di quelle che utilizzano l'ordinamento di unione esterno. Il primo, però, può portare all'esaurimento della memoria. Per risolvere questo problema, imposta un valore di work_mem sufficientemente ragionevole in modo da bilanciare entrambe le operazioni di ordinamento 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 verificare 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 un numero elevato di sessioni attive, ad esempio i blocchi delle transazioni.

  • Imposta shared_buffers

    Se il valore di shared_buffers viene 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, ad esempio work_mem, o per la creazione di nuove connessioni.

    Rapporto di hit della cache

    In genere PostgreSQL cerca di mantenere nella cache i dati a cui accedi più spesso. Quando i dati vengono richiesti da un client, se sono già memorizzati nella cache in buffer condivisi, vengono inviati direttamente al client. In tal caso si parla di 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. Questo prende il nome di fallimento della cache. Il rapporto di successo della cache misura il numero di richieste di contenuti gestite dalla cache rispetto alle richieste ricevute. Esegui la query seguente per verificare il percentuale successi cache per le richieste della 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 query seguente per verificare il percentuale successi cache per le richieste di indicizzazione 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, un rapporto di successo della cache pari al 95-99% è considerato un buon valore.

  • Abilita pagine di grandi dimensioni Per impostazione predefinita, Cloud SQL per PostgreSQL è abilitato huge_pages per una migliore gestione della memoria. Ti consigliamo di abilitarla. Per saperne 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 di database che possono essere bloccati contemporaneamente. Nella maggior parte dei casi, il valore predefinito di 64 è sufficiente. Tuttavia, se hai a che fare con un set di dati di grandi dimensioni, potresti ritrovarti con degli OOM. Valuta la possibilità di aumentare il valore di max_locks_per_transaction in modo abbastanza alto da evitare gli eventi OOM.

    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 se il valore di max_connections è 200, max_locks_per_transaction dovrebbe essere 1500.

  • Imposta max_pred_locks_per_transaction

    La transazione potrebbe non riuscire se i tuoi client toccano molte tabelle diverse in un'unica transazione serializzabile. In questo caso, ti consigliamo di aumentare il valore di max_pred_locks_per_transaction fino a un valore ragionevolmente elevato. Come max_locks_per_transaction, anche max_pred_locks_per_transaction utilizza la memoria condivisa, quindi non impostare un valore irragionevole alto.

  • Se l'utilizzo della memoria è ancora elevato e ritieni che le query siano traffico legittimo, valuta la possibilità di aumentare il numero di risorse di memoria nell'istanza per evitare arresti anomali o tempi di inattività del database.