ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > ืื™ื™ืŸ ืื•ืคึฟืŸ ืฆื• ื‘ืึทืงื•ืžืขืŸ ื•ื•ืขืจืงืœืึธื•ื“ ืคึผืจืึธืคื™ืœ ืื•ืŸ ื•ื•ืึทืจื˜ืŸ ื’ืขืฉื™ื›ื˜ืข ืื™ืŸ PostgreSQL

ืื™ื™ืŸ ืื•ืคึฟืŸ ืฆื• ื‘ืึทืงื•ืžืขืŸ ื•ื•ืขืจืงืœืึธื•ื“ ืคึผืจืึธืคื™ืœ ืื•ืŸ ื•ื•ืึทืจื˜ืŸ ื’ืขืฉื™ื›ื˜ืข ืื™ืŸ PostgreSQL

ื”ืžืฉืš ืคื•ืŸ ื“ืขื ืึทืจื˜ื™ืงืœ "ืึทืŸ ืคึผืจื•ื•ื•ืŸ ืฆื• ืฉืึทืคึฟืŸ ืึทืŸ ืึทื ืึทืœืึธื’ ืคื•ืŸ ASH ืคึฟืึทืจ PostgreSQL ".

ื“ืขืจ ืึทืจื˜ื™ืงืœ ื•ื•ืขื˜ ื•ื ื˜ืขืจื–ื•ื›ืŸ ืื•ืŸ ื•ื•ื™ื™ึทื–ืŸ, ื ื™ืฆืŸ ืกืคึผืขืฆื™ืคื™ืฉ ืคึฟืจืื’ืŸ ืื•ืŸ ื‘ื™ื™ืฉืคื™ืœืŸ, ื•ื•ืึธืก ื ื•ืฆื™ืง ืื™ื ืคึฟืึธืจืžืึทืฆื™ืข ืงืขื ืขืŸ ื–ื™ื™ืŸ ื‘ืืงื•ืžืขืŸ ืžื™ื˜ ื“ื™ ื’ืขืฉื™ื›ื˜ืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ื“ื™ ืคึผื’_ืกื˜ืึทื˜_ืึทืงื˜ื™ื•ื•ื™ื˜ื™ ืžื™ื™ื ื•ื ื’.

Warning
ืจืขื›ื˜ ืฆื• ื“ืขืจ ื ื™ื™ึทืงื™ื™ึทื˜ ืคื•ืŸ ื“ื™ ื˜ืขืžืข ืื•ืŸ ื“ื™ ื™ื ืงืึธืžืคึผืœืขื˜ืขื ืขืกืก ืคื•ืŸ ื“ื™ ื˜ืขืกื˜ื™ื ื’ ืฆื™ื™ึทื˜, ื“ืขืจ ืึทืจื˜ื™ืงืœ ืงืขืŸ ืึทื ื˜ื”ืึทืœื˜ืŸ ืขืจืจืึธืจืก. ืงืจื™ื˜ื™ืง ืื•ืŸ ื‘ืึทืžืขืจืงื•ื ื’ืขืŸ ื–ืขื ืขืŸ ื‘ืึทื’ืจื™ืกื•ื ื’ ืื•ืŸ ื“ืขืจื•ื•ืึทืจื˜.

ืึทืจื™ื™ึทื ืฉืจื™ื™ึทื‘ ื“ืึทื˜ืŸ

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

ื“ืขืจ ื˜ื™ืฉ ืื™ื– ืคึผืึทืคึผื™ืึทืœื™ื™ื˜ืึทื“ ื™ืขื“ืขืจ ืฉืขื” ืžื™ื˜ ื“ื‘ืœื™ื ืง ืฆื• ื“ื™ ืฆื™ืœ ื“ืึทื˜ืึทื‘ื™ื™ืก. ื“ื™ ืžืขืจืกื˜ ื˜ืฉื™ืงืึทื•ื•ืข ืื•ืŸ ื ื•ืฆื™ืง ื–ื™ื™ึทืœ ืื™ืŸ ื“ื™ ื˜ื™ืฉ, ืคื•ืŸ ืงื•ืจืก queryid.

pg_stat_activity ื–ืขืŸ ื’ืขืฉื™ื›ื˜ืข

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

ื“ืขืจ ื˜ื™ืฉ ืื™ื– ืึท ื’ืขืฉื™ื›ื˜ืข_ืคึผื’_ืกื˜ืึทื˜_ืึทืงื˜ื™ื•ื•ื™ื˜ื™ ื˜ื™ืฉ ืคึผืึทืจื˜ื™ืฉืึทื ื“ ื“ื•ืจืš ืฉืขื” (ืžืขืจ ื“ืขื˜ืึทื™ืœืก ื“ืึธ - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? ืื•ืŸ ื“ื - ืึทืŸ ืคึผืจื•ื•ื•ืŸ ืฆื• ืฉืึทืคึฟืŸ ืึทืŸ ืึทื ืึทืœืึธื’ ืคื•ืŸ ASH ืคึฟืึทืจ PostgreSQL.)

ะ’ั‹ั…ะพะดะฝั‹ะต ะดะฐะฝะฝั‹ะต

ืงื ื•ื™ืœ ืงืคึผื• ืฆื™ื™ื˜ (ืกื™ืกื˜ืขื + ืงืœื™ื™ืึทื ืฅ)

ื‘ืขื˜ืŸ

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 ;

ื‘ื™ื™ึทืฉืคึผื™ืœ

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

Cluster WAITINGS TIME

ื‘ืขื˜ืŸ

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 ;

ื‘ื™ื™ึทืฉืคึผื™ืœ

CLUSTER WAITINGS TIME : 30:12:49

ื’ืึทื ืฅ pg_stat_statements ื•ื•ืึทืœื•ืขืก

ื‘ืขื˜ืŸ

 --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 - ื’ืึทื ืฅ ืึธื ืคึฟืจืขื’ ื“ื•ืจื›ืคื™ืจื•ื ื’ ืฆื™ื™ื˜

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

SQL DBTIME : 136:49:36

SQL CPU TIME - ืงืคึผื• ืฆื™ื™ื˜ ืคืืจื‘ืจืื›ื˜ ืขืงืกืึทืงื™ื•ื˜ื™ื ื’ ืงื•ื•ื™ืจื™ื–

ื‘ืขื˜ืŸ

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 ;

ื‘ื™ื™ึทืฉืคึผื™ืœ

SQL CPU TIME : 27:40:15

SQL WAITINGS TIME - ื’ืึทื ืฅ ื•ื•ืืจื˜ืŸ ืฆื™ื™ื˜ ืคึฟืึทืจ ืคึฟืจืื’ืŸ

ื‘ืขื˜ืŸ

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 ;

ื‘ื™ื™ึทืฉืคึผื™ืœ

SQL WAITINGS TIME : 30:04:09

ื“ื™ ืคืืœื’ืขื ื“ืข ืคึฟืจืื’ืŸ ื–ืขื ืขืŸ ื ื™ืฉื˜ื™ืง ืื•ืŸ ื™ืžืคึผืœืึทืžืขื ื˜ื™ื™ืฉืึทืŸ ื“ืขื˜ืึทื™ืœืก ื–ืขื ืขืŸ ืื™ื‘ืขืจื’ืขื”ื™ืคึผืขืจื˜ ืฆื• ืฉืคึผืึธืจืŸ ืคึผืœืึทืฅ:

ื‘ื™ื™ึทืฉืคึผื™ืœ

| 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

ืœืึธืžื™ืจ ื’ื™ื™ืŸ ืฆื• ื“ื™ ืžืขืจืกื˜ ื˜ืฉื™ืงืึทื•ื•ืข ืึธืคึผื˜ื™ื™ืœื•ื ื’

WAITINGS STATIC

ื“ื™ ืฉืคึผื™ืฅ 10 ื•ื•ืืจื˜ืŸ ืœื•ื™ื˜ ื’ืึทื ืฅ ื•ื•ืึทืจื˜ืŸ ืฆื™ื™ื˜ ืคึฟืึทืจ ืงืœื™ื™ืึทื ืฅ ืคึผืจืึทืกืขืกืึทื–

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

+------------------------------------------------ ---------------------------------- | ืฉืคึผื™ืฅ 10 ื•ื•ืึทื™ื˜ื™ื ื’ ื“ื•ืจืš ื’ืึทื ืฅ ื•ื•ืึทืจื˜ืŸ ืฆื™ื™ื˜ ืคึฟืึทืจ ืกื™ืกื˜ืขื ืคึผืจืึทืกืขืกืึทื– +-----+-------------------------------------+-- ------------------+------------------------ | #| wait_event_type| wait_event| ื’ืขื“ื•ื™ืขืจ +----+-------------------- -------+--------------------------- | 1| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| LogicalLauncherMain| 10:43:28 | 2| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| AutoVacuumMain| 10:42:49 | 3| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| WalWriterMain| 10:28:53 | 4| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| CheckpointerMain| 10:23:50 | 5| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| BgWriterMain| 09:11:59 | 6| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| 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 ื•ื•ืืจื˜ืŸ ืœื•ื™ื˜ ื’ืึทื ืฅ ื•ื•ืึทืจื˜ืŸ ืฆื™ื™ื˜ ืคึฟืึทืจ ืงืœื™ื™ืึทื ืฅ ืคึผืจืึทืกืขืกืึทื–

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

+---------+----------------------------- --- ----------+----------+----------- | #| wait_event_type| wait_event| ื’ืขื“ื•ื™ืขืจ| % DBtime +----+------------------------------+ ----------+----------+----------- | 1| ืฉืœืึธืก| ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ | 08:16:47| 6.05 | 2| IO| DataFileRead| 06:13:41| 4.55 | 3| ื˜ื™ื™ืžืึทื•ื˜| 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| ืฉืœืึธืก| tuple| 00:01:32| 0.02 | 8| ืงืœื™ืขื ื˜| ClientRead| 00:01:19| 0.02 | 9| IO| BuffFileRead| 00:00:37| 0.01 | 10| LWLock| buffer_content| 00:00:08| 0 +-----+----------------------------+----- -------+------------+-----------

ื•ื•ืืจื˜ืŸ ื˜ื™ื™ืคึผืก ืœื•ื™ื˜ ื’ืึทื ืฅ ื•ื•ืึทืจื˜ืŸ ืฆื™ื™ื˜, ืคึฟืึทืจ ืกื™ืกื˜ืขื ืคึผืจืึทืกืขืกืึทื–

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

+---------+----------------------------- --- -------- | #| wait_event_type| ื’ืขื“ื•ื™ืขืจ +----+-------------------- ------- | 1| ืึทืงื˜ื™ื•ื•ื™ื˜ืขื˜| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock| 00:03:02 +-------------------------- -------------

ื•ื•ืืจื˜ืŸ ื˜ื™ื™ืคึผืก ืœื•ื™ื˜ ื’ืึทื ืฅ ื•ื•ืึทืจื˜ืŸ ืฆื™ื™ื˜, ืคึฟืึทืจ ืงืœื™ื™ืึทื ืฅ ืคึผืจืึทืกืขืกืึทื–

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

+---------+---------------------------- --- ----------+---------------- | #| wait_event_type| ื’ืขื“ื•ื™ืขืจ| % DBtime +----+------------------------------+ ----------+------------------------ | 1| ืฉืœืึธืก| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| ื˜ื™ื™ืžืึทื•ื˜| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| ืงืœื™ืขื ื˜| 00:01:19| 0.02 | 6| IPC| 00:00:04| 0 +-----+----------------------------+----- -------+--------------------

ื•ื•ืึทืจื˜ืŸ ืคึฟืึทืจ ืกื™ืกื˜ืขื ืคึผืจืึทืกืขืกืึทื– ืื•ืŸ ื™ื—ื™ื“ ืจื™ืงื•ื•ืขืก.

ื•ื•ืืจื˜ืŸ ืคึฟืึทืจ ืกื™ืกื˜ืขื ืคึผืจืึทืกืขืกืึทื–

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 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 - ื•ื•ื™ื™ืฅ ืคึฟืึทืจ ื™ื—ื™ื“ ืงื•ื•ื™ืจื™ื– ื“ื•ืจืš queryid

ื‘ืขื˜ืŸ

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

ื‘ื™ื™ึทืฉืคึผื™ืœ

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

ืงืœื™ืขื ื˜ ืกืงืœ ืกื˜ืึทื˜ื™ืงืก - ืฉืคึผื™ืฅ ืคึฟืจืื’ืŸ

ืจื™ืงื•ื•ืขืก ืคึฟืึทืจ ืจื™ืกื™ื•ื•ื™ื ื’ ื–ืขื ืขืŸ ื•ื•ื™ื“ืขืจ ื ื™ืฉื˜ื™ืง ืื•ืŸ ื–ืขื ืขืŸ ื ื™ืฉื˜ ื’ืขื’ืขื‘ืŸ ืฆื• ืฉืคึผืึธืจืŸ ืคึผืœืึทืฅ.

ืขืงืกืึทืžืคึผืœืขืก

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

ื’ืึทื ืฅ

ื ื™ืฆืŸ ื“ื™ ื“ืขืจืœืื ื’ื˜ ืคึฟืจืื’ืŸ ืื•ืŸ ื“ื™ ืจื™ื–ืึทืœื˜ื™ื ื’ ืจื™ืคึผืึธืจื˜ื™ื ื’, ืื™ืจ ืงืขื ืขืŸ ื‘ืึทืงื•ืžืขืŸ ืึท ืžืขืจ ืคื•ืœืฉื˜ืขื ื“ื™ืง ื‘ื™ืœื“ ืคึฟืึทืจ ืึทื ืึทืœื™ื™ื–ื™ื ื’ ืื•ืŸ ืกืึทืœื•ื•ื™ื ื’ ืคึผืจืึธื‘ืœืขืžืก ืคื•ืŸ ืคืึธืจืฉื˜ืขืœื•ื ื’ ื“ืขืจื ื™ื“ืขืจื™ืงื•ื ื’ ืคึฟืึทืจ ื™ื—ื™ื“ ืงื•ื•ื™ืจื™ื– ืื•ืŸ ื“ื™ ื’ืื ืฆืข ืงื ื•ื™ืœ ื•ื•ื™ ืึท ื’ืึทื ืฅ.

ืึทื ื˜ื•ื•ื™ืงืœื•ื ื’

ื‘ื™ื– ืื™ืฆื˜, ื“ื™ ืึทื ื˜ื•ื•ื™ืงืœื•ื ื’ ืคึผืœืึทื ื– ื–ืขื ืขืŸ ื•ื•ื™ ื’ื™ื™ื˜:

  • ื”ืขืกืึธืคืข ืจื™ืคึผืึธืจื˜ื™ื ื’ ืžื™ื˜ ื‘ืœืึทืงื™ื ื’ ื’ืขืฉื™ื›ื˜ืข. ืจื™ืงื•ื•ืขืก ื–ืขื ืขืŸ ื˜ืขืกื˜ืขื“ ืื•ืŸ ื•ื•ืขื˜ ื–ื™ื™ืŸ ื“ืขืจืœืื ื’ื˜ ื‘ืึทืœื“.
  • ื ื™ืฆืŸ ื“ื™ TimescaleDB ืคืึทืจืœืขื ื’ืขืจื•ื ื’ ืฆื• ืงืจืึธื pg_stat_activity ืื•ืŸ pg_locks ื’ืขืฉื™ื›ื˜ืข.
  • ืฆื•ื’ืจื™ื™ื˜ืŸ ืึท ืคึผืขืงืœ ืœื™ื™ื–ื•ื ื’ ืื•ื™ืฃ ื’ื™ื˜ื”ื•ื‘ ืคึฟืึทืจ ืžืึทืกืข ื“ื™ืคึผืœื•ื™ืžืึทื ื˜ ืื•ื™ืฃ ืคึผืจืึธื“ื•ืงืฆื™ืข ื‘ืึทืกืขืก.

ืฆื• ืคืึธืจื–ืขืฆืŸ ...

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’