go mysql 监控_GO 小程序《监控DBqps和tps》-CSDN博客

本文链接：https://blog.csdn.net/weixin_42422285/article/details/113323299

这是一个使用Go语言编写的程序，用于监控MySQL、Oracle和SQL Server数据库的QPS（每秒查询次数）和TPS（每秒事务次数）。程序通过连接数据库并查询性能计数器获取数据，收集指定间隔内的QPS和TPS，并将其写入CSV文件进行记录和分析。同时，程序还提供了排序算法以找出最大QPS和TPS值。

摘要由CSDN通过智能技术生成

1 package main2

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

26 //Oracle 变量

27 QPS float3228 TPS float3229 MBPS float3230

31 FormatTimes = time.Now().Format("2006-01-02") //定义备份的文件显示的日期格式

32 ListQps = make([]int, 0) //提供收集统计最大QPS

33 ListTps = make([]int, 0) //提供收集统计最大TPS34

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

44 README()45

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

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\n")88

90 //查询这个参数是否开启,如果开启不做操作,没有开启进行自动开启。91 //show variables like '%show_compatibility_56%';92 //set global show_compatibility_56=on;

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 forQps1.Next() {110 if err := Qps1.Scan(&QPS_Totol); err !=nil {111 log.Fatal(err)112 }113 QPS1 =QPS_Totol114 }115

116 forTps1.Next() {117 if err := Tps1.Scan(&TPS_Totol); err !=nil {118 log.Fatal(err)119 }120 TPS1 =TPS_Totol121

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 forQps2.Next() {135 if err := Qps2.Scan(&QPS_Totol); err !=nil {136 log.Fatal(err)137 }138 QPS2 =QPS_Totol139 }140

141 forTps2.Next() {142 if err := Tps2.Scan(&TPS_Totol); err !=nil {143 log.Fatal(err)144 }145 TPS2 =TPS_Totol146 }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 + "\n")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 \n",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\n")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 |\n", "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 forOra01.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 + "\n")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 \n",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\n")293

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 whereltrim(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 fromsys.dm_os_performance_counters305 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 forrows.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 whereltrim(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 fromsys.dm_os_performance_counters332 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 forrows1.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 + "\n")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 \n",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 returndbtype, host, username, password, port, db,Interval398

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