рд▓реЗрдЦ рд╕реБрд░реВ рдареЗрд╡рдгреЗ "
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 рдкрд╛рд╣рдгреНрдпрд╛рдЪрд╛ рдЗрддрд┐рд╣рд╛рд╕
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
);
рд╕рд╛рд░рдгреА рддрд╛рд╕рд╛рдиреБрд╕рд╛рд░ рд╡рд┐рднрд╛рдЬрд┐рдд рдХреЗрд▓реЗрд▓реА 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
SQL CPU TIME - рдХреНрд╡реЗрд░реА рдХрд╛рд░реНрдпрд╛рдиреНрд╡рд┐рдд рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА 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
backend_type = 'client backend' AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;
рдЙрджрд╛рд╣рд░рдг:
SQL CPU TIME : 27:40:15
SQL рдкреНрд░рддреАрдХреНрд╖рд╛ рд╡реЗрд│ - рдкреНрд░рд╢реНрдирд╛рдВрд╕рд╛рдареА рдПрдХреВрдг рдкреНрд░рддреАрдХреНрд╖рд╛ рд╡реЗрд│
рдЪреМрдХрд╢реА
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 | рей| рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдк| WalWriterMain| 3:10:28 | 53| рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдк| рдЪреЗрдХрдкреЙрдЗрдВрдЯрд░рдореЗрди| 4:10:23 | рел| рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдк| BgWriterMain| 50:5:09 | рем| рдХреНрд░рд┐рдпрд╛рдХрд▓рд╛рдк| BgWriterHibernate| 11:59:6 | рен| IO| рдмрдлрдлрд╛рдЗрд▓рд░рд╛рдИрдЯ| 01:37:46 | рео| LWLock| рдмрдлрд░_рдореЕрдкрд┐рдВрдЧ| 7:00:02 | реп| IO| DataFileRead| 35:8:00 | 01| IO| рд╡реЙрд▓рд░рд╛рдЗрдЯ| 54:9:00 +------+-------------------------------+--------- -------------+------------
рдХреНрд▓рд╛рдпрдВрдЯ рдкреНрд░рдХреНрд░рд┐рдпреЗрд╕рд╛рдареА рдПрдХреВрдг рдкреНрд░рддреАрдХреНрд╖рд╛ рд╡реЗрд│реЗрдиреБрд╕рд╛рд░ рд╢реАрд░реНрд╖ 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| рд▓реЙрдХ | transactionid| 08:16:47 | рем.режрел | 6.05| IO| DataFileRead| 2:06:13| рек.релрел | рей| рдХрд╛рд▓рдмрд╛рд╣реНрдп | PgSleep| 41:4.55:3| 02 | 53| LWLock| рдмрдлрд░_рдореЕрдкрд┐рдВрдЧ| 21:2.11:4| 00 | рел| LWLock| buffer_io| 40:42:0.5| 5 | рем| IO| рдмрдлрдлрд╛рдЗрд▓рд░рд╛рдИрдЯ| 00:17:17| реж.режреи | рен| рд▓реЙрдХ | рдЯрдкрд▓ | 0.21:6:00| реж.режреи | рео| рдЧреНрд░рд╛рд╣рдХ| ClientRead| 01:34:0.02 | реж.режреи | реп| IO| рдмрдлрдлрд╛рдЗрд▓рд░реАрдб| 7:00:01| реж.режрез | 32| LWLock| рдмрдлрд░_рд╕рд╛рдордЧреНрд░реА| 0.02:8:00| 01 +------+-----------------------------------------+------------ -------+------------+---------
рд╕рд┐рд╕реНрдЯрдо рдкреНрд░рдХреНрд░рд┐рдпреЗрд╕рд╛рдареА, рдПрдХреВрдг рдкреНрд░рддреАрдХреНрд╖рд╛ рд╡реЗрд│реЗрдиреБрд╕рд╛рд░ рдкреНрд░рддреАрдХреНрд╖рд╛рдЪреЗ рдкреНрд░рдХрд╛рд░
рдЪреМрдХрд╢реА
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| IO| 00:06:24 | рей| LWLock| 3:00:03 +------+-----------------------------------------+--------- -------------
рдХреНрд▓рд╛рдпрдВрдЯрдЪреНрдпрд╛ рдкреНрд░рдХреНрд░рд┐рдпреЗрд╕рд╛рдареА рдПрдХреВрдг рдкреНрд░рддреАрдХреНрд╖рд╛ рд╡реЗрд│реЗрдиреБрд╕рд╛рд░ рдкреНрд░рддреАрдХреНрд╖рд╛рдЪреЗ рдкреНрд░рдХрд╛рд░
рдЪреМрдХрд╢реА
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
рдЙрджрд╛рд╣рд░рдг:
+------------ +---------------------------- +--------- ------------+---------- | #| рдкреНрд░рддреАрдХреНрд╖рд╛_рдЗрд╡реНрд╣реЗрдВрдЯ_рдкреНрд░рдХрд╛рд░| рдХрд╛рд▓рд╛рд╡рдзреА| % dbtime +------+--------------------------------------------+--------- ----------+---------------------- | 1| рд▓реЙрдХ | 08:18:19 | рем.режрен | 6.07| IO| 2:06:16| рек.релрео | рей| рдХрд╛рд▓рдмрд╛рд╣реНрдп | 01:4.58:3| 02 | 53| LWLock| 21:2.11:4| 00 | рел| рдЧреНрд░рд╛рд╣рдХ| 58:12:0.71 | реж.режреи | рем| IPC| 5:00:01| 19 +------+-----------------------------------------+------------ ------------------------------
рд╕рд┐рд╕реНрдЯрдо рдкреНрд░рдХреНрд░рд┐рдпрд╛ рдЖрдгрд┐ рд╡реИрдпрдХреНрддрд┐рдХ рд╡рд┐рдирдВрддреНрдпрд╛рдВрд╕рд╛рдареА рдХрд╛рд▓рд╛рд╡рдзреА рдкреНрд░рддреАрдХреНрд╖рд╛ рдХрд░рд╛.
рд╕рд┐рд╕реНрдЯрдо рдкреНрд░рдХреНрд░рд┐рдпреЗрдЪреА рдкреНрд░рддреАрдХреНрд╖рд╛
рдЪреМрдХрд╢реА
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| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
рдХреНрд▓рд╛рдпрдВрдЯ SQL рд╕реНрдЯреЕрдЯрд┐рдХрд┐рдХреНрд╕ - рд╢реАрд░реНрд╖ рдХреНрд╡реЗрд░реА
рдкреНрд░рд╛рдкреНрдд рдХрд░рдгреНрдпрд╛рдЪреНрдпрд╛ рд╡рд┐рдирдВрддреНрдпрд╛ рдкреБрдиреНрд╣рд╛ рдХреНрд╖реБрд▓реНрд▓рдХ рдЖрд╣реЗрдд рдЖрдгрд┐ рдЬрд╛рдЧрд╛ рд╡рд╛рдЪрд╡рдгреНрдпрд╛рд╕рд╛рдареА рджрд┐рд▓реНрдпрд╛ рдЬрд╛рдд рдирд╛рд╣реАрдд.
рдЙрджрд╛рд╣рд░рдгреЗ
+------------------------------------------------------------------------------------ | 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