Condividi tramite

Risoluzione dei problemi di elevato utilizzo di IOPS nel server flessibile di Azure Database per PostgreSQL

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

Questo articolo illustra come identificare rapidamente la causa principale 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 il server flessibile di Database di Azure per PostgreSQL.

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 principali, 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

Utilizzando le guide alla risoluzione dei problemi disponibili nel portale del server flessibile Azure Database for PostgreSQL, è possibile individuare la probabile causa principale e le raccomandazioni per mitigare scenari di elevato utilizzo degli IOPS. Per configurare le guide alla risoluzione dei problemi e poterle utilizzare, seguire le istruzioni riportate in configurare guide alla risoluzione dei problemi.

Strumenti per identificare un utilizzo elevato di I/O

Valutare l'uso degli 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 momenti 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 conserva per la revisione. Seziona i dati in base al tempo per visualizzare i modelli di utilizzo temporali. I dati relativi a tutti gli utenti, tutti i database e tutte le query vengono archiviati in un database denominato azure_sys nell'istanza di Database di Azure per PostgreSQL - Server flessibile. 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;

Annotazioni

Quando si usa Query Store o pg_stat_statements per le colonne blk_read_time e blk_write_time da popolare, è necessario abilitare il parametro server track_io_timing. Per altre informazioni su track_io_timing, vedere Parametri del server.

Identificare le cause principali

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, il che può 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

Un’elevata attività di I/O può verificarsi anche in scenari in cui i checkpoint avvengono con troppa frequenza. Un modo per identificare questo problema consiste nel controllare il file di log del server flessibile di Database di Azure per PostgreSQL, cercando il seguente messaggio di log: "LOG: checkpoints avvengono troppo frequentemente."

È anche possibile indagare utilizzando un approccio in cui vengono salvati snapshot periodici di pg_stat_bgwriter con un timestamp. Utilizzando gli snapshot salvati, è possibile calcolare l'intervallo medio tra i 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 vaccum.

  • last_autovacuum: data e ora dell'ultima esecuzione dell'autovacuum nella tabella.
  • autovacuum_count: numero di volte in cui la tabella è stata sottoposta a vacuum.
  • 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.

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 comando EXPLAIN ANALYZE, vedere il piano EXPLAIN.

Terminare le transazioni con esecuzione prolungata

Valutare l'opzione di eliminare una transazione a esecuzione prolungata.

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 max_wal_size fino a quando la maggior parte dei checkpoint non viene determinata dal tempo, anziché da richiesta. Alla fine, il 90% o più deve essere basato sul tempo e l'intervallo tra due checkpoint deve essere vicino al valore checkpoint_timeout impostato nel server.

  • max_wal_size: ore lavorative di punta sono un buon momento per arrivare a un valore max_wal_size. 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 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 checkpoint_completion_target potrebbe comportare un aumento del carico di I/O nel server.

  • checkpoint_timeout: è possibile aumentare il valore dal valore checkpoint_timeout predefinito impostato nel server. Man mano che si aumenta il valore, tenere in considerazione che l'aumento comporta anche un aumento del tempo necessario per il ripristino a seguito dell'arresto anomalo del sistema.

Ottimizzare l'autovacuum per ridurre le interruzioni

Per altre informazioni sul monitoraggio e l'ottimizzazione in 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.

Contenuti correlati