监控DB qps和tps
1.想直接使用现成软件的朋友 可以直接查看最后的 连接中进行下载使用。
2. 想学习交流的朋友 可以慢慢查看代码和提出建议。
1. 软件说明：
1 声明; 本人是go语言初学者,软件是到达了可以实现功能的层次,希望大家提交和反馈优化我的代码或者在这基础上增加功能,向大家学习,本代码逻辑混乱,小弟不才 2 mysql ：监控QPS和TPS很准确,但是需要开启一个参数[set global show_compatibility_56=on;] 3 oralce： 因数据库底层视图原因,获取的数值不太准确,仅供性能测试参考。 4 sqlserver: QPS数值准确,TPS数值因数据库底层视图原因不太准确。
2. 功能列表：
1 #增加mysql,oracle,sqlserver qps,tps监控 2 3 #增加自动输出csv监控日志. 4 5 #执行脚本 自动创建README.md说明文件 6 7 # 增加可选间隔次数,比如 只监控60次 也就是1分钟时间。 8 9 #增加结束后输出 最大监控数值
3. 效果截图和用法:
1. mysql
2. oracle
3. sqlserver
4. 源代码
1 package main 2 3 import ( 4 "database/sql" 5 "flag" 6 "fmt" 7 _ "github.com/go-sql-driver/mysql" 8 _ "github.com/denisenkom/go-mssqldb" 9 _ "github.com/mattn/go-oci8" 10 "log" 11 "os" 12 "time" 13 "io/ioutil" 14 ) 15 16 var ( 17 //mysql 变量 18 QPS1 int 19 QPS2 int 20 TPS1 int 21 TPS2 int 22 QPS_Totol int 23 TPS_Totol int 24 Varlues string 25 26 //Oracle 变量 27 QPS float32 28 TPS float32 29 MBPS float32 30 31 FormatTimes = time.Now().Format("2006-01-02") //定义备份的文件显示的日期格式 32 ListQps = make([]int, 0) //提供收集统计最大QPS 33 ListTps = make([]int, 0) //提供收集统计最大TPS 34 35 //oracle 有小数点 36 OListQps = make([]float32, 0) //提供收集统计最大QPS 37 OListTps = make([]float32, 0) //提供收集统计最大TPS 38 OListMbps = make([]float32, 0) //提供收集统计最大MPS 39 ) 40 41 func main() { 42 43 44 README() 45 46 47 //获取参数值 48 dbtype, host, username, password, port, db, Interval := GetValues() 49 50 //判断当前数据库类型 51 if dbtype == "mysql" || dbtype == "MYSQL" { 52 Mysql(username, password, host, port, db, Interval) 53 54 } else if dbtype == "oracle" || dbtype == "ORACLE" { 55 Oracl1(username, password, host, port, db, Interval ) 56 57 }else if dbtype == "sqlserver" || dbtype == "SQLSERVER" || dbtype == "MSSQL" || dbtype == "mssql" { 58 59 Sqlserver(username, password, host, port, db, Interval ) 60 61 } else { 62 fmt.Println("error: Execute - h for query option, please confirm whether the parameter input is correct...") 63 } 64 65 66 } 67 68 func Mysql(username, password, host, port, db string,Interval int) { 69 70 //将数据转换成数据库url作为返回值 71 conn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8&parseTime=True&loc=Local", username, password, host, port, db) 72 open, err := sql.Open("mysql", conn) 73 if err != nil { 74 log.Printf("open database error:%v", err) 75 } 76 defer open.Close() 77 if err != nil { 78 log.Println(err) 79 } 80 81 //创建日志文件 82 file, err := os.OpenFile("./"+FormatTimes+"_Mysql_qtps.csv", os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0666) 83 if err != nil { 84 fmt.Println("File open failed: ", err) 85 } 86 defer file.Close() 87 file.WriteString("date,QPS,TPS ") 88 89 90 //查询这个参数是否开启,如果开启不做操作,没有开启进行自动开启。 91 // show variables like '%show_compatibility_56%'; 92 //set global show_compatibility_56=on; 93 94 fmt.Println(" ------------------------------") 95 fmt.Println(" | Mysql：QPS,TPS monitor v2.0 |") 96 fmt.Println(" ------------------------------") 97 fmt.Println("") 98 fmt.Printf(" %s | %s |%s | ", "date", "QPS", "TPS") 99 100 for i := 0; i < Interval; i++ { 101 //一秒前的数据 102 Qps1, err := open.Query(" select sum(VARIABLE_VALUE) QPS_Totol from information_schema.GLOBAL_STATUS where VARIABLE_NAME in (?,?,?,?)", "com_select", "com_insert", "com_delete", "com_update") 103 Tps1, err := open.Query(" select sum(VARIABLE_VALUE) TPS_Totol from information_schema.GLOBAL_STATUS where VARIABLE_NAME in (?,?,?,?,?)", "Com_commit", "Com_rollback", "com_insert", "com_delete", "com_update") 104 105 if err != nil { 106 log.Fatal(err) 107 } 108 109 for Qps1.Next() { 110 if err := Qps1.Scan(&QPS_Totol); err != nil { 111 log.Fatal(err) 112 } 113 QPS1 = QPS_Totol 114 } 115 116 for Tps1.Next() { 117 if err := Tps1.Scan(&TPS_Totol); err != nil { 118 log.Fatal(err) 119 } 120 TPS1 = TPS_Totol 121 122 } 123 124 //停顿1秒 125 time.Sleep(time.Second * 1) 126 127 //一秒后的数据 128 Qps2, err := open.Query(" select sum(VARIABLE_VALUE) QPS_Totol from information_schema.GLOBAL_STATUS where VARIABLE_NAME in (?,?,?,?)", "com_select", "com_insert", "com_delete", "com_update") 129 Tps2, err := open.Query(" select sum(VARIABLE_VALUE) TPS_Totol from information_schema.GLOBAL_STATUS where VARIABLE_NAME in (?,?,?,?,?)", "Com_commit", "Com_rollback", "com_insert", "com_delete", "com_update") 130 if err != nil { 131 log.Fatal(err) 132 } 133 134 for Qps2.Next() { 135 if err := Qps2.Scan(&QPS_Totol); err != nil { 136 log.Fatal(err) 137 } 138 QPS2 = QPS_Totol 139 } 140 141 for Tps2.Next() { 142 if err := Tps2.Scan(&TPS_Totol); err != nil { 143 log.Fatal(err) 144 } 145 TPS2 = TPS_Totol 146 } 147 log.Println("|", QPS2-QPS1-2, "|", TPS2-TPS1, "|") 148 149 ListQps = append(ListQps, QPS2-QPS1-2) 150 ListTps = append(ListTps, TPS2-TPS1) 151 152 //写入日志 153 Nows := time.Now().Format("2006/1/2 15:04:05") 154 sprintf := fmt.Sprintf("%v,%v,%v", Nows, QPS2-QPS1-2, TPS2-TPS1) 155 file.WriteString(sprintf + " ") 156 157 } 158 //排序算法来排序QPS哪个最大 159 for i := 0; i < len(ListQps)-1; i++ { 160 for j := i + 1; j < len(ListQps); j++ { 161 if ListQps[j] > ListQps[i] { 162 ListQps[i], ListQps[j] = ListQps[j], ListQps[i] 163 } 164 } 165 } 166 167 //排序算法来排序TPS哪个最大 168 for i := 0; i < len(ListTps)-1; i++ { 169 for j := i + 1; j < len(ListTps); j++ { 170 if ListTps[j] > ListTps[i] { 171 ListTps[i], ListTps[j] = ListTps[j], ListTps[i] 172 } 173 } 174 } 175 176 fmt.Println() 177 fmt.Printf(" MAX QPS values: %v MAX TPS values: %v ",ListQps[0],ListTps[0]) 178 179 } 180 181 func Oracl1(username, password, host, ports, dbs string,Interval int) { 182 ORA_conn := fmt.Sprintf("%s/%s@%s:%s/%s", username, password, host, ports, dbs) 183 db, err := sql.Open("oci8", ORA_conn) 184 if err != nil { 185 log.Fatal(err) 186 } 187 defer db.Close() 188 189 190 //创建日志文件 191 file1, err := os.OpenFile("./"+FormatTimes+"_ORA_qtps.csv", os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0666) 192 if err != nil { 193 fmt.Println("文件打开失败: ", err) 194 } 195 defer file1.Close() 196 file1.WriteString("date,QPS,TPS,MBPS ") 197 198 fmt.Println(" ------------------------------------") 199 fmt.Println(" | Oracle：QPS,TPS,MBPS monitor v2.0 |") 200 fmt.Println(" ------------------------------------") 201 fmt.Println("") 202 fmt.Printf(" %s | %s |%s |%s | ", "date", "QPS", "TPS", "MBPS") 203 204 for i := 0; i < Interval; i++ { 205 Ora01, err := db.Query("select round((select sum(value) from gv$sysmetric where metric_name='I/O Requests per Second'),1)as qps,round((select sum(value) from gv$sysmetric where metric_name='User Transaction Per Sec'),1) as tps,round((select sum(value) from gv$sysmetric where metric_name='I/O Megabytes per Second'),1) as mbps from dual") 206 if err != nil { 207 log.Fatal(err) 208 } 209 210 for Ora01.Next() { 211 212 Ora01.Scan(&QPS, &TPS, &MBPS) 213 } 214 215 Ora01.Close() 216 log.Println("|", QPS, "|", TPS, "|", MBPS, "|") 217 218 //将每次获取的数据存入列表中 219 OListQps = append(OListQps, QPS) 220 OListTps = append(OListTps, TPS) 221 OListMbps= append(OListMbps, MBPS) 222 223 //写入日志 224 Nows := time.Now().Format("2006/1/2 15:04:05") 225 sprintf := fmt.Sprintf("%v,%v,%v,%v", Nows, QPS, TPS, MBPS) 226 file1.WriteString(sprintf + " ") 227 228 //停顿1秒 229 time.Sleep(time.Second * 1) 230 231 } 232 233 //排序算法来排序QPS哪个最大 234 for i := 0; i < len(OListQps)-1; i++ { 235 for j := i + 1; j < len(OListQps); j++ { 236 if OListQps[j] > OListQps[i] { 237 OListQps[i], OListQps[j] = OListQps[j], OListQps[i] 238 } 239 } 240 } 241 242 //排序算法来排序TPS哪个最大 243 for i := 0; i < len(OListTps)-1; i++ { 244 for j := i + 1; j < len(OListTps); j++ { 245 if OListTps[j] > OListTps[i] { 246 OListTps[i], OListTps[j] = OListTps[j], OListTps[i] 247 } 248 } 249 } 250 251 //排序算法来排序MBPS哪个最大 252 for i := 0; i < len(OListMbps)-1; i++ { 253 for j := i + 1; j < len(OListMbps); j++ { 254 if OListMbps[j] > OListMbps[i] { 255 OListMbps[i], OListMbps[j] = OListMbps[j], OListMbps[i] 256 } 257 } 258 } 259 260 261 fmt.Println() 262 fmt.Printf(" MAX QPS values: %v MAX TPS values: %v MAX MBPS values: %v ",OListQps[0],OListTps[0],OListMbps[0]) 263 264 265 } 266 267 268 func Sqlserver(username, password, host, port, db string,Interval int) { 269 270 var QPS3 int 271 var QPS4 int 272 var TPS3 int 273 var TPS4 int 274 275 276 //连接字符串 277 connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", host, port, db, username, password) 278 //建立连接 279 conn, err := sql.Open("mssql", connString) 280 if err != nil { 281 log.Fatal("Open Connection failed:", err.Error()) 282 } 283 defer conn.Close() 284 285 //创建日志文件 286 file, err := os.OpenFile("./"+FormatTimes+"_SQL_qtps.csv", os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0666) 287 if err != nil { 288 fmt.Println("File open failed: ", err) 289 } 290 defer file.Close() 291 292 file.WriteString("date,QPS,TPS ") 293 294 fmt.Println(" ----------------------------------") 295 fmt.Println(" | Sqlserver：QPS,TPS monitor v2.0 |") 296 fmt.Println(" ----------------------------------") 297 fmt.Println("") 298 fmt.Printf(" %s | %s |%s | ", "date", "QPS", "TPS") 299 300 for i := 0; i < Interval; i++ { 301 //通过连接对象执行查询 302 rows, err := conn.Query(`select (select sum(cntr_value) QPS3 from sys.dm_os_performance_counters where ltrim(rtrim(instance_name)) 303 not in ('master','model','msdb','tempdb','mssqlsystemresource','_Total') 304 and rtrim(counter_name) in ('Batch Requests/sec')) QPS3 ,(select sum(cntr_value) TPS3 from sys.dm_os_performance_counters 305 where ltrim(rtrim(instance_name)) not in ('master','model','msdb','tempdb','mssqlsystemresource','_Total') 306 and rtrim(counter_name) in ('Transactions/sec') ) TPS3 `) 307 308 309 310 if err != nil { 311 log.Fatal("Query failed:", err.Error()) 312 } 313 defer rows.Close() 314 315 316 for rows.Next() { 317 rows.Scan(&QPS3,&TPS3) 318 319 } 320 //fmt.Println("1#####",TPS3,IOPS3) 321 322 323 324 //停顿1秒 325 time.Sleep(time.Second * 1) 326 327 328 //通过连接对象执行查询 329 rows1, err := conn.Query(`select (select sum(cntr_value) QPS4 from sys.dm_os_performance_counters where ltrim(rtrim(instance_name)) 330 not in ('master','model','msdb','tempdb','mssqlsystemresource','_Total') 331 and rtrim(counter_name) in ('Batch Requests/sec')) QPS4 ,(select sum(cntr_value) TPS4 from sys.dm_os_performance_counters 332 where ltrim(rtrim(instance_name)) not in ('master','model','msdb','tempdb','mssqlsystemresource','_Total') 333 and rtrim(counter_name) in ('Transactions/sec') ) TPS4 `) 334 335 if err != nil { 336 log.Fatal("Query failed:", err.Error()) 337 } 338 defer rows1.Close() 339 340 341 for rows1.Next() { 342 rows1.Scan(&QPS4,&TPS4) 343 } 344 //fmt.Println("2#####",QPS4,TPS4) 345 346 347 log.Println("|",QPS4-QPS3,"|",TPS4-TPS3,"|" ) 348 349 ListQps = append(ListQps, QPS4-QPS3) 350 ListTps = append(ListTps, TPS4-TPS3) 351 352 //写入日志 353 Nows := time.Now().Format("2006/1/2 15:04:05") 354 sprintf := fmt.Sprintf("%v,%v,%v", Nows,QPS4-QPS3,TPS4-TPS3) 355 file.WriteString(sprintf + " ") 356 357 } 358 359 //排序算法来排序QPS哪个最大 360 for i := 0; i < len(ListQps)-1; i++ { 361 for j := i + 1; j < len(ListQps); j++ { 362 if ListQps[j] > ListQps[i] { 363 ListQps[i], ListQps[j] = ListQps[j], ListQps[i] 364 } 365 } 366 } 367 368 //排序算法来排序TPS哪个最大 369 for i := 0; i < len(ListTps)-1; i++ { 370 for j := i + 1; j < len(ListTps); j++ { 371 if ListTps[j] > ListTps[i] { 372 ListTps[i], ListTps[j] = ListTps[j], ListTps[i] 373 } 374 } 375 } 376 377 fmt.Println() 378 fmt.Printf(" MAX QPS values: %v MAX TPS values: %v ",ListQps[0],ListTps[0]) 379 380 } 381 382 383 384 //定义人工输入参数 385 func GetValues() (dbtype, host, username, password, port, db string,Interval int) { 386 387 flag.StringVar(&dbtype, "dbtype", "", "* Database support type (mysql,oracle,sqlserver)") 388 flag.StringVar(&host, "host", "127.0.0.1", "* Database address") 389 flag.StringVar(&username, "user", "root", "* database username") 390 flag.StringVar(&password, "pass", "", "* Database password [nill]") 391 flag.StringVar(&port, "port", "3306", "Database port") 392 flag.StringVar(&db, "instance", "", "Specify the instance name or database name: (Mysql is db, Oracle is an instance, Sqlserver is an instance)") 393 flag.IntVar(&Interval, "interval", 99999999, "Data acquisition times: once every 1 second") 394 395 //解析胡获取参数 396 flag.Parse() //解析一下 397 return dbtype, host, username, password, port, db,Interval 398 399 } 400 401 //软件使用介绍 402 func README(){ 403 404 dataStr := ` 405 1. Software introduction: 406 This tool mainly realizes monitoring the QPS information of the DB to detect the pressure of the database. 407 408 409 2. Instructions: 410 411 412 example Mysql: 413 414 qtps.exe -dbtype mysql -host 127.0.0.1 -user monitor -pass monitor -port 3306 -instance mysql -interval 5 415 416 417 If there is Error 3167: The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' 418 419 Solution: 420 Execute the command with root authority 【set global show_compatibility_56=on】 421 422 423 424 example oracle: 425 426 qtps.exe -dbtype oracle -host 127.0.0.1 -user monitor -pass monitor -port 1521 -instance ORCL -interval 5 427 428 429 430 example sqlserver: 431 432 qtps.exe -dbtype sqlserver -host 127.0.0.1 -user monitor -pass monitor -interval 10 433 434 435 ` 436 437 //字符串转为字节类型 438 dataBytes := []byte(dataStr) 439 440 err := ioutil.WriteFile("./README.md", dataBytes, 0666) 441 if err != nil { 442 fmt.Println("An error has occurred: ", err) 443 } 444 445 446 }
5. 现成软件下载和详细信息:
http://note.youdao.com/noteshare?id=b629cee746e9d69baff4c53aaae056c5&sub=0A5CFB29B3594CC4BCB024E9DDC94AD0
