рд▓реЗрдЦрдХреЛ рдирд┐рд░рдиреНрддрд░рддрд╛ "
рд▓реЗрдЦрд▓реЗ рд╡рд┐рд╢реЗрд╖ рдкреНрд░рд╢реНрдирд╣рд░реВ рд░ рдЙрджрд╛рд╣рд░рдгрд╣рд░реВ рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░, 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
);
рддрд╛рд▓рд┐рдХрд╛ рдШрдиреНрдЯрд╛ рджреНрд╡рд╛рд░рд╛ рд╡рд┐рднрд╛рдЬрди рдЧрд░рд┐рдПрдХреЛ рдЗрддрд┐рд╣рд╛рд╕_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 рдкреНрд░рддрд┐рдХреНрд╖рд╛рд╣рд░реВ +------+---------------+--- ------------------+---------------------- | #| рдкреНрд░рддреАрдХреНрд╖рд╛_рдШрдЯрдирд╛_рдкреНрд░рдХрд╛рд░| рдкреНрд░рддреАрдХреНрд╖рд╛_рдШрдЯрдирд╛ | рдЕрд╡рдзрд┐ +------+----------------------------+------------ -------+------------ | рез| рдЧрддрд┐рд╡рд┐рдзрд┐ | LogicalLauncherMain| резреж:рекрей:реирео | реи| рдЧрддрд┐рд╡рд┐рдзрд┐ | AutoVacuumMain| резреж:рекреи:рекреп | рей| рдЧрддрд┐рд╡рд┐рдзрд┐ | WalWriterMain | резреж:реирео:релрей | рек| рдЧрддрд┐рд╡рд┐рдзрд┐ | CheckpointerMain| резреж:реирей:релреж | рел| рдЧрддрд┐рд╡рд┐рдзрд┐ | BgWriterMain| режреп:резрез:релреп | рем| рдЧрддрд┐рд╡рд┐рдзрд┐ | BgWriterHibernate| режрез:рейрен:рекрем | рен | IO| рдмрдлрдлрд╛рдЗрд▓рд░рд╛рдЗрдЯ| 1:10:43 | рео| LWLock | buffer_mapping | 28:2:10 | реп| IO| DataFileRead| 42:49:3 | резреж| IO| WALWrite | 10:28:53 +------+-----------------------------------------+------ --------------+------------
рдЧреНрд░рд╛рд╣рдХ рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ рдХреБрд▓ рдкреНрд░рддреАрдХреНрд╖рд╛ рд╕рдордп рджреНрд╡рд╛рд░рд╛ рд╢реАрд░реНрд╖ 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 +------+--------------------------------------------+--------- -----------+--------- +------------ | рез| рддрд╛рд▓рд╛ | рд▓реЗрдирджреЗрди режрео:резрем:рекрен | рем.режрел | реи| IO| DataFileRead| режрем:резрей:рекрез | рек.релрел | рей| рдЯрд╛рдЗрдордЖрдЙрдЯ | PgSleep | режреи:релрей:реирез | 1 | рек| LWLock | buffer_mapping | 08:16:47 | реж.рел | рел| LWLock | buffer_io| 6.05:2:06 | реж.реирез | рем| IO| рдмрдлрдлрд╛рдЗрд▓рд░рд╛рдЗрдЯ| 13:41:4.55 | реж.режреи | рен | рддрд╛рд▓рд╛ | рдЯрдкрд▓ | 3:02:53 | реж.режреи | рео| рдЧреНрд░рд╛рд╣рдХ | рдЧреНрд░рд╛рд╣рдХ рдкрдвреНрдиреБрд╣реЛрд╕реН | 21:2.11:4 | реж.режреи | реп| IO| рдмрдлрдлрд╛рдЗрд▓рд░реАрдб| 00:40:42 | реж.режрез | резреж| LWLock | buffer_content | 0.5:5:00 | реж +------+----------------------------+------------ --------+------------+---------
рдкреНрд░рдгрд╛рд▓реА рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ рдХреБрд▓ рдкрд░реНрдЦрдиреЗ рд╕рдордп рджреНрд╡рд╛рд░рд╛ рдкреНрд░рддреАрдХреНрд╖рд╛ рдкреНрд░рдХрд╛рд░рд╣рд░реВ
рдЕрдиреБрд░реЛрдз
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
рдЙрджрд╛рд╣рд░рдг:
+------------ +---------------------------- +--------- ------------ | #| рдкреНрд░рддреАрдХреНрд╖рд╛_рдШрдЯрдирд╛_рдкреНрд░рдХрд╛рд░| рдЕрд╡рдзрд┐ +------+----------------------------+------------ ------- | рез| рдЧрддрд┐рд╡рд┐рдзрд┐ | релрей:режрео:рекрел | реи| IO| 1:53:08 | рей| LWLock | 45:2:00 +------+-----------------------------------------+------ --------------
рдХреБрд▓ рдкреНрд░рддреАрдХреНрд╖рд╛ рд╕рдордп рджреНрд╡рд╛рд░рд╛ рдкреНрд░рддреАрдХреНрд╖рд╛ рдкреНрд░рдХрд╛рд░, рдЧреНрд░рд╛рд╣рдХ рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реБ рдХреЛ рд▓рд╛рдЧреА
рдЕрдиреБрд░реЛрдз
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 +------+--------------------------------------------+--------- -----------+---------------------- | рез| рддрд╛рд▓рд╛ | режрео:резрео:резреп | рем.режрен | реи| IO| режрем:резрем:режрез | рек.релрео | рей| рдЯрд╛рдЗрдордЖрдЙрдЯ | режреи:релрей:реирез | 1 | рек| LWLock | 08:18:19 | реж.ренрез | рел| рдЧреНрд░рд╛рд╣рдХ | 6.07:2:06 | реж.режреи | рем| рдЖрдИрдкреАрд╕реА| 16:01:4.58 | реж +------+----------------------------+------------ -------------------------------------------
рдкреНрд░рдгрд╛рд▓реА рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реВ рд░ рд╡реНрдпрдХреНрддрд┐рдЧрдд рдЕрдиреБрд░реЛрдзрд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ рдЕрд╡рдзрд┐ рдкрд░реНрдЦрдиреБрд╣реЛрд╕реНред
рдкреНрд░рдгрд╛рд▓реА рдкреНрд░рдХреНрд░рд┐рдпрд╛рд╣рд░реВрдХреЛ рд▓рд╛рдЧрд┐ рдкреНрд░рддреАрдХреНрд╖рд╛ рдЧрд░реНрджреИ
рдЕрдиреБрд░реЛрдз
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 +-----+-----------------------------+----------+--------------------+----------------------+--------------------
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| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
рдЧреНрд░рд╛рд╣рдХ SQL STATICTICS - рд╢реАрд░реНрд╖ рдкреНрд░рд╢реНрдирд╣рд░реВ
рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрдирдХрд╛ рд▓рд╛рдЧрд┐ рдЕрдиреБрд░реЛрдзрд╣рд░реВ рдлреЗрд░рд┐ рддреБрдЪреНрдЫ рдЫрдиреН рд░ рдард╛рдЙрдБ рдмрдЪрдд рдЧрд░реНрди рджрд┐рдЗрдПрдХреЛ рдЫреИрдиред
рдЙрджрд╛рд╣рд░рдг
+------------------------------------------------------------------------------------ | 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 рд╡рд┐рд╕реНрддрд╛рд░ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрдиреБрд╣реЛрд╕реНред
- рдЙрддреНрдкрд╛рджрди рдЖрдзрд╛рд░рд╣рд░реВрдорд╛ рдареВрд▓реЛ рдкрд░рд┐рдирд┐рдпреЛрдЬрдирдХреЛ рд▓рд╛рдЧрд┐ github рдорд╛ рдкреНрдпрд╛рдХреЗрдЬ рд╕рдорд╛рдзрд╛рди рддрдпрд╛рд░ рдЧрд░реНрдиреБрд╣реЛрд╕реНред
рдлреЗрд░рд┐ рдЬрд╛рд░реА рд░рд╛рдЦреМрдВрд▓рд╛тАж
рд╕реНрд░реЛрдд: www.habr.com