Condividi tramite

Utilizzo delle risorse / Memoria

  • Articolo

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

hash_mem_multiplier

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Multiplo di work_mem da usare per tabelle hash.
Tipo di dati numeric
Valore predefinito 2
Valori consentiti 2
Tipo parametro sola lettura
Documentazione hash_mem_multiplier

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

huge_page_size

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Dimensioni di una huge page che deve essere richiesta.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione huge_page_size

logical_decoding_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per la decodifica logica.
Tipo di dati integer
Valore predefinito 65536
Valori consentiti 65536
Tipo parametro sola lettura
Documentazione logical_decoding_work_mem

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

min_dynamic_shared_memory

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Quantità di memoria condivisa dinamica riservata all'avvio.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione min_dynamic_shared_memory

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa usata per l'area di memoria condivisa principale.
Tipo di dati enumerazione
Valore predefinito mmap
Valori consentiti mmap
Tipo parametro sola lettura
Documentazione shared_memory_type

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

hash_mem_multiplier

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Multiplo di work_mem da usare per tabelle hash.
Tipo di dati numeric
Valore predefinito 2
Valori consentiti 2
Tipo parametro sola lettura
Documentazione hash_mem_multiplier

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

huge_page_size

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Dimensioni di una huge page che deve essere richiesta.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione huge_page_size

logical_decoding_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per la decodifica logica.
Tipo di dati integer
Valore predefinito 65536
Valori consentiti 65536
Tipo parametro sola lettura
Documentazione logical_decoding_work_mem

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

min_dynamic_shared_memory

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Quantità di memoria condivisa dinamica riservata all'avvio.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione min_dynamic_shared_memory

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa usata per l'area di memoria condivisa principale.
Tipo di dati enumerazione
Valore predefinito mmap
Valori consentiti mmap
Tipo parametro sola lettura
Documentazione shared_memory_type

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

hash_mem_multiplier

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Multiplo di work_mem da usare per tabelle hash.
Tipo di dati numeric
Valore predefinito 1
Valori consentiti 1
Tipo parametro sola lettura
Documentazione hash_mem_multiplier

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

huge_page_size

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Dimensioni di una huge page che deve essere richiesta.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione huge_page_size

logical_decoding_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per la decodifica logica.
Tipo di dati integer
Valore predefinito 65536
Valori consentiti 65536
Tipo parametro sola lettura
Documentazione logical_decoding_work_mem

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

min_dynamic_shared_memory

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Quantità di memoria condivisa dinamica riservata all'avvio.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0
Tipo parametro sola lettura
Documentazione min_dynamic_shared_memory

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa usata per l'area di memoria condivisa principale.
Tipo di dati enumerazione
Valore predefinito mmap
Valori consentiti mmap
Tipo parametro sola lettura
Documentazione shared_memory_type

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

hash_mem_multiplier

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Multiplo di work_mem da usare per tabelle hash.
Tipo di dati numeric
Valore predefinito 1
Valori consentiti 1
Tipo parametro sola lettura
Documentazione hash_mem_multiplier

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

logical_decoding_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per la decodifica logica.
Tipo di dati integer
Valore predefinito 65536
Valori consentiti 65536
Tipo parametro sola lettura
Documentazione logical_decoding_work_mem

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa usata per l'area di memoria condivisa principale.
Tipo di dati enumerazione
Valore predefinito mmap
Valori consentiti mmap
Tipo parametro sola lettura
Documentazione shared_memory_type

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

hash_mem_multiplier

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Multiplo di work_mem da usare per tabelle hash.
Tipo di dati numeric
Valore predefinito 1
Valori consentiti 1
Tipo parametro sola lettura
Documentazione hash_mem_multiplier

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa usata per l'area di memoria condivisa principale.
Tipo di dati enumerazione
Valore predefinito mmap
Valori consentiti mmap
Tipo parametro sola lettura
Documentazione shared_memory_type

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.

autovacuum_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per ogni processo di lavoro autovacuum.
Tipo di dati integer
Valore predefinito -1
Valori consentiti -1-2097151
Tipo parametro dynamic
Documentazione autovacuum_work_mem

dynamic_shared_memory_type

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Seleziona l'implementazione della memoria condivisa dinamica usata.
Tipo di dati enumerazione
Valore predefinito posix
Valori consentiti posix
Tipo parametro sola lettura
Documentazione dynamic_shared_memory_type

huge_pages

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Abilita/disabilita l'uso di grandi pagine di memoria. Questa impostazione non è applicabile ai server con meno di 4 vCore.
Tipo di dati enumerazione
Valore predefinito try
Valori consentiti on,off,try
Tipo parametro static
Documentazione huge_pages

Descrizione

Le huge page offrono una funzionalità che consente di gestire la memoria in blocchi più grandi. Rispetto alle pagine standard da 4 KB, permettono solitamente di gestire blocchi fino a 2 MB.

L'uso di huge page può offrire vantaggi in termini di prestazioni che consentono l'offload efficiente della CPU:

  • Riducono il sovraccarico associato alle attività di gestione della memoria, diminuendo ad esempio il numero di mancati riscontri nel TLB.
  • Riducono il tempo necessario per la gestione della memoria.

In PostgreSQL, in particolare, è possibile usare le huge page solo per l'area di memoria condivisa, di cui una parte significativa viene allocata per i buffer condivisi.

Un altro vantaggio offerto dalle huge page è la capacità di impedire lo scambio dell'area di memoria condivisa su disco, stabilizzando ulteriormente le prestazioni.

Consigli

  • Per i server con risorse di memoria significative, quindi, è opportuno evitare la disattivazione delle huge page, che potrebbe compromettere le prestazioni.
  • Se si inizia con un server più piccolo che non supporta le huge page, ma si prevede di passare a un server in grado di supportarle, mantenere l'impostazione huge_pages su TRY per una transizione lineare e prestazioni ottimali.

Note specifiche su Azure

Nei server con quattro o più vCore, le huge page vengono allocate automaticamente dal sistema operativo sottostante. Questa funzionalità non è disponibile nei server con meno di quattro vCore. Il numero di huge page viene adattato automaticamente se vengono cambiate le impostazioni di memoria condivisa, incluse eventuali modifiche a shared_buffers.

maintenance_work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la memoria massima da usare per le operazioni di manutenzione, ad esempio VACUUM, Create Index.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 1024-2097151
Tipo parametro dynamic
Documentazione maintenance_work_mem

Descrizione

maintenance_work_mem è un parametro di configurazione in PostgreSQL. Determina la quantità di memoria allocata per operazioni di manutenzione come VACUUM, CREATE INDEX e ALTER TABLE. A differenza di work_mem, che interessa l'allocazione di memoria per le operazioni di query, il parametro maintenance_work_mem è riservato alle attività di gestione e ottimizzazione della struttura dei database.

Punti chiave

  • Limite di memoria per le operazioni vacuum : se si vuole velocizzare la rimozione delle tuple inattive aumentando il valore di maintenance_work_mem, tenere presente che in VACUUM è presente una limitazione predefinita per la raccolta di identificatori di tuple inattive. Per questo processo, infatti, può usare fino a 1 GB di memoria.
  • Separazione della memoria per operazioni di autovacuum: è possibile usare l'impostazione autovacuum_work_mem per controllare la memoria usata in modo indipendente dalle operazioni autovacuum. Questa impostazione svolge la propria funzione come subset di maintenance_work_mem. È possibile decidere la quantità di memoria usata dalle operazioni di autovacuum senza modificare l'allocazione della memoria per altre attività di manutenzione e operazioni di definizione dei dati.

max_prepared_transactions

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di transazioni preparate contemporaneamente. Quando si esegue un server di replica, è necessario impostare questo parametro su un valore identico o superiore rispetto a quello per il server primario.
Tipo di dati integer
Valore predefinito 0
Valori consentiti 0-262143
Tipo parametro static
Documentazione max_prepared_transactions

max_stack_depth

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la profondità massima dello stack, in kilobyte.
Tipo di dati integer
Valore predefinito 2048
Valori consentiti 2048
Tipo parametro sola lettura
Documentazione max_stack_depth

shared_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero di buffer di memoria condivisa usati dal server. L'unità è 8 kB. I valori consentiti sono inclusi nell'intervallo compreso tra il 10% e il 75% della memoria disponibile.
Tipo di dati integer
Valore predefinito Dipende dalle risorse (vCore, RAM o spazio su disco) allocate al server.
Valori consentiti 16-1073741823
Tipo parametro static
Documentazione shared_buffers

Descrizione

Il parametro di configurazione shared_buffers determina la quantità di memoria di sistema allocata al database PostgreSQL per il buffering dei dati. Svolge inoltre la funzione di pool di memoria centralizzato accessibile a tutti i processi di database.

Nel momento in cui sono necessari dati specifici, il processo di database controlla prima il buffer condiviso. Se al suo interno sono presenti i dati richiesti, questi vengono rapidamente recuperati, in modo da evitare la lettura del disco che richiederebbe più tempo. Svolgendo la funzione di intermediario tra i processi di database e il disco, shared_buffers riduce in modo efficiente il numero di operazioni di I/O necessarie.

Note specifiche su Azure

L'impostazione shared_buffers viene dimensionata all'incirca linearmente con l'aumento di vCore in un livello.

temp_buffers

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta il numero massimo di buffer temporanei usati da ogni sessione del database.
Tipo di dati integer
Valore predefinito 1024
Valori consentiti 100-1073741823
Tipo parametro dynamic
Documentazione temp_buffers

work_mem

Attributo valore
Categoria Utilizzo delle risorse / Memoria
Descrizione Imposta la quantità di memoria che deve essere usata dalle operazioni di ordinamento interne e dalle tabelle hash prima della scrittura in file disco temporanei.
Tipo di dati integer
Valore predefinito 4096
Valori consentiti 4096-2097151
Tipo parametro dynamic
Documentazione work_mem

Descrizione

In PostgreSQL, il parametro work_mem controlla la quantità di memoria allocata per determinate operazioni interne all'area di memoria privata di ogni sessione di database. Queste operazioni possono essere, ad esempio, l'ordinamento e l'hashing.

A differenza dei buffer condivisi, che si trovano nell'area di memoria condivisa, il parametro work_mem viene allocato in uno spazio di memoria privata a livello di sessione o di query. Impostando una dimensione adeguata per work_mem, è possibile migliorare in modo significativo l'efficienza di queste operazioni e ridurre la necessità di scrivere dati temporanei su disco.

Punti chiave

  • Memoria per connessioni private: work_mem fa parte della memoria privata usata da ogni sessione di database ed è distinta dall'area di memoria condivisa usata da shared_buffers.
  • Utilizzo specifico delle query: il parametro work_mem non viene usato da tutte le sessioni o tutte le query. Query semplici come SELECT 1, ad esempio, difficilmente richiedono il parametro work_mem. Al contrario, query complesse che coinvolgono operazioni come l'ordinamento o l'hashing possono usare uno o più blocchi di work_mem.
  • Operazioni parallele: per le query che si estendono su più back-end paralleli, ognuno di essi può potenzialmente usare uno o più blocchi di work_mem.

Monitoraggio e regolazione del parametro work_mem

È essenziale monitorare continuamente le prestazioni del sistema e regolare work_mem in base alle esigenze, soprattutto se i tempi di esecuzione delle query inerenti a operazioni di ordinamento o hashing sono lenti. Sono disponibili vari modi per monitorare le prestazioni usando gli strumenti disponibili nel portale di Azure:

Regolazione granulare

Mentre si gestisce il parametro work_mem, è spesso più efficiente adottare un approccio di regolazione granulare anziché impostare un valore globale. Questo approccio consente infatti di allocare correttamente la memoria in funzione delle esigenze specifiche di processi e utenti. Riduce inoltre in modo sensibile il rischio di incorrere in problemi di memoria insufficiente. Ecco come procedere:

  • Livello utente: se un determinato utente è coinvolto principalmente in attività a elevato utilizzo di memoria, come attività di aggregazione o creazione di report, è consigliabile personalizzare il valore di work_mem in base alle esigenze dell'utente. Usare quindi il comando ALTER ROLE per migliorare le prestazioni delle operazioni dell'utente.

  • Livello di routine/funzione: se determinate funzioni o routine stanno generando file temporanei di notevoli dimensioni, può essere utile aumentare il valore di work_mem a livello di routine o di funzione. Usare quindi il comando ALTER FUNCTION o ALTER PROCEDURE per allocare in modo specifico più memoria a queste operazioni.

  • Livello di database: modificare il parametro work_mem a livello di database se solo specifici database stanno generando un numero elevato di file temporanei.

  • Livello globale: se da un'analisi del sistema emerge che la maggior parte delle query genera piccoli file temporanei e solo alcune creano file di grandi dimensioni, potrebbe essere prudente aumentare globalmente il valore di work_mem. Questa azione favorisce infatti l'elaborazione della maggior parte delle query in memoria, in modo da evitare operazioni basate su disco e migliorare l'efficienza. Tuttavia, monitorare sempre con attenzione l'utilizzo della memoria nel server per assicurarsi che sia in grado di gestire il valore incrementato del parametro work_mem.

Determinazione del valore minimo di work_mem per operazioni di ordinamento

Per trovare il valore minimo di work_mem per una query specifica, soprattutto se genera file temporanei su disco durante il processo di ordinamento, iniziare esaminando le dimensioni dei file temporanei generati durante l'esecuzione della query. Se, ad esempio, una query genera un file temporaneo di 20 MB:

  1. Connettersi al database usando psql o il client PostgreSQL preferito.
  2. Impostare un valore iniziale di work_mem leggermente superiore a 20 MB per supportare eventuali intestazioni aggiuntive durante l'elaborazione in memoria. Usare un comando, ad esempio: SET work_mem TO '25MB'.
  3. Nella stessa sessione, eseguire EXPLAIN ANALYZE nella query problematica.
  4. Esaminare l'output relativo a "Sort Method: quicksort Memory: xkB". Se indica "external merge Disk: xkB", aumentare progressivamente il valore di work_mem e ripetere l'operazione fino a quando non viene visualizzato "quicksort Memory". La presenza di "quicksort Memory" indica che la query è ora in esecuzione in memoria.
  5. Dopo aver determinato il valore da usare tramite questo metodo, è possibile applicarlo a livello globale o a un livello più granulare (come descritto in precedenza), in base alle proprie esigenze operative.