ProHoster > Blogs > Administrācija > Viena metode darba slodzes profila un gaidīšanas vēstures iegūšanai PostgreSQL

Viena metode darba slodzes profila un gaidīšanas vēstures iegūšanai PostgreSQL

Raksta turpinājums "Mēģinājums izveidot PostgreSQL ASH analogu ".

Rakstā tiks apskatīts un, izmantojot konkrētus vaicājumus un piemērus, tiks parādīts, kādu noderīgu informāciju var iegūt, izmantojot pg_stat_activity skata vēsturi.

Brīdinājums.
Tēmas novitātes un nepabeigtā testēšanas perioda dēļ rakstā var būt kļūdas. Kritika un komentāri ir ļoti apsveicami un gaidīti.

Ievadiet datus

pg_stat_statements iesniegšanas vēsture

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 );

Tabula tiek aizpildīta katru stundu, izmantojot dblink uz mērķa datu bāzi. Interesantākā un noderīgākā tabula tabulā, protams queryid.

pg_stat_activity skatījumu vēsture

archive_pg_stat_activity

CREATE TABLE archive_pg_stat_activity
(
  timepoint timestamp without time zone,
  datid             oid, 
  datname           name,
  pid               integer,
  usesysid          oid,
  usename           name,
  application_name  text,
  client_addr       inet,
  client_hostname   text,
  client_port       integer,
  backend_start     timestamp without time zone,
  xact_start        timestamp without time zone,
  query_start       timestamp without time zone,
  state_change      timestamp without time zone,
  wait_event_type   text,                     
  wait_event        text,                   
  state             text,                  
  backend_xid       xid,                 
  backend_xmin      xid,                
  query             text,               
  backend_type      text,
  queryid           bigint
);

Tabula ir vēsture_pg_stat_activity tabula, kas sadalīta pa stundām (sīkāka informācija šeit - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? un šeit - Mēģinājums izveidot ASH analogu PostgreSQL.)

Izeja

KLASTERA CPU LAIKS (SISTĒMA + KLIENTI)

Pretenzija

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
			( aa.wait_event_type IS NULL  ) AND
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Piemērs

CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46

KLASTERA GAIDA LAIKS

Pretenzija

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
			( aa.wait_event_type IS NOT NULL  ) AND
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Piemērs

CLUSTER WAITINGS TIME : 30:12:49

Kopējās pg_stat_statements vērtības

Pretenzija

 --TOTAL pg_stat
  SELECT 
    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) temp_blks_written , 
	SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time
  INTO 
    pg_total_stat_history_rec
  FROM 
    pg_stat_history
  WHERE 
 	snapshot_timestamp BETWEEN pg_stat_history_begin AND pg_stat_history_end AND 
	queryid IS NULL;

SQL DBTIME — kopējais vaicājuma izpildes laiks

Pretenzija

dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;

Piemērs

SQL DBTIME : 136:49:36

SQL CPU LAIKS — CPU laiks, kas pavadīts vaicājumu izpildei

Pretenzija

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
			( aa.wait_event_type IS NULL  ) AND
			backend_type = 'client backend' AND 
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Piemērs

SQL CPU TIME : 27:40:15

SQL GAIDĪŠANAS LAIKS — kopējais vaicājumu gaidīšanas laiks

Pretenzija

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
			( aa.wait_event_type IS NOT NULL  ) AND
			aa.state = 'active' AND 
		backend_type = 'client backend'
 )
 SELECT count(*) 
 INTO waiting_total
 FROM t ;

Piemērs

SQL WAITINGS TIME : 30:04:09

Šie vaicājumi ir nenozīmīgi, un, lai ietaupītu vietu, informācija par ieviešanu ir izlaista.

Piemērs

| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL CALLS : 12188248
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 7997039120
| SQL SHARED BLOCKS HITS : 8868286092
| SQL SHARED BLOCKS HITS/READS % : 110.89
| SQL SHARED BLOCKS DIRTED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938

Pāriesim pie interesantākās sadaļas

GAIDA STATISKA

10 populārākie GAIDĪJUMI PĒC KOPĒJĀ KLIENTU PROCESU GAIDĪŠANAS LAIKA

Pretenzija

SELECT 
  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') ) as duration 
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY 
  wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10

Piemērs

+-------------------------------------------------- ------------------------------------ | TOP 10 GAIDĀJUMI PĒC KOPĒJĀ SISTĒMAS PROCESU GAIDĪŠANAS LAIKA +-----+-------------------------------+-- ------------------+--------------------- | #| gaidīt_notikuma_veids| gaidīt_notikumu| ilgums +-----+-----------------------------+----------- -- -------+---------- | 1| Aktivitāte| LogicalLauncherMain| 10:43:28 | 2| Aktivitāte| AutoVacuumMain| 10:42:49 | 3| Aktivitāte| WalWriterMain| 10:28:53 | 4| Aktivitāte| CheckpointerMain| 10:23:50 | 5| Aktivitāte| BgWriterMain| 09:11:59 | 6| Aktivitāte| BgWriterHibernate| 01:37:46 | 7| IO| BuffFileWrite| 00:02:35 | 8| LWLock| buffer_mapping| 00:01:54 | 9| IO| DataFileRead| 00:01:23 | 10| IO| WALWrite| 00:00:59 +-----+-------------------------------+-------- -------------+-----------

10 populārākie GAIDĪJUMI PĒC KOPĒJĀ KLIENTU PROCESU GAIDĪŠANAS LAIKA

Pretenzija

SELECT 
  wait_event_type , wait_event ,
  get_clients_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') ) as duration
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10

Piemērs

+----------+-----------------------------+--------- --- ---------+-----------+----------- | #| gaidīt_notikuma_veids| gaidīt_notikumu| ilgums| % dbtime +-----+-------------------------------+---------- ----------+-----------+----------- | 1| Slēdzene| transakciju| 08:16:47| 6.05 | 2| IO| DataFileRead| 06:13:41| 4.55 | 3| Taimauts| 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| BuffFileWrite| 00:01:34| 0.02 | 7| Slēdzene| tuple| 00:01:32| 0.02 | 8| Klients| ClientRead| 00:01:19| 0.02 | 9| IO| BuffFileRead| 00:00:37| 0.01 | 10| LWLock| bufera_saturs| 00:00:08| 0 +-----+-----------------------------+----------- -- -------+-----------+----------

GAIDĪJUMU VEIDI PĒC KOPĒJĀ GAIDĪŠANAS LAIKA SISTĒMAS PROCESS

Pretenzija

SELECT 
  wait_event_type ,
  get_system_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
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  backend_type != 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY wait_event_type
ORDER BY 2 DESC

Piemērs

+----------+-----------------------------+--------- --- -------- | #| gaidīt_notikuma_veids| ilgums +-----+-----------------------------+----------- -- ------- | 1| Aktivitāte| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock| 00:03:02 +-----+-------------------------------+-------- --------------

GAIDĪŠANAS VEIDI PĒC KOPĒJĀ GAIDĪŠANAS LAIKA KLIENTU PROCESS

Pretenzija

SELECT 
  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
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY wait_event_type
ORDER BY 2 DESC

Piemērs

+----------+-----------------------------+--------- --- ---------+----------- | #| gaidīt_notikuma_veids| ilgums| % dbtime +-----+-------------------------------+---------- ----------+--------------------- | 1| Slēdzene| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| Taimauts| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| Klients| 00:01:19| 0.02 | 6| IPC| 00:00:04| 0 +-----+-----------------------------+----------- -- -------+---------------------

Sistēmas procesu un atsevišķu pieprasījumu gaidīšanas ilgums.

GAIDA SISTĒMAS PROCESUS

Pretenzija

SELECT 
  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 * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration 
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY backend_type , datname , wait_event_type , wait_event
ORDER BY 5 DESC

Piemērs

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 backend_type|    dbname|     wait_event_type|            wait_event|            duration
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    1| logical replication launcher|          |            Activity|   LogicalLauncherMain|            10:43:28
|    2|          autovacuum launcher|          |            Activity|        AutoVacuumMain|            10:42:49
|    3|                    walwriter|          |            Activity|         WalWriterMain|            10:28:53
|    4|                 checkpointer|          |            Activity|      CheckpointerMain|            10:23:50
|    5|            background writer|          |            Activity|          BgWriterMain|            09:11:59
|    6|            background writer|          |            Activity|     BgWriterHibernate|            01:37:46
|    7|              parallel worker|      tdb1|                  IO|          BufFileWrite|            00:02:35
|    8|              parallel worker|      tdb1|              LWLock|        buffer_mapping|            00:01:41
|    9|              parallel worker|      tdb1|                  IO|          DataFileRead|            00:01:22
|   10|              parallel worker|      tdb1|                  IO|           BufFileRead|            00:00:59
|   11|                    walwriter|          |                  IO|              WALWrite|            00:00:57
|   12|              parallel worker|      tdb1|              LWLock|             buffer_io|            00:00:47
|   13|            autovacuum worker|      tdb1|              LWLock|        buffer_mapping|            00:00:13
|   14|            background writer|          |                  IO|         DataFileWrite|            00:00:12
|   15|                 checkpointer|          |                  IO|         DataFileWrite|            00:00:11
|   16|                    walwriter|          |              LWLock|          WALWriteLock|            00:00:09
|   17|                 checkpointer|          |              LWLock|          WALWriteLock|            00:00:06
|   18|            background writer|          |              LWLock|          WALWriteLock|            00:00:06
|   19|                    walwriter|          |                  IO|          WALInitWrite|            00:00:02
|   20|            autovacuum worker|      tdb1|              LWLock|          WALWriteLock|            00:00:02
|   21|                    walwriter|          |                  IO|           WALInitSync|            00:00:02
|   22|            autovacuum worker|      tdb1|                  IO|          DataFileRead|            00:00:01
|   23|                 checkpointer|          |                  IO| ControlFileSyncUpdate|            00:00:01
|   24|            background writer|          |                  IO|              WALWrite|            00:00:01
|   25|            background writer|          |                  IO|         DataFileFlush|            00:00:01
|   26|                 checkpointer|          |                  IO|         SLRUFlushSync|            00:00:01
|   27|            autovacuum worker|      tdb1|                  IO|              WALWrite|            00:00:01
|   28|                 checkpointer|          |                  IO|          DataFileSync|            00:00:01
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

WAITINGS FOR SQL - gaida atsevišķus vaicājumus pēc vaicājuma ID

Pretenzija

SELECT 
queryid , datname , wait_event_type , wait_event , get_query_waiting_duration( queryid ,  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') ) as duration 
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL AND queryid IS NOT NULL 
GROUP BY queryid , datname , wait_event_type , wait_event
ORDER BY 1 , 5 DESC

Piemērs

+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    #|                  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|
+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------

KLIENTA SQL STATIKA — TOP vaicājumi

Saņemšanas pieprasījumi atkal ir mazsvarīgi un netiek sniegti, lai ietaupītu vietu.

piemēri

+------------------------------------------------------------------------------------
| CLIENT SQL ordered by Elapsed Time
+--------------------+----------+----------+----------+----------+----------+--------------------
|        elapsed time|     calls|  % 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 ordered by CPU Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu time|     calls|  % dbtime|total_time|     % 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 ordered by User I/O Wait Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_wait time|     calls|  % dbtime|total_time|     % 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 ordered by Shared Buffers Reads
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       buffers reads|     calls|  % dbtime|total_time|     % 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 ordered by Disk Reads Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           read time|     calls|  % dbtime|total_time|     % 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 ordered by Executions
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               calls|      rows|  % dbtime|total_time|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Kopsavilkums

Izmantojot iesniegtos vaicājumus un iegūtos pārskatus, varat iegūt pilnīgāku priekšstatu par atsevišķu vaicājumu un visa klastera veiktspējas pasliktināšanās problēmu analīzi un risināšanu.

Attīstība

Līdz šim attīstības plāni ir šādi:

  • Papildiniet ziņošanu ar bloķēšanas vēsturi. Pieprasījumi tiek pārbaudīti un drīzumā tiks iesniegti.
  • Izmantojiet paplašinājumu TimescaleDB, lai saglabātu pg_stat_activity un pg_locks vēsturi.
  • Sagatavojiet pakotnes risinājumu github masveida izvietošanai ražošanas bāzēs.

Lai varētu turpināt ...

Avots: www.habr.com

Pievieno komentāru