āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§ā§āĻ° āĻ§āĻžāĻ°āĻžāĻŦāĻžāĻšāĻŋāĻāĻ¤āĻž ""āĨ¤
āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻāĻŋ āĻāĻ˛ā§āĻāĻ¨āĻž āĻāĻ°āĻŦā§ āĻāĻŦāĻ āĻĻā§āĻāĻžāĻŦā§, āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻŦāĻ āĻāĻĻāĻžāĻšāĻ°āĻŖ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§, pg_stat_activity āĻĻāĻ°ā§āĻļāĻ¨ā§āĻ° āĻāĻ¤āĻŋāĻšāĻžāĻ¸ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻā§ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻ¤āĻĨā§āĻ¯ āĻĒāĻžāĻāĻ¯āĻŧāĻž āĻ¯ā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
āĻ¸āĻ¤āĻ°ā§āĻāĻŦāĻžāĻŖā§āĨ¤
āĻŦāĻŋāĻˇāĻ¯āĻŧā§āĻ° āĻ¨āĻ¤ā§āĻ¨āĻ¤ā§āĻŦ āĻāĻŦāĻ āĻ āĻ¸āĻŽāĻžāĻĒā§āĻ¤ āĻĒāĻ°ā§āĻā§āĻˇāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ā§āĻ° āĻāĻžāĻ°āĻŖā§, āĻ¨āĻŋāĻŦāĻ¨ā§āĻ§āĻāĻŋāĻ¤ā§ āĻ¤ā§āĻ°ā§āĻāĻŋ āĻĨāĻžāĻāĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤ āĻ¸āĻŽāĻžāĻ˛ā§āĻāĻ¨āĻž āĻāĻŦāĻ āĻŽāĻ¨ā§āĻ¤āĻŦā§āĻ¯ āĻĻā§āĻĸāĻŧāĻāĻžāĻŦā§ āĻ¸ā§āĻŦāĻžāĻāĻ¤ āĻāĻŦāĻ āĻĒā§āĻ°āĻ¤ā§āĻ¯āĻžāĻļāĻŋāĻ¤.
āĻ¤āĻĨā§āĻ¯ āĻ āĻ¨ā§āĻ¤āĻ°ā§āĻā§āĻā§āĻ¤ā§
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 );āĻāĻžāĻ°ā§āĻā§āĻ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§ dblink āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻĒā§āĻ°āĻ¤āĻŋ āĻāĻ¨ā§āĻāĻžāĻ¯āĻŧ āĻā§āĻŦāĻŋāĻ˛āĻāĻŋ āĻĒāĻĒā§āĻ˛ā§āĻ āĻāĻ°āĻž āĻšāĻ¯āĻŧāĨ¤ āĻ āĻŦāĻļā§āĻ¯āĻ āĻā§āĻŦāĻŋāĻ˛ā§āĻ° āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻāĻŦāĻ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻāĻ˛āĻžāĻŽ queryid.
pg_stat_activity āĻĻā§āĻāĻžāĻ° āĻāĻ¤āĻŋāĻšāĻžāĻ¸
āĻ¸āĻāĻ°āĻā§āĻˇāĻŖāĻžāĻāĻžāĻ°_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
);āĻā§āĻŦāĻŋāĻ˛āĻāĻŋ āĻšāĻ˛ āĻāĻāĻāĻŋ history_pg_stat_activity āĻā§āĻŦāĻŋāĻ˛ āĻāĻ¨ā§āĻāĻž āĻĻā§āĻŦāĻžāĻ°āĻž āĻŦāĻŋāĻāĻā§āĻ¤ (āĻāĻ°āĻ āĻŦāĻŋāĻļāĻĻ āĻāĻāĻžāĻ¨ā§ - āĻāĻŦāĻ āĻāĻāĻžāĻ¨ā§ -
āĻāĻāĻāĻĒā§āĻ
āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° CPU āĻ¸āĻŽāĻ¯āĻŧ (āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ + āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ)
āĻ¤āĻĻāĻ¨ā§āĻ¤
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āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ° āĻ āĻĒā§āĻā§āĻˇāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ
āĻ¤āĻĻāĻ¨ā§āĻ¤
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āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ¸āĻŋāĻĒāĻŋāĻāĻ āĻāĻžāĻāĻŽ - āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻžāĻ˛āĻžāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŋāĻĒāĻŋāĻāĻ āĻ¸āĻŽāĻ¯āĻŧ āĻŦā§āĻ¯āĻ¯āĻŧ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§
āĻ¤āĻĻāĻ¨ā§āĻ¤
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āĻāĻ¸āĻāĻŋāĻāĻāĻ˛ āĻ āĻĒā§āĻā§āĻˇāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ - āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻŽā§āĻ āĻ āĻĒā§āĻā§āĻˇāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ
āĻ¤āĻĻāĻ¨ā§āĻ¤
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
āĻāĻ° āĻ¸āĻŦāĻā§āĻ¯āĻŧā§ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻŦāĻŋāĻāĻžāĻā§ āĻāĻāĻŋāĻ¯āĻŧā§ āĻāĻ˛ā§āĻ¨
āĻāĻ¯āĻŧā§āĻāĻŋāĻ āĻ¸ā§āĻā§āĻ¯āĻžāĻāĻŋāĻ
āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻŽā§āĻ āĻāĻ¯āĻŧā§āĻ āĻāĻžāĻāĻŽ āĻĻā§āĻŦāĻžāĻ°āĻž āĻ¸ā§āĻ°āĻž 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āĻāĻŋ āĻ āĻĒā§āĻā§āĻˇāĻž +------+------------------ ------------------------------ | #| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ_āĻāĻžāĻāĻĒ| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ| āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ +------+----------------------------+------------ -------+------------ | 1| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| āĻ˛āĻāĻŋāĻā§āĻ¯āĻžāĻ˛ āĻ˛āĻā§āĻāĻžāĻ°āĻŽā§āĻāĻ¨| 10:43:28 | 2| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| AutoVacuumMain| 10:42:49 | 3| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| WalWriterMain| 10:28:53 | 4| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| āĻā§āĻāĻĒāĻ¯āĻŧā§āĻ¨ā§āĻāĻžāĻ° āĻŽā§āĻāĻ¨| 10:23:50 | 5| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| BgWriterMain| 09:11:59 | 6| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| BgWriterHibernate| 01:37:46 | 7| āĻāĻāĻ| BuffFileWrite| 00:02:35 | 8| LWLock| āĻŦāĻžāĻĢāĻžāĻ°_āĻŽā§āĻ¯āĻžāĻĒāĻŋāĻ| 00:01:54 | 9| āĻāĻāĻ| DataFileRead| 00:01:23 | 10| āĻāĻāĻ| āĻāĻ¯āĻŧāĻžāĻ˛āĻ°āĻžāĻāĻ| 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āĻāĻĻāĻžāĻšāĻ°āĻŖ
+--------------------------------------------------------------- ------------+----------+------------ | #| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ_āĻāĻžāĻāĻĒ| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ| āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛| % dbtime +------+-----------------------------------------------+--------- ----------+---------+------------ | 1| āĻ¤āĻžāĻ˛āĻž | āĻ˛ā§āĻ¨āĻĻā§āĻ¨ 08:16:47 | ā§Ŧ.ā§Ļā§Ģ | 6.05| āĻāĻāĻ| DataFileRead| 2:06:13 | 41 | 4.55| āĻāĻžāĻāĻŽāĻāĻāĻ| PgSleep| 3:02:53 | 21 | 2.11| LWLock| āĻŦāĻžāĻĢāĻžāĻ°_āĻŽā§āĻ¯āĻžāĻĒāĻŋāĻ| 4:00:40 | 42 | 0.5| LWLock| āĻŦāĻžāĻĢāĻžāĻ°_io| 5:00:17 | 17 | 0.21| āĻāĻāĻ| BuffFileWrite| 6:00:01| 34 | 0.02| āĻ¤āĻžāĻ˛āĻž | āĻāĻŋāĻĒāĻ˛ | 7:00:01 | 32 | 0.02| āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ| āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻāĻ°āĻŋāĻĄ| 8:00:01 | 19 | 0.02| āĻāĻāĻ| BuffFileRead| 9:00:00 | 37 | 0.01| LWLock| buffer_content| 10:00:00 | 08 +------+-----------------------------------------+------------ -------+------------+---------
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻŽā§āĻ āĻ āĻĒā§āĻā§āĻˇāĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻĻā§āĻŦāĻžāĻ°āĻž āĻ āĻĒā§āĻā§āĻˇāĻžāĻ° āĻĒā§āĻ°āĻāĻžāĻ°āĻā§āĻ˛āĻŋ
āĻ¤āĻĻāĻ¨ā§āĻ¤
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āĻāĻĻāĻžāĻšāĻ°āĻŖ
+--------------------------------------------------------------- ------------ | #| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ_āĻāĻžāĻāĻĒ| āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ +------+----------------------------+------------ ------- | 1| āĻāĻžāĻ°ā§āĻ¯āĻāĻ˛āĻžāĻĒ| 53:08:45 | 2| āĻāĻāĻ| 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āĻāĻĻāĻžāĻšāĻ°āĻŖ
+--------------------------------------------------------------- ------------+------------ | #| āĻ āĻĒā§āĻā§āĻˇāĻž_āĻāĻā§āĻ¨ā§āĻ_āĻāĻžāĻāĻĒ| āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛| % āĻĄāĻŋāĻŦāĻŋāĻāĻžāĻāĻŽ +------+----------------------------------+--------- ----------+---------------------- | 1| āĻ¤āĻžāĻ˛āĻž | 08:18:19 | ā§Ŧ.ā§Ļā§ | 6.07| āĻāĻāĻ| 2:06:16 | 01 | 4.58| āĻāĻžāĻāĻŽāĻāĻāĻ| 3:02:53 | 21 | 2.11| LWLock| 4:00:58 | 12 | 0.71| āĻā§āĻ˛āĻžāĻ¯āĻŧā§āĻ¨ā§āĻ| 5:00:01 | 19 | 0.02| āĻāĻāĻĒāĻŋāĻ¸āĻŋ| 6:00:00 | 04 +------+-------------------------------+------------ ------------------------------
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻāĻŦāĻ āĻĒā§āĻĨāĻ āĻ āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ āĻ āĻĒā§āĻā§āĻˇāĻž āĻāĻ°ā§āĻ¨āĨ¤
āĻ¸āĻŋāĻ¸ā§āĻā§āĻŽ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻĒā§āĻā§āĻˇāĻž
āĻ¤āĻĻāĻ¨ā§āĻ¤
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 +-----+-----------------------------+----------+--------------------+----------------------+--------------------
āĻāĻ¸āĻāĻŋāĻāĻāĻ˛-āĻāĻ° āĻāĻ¨ā§āĻ¯ āĻ āĻĒā§āĻā§āĻˇāĻž - 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 +--------------------+----------+----------+----------+----------+----------+----------+--------------------
āĻĢāĻ˛āĻžāĻĢāĻ˛
āĻāĻŽāĻž āĻĻā§āĻāĻ¯āĻŧāĻž āĻĒā§āĻ°āĻļā§āĻ¨ āĻāĻŦāĻ āĻĢāĻ˛āĻžāĻĢāĻ˛ āĻ°āĻŋāĻĒā§āĻ°ā§āĻāĻŋāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§, āĻāĻĒāĻ¨āĻŋ āĻĒā§āĻĨāĻ āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻāĻŦāĻ āĻ¸āĻŽāĻā§āĻ° āĻā§āĻ˛āĻžāĻ¸ā§āĻāĻžāĻ°ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻšā§āĻ°āĻžāĻ¸ā§āĻ° āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻā§āĻ˛āĻŋ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖ āĻāĻŦāĻ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻ°āĻ āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻāĻŋāĻ¤ā§āĻ° āĻĒā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨āĨ¤
āĻāĻ¨ā§āĻ¨āĻ¯āĻŧāĻ¨
āĻāĻāĻ¨ āĻĒāĻ°ā§āĻ¯āĻ¨ā§āĻ¤ āĻāĻ¨ā§āĻ¨āĻ¯āĻŧāĻ¨ āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻž āĻ¨āĻŋāĻŽā§āĻ¨āĻ°ā§āĻĒ:
- āĻŦā§āĻ˛āĻāĻŋāĻ āĻāĻ¤āĻŋāĻšāĻžāĻ¸ā§āĻ° āĻ¸āĻžāĻĨā§ āĻ¸āĻŽā§āĻĒā§āĻ°āĻ āĻ°āĻŋāĻĒā§āĻ°ā§āĻāĻŋāĻāĨ¤ āĻ āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻĒāĻ°ā§āĻā§āĻˇāĻž āĻāĻ°āĻž āĻšāĻā§āĻā§ āĻāĻŦāĻ āĻļā§āĻā§āĻ°āĻ āĻāĻŽāĻž āĻĻā§āĻāĻ¯āĻŧāĻž āĻšāĻŦā§āĨ¤
- pg_stat_activity āĻāĻŦāĻ pg_locks āĻāĻ¤āĻŋāĻšāĻžāĻ¸ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻ¤ā§ TimescaleDB āĻāĻā§āĻ¸āĻā§āĻ¨āĻļāĻ¨ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§āĻ¨āĨ¤
- āĻā§āĻĒāĻžāĻĻāĻ¨ āĻāĻžāĻāĻāĻŋāĻ¤ā§ āĻāĻ° āĻ¸ā§āĻĨāĻžāĻĒāĻ¨ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻŋāĻĨā§āĻŦā§ āĻāĻāĻāĻŋ āĻĒā§āĻ¯āĻžāĻā§āĻ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻĒā§āĻ°āĻ¸ā§āĻ¤ā§āĻ¤ āĻāĻ°ā§āĻ¨āĨ¤
āĻāĻ˛āĻŦā§âĻ
āĻāĻ¤ā§āĻ¸: www.habr.com