Come posso identificare e risolvere i problemi di prestazioni e di query a esecuzione lenta nella mia istanza database Amazon RDS per PostgreSQL o compatibile con Aurora PostgreSQL?

Risoluzione

Hardware sottodimensionato, variazioni del carico di lavoro, aumento del traffico, problemi di memoria o query non ottimizzate influiscono sulle prestazioni dell'istanza database. Per risolvere i problemi di prestazioni, procedi come segue.

Identificazione della causa

Controllo delle metriche CloudWatch

Per identificare i colli di bottiglia delle prestazioni causati da risorse insufficienti, monitora le metriche CPUUtilization, FreaableMemory e SwapUsage di Amazon CloudWatch.

Quando l'utilizzo della CPU è elevato, un carico di lavoro attivo sull'istanza database richiede più risorse di CPU. La scarsa disponibilità di memoria per il carico di lavoro causa un frequente swapping che si traduce in un elevato utilizzo della memoria e dello spazio di swap. Query di lunga durata, un aumento improvviso del traffico o un gran numero di connessioni inattive possono causare un elevato utilizzo della CPU e delle risorse di memoria.

Per visualizzare le query attive con il runtime, esegui il seguente comando:

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Per visualizzare le connessioni inattive nel database, esegui il seguente comando:

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Per terminare le connessioni inattive, esegui il seguente comando:

SELECT pg_terminate_backend(example-pid);

Nota: sostituisci example-pid con l'ID processo della connessione inattiva.

Per verificare che l'istanza database raggiunga il throughput di rete previsto, controlla le metriche NetworkReceiveThroughput e NetworkTransmitThroughput. Classi di istanze Amazon Elastic Block Service (Amazon EBS) sottodimensionate o non ottimizzate possono influire sul throughput di rete e causare istanze lente. Un basso throughput di rete può comportare risposte lente per tutte le richieste dell'applicazione, a prescindere dalle prestazioni del database.

Per valutare le prestazioni I/O, controlla le metriche ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, ReadThroughput, WriteThroughput e DiskQueueDepth. Per ulteriori informazioni, consulta Come posso risolvere i problemi di latenza dei volumi Amazon EBS causata da un collo di bottiglia IOPS sulla mia istanza Amazon RDS?

Utilizzo di Monitoraggio avanzato

Utilizza Monitoraggio avanzato per visualizzare le metriche a livello di sistema operativo ed elencare i primi 100 processi con uso intensivo di CPU e memoria. Attiva Monitoraggio avanzato con Granularità impostata su 1 per identificare i problemi di prestazioni intermittenti sull'istanza database.

Valuta le metriche del sistema operativo disponibili per diagnosticare i problemi di prestazioni relativi a CPU, carico di lavoro, I/O, memoria e rete. Dall'elenco dei processi, identifica i processi con valori elevati per CPU% o Mem%.

Esempio:

NOME	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]	250,66 MB	27,7 MB	85,93	2,21	illimitato

Connettiti al database ed esegui la seguente query per trovare la connessione con CPU elevata nel database:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Nota: sostituisci 10322 con l'ID processo della connessione.

Controllo delle metriche di Approfondimenti sulle prestazioni

Utilizza Approfondimenti sulle prestazioni per valutare i carichi di lavoro del database per attese, SQL, host o utenti. Puoi anche ottenere il database e le metriche a livello SQL.

Utilizza la scheda SQL principale nella dashboard di Approfondimenti sulle prestazioni per visualizzare le istruzioni SQL che contribuiscono maggiormente al carico del database. Se il carico del database o il carico per attese (AAS) è superiore a vCPU max, il carico di lavoro sulla classe dell'istanza database viene limitato.

Utilizza la latenza media per chiamata nelle statistiche SQL per visualizzare il tempo di esecuzione medio di una query. SQL principale si basa sul tempo di esecuzione totale. Di conseguenza, l'SQL con il tempo di esecuzione più elevato è spesso diverso dall'SQL che contribuisce maggiormente al carico del database.

Controllo delle statistiche del database

Per valutare le prestazioni del database in PostgreSQL, controlla le statistiche di distribuzione dei dati, le statistiche estese e le statistiche di monitoraggio. Per informazioni sulle statistiche, consulta Understanding statistics in PostgreSQL.

Controllo degli strumenti nativi del database

Per identificare le query lente, utilizza lo strumento pgbadger nativo sul sito web di GitHub. Per ulteriori informazioni, consulta Optimizing and tuning queries in Amazon RDS for PostgreSQL based on native and external tools.

Ottimizzazione delle prestazioni

Messa a punto delle impostazioni di memoria

Puoi impostare il parametro shared_buffers su un valore che aiuti a migliorare le prestazioni delle query.

I parametri work_mem e maintenance_work_mem definiscono la quantità di memoria utilizzata per i processi di backend. Per ulteriori informazioni, consulta 20.4 Resource consumption sul sito web di PostgreSQL. Se si verifica spesso un uso elevato di memoria sull'istanza database, riduci i valori dei parametri nel gruppo di parametri personalizzati collegato all'istanza.

Utilizzo della gestione del piano di query compatibile con Aurora PostgreSQL

Utilizza la gestione del piano di query compatibile con Aurora PostgreSQL per controllare come e quando i piani di esecuzione delle query vengono modificati. Per ulteriori informazioni, consulta Best practices for Aurora PostgreSQL query plan management (Best practice per la gestione del piano di query Aurora PostgreSQL).

Risoluzione dei problemi relativi alle query a esecuzione lenta

Problemi di infrastruttura, pianificazione non ottimizzata delle query o un elevato utilizzo delle risorse complessive causano l'esecuzione lenta delle query. Il pianificatore di query di PostgreSQL utilizza le statistiche delle tabelle per creare piani di query. Le modifiche allo schema e le vecchie statistiche potrebbero influenzare i piani. Anche le tabelle e gli indici sovraccarichi possono causare query a esecuzione lenta.

Quando una tabella raggiunge la soglia di tuple morte, il daemon autovacuum crea processi worker autovacuum che rimuovono le tuple morte dalla tabella. Il daemon autovacuum esegue anche l'operazione ANALYZE che aggiorna le statistiche della tabella.

Esegui la seguente query per verificare la presenza di tuple morte e di operazioni di autovacuum o vacuum e per analizzare o analizzare automaticamente le esecuzioni:

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Utilizza la vista pg\ _stat\ _activity per trovare i dati correlati alle attività correnti, come l'ID processo di backend o la query. Per trovare query a lunga esecuzione, esegui la seguente query:

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU'
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

Le query che attendono i blocchi potrebbero essere lente. Per verificare se una query è in attesa di blocchi, esegui la seguente query:

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

Utilizza pg\ _stat\ _statements per visualizzare le statistiche delle query. Prima di creare l'estensione pg\ _stat_statements, aggiungi la voce pg\ _stat\ _statements a shared_preload_libraries. Per creare l'estensione pg\ _stat_statements all'interno del database, esegui la seguente query:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Nota: puoi modificare i parametri per pg_stats_statements solo quando un gruppo di parametri personalizzati è collegato all'istanza database.

Per identificare le query SQL che influiscono sulle prestazioni dell'istanza database, esegui le seguenti query.

PostgreSQL versioni 12 e precedenti:

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL versioni 13 e successive:

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Per trovare le query con un tasso di occorrenze nella cache del buffer più basso, esegui le seguenti query.

PostgreSQL versioni 12 e precedenti:

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM pg_stat_statements ORDER BY total_time
DESC LIMIT 10;

PostgreSQL versioni 13 e successive:

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

Per trovare query a lunga esecuzione o piani di query nei log di errore del database, configura il parametro log_min_duration_statement per l'istanza database, quindi utilizza il modulo auto_explain.

Puoi anche utilizzare i comandi explain ed explain analyze per ottenere il piano di query. Utilizza il modulo auto_explain o i comandi explain per identificare il modo in cui è possibile ottimizzare la messa a punto delle query. Per ulteriori informazioni, consulta 14.1 Using EXPLAIN e F3. auto_explain - log execution plans of slow queries sul sito web di PostgreSQL.

Se il sistema è stato ottimizzato e si riscontrano ancora problemi di prestazioni, è consigliabile aumentare verticalmente la classe di istanza database. Quando si aumenta verticalmente l'istanza database, si allocano più risorse di calcolo e di memoria.

Informazioni correlate

Come posso risolvere i problemi di utilizzo elevato di CPU per Amazon RDS o Amazon Aurora PostgreSQL?

Working with parameters on your RDS for PostgreSQL DB instance (Utilizzo dei parametri sull'istanza database RDS per PostgreSQL)

Perché la mia istanza database Amazon RDS utilizza la memoria di swap quando la memoria è sufficiente?