Come posso identificare cosa ha bloccato una query nella mia istanza database Amazon RDS per PostgreSQL o Aurora compatibile con PostgreSQL?

Risoluzione

Le transazioni non confermate possono bloccare nuove query, sospenderle o causarne l'esito negativo quando superano il timeout di attesa del blocco o il timeout dell'istruzione. Per risolvere il problema, identifica e arresta le transazioni non confermate.

Identifica le transazioni bloccate

Per identificare le transazioni bloccate, attiva Performance Insights e utilizza Database Insights. Utilizza il grafico di carico del database per visualizzare le dimensioni di carico del database per Attese, Host, SQL o Utente. Per ulteriori informazioni, consulta Monitoraggio del carico del database con Performance Insights in Amazon RDS.

Per identificare lo stato corrente della transazione bloccata, esegui questa istruzione:

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

Nota: sostituisci TABLE NAME con il nome o la condizione della tua tabella.

Se il valore nella colonna wait_event_type non è Lock (Blocco), è presente un collo di bottiglia nelle prestazioni per risorse come CPU, spazio di archiviazione o capacità di rete. Per risolvere i problemi di prestazioni, ottimizza le prestazioni del database. Ad esempio, puoi aggiungere indici, riscrivere query o eseguire comandi vacuum e analyze. Per ulteriori informazioni, consulta Best practice per l'utilizzo di PostgreSQL.

Se il valore della colonna wait_event_type è Lock (Blocco), altre transazioni o query bloccano la query. Per identificare la causa del blocco della transazione, esegui questa istruzione:

SELECT blocked_locks.pid     AS blocked_pid,       blocked_activity.usename  AS blocked_user,  
       blocked_activity.client_addr as blocked_client_addr,  
       blocked_activity.client_hostname as blocked_client_hostname,  
       blocked_activity.client_port as blocked_client_port,  
       blocked_activity.application_name as blocked_application_name,  
       blocked_activity.wait_event_type as blocked_wait_event_type,  
       blocked_activity.wait_event as blocked_wait_event,  
       blocked_activity.query    AS blocked_statement,  
       blocking_locks.pid     AS blocking_pid,  
       blocking_activity.usename AS blocking_user,  
       blocking_activity.client_addr as blocking_user_addr,  
       blocking_activity.client_hostname as blocking_client_hostname,  
       blocking_activity.client_port as blocking_client_port,  
       blocking_activity.application_name as blocking_application_name,  
       blocking_activity.wait_event_type as blocking_wait_event_type,  
       blocking_activity.wait_event as blocking_wait_event,  
       blocking_activity.query   AS current_statement_in_blocking_process  
 FROM  pg_catalog.pg_locks         blocked_locks  
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid  
    JOIN pg_catalog.pg_locks         blocking_locks   
        ON blocking_locks.locktype = blocked_locks.locktype  
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE  
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation  
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page  
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple  
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid  
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid  
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid  
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid  
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid  
        AND blocking_locks.pid != blocked_locks.pid  
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

Per identificare le sessioni che bloccano le transazioni, esamina i valori blocking_user, blocking_user_addr e blocking_client_port. Nel seguente esempio di output, l'utente master utilizza psql per bloccare la transazione in esecuzione sull'host 27.0.3.146. Il valore blocking_pid è 8740.

blocked_pid                           | 9069blocked_user                          | master  
blocked_client_addr                   | 27.0.3.146  
blocked_client_hostname               |  
blocked_client_port                   | 50035  
blocked_application_name              | psql  
blocked_wait_event_type               | Lock  
blocked_wait_event                    | transactionid  
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;  
blocking_pid                          | 8740  
blocking_user                         | master  
blocking_user_addr                    | 27.0.3.146  
blocking_client_hostname              |  
blocking_client_port                  | 26259  
blocking_application_name             | psql  
blocking_wait_event_type              | Client  
blocking_wait_event                   | ClientRead  
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

Arresta le transazioni

Prima di arrestare le transazioni, valuta il potenziale effetto che ogni transazione ha sullo stato del database e dell'applicazione.

Per arrestare le transazioni, esegui questa istruzione:

SELECT pg_terminate_backend(PID);

Nota: sostituisci PID con il valore blocking_pid della transazione.

Informazioni correlate

Eventi di attesa di Amazon Aurora PostgreSQL