Come posso risolvere i problemi causati da un evento di attesa LWLock:pg_stat_statements in Amazon RDS per PostgreSQL o Aurora compatibile con PostgreSQL?

Risoluzione

Il modulo pg_stat_statements monitora le statistiche delle istruzioni SQL. Per le istanze database PostgreSQL compatibili con PostgreSQL 11 o versioni successive, la libreria pg_stat_statements viene caricata per impostazione predefinita. Per ulteriori informazioni, consulta pg_stat_statements sul sito web PostgreSQL.

Quando il numero di istruzioni univoche monitorate supera il valore pg_stat_statements.max, PostgreSQL dealloca le statistiche per le query eseguite meno frequentemente dalla tabella hash nella memoria condivisa. La deallocazione crea spazio per nuove voci.

Durante la deallocazione, PostgreSQL utilizza LWLock per la tabella hash per impedire l'accesso simultaneo. Ciò potrebbe bloccare i processi di backend simultanei e potresti visualizzare l'evento di attesa LWLock:pg_stat_statements in Approfondimenti sulle prestazioni.

Nota: se la tabella hash alloca frequentemente voci, le prestazioni complessive del carico di lavoro potrebbero peggiorare.

Aumenta il modulo pg_stat_statements.max

Per ridurre gli eventi di attesa LWLock:pg_stat_statements, aumenta il valore pg_stat_statements.max nel gruppo di parametri. Per ulteriori informazioni, consulta pg_stat_statements.max sul sito web PostgreSQL.

Nota: quando aumenti il valore pg_stat_statements.max, la tabella hash consuma memoria condivisa aggiuntiva per archiviare più informazioni sulle istruzioni SQL.

Per Amazon RDS per PostgreSQL, puoi modificare pg_stat_statements.max nel gruppo di parametri del database.

Per Aurora compatibile con PostgreSQL, puoi modificare il valore nel gruppo di parametri del cluster di database o nel gruppo di parametri del database.

Dopo aver modificato pg_stat_statements.max nel gruppo di parametri, riavvia l'istanza database per applicare la modifica. Durante il riavvio, potrebbe verificarsi una breve interruzione. Per ulteriori informazioni, consulta Riavvio di un'istanza database e Riavvio di un'istanza database in un cluster Aurora.

Non puoi modificare i parametri nel gruppo di parametri del database predefinito o nel gruppo di parametri del cluster di database predefinito. Per modificare i parametri nei gruppi predefiniti, crea un gruppo di parametri del database personalizzato o un gruppo di parametri del cluster di database personalizzato. Quindi associalo all'istanza database o al cluster di database.

Nota: puoi archiviare testi di query lunghi in un file su disco separato. Se il file diventa troppo grande a causa della lunghezza delle query o dei valori pg_stat_statements.max elevati, tutti i testi delle query potrebbero essere eliminati. Quindi i campi pg_stat_statements.query potrebbero diventare vuoti. Per ulteriori informazioni, consulta pg_stat_statements -- track statistics of SQL planning and execution (pg_stat_statements -- monitoraggio delle statistiche della pianificazione e dell’esecuzione di SQL) sul sito web PostgreSQL.

Riduci le voci pg_stat_statements

È consigliabile utilizzare PostgreSQL versione 17 per ridurre le voci pg_stat_statements. Ad esempio, quando utilizzi PostgreSQL versioni 17 e successive, puoi sostituire i nomi SAVEPOINT costanti con segnaposto. Ad esempio, puoi salvare SAVEPOINT sp1 e SAVEPOINT sp2 come due diverse voci pg_stat_statements nelle versioni precedenti di PostgreSQL. Tuttavia, nelle versioni 17 e successive di PostgreSQL, queste due istruzioni vengono archiviate come un'unica voce, ad esempio come SAVEPOINT $1. Per ulteriori informazioni, consulta pg_stat_statements sul sito web PostgreSQL.

Se non riesci a eseguire l'aggiornamento a PostgreSQL 17 o versioni successive, verifica se l'applicazione, gli OBJECT-Relational Mapper (ORM) e i driver del database emettono automaticamente i comandi SQL come SAVEPOINT. I comandi SAVEPOINT automatici potrebbero aumentare il numero di voci pg_stat_statements.

Verifica quante deallocazioni sono avvenute

PostgreSQL versione 14 e successive

Per verificare il numero totale di deallocazioni, esamina la colonna dealloc della vista pg_stat_statements_info. Puoi utilizzare queste informazioni per modificare pg_stat_statements.max in modo appropriato. Per ulteriori informazioni, consulta pg_stat_statements_info view (Vista pg_stat_statements_info) sul sito web PostgreSQL.

Per verificare il numero di deallocazioni in un determinato periodo di tempo, seleziona periodicamente la vista pg_stat_statements_info e calcola la differenza rispetto a pg_stat_statements_info.dealloc.

Quando selezioni la vista pg_stat_statements_info, potresti ricevere il messaggio di errore "ERROR: relation 'pg_stat_statements_info' does not exist". L'errore si verifica quando pg_stat_statements viene caricato solo in memoria ma non viene installato nel database. Per risolverlo, installa l'estensione pg_stat_statements nel database a cui si connette l'applicazione. Connettiti al database, quindi esegui questa istruzione SQL per installare pg_stat_statements nel database:

CREATE EXTENSION pg_stat_statements;

Per ulteriori informazioni, vedere CREATE EXTENSION sul sito web PostgreSQL.