Ottimizza l'utilizzo elevato di memoria nelle istanze

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

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

Identificare un utilizzo elevato della memoria

Utilizzare 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 Approfondimenti sulle query per analizzare il piano di spiegazione per le query che consumano molte risorse

Query Insights ti aiuta a rilevare, diagnosticare e prevenire i problemi di prestazioni delle query per i database Cloud SQL. Query Insights fornisce un elenco di query in esecuzione prolungata e il relativo piano di spiegazione (documentazione PostgreSQL). Esamina il piano di spiegazione e identifica la parte della query con un metodo di scansione che utilizza molta memoria. Indipendentemente dal tempo di esecuzione della query, gli Approfondimenti sulle query forniscono il piano di spiegazione per tutte le query. Identifica le query complesse che richiedono più tempo per sapere quali bloccano la memoria per periodi più lunghi.

I metodi di scansione PostgreSQL comuni che utilizzano una grande quantità di memoria includono:

  • Scansione dell'heap bitmap
  • Ordinamento rapido
  • Join con hash o hash

Utilizzo elevato della memoria e log pertinenti per le istanze con Gemini abilitato

Se hai attivato Gemini, anziché un errore di esaurimento della memoria (OOM) che causa il tempo di riposo del database, una connessione che esegue una query con un utilizzo elevato della memoria viene interrotta, impedendo così il tempo di riposo 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 utilizzo elevato della memoria che è stata interrotta per evitare 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 annullate nelle ultime 24 ore.
  • Un link alla documentazione di Google sull'ottimizzazione dell'utilizzo della memoria.

Utilizzo elevato della memoria - Consigli

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

Out of Memory: Killed process 12345 (postgres)

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

  • Imposta work_mem

    Le query che utilizzano la ricerca rapida sono più veloci di quelle che utilizzano la ricerca combinata esterna. Tuttavia, la prima potrebbe comportare l'esaurimento della memoria. Per risolvere il problema, imposta un valore work_mem sufficientemente ragionevole in modo da bilanciare 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 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 fenomeno, ad esempio i blocchi delle transazioni.

  • Imposta shared_buffers

    Se shared_buffers è impostato su un valore più alto, ti consigliamo 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 di hit della cache

    In genere, PostgreSQL tenta di mantenere nella cache i dati a cui accedi più di frequente. Quando i dati vengono richiesti da un client, se sono già memorizzati nella cache in buffer condivisi, vengono forniti direttamente al client. Questa situazione viene definita hit della cache. Se i dati non sono presenti nei buffer condivisi, vengono prima recuperati dai buffer condivisi da un disco e poi dati al client. Questa situazione viene definita mancata corrispondenza della cache. La percentuale di hit della cache misura quante richieste di contenuti sono state gestite dalla cache rispetto alle richieste ricevute. Esegui la seguente query per controllare il percentuale successi cache per le richieste di tabelle 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 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 buon valore è compreso tra il 95 e il 99% di percentuale successi cache.

  • Attiva le pagine enormi Per impostazione predefinita, Cloud SQL per PostgreSQL ha huge_pages abilitato per una migliore gestione della memoria. Ti consigliamo di attivarla. 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 hai a che fare con un set di dati di grandi dimensioni, potresti riscontrare errori OOM. Valuta la possibilità di aumentare il valore di max_locks_per_transaction in modo sufficientemente elevato da evitare 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 mila 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 caso, ti consigliamo di aumentare max_pred_locks_per_transaction 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 eccessivamente elevato.

  • Se l'utilizzo della memoria è ancora elevato e ritieni che queste query siano traffico legittimo, ti consigliamo di aumentare il numero di risorse di memoria nell'istanza per evitare arresti anomali o tempi di riposo del database.