Risolvere i problemi di utilizzo elevato delle operazioni di I/O al secondo in Database di Azure per PostgreSQL - Server flessibile

SI APPLICA A: Database di Azure per PostgreSQL - Server flessibile

Questo articolo illustra come identificare rapidamente la causa radice dell'utilizzo elevato delle operazioni di I/O al secondo (operazioni di input/output al secondo) e fornisce azioni correttive per controllare l'utilizzo delle operazioni di I/O al secondo quando si usa Database di Azure per PostgreSQL server flessibile.

In questo articolo vengono illustrate le operazioni seguenti:

• Informazioni sulle guide alla risoluzione dei problemi per identificare e ottenere raccomandazioni per attenuare le cause radice.
• Usare gli strumenti per identificare l'utilizzo elevato di input/output (I/O), ad esempio Metriche di Azure, Query Store e pg_stat_statements.
• Identificare le cause radice, ad esempio query a esecuzione prolungata, intervalli di checkpoint, un processo daemon autovacuum di interruzione e un utilizzo elevato dell'archiviazione.
• Risolvere l'utilizzo elevato di I/O usando Explain Analyze, ottimizzare i parametri del server correlati ai checkpoint e ottimizzare il daemon autovacuum.

Guide alla risoluzione dei problemi

È possibile trovare le guide alla risoluzione dei problemi di funzionalità disponibili nel portale del server flessibile Database di Azure per PostgreSQL la probabile causa radice e le raccomandazioni per ridurre lo scenario di utilizzo elevato delle operazioni di I/O al secondo. Come configurare le guide alla risoluzione dei problemi per usarle, seguire le guide alla risoluzione dei problemi di installazione.

Strumenti per identificare un utilizzo elevato di I/O

Prendere in considerazione gli strumenti seguenti per identificare l'utilizzo elevato delle operazioni di I/O.

Metriche di Azure

Metriche di Azure è un buon punto di partenza per controllare l'utilizzo di I/O per una data e un periodo definiti. Le metriche forniscono informazioni sul tempo durante il quale l'utilizzo di I/O è elevato. Confrontare i grafici di operazioni di I/O di scrittura, operazioni di I/O di lettura, velocità effettiva di lettura e velocità effettiva di scrittura per individuare i tempi in cui il carico di lavoro causa un utilizzo elevato delle operazioni di I/O. Per il monitoraggio proattivo, è possibile configurare gli avvisi sulle metriche. Per indicazioni dettagliate, vedere Metriche di Azure.

Archivio query

La funzionalità Query Store acquisisce automaticamente la cronologia delle query e delle statistiche di runtime e le mantiene per la revisione. Seziona i dati in base al tempo per visualizzare i modelli di utilizzo temporali. I dati per tutti gli utenti, i database e le query vengono archiviati in un database denominato azure_sys nell'istanza del server flessibile Database di Azure per PostgreSQL. Per indicazioni dettagliate, vedere Monitorare le prestazioni con Query Store.

Usare l'istruzione seguente per visualizzare le prime cinque istruzioni SQL che usano le operazioni di I/O:

select * from query_store.qs_view qv where is_system_query is FALSE
order by blk_read_time + blk_write_time  desc limit 5;

Estensione pg_stat_statements

L'estensione pg_stat_statements consente di identificare le query che utilizzano I/O nel server.

Usare l'istruzione seguente per visualizzare le prime cinque istruzioni SQL che usano le operazioni di I/O:

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;

Identificare le cause radice

Se i livelli di consumo di I/O sono elevati in generale, le cause principali potrebbero essere le seguenti:

Transazioni con esecuzione prolungata

Le transazioni con esecuzione prolungata possono utilizzare operazioni di I/O, che possono causare un utilizzo elevato delle operazioni di I/O.

La query seguente consente di identificare le connessioni in esecuzione per il tempo più lungo:

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

Intervalli dei checkpoint

L'I/O elevato può essere visualizzato anche negli scenari in cui si verifica un checkpoint troppo frequentemente. Un modo per identificare questo problema consiste nel controllare il file di log del server flessibile Database di Azure per PostgreSQL per il testo del log seguente: "LOG: i checkpoint si verificano troppo frequentemente".

È anche possibile analizzare usando un approccio in cui vengono salvati snapshot periodici di pg_stat_bgwriter con un timestamp. Usando gli snapshot salvati, è possibile calcolare l'intervallo medio del checkpoint, il numero di checkpoint richiesti e il numero di checkpoint programmati.

Processo daemon autovacuum di interruzione

Eseguire la query seguente per monitorare autovacuum:

SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE n_live_tup > 0;

La query viene usata per verificare la frequenza con cui le tabelle nel database vengono sottoposte a vuoto.

• last_autovacuum: data e ora dell'ultima esecuzione dell'autovacuum nella tabella.
• autovacuum_count: numero di volte in cui la tabella è stata vuoto.
• autoanalyze_count: numero di volte in cui la tabella è stata analizzata.

Risolvere l'utilizzo elevato di operazioni di I/O

Per risolvere l'utilizzo elevato di I/O, è possibile usare uno dei tre metodi seguenti.

Il comando EXPLAIN ANALYZE

Dopo aver identificato la query che utilizza operazioni di I/O elevate, usare EXPLAIN ANALYZE per analizzare ulteriormente la query e ottimizzarla. Per altre informazioni sul EXPLAIN ANALYZE comando, vedere il piano EXPLAIN.

Terminare le transazioni con esecuzione prolungata

È possibile considerare l'eliminazione di una transazione a esecuzione prolungata come opzione.

Per terminare l'ID processo di una sessione, è necessario rilevare il PID usando la query seguente:

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

È anche possibile filtrare in base ad altre proprietà, ad esempio usename (nome utente) o datname (nome database).

Dopo aver ottenuto il PID della sessione, è possibile terminarlo usando la query seguente:

SELECT pg_terminate_backend(pid);

Ottimizzare i parametri del server

Se si osserva che il checkpoint si verifica troppo frequentemente, aumentare il parametro del server fino a quando la max_wal_size maggior parte dei checkpoint non viene determinata dal tempo, anziché richiesta. Alla fine, il 90% o più deve essere basato sul tempo e l'intervallo tra due checkpoint deve essere vicino al checkpoint_timeout valore impostato nel server.

• max_wal_size: ore lavorative di punta sono un buon momento per arrivare a un max_wal_size valore. Per arrivare a un valore, eseguire le operazioni seguenti:

  1. Eseguire la query seguente per ottenere l'LSN WAL corrente e quindi prendere nota del risultato:

     select pg_current_wal_lsn();
     

  2. Attendere alcuni checkpoint_timeout secondi. Eseguire la query seguente per ottenere l'LSN WAL corrente e quindi prendere nota del risultato:

     select pg_current_wal_lsn();
     

  3. Eseguire la query seguente, che usa i due risultati, per verificare la differenza, in gigabyte (GB):

     select round (pg_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;
     

• checkpoint_completion_target: è consigliabile impostare il valore su 0,9. Ad esempio, un valore pari a 0,9 per un checkpoint_timeout valore di 5 minuti indica che la destinazione per completare un checkpoint è di 270 secondi (0,9*300 secondi). Un valore pari a 0,9 fornisce un carico di I/O abbastanza coerente. Un valore aggressivo di potrebbe comportare un aumento del checkpoint_completion_target carico di I/O nel server.

• checkpoint_timeout: è possibile aumentare il checkpoint_timeout valore dal valore predefinito impostato nel server. Man mano che si aumenta il valore, prendere in considerazione che l'aumento aumenterebbe anche il tempo per il ripristino di arresto anomalo del sistema.

Ottimizzare l'autovacuum per ridurre le interruzioni

Per altre informazioni sul monitoraggio e l'ottimizzazione negli scenari in cui autovacuum è troppo problematico, vedere Ottimizzazione automatica.

Aumentare lo spazio di archiviazione

L'aumento dello spazio di archiviazione consente di aggiungere più operazioni di I/O al secondo al server. Per altre informazioni sull'archiviazione e sulle operazioni di I/O al secondo associate, vedere Opzioni di calcolo e archiviazione.

Contenuto correlato

• Risolvere i problemi e ottimizzare autovacuum
• Opzioni di calcolo e archiviazione