CollMySQLBySql.java
25 KB
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
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
package com.sitech.ismp.coll;
import com.sitech.ismp.check.downtime.CheckConnectionState;
import com.sitech.ismp.check.downtime.ParamterBean;
import com.sitech.ismp.coll.basic.TblATO_KPIDETAIL;
import com.sitech.util.DES3;
import com.sitech.util.Formater;
import com.sitech.util.JSONUtil;
import org.apache.log4j.Logger;
import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;
import java.util.Vector;
/**
* Created with IntelliJ IDEA.
* User: mooker
* Date: 14-3-20
* Time: 下午1:32
* To change this template use File | Settings | File Templates.
*/
public class CollMySQLBySql {
private Logger logger = Logger.getLogger("COLL");
private static Logger log = Logger.getLogger(CollMySQLBySql.class);
private String KBP_CLASS = "10-11-35";
private String DB_TYPE = "MySQL";
private String CLASSNAME = "com.mysql.jdbc.Driver";
private String dburl = null;
private String dbuser = null;
private String passwd = null;
private String ip = null; // 2017-04-24 frank 添加，用于获取数据库所在主机的ping状态
private String device_id = null;
private String NEATENSID = null;
private boolean getParams(HashMap<String, String> params) {
try {
dburl = params.get("DB_URL");
int start=dburl.indexOf("/");
int end=dburl.lastIndexOf(":");
ip=dburl.substring(start+2,end);
dbuser = params.get("USERNAME");
passwd = DES3.decrypt(params.get("PASSWORD"));
device_id = (String) params.get("DEVICE_ID");
NEATENSID = Formater.neatenunitid(this.device_id);
log.info("*****ip ="+ip+"**dbuser="+dbuser+"*************************");
} catch (Exception e) {
throw new RuntimeException("Exception while init params:" + JSONUtil.toJSON(params), e);
}
return true;
}
/**
* 获取数据库基本信息，frank 2017-04-24 修改，添加设备状态(UP/DOWN)即主机ping的结果。
* @param params
* @return
* @throws Exception
*/
public Vector<TblATO_KPIDETAIL> getSystem(HashMap<String, String> params) throws Exception {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
getSystem(colldb);
Vector sets = colldb.getKPISet();
colldb.release();
return sets;
}
/**
* 获取数据库基本信息
* @param colldb
*/
private void getSystem(SQLTarget colldb) {
String UNIT_ID = KBP_CLASS + "-10" + ":" + NEATENSID + "-SYS";
// CM-00-03-001-02
colldb.addKPI(UNIT_ID, "CM-00-03-001-02", colldb.getSQLKPIInfo("select version()"));
// 全库数据量 PM-00-03-009-12
colldb.addKPI(UNIT_ID, "PM-00-03-009-12", colldb.getSQLKPIInfo("select round(sum(DATA_LENGTH/1024/1024/1024), 2) from information_schema.tables"));
// TABLE_SCHEMA
Vector tableSchema = colldb.getSQLKPIResult("select TABLE_SCHEMA, round(sum(DATA_LENGTH/1024/1024/1024), 2) TS_LENGTH from information_schema.tables group by TABLE_SCHEMA");
for (int i = 0; i < tableSchema.size(); i++) {
String tableschema = (String) ((Vector) tableSchema.elementAt(i)).get(0);
// scheme名称 CM-00-03-001-30
colldb.addKPI(KBP_CLASS + "-12" + ":" + NEATENSID + "-" + tableschema, "CM-00-03-001-30", tableschema);
// 数据量大小 PM-00-03-011-56
colldb.addKPI(KBP_CLASS + "-12" + ":" + NEATENSID + "-" + tableschema, "PM-00-03-011-56", (String) ((Vector) tableSchema.elementAt(i)).get(1));
}
// 会话数量 PM-00-03-009-03
colldb.addKPI(UNIT_ID, "PM-00-03-009-03", colldb.getSQLKPISet("show full processlist").size() + "");
// 数据库的在线情况 PM-00-03-009-17
colldb.addKPI(UNIT_ID, "PM-00-03-009-17", colldb.getSQLKPISet("select user()").size() + "");
// PM-00-03-011-09
colldb.addKPI(UNIT_ID, "PM-00-03-011-09", colldb.getSQLKPISetInfo("show global status like 'Created_tmp_disk_tables'").split(",")[2]);
// 内存临时表数量 PM-00-03-011-10
colldb.addKPI(UNIT_ID, "PM-00-03-011-10", colldb.getSQLKPISetInfo("show global status like 'Created_tmp_tables'").split(",")[2]);
// 已经创建的临时文件的数量 PM-00-03-011-38
colldb.addKPI(UNIT_ID, "PM-00-03-011-38", colldb.getSQLKPISetInfo("show global status like 'Created_tmp_files'").split(",")[2]);
// PM-00-03-011-11
colldb.addKPI(UNIT_ID, "PM-00-03-011-11", colldb.getSQLKPISetInfo("show global status like 'Aborted_clients'").split(",")[2]);
String uptime = colldb.getSQLKPISetInfo("show global status like 'Uptime'").split(",")[2];
String keyReadRequests = colldb.getSQLKPISetInfo("show global status like 'Key_read_requests'").split(",")[2];
// 从缓存读键的数据块的请求数 PM-00-03-011-28
colldb.addKPI(UNIT_ID, "PM-00-03-011-28", keyReadRequests);
String keyReads = colldb.getSQLKPISetInfo("show global status like 'Key_reads'").split(",")[2];
// 读文件系统上面的索引的次数 PM-00-03-011-19
colldb.addKPI(UNIT_ID, "PM-00-03-011-19", keyReads);
String keyWriteRequests = colldb.getSQLKPISetInfo("show global status like 'key_write_requests'").split(",")[2];
// 将键的数据块写入缓存的请求数 PM-00-03-011-29
colldb.addKPI(UNIT_ID, "PM-00-03-011-29", keyWriteRequests);
String keyWrites = colldb.getSQLKPISetInfo("show global status like 'key_writes'").split(",")[2];
// 向硬盘物理写操作的次数 PM-00-03-011-30
colldb.addKPI(UNIT_ID, "PM-00-03-011-30", keyWrites);
// key Buffer 命中率 PM-00-03-011-13
String _keyReads = String.valueOf(Float.valueOf(keyReadRequests) - Float.valueOf(keyReads));
// colldb.addKPI(UNIT_ID, "PM-00-03-011-13", (100.0 - Float.parseFloat(Formater.dev2IntIntoFloat(keyReads, keyReadRequests, 2))) + "");
colldb.addKPI(UNIT_ID, "PM-00-03-011-13", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(_keyReads, keyReadRequests, 2)));
colldb.addKPI(UNIT_ID, "CM-00-03-003-02", colldb.getSQLKPIInfo(
"SELECT ROUND(SUM(data_length) / 1024, 2) " +
"FROM information_schema.TABLES WHERE table_schema = '" + device_id + "'"));
// TPS(每秒事务量) PM-00-03-011-14
String comCommit = colldb.getSQLKPISetInfo("show global status like 'Com_commit'").split(",")[2];
String comRollback = colldb.getSQLKPISetInfo("show global status like 'Com_rollback'").split(",")[2];
Float comTotal = Float.parseFloat(comCommit) + Float.parseFloat(comRollback);
colldb.addKPI(UNIT_ID, "PM-00-03-011-14", Formater.formatDecimalByScale(String.valueOf(comTotal * 100 / Float.parseFloat(uptime)), 2));
String qcacheHits = colldb.getSQLKPISetInfo("show global status like 'Qcache_hits'").split(",")[2];
// 查询缓存被访问的次数 PM-00-03-011-31
colldb.addKPI(UNIT_ID, "PM-00-03-011-31", qcacheHits);
String qcacheInserts = colldb.getSQLKPISetInfo("show global status like 'Qcache_inserts'").split(",")[2];
// 加入到缓存的查询数量 PM-00-03-011-32
colldb.addKPI(UNIT_ID, "PM-00-03-011-32", qcacheInserts);
// Query Cache命中率 PM-00-03-011-15 (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
Float qcacheTotal = Float.parseFloat(qcacheHits) + Float.parseFloat(qcacheInserts);
if (0 < qcacheTotal) {
// colldb.addKPI(UNIT_ID, "PM-00-03-011-15", Formater.formatDecimalByScale(String.valueOf(Float.parseFloat(qcacheHits) * 100 / qcacheTotal), 2));
colldb.addKPI(UNIT_ID, "PM-00-03-011-15", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(qcacheHits, String.valueOf(qcacheTotal), 2)));
} else {
colldb.addKPI(UNIT_ID, "PM-00-03-011-15", "0.00");
}
// 临时文件缓存的事务数量 PM-00-03-011-16 缓存二进制日志用到了临时文件的次数
colldb.addKPI(UNIT_ID, "PM-00-03-011-16", colldb.getSQLKPISetInfo("show global status like 'Binlog_cache_disk_use'").split(",")[2]);
// 日志缓存的事务数量 PM-00-03-011-17 用binlog_cache_size缓存的次数
colldb.addKPI(UNIT_ID, "PM-00-03-011-17", colldb.getSQLKPISetInfo("show global status like 'Binlog_cache_use'").split(",")[2]);
// 非事务语句使用二进制日志缓存 PM-00-03-011-39
colldb.addKPI(UNIT_ID, "PM-00-03-011-39", colldb.getSQLKPISetInfo("show global status like 'Binlog_stmt_cache_disk_use'").split(",")[2]);
// 临时文件缓存的非事务数量 PM-00-03-011-40
colldb.addKPI(UNIT_ID, "PM-00-03-011-40", colldb.getSQLKPISetInfo("show global status like 'Binlog_stmt_cache_use'").split(",")[2]);
}
public Vector<TblATO_KPIDETAIL> getPerformance(HashMap<String,String> params) {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
String UNIT_ID = KBP_CLASS + "-10" + ":" + NEATENSID + "-SYS";
if (colldb.getState()) {
// 运行时间 CM-00-01-999-03
colldb.addKPI(UNIT_ID, "CM-00-01-999-03", colldb.getSQLKPISetInfo("show status like 'Uptime'").split(",")[2]);
// 数据库连接数 PM-00-03-009-15
colldb.addKPI(UNIT_ID, "PM-00-03-009-15", colldb.getSQLKPISetInfo("show global status like 'Threads_connected'").split(",")[2]);
// 持续查询的语句 PM-00-03-011-24
colldb.addKPI(UNIT_ID, "PM-00-03-011-24", colldb.getSQLKPISetInfo("show global status like 'Threads_running'").split(",")[2]);
// 持续增加的连接数 PM-00-03-011-27
String threadsCreated = colldb.getSQLKPISetInfo("show global status like 'Threads_created'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-27", threadsCreated);
// Thread Cache 命中率 PM-00-03-011-43 (1 - Threads_created / connections ) * 100%
String connections = colldb.getSQLKPISetInfo("show global status like 'Connections'").split(",")[2];
String _threadCache = String.valueOf(Float.valueOf(connections) - Float.valueOf(threadsCreated));
colldb.addKPI(UNIT_ID, "PM-00-03-011-43", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(_threadCache, connections, 2)));
// 全索引的扫描次数 PM-00-03-009-16
colldb.addKPI(UNIT_ID, "PM-00-03-009-16", colldb.getSQLKPISetInfo("show global status like 'Handler_read_first'").split(",")[2]);
// 重启后到现在最大连接数 PM-00-03-011-20
colldb.addKPI(UNIT_ID, "PM-00-03-011-20", colldb.getSQLKPISetInfo("show global status like 'Max_used_connections'").split(",")[2]);
// 所有已经打开表的数量 PM-00-03-011-12
String openedTables = colldb.getSQLKPISetInfo("show global status like 'Opened_tables'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-12", openedTables);
// 当前正在打开表的数量 PM-00-03-011-21
String openTables = colldb.getSQLKPISetInfo("show global status like 'Open_tables'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-21", openTables);
// Table Cache状态量 PM-00-03-011-42 Open_tables / Opened_tables
colldb.addKPI(UNIT_ID, "PM-00-03-011-42", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(openTables, openedTables, 2)));
// 全连接的查询数目 PM-00-03-011-22
colldb.addKPI(UNIT_ID, "PM-00-03-011-22", colldb.getSQLKPISetInfo("show global status like 'Select_full_join'").split(",")[2]);
// 全表搜索查询的数量 PM-00-03-011-26
colldb.addKPI(UNIT_ID, "PM-00-03-011-26", colldb.getSQLKPISetInfo("show global status like 'Select_scan'").split(",")[2]);
// 慢查询的数目 PM-00-03-011-23
colldb.addKPI(UNIT_ID, "PM-00-03-011-23", colldb.getSQLKPISetInfo("show global status like 'Slow_queries'").split(",")[2]);
// 从数据库状态 FM-00-03-001-10
colldb.addKPI(UNIT_ID, "FM-00-03-001-10", colldb.getSQLKPISetInfo("show global status like 'Slave_running'").split(",")[2]);
// QPS(每秒钟获得的查询数量) PM-00-03-011-25
String uptime = colldb.getSQLKPISetInfo("show global status like 'Uptime'").split(",")[2];
String questions = colldb.getSQLKPISetInfo("show global status like 'Questions'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-25", Formater.formatDecimalByScale(String.valueOf(Float.parseFloat(questions) / Float.parseFloat(uptime)), 2));
// 打开的文件的数目 PM-00-03-011-33
colldb.addKPI(UNIT_ID, "PM-00-03-011-33", colldb.getSQLKPISetInfo("show global status like 'Open_files'").split(",")[2]);
// 打开的流的数量 PM-00-03-011-34
colldb.addKPI(UNIT_ID, "PM-00-03-011-34", colldb.getSQLKPISetInfo("show global status like 'Open_streams'").split(",")[2]);
// 被缓存的FRM文件数量 PM-00-03-011-35
colldb.addKPI(UNIT_ID, "PM-00-03-011-35", colldb.getSQLKPISetInfo("show global status like 'Open_table_definitions'").split(",")[2]);
// 已经打开的表的数量 PM-00-03-011-36
colldb.addKPI(UNIT_ID, "PM-00-03-011-36", colldb.getSQLKPISetInfo("show global status like 'Opened_files'").split(",")[2]);
// 被缓存过的FRM文件的数量 PM-00-03-011-37
colldb.addKPI(UNIT_ID, "PM-00-03-011-37", colldb.getSQLKPISetInfo("show global status like 'Opened_table_definitions'").split(",")[2]);
// 锁定状态 PM-00-03-011-44 Table_locks_waited / Table_locks_immediate
String tableLocksWaited = colldb.getSQLKPISetInfo("show global status like 'Table_locks_waited'").split(",")[2];
String tablelocksImmediate = colldb.getSQLKPISetInfo("show global status like 'Table_locks_immediate'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-44", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(tableLocksWaited, tablelocksImmediate, 2)));
// Tmp Table 状况(临时表状况) PM-00-03-011-45 Created_tmp_disk_tables/Created_tmp_tables
String createdTmpDiskTables = colldb.getSQLKPISetInfo("show global status like 'Created_tmp_disk_tables'").split(",")[2];
String createdTmpTables = colldb.getSQLKPISetInfo("show global status like 'Created_tmp_tables'").split(",")[2];
colldb.addKPI(UNIT_ID, "PM-00-03-011-45", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(createdTmpDiskTables, createdTmpTables, 2)));
String bytesReceived = colldb.getSQLKPISetInfo("show global status like 'Bytes_received'").split(",")[2];
String bytesSend = colldb.getSQLKPISetInfo("show global status like 'Bytes_sent'").split(",")[2];
String _bytesTotal = String.valueOf((Float.valueOf(bytesReceived) / 1024 / 1024) + (Float.valueOf(bytesSend) / 1024 / 1024));
colldb.addKPI(UNIT_ID, "PM-00-03-011-54", _bytesTotal);
String vProcessList = colldb.getSQLKPISetInfo("show full processlist");
String[] arrProcessList = vProcessList.split("\n");
int i = 0;
int j = 0;
for (String processInfo : arrProcessList) {
if (processInfo.length() > 0) {
String processCommand = processInfo.split(",")[5];
if ("sleep".equalsIgnoreCase(processCommand)) {
i++;
} else {
j++;
}
}
}
colldb.addKPI(UNIT_ID, "PM-00-03-011-47", "sleep: " + i + " Query: " + j);
logger.info("----mysql--- " + device_id + "--state--ok----");
} else {
colldb.addKPI(UNIT_ID, "FM-00-03-001-01", "disconnected");
logger.error("----mysql--- " + device_id + "--state--err----");
}
Vector sets = colldb.getKPISet();
colldb.release();
return sets;
}
/**
* 获取数据库ping状态。
* @param params
* @return
*/
public Vector<TblATO_KPIDETAIL> getPingStatus(HashMap<String,String> params) {
String isPing = params.get("isPing"); // frank 2017-05-19 添加。Y进行ping操作，N不执行
Vector<TblATO_KPIDETAIL> vector=new Vector<TblATO_KPIDETAIL>();
if("Y".equals(isPing)){
String url = params.get("DB_URL");
String deviceId = params.get("DEVICE_ID");
log.info(" ***//////url="+url+"///////////// deviceId="+deviceId+"//////////////////");
int start=url.indexOf("/");
int end=url.lastIndexOf(":");
String ipAddr=url.substring(start+2,end);
log.info(" ***/////////////////// ipAddr="+ipAddr+"//////////////////");
String kpiId="FM-00-10-001-999"; // mysql 主机连接状态
CheckConnectionState server=new CheckConnectionState();
String unitId = KBP_CLASS + "-10" + ":" + deviceId + "-SYS";
ParamterBean bean=new ParamterBean();
bean.setType("mysql");
bean.setDEVICE_IP(ipAddr);
String stat=server.getPingState(bean);
log.info(" **** getPingStatus ****UNIT_ID ="+unitId+"****stat="+stat+"**");
TblATO_KPIDETAIL kpidetail=new TblATO_KPIDETAIL();
try {
kpidetail.setUNIT_ID(unitId);
kpidetail.setKPI_ID(kpiId);
kpidetail.setCLL_TIME(new Date());
kpidetail.setKPI_VALUE(stat);
} catch (ParseException e) {
log.error(" ****** kpidetail.setUNIT_ID(unitId) has error!");
e.printStackTrace();
}
vector.add(kpidetail);
}
return vector;
}
/**
* 获取数据库的连接(jdbc)状态
* @param params
* @return
*/
public Vector<TblATO_KPIDETAIL> getStatus(HashMap<String,String> params) {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
String UNIT_ID = KBP_CLASS + "-10" + ":" + NEATENSID + "-SYS";
// 修改状态的值 UP:连接正常/DOWN:连接异常
if (colldb.getState()) {
colldb.addKPI(UNIT_ID, "FM-00-03-001-01", "UP");
} else {
colldb.addKPI(UNIT_ID, "FM-00-03-001-01", "DOWN");
logger.error("----mysql--- " + device_id + "--state--err----");
}
Vector sets = colldb.getKPISet();
return sets;
}
public Vector<TblATO_KPIDETAIL> getInnodb(HashMap<String, String> params) throws Exception {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
getInnodb(colldb);
Vector sets = colldb.getKPISet();
colldb.release();
return sets;
}
private void getInnodb(SQLTarget colldb) {
String UNIT_ID = KBP_CLASS + "-11" + ":" + NEATENSID + "-Innodb";
colldb.addKPI(UNIT_ID, "PM-00-03-011-01", colldb.getSQLKPISetInfo("show status like 'Innodb_buffer_pool_pages_total'").split(",")[2]);
// 等待实例数 PM-00-03-011-18
colldb.addKPI(UNIT_ID, "PM-00-03-011-18", colldb.getSQLKPISetInfo("show status like 'Innodb_buffer_pool_wait_free'").split(",")[2]);
// InnoDB Buffer Pool 的命中率 PM-00-03-011-41 (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
String innodbBufferPoolReads = colldb.getSQLKPISetInfo("show status like 'Innodb_buffer_pool_reads'").split(",")[2];
String innodbBufferPollReadRequests = colldb.getSQLKPISetInfo("show status like 'Innodb_buffer_pool_read_requests'").split(",")[2];
String _innodbBufferPollRead = String.valueOf(Float.valueOf(innodbBufferPollReadRequests) - Float.valueOf(innodbBufferPoolReads));
colldb.addKPI(UNIT_ID, "PM-00-03-011-41", Formater.formatDecimalKpivalue(Formater.dev2IntIntoFloat(_innodbBufferPollRead, innodbBufferPollReadRequests, 2)));
// InnoDB页大小 PM-00-03-011-02
colldb.addKPI(UNIT_ID, "PM-00-03-011-02", colldb.getSQLKPISetInfo("show status like 'Innodb_page_size'").split(",")[2]);
// 创建的页数 PM-00-03-011-03
colldb.addKPI(UNIT_ID, "PM-00-03-011-03", colldb.getSQLKPISetInfo("show status like 'Innodb_pages_created'").split(",")[2]);
// 待锁定的行数 PM-00-03-011-04
colldb.addKPI(UNIT_ID, "PM-00-03-011-04", colldb.getSQLKPISetInfo("show status like 'Innodb_row_lock_current_waits'").split(",")[2]);
// 行锁定花费的总时间 PM-00-03-011-05
colldb.addKPI(UNIT_ID, "PM-00-03-011-05", colldb.getSQLKPISetInfo("show status like 'Innodb_row_lock_time'").split(",")[2]);
// 行锁定的平均时间 PM-00-03-011-06
colldb.addKPI(UNIT_ID, "PM-00-03-011-06", colldb.getSQLKPISetInfo("show status like 'Innodb_row_lock_time_avg'").split(",")[2]);
// 行锁定的最长时间 PM-00-03-011-07
colldb.addKPI(UNIT_ID, "PM-00-03-011-07", colldb.getSQLKPISetInfo("show status like 'Innodb_row_lock_time_max'").split(",")[2]);
// 一行锁定必须等待的时间数 PM-00-03-011-08
colldb.addKPI(UNIT_ID, "PM-00-03-011-08", colldb.getSQLKPISetInfo("show status like 'Innodb_row_lock_waits'").split(",")[2]);
// Innodb_log_waits 量 PM-00-03-011-46
colldb.addKPI(UNIT_ID, "PM-00-03-011-46", colldb.getSQLKPISetInfo("show status like 'innodb_log_waits'").split(",")[2]);
}
public Vector<TblATO_KPIDETAIL> getSlave(HashMap<String, String> params) throws Exception {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
getSlave(colldb);
Vector sets = colldb.getKPISet();
colldb.release();
return sets;
}
private void getSlave(SQLTarget colldb) {
String UNIT_ID = KBP_CLASS + "-13:" + NEATENSID + "-Slave";
Vector slave = colldb.getSQLKPIResult("show slave status");
for (int i = 0; i < slave.size(); i++) {
// PM-00-03-011-48 Slave_IO_State 等待master发生事件
colldb.addKPI(UNIT_ID, "PM-00-03-011-48", (String) ((Vector) slave.elementAt(i)).get(0));
// PM-00-03-011-49 Master_Host 当前的主服务器主机
colldb.addKPI(UNIT_ID, "PM-00-03-011-49", (String) ((Vector) slave.elementAt(i)).get(1));
// PM-00-03-011-50 Master_User 被用于连接主服务器的当前用户
colldb.addKPI(UNIT_ID, "PM-00-03-011-50", (String) ((Vector) slave.elementAt(i)).get(2));
// PM-00-03-011-51 Master_Port 当前的主服务器端口
colldb.addKPI(UNIT_ID, "PM-00-03-011-51", (String) ((Vector) slave.elementAt(i)).get(3));
// PM-00-03-011-52 Slave_IO_Running I/O线程是否被启动并成功的连接到主服务器上
colldb.addKPI(UNIT_ID, "PM-00-03-011-52", (String) ((Vector) slave.elementAt(i)).get(10));
// PM-00-03-011-53 Slave_SQL_Running SQL线程是否被启动
colldb.addKPI(UNIT_ID, "PM-00-03-011-53", (String) ((Vector) slave.elementAt(i)).get(11));
// PM-00-03-011-57 Seconds_Behind_Master 主备同步延迟时间
colldb.addKPI(UNIT_ID, "PM-00-03-011-57", (String) ((Vector) slave.elementAt(i)).get(32));
}
}
public Vector<TblATO_KPIDETAIL> getTableSpaceIO(HashMap<String, String> params) throws Exception {
getParams(params);
SQLTarget colldb = new SQLTarget();
colldb.initWithClassname(DB_TYPE, CLASSNAME, dburl, dbuser, passwd);
getTableSpaceIO(colldb);
Vector sets = colldb.getKPISet();
colldb.release();
return sets;
}
private void getTableSpaceIO(SQLTarget colldb) {
Vector tsIO = colldb.getSQLKPIResult("select event_name, avg_timer_wait, avg_timer_read, avg_timer_write, avg_timer_misc from performance_schema.file_summary_by_instance");
// Vector tsIO = colldb.getSQLKPIResult("select guid, expression_key, fun_id, fun_name, fun_fields_json, kpi_id from TB_CFG_EVENT_FUN where guid = '2395D4AA-1E06-422E-A32C-2C07B41300D6'");
for (int i = 0; i < tsIO.size(); i++) {
String eventName = (String) ((Vector) tsIO.elementAt(i)).get(0);
String UNIT_ID = KBP_CLASS + "-17:" + NEATENSID + "-" + eventName;
// CM-00-03-001-21 event_name
colldb.addKPI(UNIT_ID, "CM-00-03-001-21", (String) ((Vector) tsIO.elementAt(i)).get(0));
// PM-00-03-011-58 avg_time_wait
colldb.addKPI(UNIT_ID, "PM-00-03-011-58", (String) ((Vector) tsIO.elementAt(i)).get(1));
// PM-00-03-011-59 avg_time_read
colldb.addKPI(UNIT_ID, "PM-00-03-011-59", (String) ((Vector) tsIO.elementAt(i)).get(2));
// PM-00-03-011-60 avg_time_write
colldb.addKPI(UNIT_ID, "PM-00-03-011-60", (String) ((Vector) tsIO.elementAt(i)).get(3));
// PM-00-03-011-61 avg_time_misc
colldb.addKPI(UNIT_ID, "PM-00-03-011-61", (String) ((Vector) tsIO.elementAt(i)).get(4));
}
}
}