1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
-- This program is open source, licensed under the PostgreSQL License.
-- For license terms, see the LICENSE file.
--
-- Copyright (c) 2014-2017, Dalibo
-- Copyright (c) 2018-2024, The PoWA-team
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pg_stat_kcache" to load this file. \quit
SET client_encoding = 'UTF8';
CREATE FUNCTION pg_stat_kcache(
OUT queryid bigint,
OUT userid oid,
OUT dbid oid,
OUT reads bigint, /* total reads, in bytes */
OUT writes bigint, /* total writes, in bytes */
OUT user_time double precision, /* total user CPU time used */
OUT system_time double precision, /* total system CPU time used */
OUT minflts bigint, /* total page reclaims (soft page faults) */
OUT majflts bigint, /* total page faults (hard page faults) */
OUT nswaps bigint, /* total swaps */
OUT msgsnds bigint, /* total IPC messages sent */
OUT msgrcvs bigint, /* total IPC messages received */
OUT nsignals bigint, /* total signals received */
OUT nvcsws bigint, /* total voluntary context switches */
OUT nivcsws bigint /* total involuntary context switches */
)
RETURNS SETOF record
LANGUAGE c COST 1000
AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_2_1';
GRANT ALL ON FUNCTION pg_stat_kcache() TO public;
CREATE FUNCTION pg_stat_kcache_reset()
RETURNS void
LANGUAGE c COST 1000
AS '$libdir/pg_stat_kcache', 'pg_stat_kcache_reset';
REVOKE ALL ON FUNCTION pg_stat_kcache_reset() FROM public;
CREATE VIEW pg_stat_kcache_detail AS
SELECT s.query, d.datname, r.rolname,
k.user_time,
k.system_time,
k.minflts,
k.majflts,
k.nswaps,
k.reads AS reads,
k.reads/(current_setting('block_size')::integer) AS reads_blks,
k.writes AS writes,
k.writes/(current_setting('block_size')::integer) AS writes_blks,
k.msgsnds,
k.msgrcvs,
k.nsignals,
k.nvcsws,
k.nivcsws
FROM pg_stat_kcache() k
JOIN pg_stat_statements s
ON k.queryid = s.queryid AND k.dbid = s.dbid AND k.userid = s.userid
JOIN pg_database d
ON d.oid = s.dbid
JOIN pg_roles r
ON r.oid = s.userid;
GRANT SELECT ON pg_stat_kcache_detail TO public;
CREATE VIEW pg_stat_kcache AS
SELECT datname,
SUM(user_time) AS user_time,
SUM(system_time) AS system_time,
SUM(minflts) AS minflts,
SUM(majflts) AS majflts,
SUM(nswaps) AS nswaps,
SUM(reads) AS reads,
SUM(reads_blks) AS reads_blks,
SUM(writes) AS writes,
SUM(writes_blks) AS writes_blks,
SUM(msgsnds) AS msgsnds,
SUM(msgrcvs) AS msgrcvs,
SUM(nsignals) AS nsignals,
SUM(nvcsws) AS nvcsws,
SUM(nivcsws) AS nivcsws
FROM pg_stat_kcache_detail
GROUP BY datname;
GRANT SELECT ON pg_stat_kcache TO public;
|
