Best practice per la gestione dell'utilizzo della memoria

La configurazione e il monitoraggio della memoria utilizzata per un'istanza Cloud SQL ti aiutano a prevenire colli di bottiglia delle prestazioni, problemi di memoria esaurita e tempi di inattività del database. Quando crei un'istanza, selezioni una dimensione di memoria iniziale e puoi ottimizzare i flag di database relativi alla memoria per mantenere l'istanza in stato integro man mano che il workload PostgreSQL cresce.

PostgreSQL divide i componenti della memoria in memoria globale, condivisa tra tutti i processi per eseguire le query (ad esempio shared_buffers e max_connections), e memoria locale, dedicata a ogni connessione (ad esempio work_mem, maintenance_work_mem e temp_buffers). Per altre considerazioni sulla configurazione, consulta Best practice generali e Linee guida operative.

Memoria utilizzata e flag

Ogni volta che le istanze Cloud SQL utilizzano molta memoria, potrebbero sorgere le seguenti domande:

  • Quale query o processo utilizza molta memoria?
  • Le impostazioni della memoria sono adeguate per l'attività del database?
  • Come si modificano le impostazioni dei ricordi?

Quando un database PostgreSQL è in funzione, la maggior parte della memoria utilizzata si concentra in alcune aree:

  • Buffer condiviso: si tratta della memoria condivisa che PostgreSQL alloca per contenere i dati della tabella per le operazioni read e write. Per l'operazione read, tutti i dati richiesti dal disco vengono prima recuperati nella RAM e poi forniti al client. Analogamente, in PostgreSQL, quando i dati vengono richiesti (ad esempio, SELECT * from emp), vengono prima recuperati dal disco in shared_buffers per la memorizzazione nella cache, quindi vengono forniti al client. La stessa cosa accade con l'operazione write.

    Il buffer condiviso è anche l'area di memoria condivisa per tutti i processi e le connessioni per le attività del database, come la memorizzazione nella cache dei dati, la memorizzazione nella cache delle connessioni e le operazioni di Data Manipulation Language (DML). La quantità massima che questa area può allocare è specificata dal flag shared_buffers e il valore predefinito è il 33% della memoria dell'istanza. Se il valore di shared_buffers è elevato, la dimensione dei dati memorizzati nella cache è elevata.

  • Memoria di lavoro delle query: durante l'esecuzione di una query, PostgreSQL alloca la memoria locale per ogni operazione, ad esempio l'ordinamento e l'hashing. La quantità massima che può allocare per ogni operazione di una query prima di scrivere su file di disco temporanei è configurata dal flag work_mem e il valore predefinito è 4 MB. Se il valore di work_mem è elevato, la quantità di dati che è possibile ordinare nella memoria è elevata.
  • Memoria di lavoro per la manutenzione: alcune operazioni di manutenzione come VACUUM, CREATE INDEX, ALTER TABLE e ADD FOREIGN KEY richiedono una memoria locale separata allocata da PostgreSQL. La quantità massima per il processo di backend utilizzato da queste operazioni può essere configurata tramite il flag maintenance_work_mem e il valore predefinito è 64 MB. Tieni presente che i worker di autovacuum utilizzano anche la memoria di lavoro di manutenzione e il valore massimo può essere sostituito dal flag autovacuum_work_mem. Se il valore di maintenance_work_mem è elevato, la velocità di esecuzione dell'operazione VACUUM è elevata.
  • Buffer temporanei: quando una tabella temporanea viene utilizzata in una sessione di database, PostgreSQL alloca buffer temporanei per contenere la tabella temporanea locale della sessione. La quantità massima può essere specificata dal flag temp_buffers e il valore predefinito è 8 MB.
  • Connessione al database: quando un client si connette al database, PostgreSQL crea un processo di backend per gestire la sessione del client. Oltre alla memoria per eseguire la query, PostgreSQL alloca memoria aggiuntiva per conservare informazioni come la cache del catalogo di sistema e i piani di query preparati. Il numero massimo di connessioni simultanee consentite al server di database può essere configurato dal flag max_connections. Ogni connessione inattiva utilizza circa 2-3 MB di memoria condivisa. Se il valore di max_connections è elevato, l'istanza può stabilire più connessioni, ma a scapito della memoria.

Per l'elenco completo dei componenti della memoria in PostgreSQL, consulta la documentazione di PostgreSQL. Per modificare i flag elencati in questa sezione, consulta Configura i flag di database.

Monitora l'utilizzo della memoria

Monitora regolarmente la memoria dell'istanza in Cloud Monitoring e mantienila al di sotto del limite di memoria. Una buona pratica è impostare un avviso in Cloud Monitoring per ricevere una notifica quando l'utilizzo supera il 90% del limite per 6 ore. Questo avviso può avvisarti quando la memoria utilizzata è costantemente vicina al limite.

Inoltre, monitora gli incidenti di esaurimento della memoria. Per farlo, configura una metrica basata su log per il messaggio server process .* was terminated by signal 9: Killed in Cloud Monitoring per conteggiare gli eventi di memoria esaurita, quindi invia un avviso ogni volta che si verifica un evento di questo tipo.

Se l'istanza opera costantemente al di sopra del 90% del limite di memoria o se si verifica un evento di memoria esaurita, puoi aumentare la memoria dell'istanza. In alternativa, puoi ridurre la memoria utilizzata limitando il numero di connessioni al database o riducendo i flag di database come shared_buffers, work_mem o max_connections. Se riduci questi flag, le prestazioni dell'istanza potrebbero essere limitate.

Memoria insufficiente

Quando la memoria non è sufficiente per gestire il workload del database, come ultima risorsa, il sistema operativo Linux sottostante utilizza out-of-memory (OOM) killer per terminare un processo e liberare memoria. Cloud SQL è configurato in modo che OOM killer abbia come target solo i processi worker PostgreSQL. In questa situazione, il processo postmaster viene conservato in modo che debba solo terminare tutte le connessioni al database esistenti ed eseguire un ripristino per proteggere l'integrità del database. In questo caso, si verificano momenti di interruzione del servizio e tempi di inattività del database. Nel log del database PostgreSQL vengono visualizzati messaggi come i seguenti:

2021-10-24 23:34:22.265 UTC [7]: [663-1] db=,user= LOG: server process (PID 1255039) was terminated by signal 9: Killed
2021-10-24 23:34:22.265 UTC [7]: [664-1] db=,user= DETAIL: Failed process was running: SELECT * FROM tab ORDER BY col
2021-10-24 23:34:22.277 UTC [7]: [665-1] db=,user= LOG: terminating any other active server processes
2021-10-24 23:34:22.278 UTC [1255458]: [1-1] db=postgres,user=postgres WARNING: terminating connection because of crash of another server process
2021-10-24 23:34:22.278 UTC [1255458]: [2-1] db=postgres,user=postgres DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-10-24 23:34:22.278 UTC [1255458]: [3-1] db=postgres,user=postgres HINT: In a moment you should be able to reconnect to the database and repeat your command.
2021-10-24 23:34:22.278 UTC [1255458]: [4-1] db=postgres,user=postgres CONTEXT: while updating tuple (27,18) in relation "tab"
...
2021-10-24 23:34:22.558 UTC [1255477]: [1-1] db=postgres,user=postgres FATAL: the database system is in recovery mode
...
2021-10-24 23:34:25.579 UTC [7]: [666-1] db=,user= LOG: all server processes terminated; reinitializing
...
2021-10-24 23:34:25.691 UTC [1255482]: [1-1] db=,user= LOG: database system was interrupted; last known up at 2021-10-24 23:31:53 UTC
2021-10-24 23:34:25.776 UTC [1255482]: [2-1] db=,user= LOG: database system was not properly shut down; automatic recovery in progress
2021-10-24 23:34:25.789 UTC [1255482]: [3-1] db=,user= LOG: redo starts at 227/AB359400
2021-10-24 23:34:38.957 UTC [1255482]: [4-1] db=,user= LOG: redo done at 229/4621F508
2021-10-24 23:34:38.959 UTC [1255482]: [5-1] db=,user= LOG: last completed transaction was at log time 2021-10-24 23:34:18.5535+00
2021-10-24 23:34:39.290 UTC [7]: [667-1] db=,user= LOG: database system is ready to accept connections

Query annullate a causa di un utilizzo elevato della memoria utilizzata

Quando una query del database Cloud SQL utilizza molta memoria, Cloud SQL rileva in modo proattivo la memoria utilizzata elevata e si impegna a terminare la connessione che esegue la query per evitare un errore out of memory (OOM). In questo modo si evitano tempi di inattività del database dovuti all'utilizzo elevato della memoria utilizzata.

Puoi identificare una query annullata a causa di un utilizzo elevato della memoria utilizzata esaminando il messaggio di log di una query nel log del database PostgreSQL. Ad esempio, il log di una query annullata mostrerà il seguente messaggio:

(...timestamp….) [PID]:  db=postgres,user=$user_id LOG: Process [PID] received SIGTERM
(...timestamp….) [PID]:  db=postgres,user=$user_id DETAIL: Cloud SQL cancelled this query owing to its excessive memory usage which can cause the PostgreSQL instance to crash.
(...timestamp….) [PID]:  db=postgres,user=$user_id HINT:  To avoid future interruptions, consider increasing instance memory or optimizing this query.

Se il tuo database utilizza Query Insights, puoi visualizzare i dettagli di una query specifica per trovare ulteriori informazioni sulla query annullata. Utilizza questi log per identificare la query annullata e ottimizzare l'utilizzo elevato della memoria utilizzata per il tuo database comprendendo e risolvendo le cause alla base della query annullata.

Passaggi successivi