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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
CREATE TABLE ps_snaps(
snap BIGSERIAL PRIMARY KEY,
note TEXT,
time TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- PostgreSQL Processes Stats
CREATE TABLE ps_procstat(
snap BIGINT,
pid INTEGER,
comm VARCHAR,
fullcomm VARCHAR,
state CHAR,
ppid INTEGER,
pgrp INTEGER,
session INTEGER,
tty_nr INTEGER,
tpgid INTEGER,
flags INTEGER,
minflt BIGINT,
cminflt BIGINT,
majflt BIGINT,
cmajflt BIGINT,
utime BIGINT,
stime BIGINT,
cutime BIGINT,
cstime BIGINT,
priority BIGINT,
nice BIGINT,
num_threads BIGINT,
itrealvalue BIGINT,
starttime BIGINT,
vsize BIGINT,
rss BIGINT,
exit_signal INTEGER,
processor INTEGER,
rt_priority BIGINT,
policy BIGINT,
delayacct_blkio_ticks BIGINT,
uid INTEGER,
username VARCHAR,
rchar BIGINT,
wchar BIGINT,
syscr BIGINT,
syscw BIGINT,
reads BIGINT,
writes BIGINT,
cwrites BIGINT,
datid BIGINT,
datname NAME,
usesysid BIGINT,
usename NAME,
current_query TEXT,
waiting BOOLEAN,
query_start TIMESTAMP WITH TIME ZONE,
backend_start TIMESTAMP WITH TIME ZONE,
client_addr INET,
client_port INTEGER,
CONSTRAINT proc_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
-- PostgreSQL Database Stats
CREATE TABLE ps_dbstat(
snap BIGINT,
datid BIGINT,
datname NAME,
numbackends INTEGER,
xact_commit BIGINT,
xact_rollback BIGINT,
blks_read BIGINT,
blks_hit BIGINT,
PRIMARY KEY (snap, datid),
CONSTRAINT db_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
-- PostgreSQL Table Stats
CREATE TABLE ps_tablestat(
snap BIGINT,
relid BIGINT,
schemaname NAME,
relname NAME,
seq_scan BIGINT,
seq_tup_read BIGINT,
idx_scan BIGINT,
idx_tup_fetch BIGINT,
n_tup_ins BIGINT,
n_tup_upd BIGINT,
n_tup_del BIGINT,
last_vacuum TIMESTAMP WITH TIME ZONE,
last_autovacuum TIMESTAMP WITH TIME ZONE,
last_analyze TIMESTAMP WITH TIME ZONE,
last_autoanalyze TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (snap, relid),
CONSTRAINT table_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
-- PostgreSQL Index Stats
CREATE TABLE ps_indexstat(
snap BIGINT,
relid BIGINT,
indexrelid BIGINT,
schemaname NAME,
relname NAME,
indexrelname NAME,
idx_scan BIGINT,
idx_tup_read BIGINT,
idx_tup_fetch BIGINT,
PRIMARY KEY (snap, relid, indexrelid),
CONSTRAINT index_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
-- System Processor Stats
CREATE TABLE ps_cpustat(
snap BIGINT PRIMARY KEY,
cpu_user BIGINT,
cpu_nice BIGINT,
cpu_system BIGINT,
cpu_idle BIGINT,
cpu_iowait BIGINT,
cpu_swap BIGINT,
CONSTRAINT cpu_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
-- System Memory Stats
CREATE TABLE ps_memstat(
snap BIGINT PRIMARY KEY,
memused BIGINT,
memfree BIGINT,
memshared BIGINT,
membuffers BIGINT,
memcached BIGINT,
swapused BIGINT,
swapfree BIGINT,
swapcached BIGINT,
CONSTRAINT mem_snap
FOREIGN KEY (snap)
REFERENCES ps_snaps (snap)
);
|
