-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathjob_config.yml
308 lines (284 loc) · 11.5 KB
/
job_config.yml
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
---
jobs:
# - 达梦
- name: "dm"
interval: '5s'
connections:
- 'dm://[USERNAME]:[PASSWORD]@[HOST]:[PORT]'
queries:
# 数据库qps
- name: "qps"
help: "DM QPS"
labels:
- "qps"
values:
- "stat_val"
query: |
select stat_val from v$sysstat where name = 'select statements';
# 表空间使用率 %
- name: "table_space_vacancy"
help: "DM table space vacancy"
labels:
- "tablespace_name"
values:
- "VACANCY"
query: |
SELECT A.TABLESPACE_NAME as tablespace_name, (100-ROUND((B.FREE / A.TOTAL * 100), 3)) AS VACANCY FROM ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, ( SELECT TABLESPACE_NAME, SUM(BYTES)/ 1024 / 1024 AS FREE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
# 数据库tps
- name: "tps"
help: "DM TPS"
labels:
- "tps"
values:
- "stat_val"
query: |
select stat_val from v$sysstat where name = 'transaction total count';
# 集群延迟时间 ms
- name: "cluster_delay_time"
help: "DM cluster delay time"
labels:
- "cluster_delay_time"
values:
- "timediff"
query: |
select TIMESTAMPDIFF(SQL_TSI_SECOND,APPLY_CMT_TIME,LAST_CMT_TIME) timediff from v$rapply_stat;
# 最大连接数
- name: "mx_connection"
help: "DM max connection"
labels:
- "max_conn"
values:
- "para_value"
query: |
select para_value from v$dm_ini where para_name = 'MAX_SESSIONS';
# 锁数量
- name: "total_lock"
help: "DM total lock number"
labels:
- "total_lock"
values:
- "COUNT"
query: |
SELECT COUNT(*) COUNT FROM V$LOCK;
# 死锁数量
- name: "dead_lock"
help: "DM total dead lock number"
labels:
- "dead_lock"
values:
- "DEADLOCKNUM"
query: |
SELECT COUNT(*) DEADLOCKNUM FROM V$LOCK WHERE BLOCKED=1;
# 事务数量
- name: "transaction_number"
help: "DM transaction number"
labels:
- "transaction_number"
values:
- "TRXNUM"
query: |
SELECT COUNT(*) TRXNUM FROM V$TRXWAIT;
# 线程数
- name: "total_thread"
help: "DM total thread number"
labels:
- "total_thread"
values:
- "THREADSNUM"
query: |
SELECT COUNT(*) THREADSNUM FROM V$THREADS;
# 表空间总大小
- name: "table_total_space"
help: "DM table total space"
labels:
- "tablespace_name"
values:
- "TOTAL_SIZE"
query: |
SELECT F.TABLESPACE_NAME as tablespace_name, T.TOTAL_SPACE AS TOTAL_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
# 表空间空闲大小
- name: "table_free_space"
help: "DM table free space"
labels:
- "tablespace_name"
values:
- "FREE_SIZE"
query: |
SELECT F.TABLESPACE_NAME as tablespace_name, F.FREE_SPACE AS FREE_SIZE FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME = 'GLOBAL_PAGE_SIZE') / 1024)) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;
# 数据库会话数
- name: "session_count"
help: "DM session count"
labels:
- "session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE IN ('IDLE', 'ACTIVE');
# 数据库空闲会话数
- name: "free_session_count"
help: "DM free session count"
labels:
- "free_session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'IDLE';
# 数据库活跃会话数
- name: "active_session_count"
help: "DM active session count"
labels:
- "active_session_count"
values:
- "COUNT"
query: |
SELECT COUNT(SESS_ID) AS COUNT FROM V$SESSIONS WHERE STATE = 'ACTIVE';
# 慢执行数量（大于2s）
- name: "slow_sql_num"
help: "DM slow sql number"
labels:
- "slow_sql_num"
values:
- "NUM"
query: |
SELECT COUNT(*) NUM FROM ( SELECT SESS_ID, SQL_TEXT, DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) Y_EXETIME, SF_GET_SESSION_SQL(SESS_ID) FULLSQL, CLNT_IP FROM V$SESSIONS WHERE STATE = 'ACTIVE' ) WHERE Y_EXETIME >= 2;
# 内存池总量（MB）
- name: "zone_total_space"
help: "DM zone total space"
labels:
- "zone_type"
values:
- "TOTAL_VAL"
query: |
SELECT ZONE_TYPE as zone_type,TOTAL_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);
# 内存池已使用量（MB）
- name: "zone_used_space"
help: "DM zone used space"
labels:
- "zone_type"
values:
- "CURR_VAL"
query: |
SELECT ZONE_TYPE as zone_type,CURR_VAL FROM ( SELECT 'HJ ZONE' AS ZONE_TYPE, ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (114, 115)) AS CURR_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (145)) AS RES_VAL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (144)) AS TOTAL_VAL FROM DUAL UNION ALL SELECT 'HAGR ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (116)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (143)),( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (142)) FROM DUAL UNION ALL SELECT 'SORT ZONE', ( SELECT SUM(STAT_VAL) FROM V$SYSSTAT WHERE ID IN (178)), NULL, ( SELECT STAT_VAL FROM V$SYSSTAT WHERE ID IN (177)) FROM DUAL);
# - 金仓
- name: "kingbase"
interval: '5s'
connections:
- 'kingbase://SYSTEM:[email protected]:54321/TEST?sslmode=disable'
queries:
# 数据库最大连接数
- name: "max_conn"
help: "Kingbase Max conn"
labels:
- "max_conn"
values:
- "MAX_CONN"
query: |
SELECT MAX_CONN FROM( SELECT setting::int8 AS MAX_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;
# 数据库当前连接数
- name: "now_conn"
help: "Kingbase current conn"
labels:
- "now_conn"
values:
- "NOW_CONN"
query: |
SELECT NOW_CONN FROM( SELECT setting::int8 AS max_conn, ( SELECT COUNT ( * ) FROM sys_stat_activity ) AS NOW_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;
# 数据库剩余连接数
- name: "left_conn"
help: "Kingbase remain conn"
labels:
- "remain_conn"
values:
- "REMAIN_CONN"
query: |
SELECT max_conn - now_conn "REMAIN_CONN" FROM( SELECT setting::int8 AS max_conn, ( SELECT COUNT ( * ) FROM sys_stat_activity ) AS now_conn FROM sys_settings WHERE NAME = 'max_connections' ) T;
# 表空间大小(kb)
- name: "spac_size"
help: "Kingbase space size"
labels:
- "spcname"
values:
- "size"
query: |
SELECT spcname,sys_tablespace_size(oid)/1024 AS "size" FROM sys_tablespace ORDER BY 1;
# 数据库连接限制数
- name: "limit_conn"
help: "Kingbase Max conn"
labels:
- "limit_conn"
values:
- "MAX_CONN"
query: |
SELECT MAX_CONN FROM( SELECT setting::int8 AS MAX_CONN FROM sys_settings WHERE NAME = 'max_connections' ) T;
# 数据库连接活动数
- name: "actv_conn"
help: "Kingbase active conn"
labels:
- "actv_conn"
values:
- "ACTV_CONN"
query: |
select ACTV_CONN from (select sum(case when state='active' then 1 else 0 end) ACTV_CONN from sys_stat_activity) t;
# 数据库连接可用数
- name: "remain_conn"
help: "Kingbase remain conn"
labels:
- "remain_conn"
values:
- "REMAIN_CONN"
query: |
select max_conn - actv - idle - res_for_super "REMAIN_CONN" from (select sum(case when state='active' then 1 else 0 end) actv,sum(case when state='active' then 0 else 1 end) idle,CURRENT_SETTING('superuser_reserved_connections')::int res_for_super,CURRENT_SETTING('max_connections') ::int max_conn from sys_stat_activity) t;
# 回滚比例
- name: "rollback_rate"
help: "Kingbase rollback rate"
labels:
- "datname"
values:
- "rollback"
query: |
select datname ,round(100 * xact_rollback::numeric /(xact_commit + xact_rollback + 1), 2) "rollback" from sys_stat_database where lower(datname) !~ 'template';
# 命中比例
- name: "hit_rate"
help: "Kingbase hit rate conn"
labels:
- "datname"
values:
- "HIT_RATE"
query: |
select datname,round(100 * blks_hit::numeric / (blks_read + blks_hit + 1), 2) "HIT_RATE" from sys_stat_database where lower(datname) !~ 'template';
# 数据块读磁盘用时（秒）
- name: "read_time"
help: "Kingbase read time"
labels:
- "datname"
values:
- "READ_TIME"
query: |
select datname,round(blk_read_time / 1000) "READ_TIME" from sys_stat_database where lower(datname) !~ 'template';
# 数据库写磁盘用时（秒)
- name: "write_time"
help: "Kingbase write time"
labels:
- "datname"
values:
- "WRITE_TIME"
query: |
select datname,round(blk_write_time / 10000) "WRITE_TIME" from sys_stat_database where lower(datname) !~ 'template';
# 死锁数
- name: "dead_lock_num"
help: "Kingbase dead lock num"
labels:
- "datname"
values:
- "DEAD_LOCK"
query: |
select datname,deadlocks "DEAD_LOCK" from sys_stat_database where lower(datname) !~ 'template';
# 复制冲突数
- name: "replication_conflict_num"
help: "Kingbase replication conflict num"
labels:
- "datname"
values:
- "REPLICATION_CONFLICT"
query: |
select datname,conflicts "REPLICATION_CONFLICT" from sys_stat_database where lower(datname) !~ 'template';