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, vedi Best practice per la gestione della memoria utilizzata.

Identificazione di un utilizzo elevato della memoria

Usa Esplora metriche per identificare l'utilizzo della memoria

Puoi rivedere l'utilizzo della memoria dell'istanza con Metrica database/memory/components.usage in Esplora metriche.

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). Rivedi il piano di spiegazione e identifica la parte della query con un metodo di analisi che prevede un utilizzo elevato della memoria. 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, in modo da 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 e log pertinenti per le istanze con Gemini abilitato

Se hai abilitato Gemini, invece di un errore di esaurimento della memoria (OOM) che comporta un tempo di inattività del database, una connessione che esegue una query con utilizzo elevato di memoria viene terminata, impedendo così il tempo di inattività del database. Per identificare la query predefinita, puoi controllare nei log del database 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 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 consigli riguardano i problemi comuni relativi alla memoria. Se l'istanza continua a utilizzare una quantità elevata di memoria, è 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 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. Di conseguenza, se ricevi frequenti OOM o vuoi 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 il criterio shared_buffers è impostato su un valore superiore, ti consigliamo di diminuire il valore di shared_buffers in modo che la memoria possa essere utilizzata per altre operazioni, ad esempio work_mem, o per stabilire nuove connessioni.

    Percentuale di successi 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 cliente, Se è già memorizzato nella cache in buffer condivisi, viene fornito direttamente al client. In tal caso si parla di successo della cache. Se non sono presenti nei buffer condivisi, vengono prima recuperati da un disco e poi inviati al client. In tal caso si parla di fallimento della cache. Il rapporto di successi della cache misura il numero di richieste di contenuto gestite dalla cache rispetto alle richieste ricevute. Esegui la seguente query per controllare il rapporto di hit della 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 questa query per controllare il 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 di successi della cache compresa tra 95 e 99% è considerata un buon valore.

  • Abilita pagine di grandi dimensioni Per impostazione predefinita, Cloud SQL per PostgreSQL ha huge_pages abilitato per una migliore gestione della memoria. Ti consigliamo di abilitarlo. Per scoprire di più su huge_pages, consulta la documentazione di PostreSQL.

  • 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 finire per avere degli OOM. Valuta la possibilità di aumentare il valore di max_locks_per_transaction sufficientemente alta 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 un'unica 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 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.