Utilizzo delle risorse / Memoria

autovacuum_work_mem

dynamic_shared_memory_type

hash_mem_multiplier

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

logical_decoding_work_mem

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

min_dynamic_shared_memory

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

vacuum_buffer_usage_limit

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

hash_mem_multiplier

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

logical_decoding_work_mem

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

min_dynamic_shared_memory

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

vacuum_buffer_usage_limit

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

hash_mem_multiplier

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

logical_decoding_work_mem

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

min_dynamic_shared_memory

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

hash_mem_multiplier

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

logical_decoding_work_mem

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

min_dynamic_shared_memory

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

hash_mem_multiplier

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

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

hash_mem_multiplier

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

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

shared_memory_type

temp_buffers

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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

dynamic_shared_memory_type

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

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.

Note specifiche su Azure

Il valore predefinito per il parametro del server maintenance_work_mem viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non avranno alcun effetto sul valore predefinito per il parametro server maintenance_work_mem di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il maintenance_work_mem parametro in base ai valori nella formula seguente.

La formula usata per calcolare il valore di maintenance_work_mem è (long)(82.5 * ln(memoryGiB) + 40) * 1024.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

max_prepared_transactions

max_stack_depth

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. I buffer condivisi fungono da intermediario tra i processi del database e il disco e riducono in modo efficace il numero di operazioni di I/O necessarie.

Note specifiche su Azure

Il valore predefinito per il parametro del server shared_buffers viene calcolato quando si effettua il provisioning dell'istanza del server flessibile di Database di Azure per PostgreSQL, in base al nome del prodotto selezionato per il relativo calcolo. Eventuali modifiche successive della selezione del prodotto al calcolo che supporta il server flessibile non hanno alcun effetto sul valore predefinito per il shared_buffers parametro server di tale istanza.

Ogni volta che si modifica il prodotto assegnato a un'istanza, è necessario modificare anche il valore per il shared_buffers parametro in base ai valori nelle formule seguenti.

Per le macchine virtuali con un massimo di 2 GiB di memoria, la formula usata per calcolare il valore di shared_buffers è memoryGib * 16384.

Per le macchine virtuali con più di 2 GiB, la formula usata per calcolare il valore di shared_buffers è memoryGib * 32768.

In base alla formula precedente, nella tabella seguente sono elencati i valori su cui verrà impostato il parametro del server a seconda della quantità di memoria di cui è stato effettuato il provisioning:

temp_buffers

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:

• Informazioni dettagliate sulle prestazioni delle query: nella scheda Query principali per file temporanei identificare le query che generano file temporanei. Questa situazione indica la possibile necessità di aumentare il valore di work_mem.
• Guide alla risoluzione dei problemi: nella scheda File temporanei elevati delle guide alla risoluzione dei problemi identificare le query problematiche.

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.