Continuazione dell'articolo ««.
Nell'articolo verrà esaminato e dimostrato, con richieste specifiche ed esempi, quale utile informazione si possa ottenere tramite la vista pg_stat_activity.
Attenzione.
A causa della novità dell'argomento e del periodo di test incompleto, l'articolo potrebbe contenere errori. Critiche e osservazioni sono benvenute e attese.
Dati di input
Storia della vista pg_stat_statements
pg_stat_history
CREATE TABLE pg_stat_history (
id SERIAL,
snapshot_timestamp timestamp without time zone,
database_id integer,
dbid oid,
userid oid,
queryid bigint,
query text,
calls bigint,
total_time double precision,
min_time double precision,
max_time double precision,
mean_time double precision,
stddev_time double precision,
rows bigint,
shared_blks_hit bigint,
shared_blks_read bigint,
shared_blks_dirtied bigint,
shared_blks_written bigint,
local_blks_hit bigint,
local_blks_read bigint,
local_blks_dirtied bigint,
local_blks_written bigint,
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision,
blk_write_time double precision,
baseline_id integer );La tabella viene popolata ogni ora, utilizzando dblink per connettersi al database di destinazione. La colonna più interessante e utile nella tabella, naturalmente, queryid.
Storia della vista pg_stat_activity
archive_pg_stat_activity
CREA TABELLA archive_pg_stat_activity
(
timepoint timestamp senza fuso orario,
datid oid,
datname nome,
pid intero,
usesysid oid,
usename nome,
application_name testo,
client_addr inet,
client_hostname testo,
client_port intero,
backend_start timestamp senza fuso orario,
xact_start timestamp senza fuso orario,
query_start timestamp senza fuso orario,
state_change timestamp senza fuso orario,
wait_event_type testo,
wait_event testo,
state testo,
backend_xid xid,
backend_xmin xid,
query testo,
backend_type testo,
queryid bigint
);La tabella rappresenta una tabella history_pg_stat_activity partizionata per ore (Maggiori dettagli qui — e qui —
Output
CLUSTER TEMPO CPU (SISTEMA + CLIENTI )
Richiesta
CON
t AS
(
SELEZIONA
date_trunc('secondo', timepoint)
DA activity_hist.archive_pg_stat_activity aa
DOVE timepoint TRA pg_stat_history_begin+(current_hour_diff * intervallo '1 ora') E pg_stat_history_end+(current_hour_diff * intervallo '1 ora') E
( aa.wait_event_type È NULL ) E
aa.state = 'attivo'
)
SELEZIONA count(*)
IN
DA t ;Esempio
CLUSTER TEMPO CPU (SISTEMA + CLIENTI ) : 28:37:46CLUSTER TEMPO DI ATTESA
Richiesta
CON
t AS
(
SELEZIONA
date_trunc('secondo', timepoint)
DA activity_hist.archive_pg_stat_activity aa
DOVE timepoint TRA pg_stat_history_begin+(current_hour_diff * intervallo '1 ora') E pg_stat_history_end+(current_hour_diff * intervallo '1 ora') E
( aa.wait_event_type È NON NULL ) E
aa.state = 'attivo'
)
SELEZIONA count(*)
IN
DA t ;Esempio
CLUSTER TEMPO DI ATTESA : 30:12:49Valori totali di pg_stat_statements
Richiesta
--TOTALE pg_stat
SELEZIONA
SUM(calls) AS calls, SUM(total_time) AS total_time, SUM(rows) AS rows ,
SUM(shared_blks_hit) AS shared_blks_hit, SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied, SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit , SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied , SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) AS temp_blks_written ,
SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time
IN
pg_total_stat_history_rec
DA
pg_stat_history
DOVE
snapshot_timestamp TRA pg_stat_history_begin E pg_stat_history_end E
queryid È NULL;SQL DBTIME — tempo totale di esecuzione delle query
Richiesta
dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;Esempio
SQL DBTIME : 136:49:36SQL CPU TIME — tempo CPU speso nell'esecuzione delle query
Richiesta
CON
t COME
(
SELEZIONA
date_trunc('second', timepoint)
DA activity_hist.archive_pg_stat_activity aa
DOVE timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E
( aa.wait_event_type È NULL ) E
backend_type = 'client backend' E
aa.state = 'active'
)
SELEZIONA count(*)
IN
cpu_total
DA t ;Esempio
SQL CPU TIME : 27:40:15SQL WAITINGS TIME — tempo totale di attesa per le query
Richiesta
CON
t COME
(
SELEZIONA
date_trunc('second', timepoint)
DA activity_hist.archive_pg_stat_activity aa
DOVE timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E
( aa.wait_event_type È NOT NULL ) E
aa.state = 'active' E
backend_type = 'client backend'
)
SELEZIONA count(*)
IN waiting_total
DA t ;Esempio
TEMPI DI ATTESA SQL : 30:04:09Le seguenti query sono triviali e per risparmiare spazio vengono omessi i dettagli di implementazione:
Esempio
| IOTIME SQL : 19:44:50
| TEMPO DI LETTURA SQL : 19:44:32
| TEMPO DI SCRITTURA SQL : 00:00:17
|
| CHIAMATE SQL : 12188248
-------------------------------------------------------------
| LETTURE DI BLOCCHI CONDIVISI SQL : 7997039120
| SUCCESSI DI BLOCCHI CONDIVISI SQL : 8868286092
| % SUCCESSI LETTURE BLOCCHI CONDIVISI SQL : 110.89
| BLOCCHI CONDIVISI SQL SPORCHI : 419945
| BLOCCHI CONDIVISI SCRITTI SQL : 19857
|
| LETTURE DI BLOCCHI TEMPORANEI SQL : 7836169
| BLOCCHI TEMPORANEI SCRITTI SQL : 10683938
Passiamo alla sezione più interessante
STATISTICHE DI ATTESA
TOP 10 ATTESE PER TEMPO TOTALE DI ATTESA NEI PROCESSI CLIENTI
Richiesta
SELEZIONA
wait_event_type, wait_event ,
get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * interval '1 hour') ,pg_stat_history_end+(current_hour_diff * interval '1 hour') ) come durata
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E backend_type != 'client backend' E wait_event_type È NOT NULL
RAGGRUPPA PER
wait_event_type, wait_event
ORDINA PER 3 DESC
LIMITA 10Esempio
+------------------------------------------------------------------------------------ | I 10 PROCESSI CON TEMPI DI ATTESA TOTALE +-----+------------------------------+--------------------+-------------------- | #| tipo_evento_attesa| evento_attesa| durata +-----+------------------------------+--------------------+-------------------- | 1| Attività| LogicalLauncherMain| 10:43:28 | 2| Attività| AutoVacuumMain| 10:42:49 | 3| Attività| WalWriterMain| 10:28:53 | 4| Attività| CheckpointerMain| 10:23:50 | 5| Attività| BgWriterMain| 09:11:59 | 6| Attività| BgWriterHibernate| 01:37:46 | 7| IO| BufFileWrite| 00:02:35 | 8| LWLock| buffer_mapping| 00:01:54 | 9| IO| DataFileRead| 00:01:23 | 10| IO| WALWrite| 00:00:59 +-----+------------------------------+--------------------+--------------------
TOP 10 ATTESE PER TEMPO TOTALE DI ATTESA NEI PROCESSI CLIENTI
Richiesta
SELEZIONA
tipo_evento_attesa , evento_attesa ,
get_clients_waiting_duration( tipo_evento_attesa , evento_attesa , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durata
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E backend_type = 'client backend' E tipo_evento_attesa È NON NULLA
GRUPPA PER tipo_evento_attesa, evento_attesa
ORDINA PER 3 DESC
LIMIT 10Esempio
+-----+------------------------------+--------------------+--------------------+---------- | #| tipo_evento_attesa| evento_attesa| durata| % dbtime +-----+------------------------------+--------------------+--------------------+---------- | 1| Lock| transactionid| 08:16:47| 6.05 | 2| IO| DataFileRead| 06:13:41| 4.55 | 3| Timeout| PgSleep| 02:53:21| 2.11 | 4| LWLock| buffer_mapping| 00:40:42| 0.5 | 5| LWLock| buffer_io| 00:17:17| 0.21 | 6| IO| BufFileWrite| 00:01:34| 0.02 | 7| Lock| tuple| 00:01:32| 0.02 | 8| Client| ClientRead| 00:01:19| 0.02 | 9| IO| BufFileRead| 00:00:37| 0.01 | 10| LWLock| buffer_content| 00:00:08| 0 +-----+------------------------------+--------------------+--------------------+----------
TIPI DI ATTESA PER TEMPO TOTALE DI ATTESA, PER PROCESSI DI SISTEMA
Richiesta
SELEZIONARE
tipo_evento_attesa ,
get_system_waiting_type_duration( tipo_evento_attesa , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durata
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E backend_type != 'client backend' E tipo_evento_attesa è NON NULL
GROUP BY tipo_evento_attesa
ORDINA PER 2 DESCEsempio
+-----+------------------------------+-------------------- | #| wait_event_type| duration +-----+------------------------------+-------------------- | 1| Attività| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock| 00:03:02 +-----+------------------------------+--------------------
TIPI DI ATTESA PER TEMPO TOTALE DI ATTESA, PER I PROCESSI CLIENTI
Richiesta
SELEZIONA
wait_event_type ,
get_clients_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL
GRUPPO PER wait_event_type
ORDINA PER 2 DESCEsempio
+-----+------------------------------+--------------------+-------------------- | #| wait_event_type| duration| % dbtime +-----+------------------------------+--------------------+-------------------- | 1| Lock| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| Timeout| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| Client| 00:01:19| 0.02 | 6| IPC| 00:00:04| 0 +-----+------------------------------+--------------------+--------------------
Durata delle attese, per processi di sistema e singole richieste.
ATTESE PER I PROCESSI DI SISTEMA
Richiesta
SELEZIONA
backend_type , datname , wait_event_type , wait_event , get_backend_type_waiting_duration( backend_type , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * intervallo '1 ora') , pg_stat_history_end+(current_hour_diff * intervallo '1 ora') ) come durata
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * intervallo '1 ora') E pg_stat_history_end+(current_hour_diff * intervallo '1 ora') E backend_type != 'client backend' E wait_event_type È NON NULL
RAGGRUPPA PER backend_type , datname , wait_event_type , wait_event
ORDINA PER 5 DESCEsempio
+-----+-----------------------------+----------+--------------------+----------------------+-------------------- | #| backend_type| dbname| wait_event_type| wait_event| duration +-----+-----------------------------+----------+--------------------+----------------------+-------------------- | 1| lanciatore di replica logica | | Attività | LogicalLauncherMain| 10:43:28 | 2| lanciatore autovacuum| | Attività | AutoVacuumMain| 10:42:49 | 3| walwriter | | Attività | WalWriterMain| 10:28:53 | 4| controllore | | Attività | CheckpointerMain| 10:23:50 | 5| scrittore in background | | Attività | BgWriterMain| 09:11:59 | 6| scrittore in background | | Attività | BgWriterHibernate| 01:37:46 | 7| lavoratore parallelo | tdb1| IO| BufFileWrite| 00:02:35 | 8| lavoratore parallelo | tdb1| LWLock| buffer_mapping| 00:01:41 | 9| lavoratore parallelo | tdb1| IO| DataFileRead| 00:01:22 | 10| lavoratore parallelo | tdb1| IO| BufFileRead| 00:00:59 | 11| walwriter | | IO| WALWrite| 00:00:57 | 12| lavoratore parallelo | tdb1| LWLock| buffer_io| 00:00:47 | 13| lavoratore autovacuum | tdb1| LWLock| buffer_mapping| 00:00:13 | 14| scrittore in background | | IO| DataFileWrite| 00:00:12 | 15| controllore | | IO| DataFileWrite| 00:00:11 | 16| walwriter | | LWLock| WALWriteLock| 00:00:09 | 17| controllore | | LWLock| WALWriteLock| 00:00:06 | 18| scrittore in background | | LWLock| WALWriteLock| 00:00:06 | 19| walwriter | | IO| WALInitWrite| 00:00:02 | 20| lavoratore autovacuum | tdb1| LWLock| WALWriteLock| 00:00:02 | 21| walwriter | | IO| WALInitSync| 00:00:02 | 22| lavoratore autovacuum | tdb1| IO| DataFileRead| 00:00:01 | 23| controllore | | IO| ControlFileSyncUpdate| 00:00:01 | 24| scrittore in background | | IO| WALWrite| 00:00:01 | 25| scrittore in background | | IO| DataFileFlush| 00:00:01 | 26| controllore | | IO| SLRUFlushSync| 00:00:01 | 27| lavoratore autovacuum | tdb1| IO| WALWrite| 00:00:01 | 28| controllore | | IO| DataFileSync| 00:00:01 +-----+-----------------------------+----------+--------------------+----------------------+--------------------
ATTESA PER SQL — attese per singole query per queryid
Richiesta
SELEZIONA
queryid , datname , tipo_evento_attesa , evento_attesa , get_query_waiting_duration( queryid , tipo_evento_attesa , evento_attesa , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) come durata
DA
activity_hist.archive_pg_stat_activity aa
DOVE
timepoint TRA pg_stat_history_begin+(current_hour_diff * interval '1 hour') E pg_stat_history_end+(current_hour_diff * interval '1 hour') E backend_type = 'client backend' E tipo_evento_attesa È NON NULL E queryid È NON NULL
GROUP BY queryid , datname , tipo_evento_attesa , evento_attesa
ORDERINA PER 1 , 5 DESC Esempio
+-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | #| queryid| dbname| wait_event_type| wait_event| waitings| total | | | | | | duration| duration +-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | 1| -8247416849404883188| tdb1| Client| ClientRead| 00:00:02| | 2| -6572922443698419129| tdb1| Client| ClientRead| 00:00:05| | 3| -6572922443698419129| tdb1| IO| DataFileRead| 00:00:01| | 4| -5917408132400665328| tdb1| Client| ClientRead| 00:00:04| | 5| -4091009262735781873| tdb1| Client| ClientRead| 00:00:03| | 6| -1473395109729441239| tdb1| Client| ClientRead| 00:00:01| | 7| 28942442626229688| tdb1| IO| BufFileWrite| 00:01:34| 00:46:06 | 8| 28942442626229688| tdb1| LWLock| buffer_mapping| 00:01:05| 00:46:06 | 9| 28942442626229688| tdb1| IO| DataFileRead| 00:00:44| 00:46:06 | 10| 28942442626229688| tdb1| IO| BufFileRead| 00:00:37| 00:46:06 | 11| 28942442626229688| tdb1| LWLock| buffer_io| 00:00:35| 00:46:06 | 12| 28942442626229688| tdb1| Client| ClientRead| 00:00:05| 00:46:06 | 13| 28942442626229688| tdb1| IPC| MessageQueueReceive| 00:00:03| 00:46:06 | 14| 28942442626229688| tdb1| IPC| BgWorkerShutdown| 00:00:01| 00:46:06 | 15| 389015618226997618| tdb1| Lock| transactionid| 03:55:09| 04:14:15 | 16| 389015618226997618| tdb1| IO| DataFileRead| 03:23:09| 04:14:15 | 17| 389015618226997618| tdb1| LWLock| buffer_mapping| 00:12:09| 04:14:15 | 18| 389015618226997618| tdb1| LWLock| buffer_io| 00:10:18| 04:14:15 | 19| 389015618226997618| tdb1| Lock| tuple| 00:00:35| 04:14:15 | 20| 389015618226997618| tdb1| LWLock| WALWriteLock| 00:00:02| 04:14:15 | 21| 389015618226997618| tdb1| IO| DataFileWrite| 00:00:01| 04:14:15 | 22| 389015618226997618| tdb1| LWLock| SyncScanLock| 00:00:01| 04:14:15 | 23| 389015618226997618| tdb1| Client| ClientRead| 00:00:01| 04:14:15 | 24| 734234407411547467| tdb1| Client| ClientRead| 00:00:11| | 25| 734234407411547467| tdb1| LWLock| buffer_mapping| 00:00:05| | 26| 734234407411547467| tdb1| IO| DataFileRead| 00:00:02| | 27| 1237430309438971376| tdb1| LWLock| buffer_mapping| 00:02:18| 02:45:40 | 28| 1237430309438971376| tdb1| IO| DataFileRead| 00:00:27| 02:45:40 | 29| 1237430309438971376| tdb1| Client| ClientRead| 00:00:02| 02:45:40 | 30| 2404820632950544954| tdb1| Client| ClientRead| 00:00:01| | 31| 2515308626622579467| tdb1| Client| ClientRead| 00:00:02| | 32| 4710212362688288619| tdb1| LWLock| buffer_mapping| 00:03:08| 02:18:21 | 33| 4710212362688288619| tdb1| IO| DataFileRead| 00:00:22| 02:18:21 | 34| 4710212362688288619| tdb1| Client| ClientRead| 00:00:06| 02:18:21 | 35| 4710212362688288619| tdb1| LWLock| buffer_io| 00:00:02| 02:18:21 | 36| 9150846928388977274| tdb1| IO| DataFileRead| 00:01:19| | 37| 9150846928388977274| tdb1| LWLock| buffer_mapping| 00:00:34| | 38| 9150846928388977274| tdb1| Client| ClientRead| 00:00:10| | 39| 9150846928388977274| tdb1| LWLock| buffer_io| 00:00:01| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
STATISTICHE SQL CLIENT — TOP richieste
Le richieste per ottenere nuovamente sono banali e, per risparmiare spazio, non sono riportate.
Esempi
+------------------------------------------------------------------------------------ | CLIENT SQL ordinati per Tempo di Esecuzione +--------------------+----------+----------+----------+----------+----------+-------------------- | tempo trascorso| chiamate| % dbtime| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+-------------------- | 04:14:15| 19| 3.1| 10.83| 11.52| tdb1| 389015618226997618 | 02:45:40| 746| 2.02| 4.23| 0.08| tdb1| 1237430309438971376 | 02:18:21| 749| 1.69| 3.39| 0.1| tdb1| 4710212362688288619 | 00:46:06| 375| 0.56| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordinati per Tempo di CPU +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | tempo cpu| chiamate| % dbtime|tempo_totale| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:59:49| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 01:10:12| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:56:15| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:15:35| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordinati per Tempo di Attesa I/O Utente +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | tempo di attesa io| chiamate| % dbtime|tempo_totale| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 03:23:10| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:02:54| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:00:27| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:00:22| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordinati per Letture Buffer Condivisi +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | letture buffer| chiamate| % dbtime|tempo_totale| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 1056388566| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 11709251| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 3439004| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 3373330| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordinati per Tempo di Letture Disco +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | tempo di lettura| chiamate| % dbtime|tempo_totale| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:16:30| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:04:50| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:01:10| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:00:57| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordinati per Esecuzioni +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | chiamate| righe| % dbtime|tempo_totale| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 749| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 746| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 375| 0| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 19| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 +--------------------+----------+----------+----------+----------+----------+----------+--------------------
Risultato
Utilizzando le query fornite e la reportistica ottenuta, è possibile avere una visione più completa per analizzare e risolvere i problemi di degrado delle prestazioni, sia per singole query che per l'intero cluster.
Sviluppo
I prossimi piani di sviluppo sono:
- Espandere la reportistica con una cronologia dei blocchi. Le query sono attualmente in fase di test e saranno presentate a breve.
- Utilizzare l'estensione TimescaleDB per memorizzare la cronologia di pg_stat_activity e pg_locks.
- Preparare una soluzione batch su GitHub per il deployment massivo sui database di produzione.
Continua...
Fonte: habr.com
