今天就来聊聊go里面如何绘制echarts图，
需求：使用go来生成自动化巡检报告，数据库的性能采集也是通过go来实现的，这里就不多说，那如何能够直观的展示监控数据呢，当然前面我有写过flask +python+vue 的监控系统：https://blog.csdn.net/shiyu1157758655/article/details/103278459
今天我要做的是，不通过监控系统，直接把想要的数据展示出来，生成html文件，这里主要就是介绍golang 中 go-echarts 如何使用 ，闲话少说，直接上图（部分图）
那上面的是如何实现的呢，直接上代码
package main
import (
"database/sql"
"fmt"
"github.com/go-echarts/go-echarts/charts"
_ "github.com/go-sql-driver/mysql"
"log"
"os"
"strconv"
"strings"
"time"
)
const (
mysqlHost ="xxxx" //mysql主机ip
mysqlPort="xxx" //mysql端口
mysqlUser="xxx" //mysql登录账号
mysqlPassword="xxx" //mysql登录账号密码
mysqlDbname="xxx" //mysql数据库名字
mysqlCharset="utf8" //mysql字符集
)
func checkErr(info string,err error){
if err != nil{
nowTime := time.Now().Format("2006-01-02 15:04:05")
msg := fmt.Sprintf("mysql is erro:%v !", err)
msgError := fmt.Sprintf("Problem!! %s %s %s", info, msg, nowTime)
//sendMess(msgError)
log.Println(msgError)
}
}
func Query(db *sql.DB,dbInfo string,query string)(result map[int]map[string]string) {
rows,err := db.Query(query)
checkErr(dbInfo,err)
defer rows.Close()
cols,_ :=rows.Columns() //返回所有列
vals :=make([][]byte,len(cols)) //这里表示一行所有列的值，用[]byte表示
scans :=make([]interface{},len(cols)) //这里表示一行填充数据
for k,_:=range vals{
scans[k] = &vals[k]
}
i :=0
result = make(map[int]map[string]string)
for rows.Next(){
rows.Scan(scans...) //填充数据
row :=make(map[string]string) //每行数据
for k,v := range vals { //把vals中的数据复制到row中
key :=cols[k]
//fmt.Println(string(v)) //这里把[]byte数据转成string
row[key] = string(v)
}
result[i] = row
i++
}
rows.Close()
return result
}
func stringToInt(s string)int{
toInit,err :=strconv.Atoi(s)
checkErr("dbInfo",err)
return toInit
}
func stringToInt64(s string) int64{
toInit64,err :=strconv.ParseInt(s, 10, 64)
checkErr("dbInfo",err)
return toInit64
}
func main() {
dbInfo := fmt.Sprintf("%s:%s", mysqlHost, mysqlPort) //system/oracle@10.0.1.60:1521/pig
mysqlDsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", mysqlUser, mysqlPassword, mysqlHost, mysqlPort, mysqlDbname, mysqlCharset)
//log.Println(dbDsn)
//打开连接
db, err := sql.Open("mysql", mysqlDsn)
checkErr(dbInfo, err)
ping := db.Ping()
checkErr(dbInfo, ping)
//lineBar:=charts.NewKLine()
//lineBar.SetGlobalOptions(charts.TitleOpts{Title:"当前连接数"})
dbSql:="select * from (select distinct f_id,f_ip,f_class,f_port,f_role,f_version,f_startup_time,\"oracle\" f_type from t_oracle_instances order by f_class)a union all select * from (select distinct f_id,f_ip,f_class,f_port,f_role,f_version,f_startup_time,\"mysql\" f_type from t_mysql_instances order by f_class,f_role desc) b;"
dbList := Query(db, dbInfo, dbSql)
p1 := charts.NewPage()
//p2:=charts.NewPage()
nowTime, _ := time.Parse("2006-01-02 15:04:05", time.Now().Format("2006-01-02 15:04:05"))
endTime := time.Now()
startTime := endTime.AddDate(0, 0, -1)
endUinxTime := endTime.Unix()
startUnixTime := startTime.Unix()
for i := 0; i < len(dbList); i++ {
if dbList[i]["f_type"] == "oracle" {
runTime, _ := time.Parse("2006-01-02 15:04:05", dbList[i]["f_startup_time"])
diffTime := nowTime.Sub(runTime)
diffDay := fmt.Sprintf("%v天", int(diffTime.Hours()/24))
oracleSql := fmt.Sprintf("select f_check_time ,f_database_role,f_archivelog,f_db_size,f_db_file_size,f_session_total,f_session_actives,f_session_waits,f_processes,f_dg_stats,f_dg_delay,f_buffer_hit,f_library_hit,f_latch_hit,f_in_memory_sort_hit,f_buffer_nowait_hit,f_redo_nowait_ratio,f_sga_pct,f_pga_pct,f_db_block_gets,f_consistent_gets,f_db_block_changes,f_redo_writes,f_physical_reads_direct,f_physical_writes_direct,f_opened_cursors_current,f_user_commits,f_user_rollbacks from t_oracle_monitor_status where f_instances_id=%v and f_check_time BETWEEN %v and %v", dbList[i]["f_id"], startUnixTime, endUinxTime)
result := Query(db, dbInfo, oracleSql)
var xTime []string
var dbSize []string
var dbFileSize []string
var sessionTotal []string
var sessionActives []string
var sessionWaits []string
var processes []string
var bufferHit []string
var sgaPct []string
var pgaPct []string
var userCommit []string
var userRollback []string
var timeDiff int
var dgDelay []string
if len(result) <0 {
for j := 0; j < len(result)-1; j++ {
if dbList[i]["f_role"] == "0" {
if result[j+1]["f_dg_delay"] == "-1" {
dgDelay = append(dgDelay, "null")
} else {
dgDelay = append(dgDelay, result[j+1]["f_dg_delay"])
}
}
timeDiff = stringToInt(result[j+1]["f_check_time"]) - stringToInt(result[j]["f_check_time"])
if result[j+1]["f_db_size"] == "-1" {
dbSize = append(dbSize, "null")
} else {
dbSize = append(dbSize, result[j+1]["f_db_size"])
}
if result[j+1]["f_db_file_size"] == "-1" {
dbFileSize = append(dbFileSize, "null")
} else {
dbFileSize = append(dbFileSize, result[j+1]["f_db_file_size"])
}
if result[j+1]["f_session_total"] == "-1" {
sessionTotal = append(sessionTotal, "null")
} else {
sessionTotal = append(sessionTotal, result[j+1]["f_session_total"])
}
if result[j+1]["f_session_actives"] == "-1" {
sessionActives = append(sessionActives, "null")
} else {
sessionActives = append(sessionActives, result[j+1]["f_session_actives"])
}
if result[j+1]["f_session_waits"] == "-1" {
sessionWaits = append(sessionWaits, "null")
} else {
sessionWaits = append(sessionWaits, result[j+1]["f_session_waits"])
}
if result[j+1]["f_processes"] == "-1" {
processes = append(processes, "null")
} else {
processes = append(processes, result[j+1]["f_processes"])
}
if result[j+1]["f_buffer_hit"] == "-1" {
bufferHit = append(bufferHit, "null")
} else {
bufferHit = append(bufferHit, result[j+1]["f_buffer_hit"])
}
if result[j+1]["f_sga_pct"] == "-1" {
sgaPct = append(sgaPct, "null")
} else {
sgaPct = append(sgaPct, result[j+1]["f_sga_pct"])
}
if result[j+1]["f_pga_pct"] == "-1" {
pgaPct = append(pgaPct, "null")
} else {
pgaPct = append(pgaPct, result[j+1]["f_pga_pct"])
}
if result[j+1]["f_user_commits"] == "-1" || result[j]["f_user_commits"] == "-1" {
userCommit = append(userCommit, "null")
} else {
commitValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_user_commits"]))-float64(stringToInt(result[j]["f_user_commits"])))/float64(timeDiff)), 64)
userCommit = append(userCommit, fmt.Sprintf("%v", commitValue))
}
if result[j+1]["f_user_rollbacks"] == "-1" || result[j]["f_user_rollbacks"] == "-1" {
userRollback = append(userRollback, "null")
} else {
rollbackValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_user_rollbacks"]))-float64(stringToInt(result[j]["f_user_rollbacks"])))/float64(timeDiff)), 64)
userRollback = append(userRollback, fmt.Sprintf("%v", rollbackValue))
}
xTime = append(xTime, time.Unix(stringToInt64(result[j]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))
}
//var hostInfo[] string
var isRole []string
if dbList[i]["f_role"] == "1" {
isRole = append(isRole, "主库")
} else {
isRole = append(isRole, "从库")
}
hostJs := "<table><tr><th>类型</th><th>产品线</th><th>主机</th><th>端口</th><th>版本</th><th>主从</th><th>运行时间</th><th>开始时间</th><th>结束时间</th></tr>"
hostJs += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td></tr>",
"oracle", dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], dbList[i]["f_version"], isRole[0], diffDay, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"))
hostJs += "</table>"
hostBar := charts.NewBar()
//echarts=append(echarts, hostBar)
//log.Println(hostBar)
//log.Println(echarts)
hostBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
//hostBar.Width = "100%"
hostBar.Height = "100px"
hostBarChartID := fmt.Sprintf("%v_%v_%v_serverInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
hostBar.ChartID = hostBarChartID
//js:=fmt.Sprintf("let id = document.getElementById('%v'); let code=';\n let data=%v;for(let i=0;i!=data.length;i++){\n if (i==0){\n code+='<tr><td>'+data[i]+'</td>';\n }else if(i==data.length-1){\n code+='<td>'+data[i]+'</td></tr>';\n }else{\n code+='<td>'+data[i]+'</td>';\n }\n }\n id.innerHTML=code+'<table>';",hostBarChartID,hostInfo)
hostBar.AddJSFuncs(fmt.Sprintf("document.getElementById('%v').innerHTML='%v';", hostBarChartID, hostJs))
//sessionLineBar := charts.NewLine()
sessionLineBar := charts.NewLine()
sessionLineBar.SetGlobalOptions(charts.TitleOpts{Title: "连接数(单位:个)"})
sessionLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
sessionLineBar.ChartID = fmt.Sprintf("%v_%v_%v_sessionBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
sessionLineBar.TextStyle.FontSize = 18
sessionLineBar.Height = "300px"
//sessionLineBar.Width = "100%"
sessionLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
var markPoints = []charts.SeriesOptser{
charts.MPNameTypeItem{Name: "最大值", Type: "max"},
charts.MPNameTypeItem{Name: "平均值", Type: "average"},
charts.MPNameTypeItem{Name: "最小值", Type: "min"},
charts.MPStyleOpts{Label: charts.LabelTextOpts{Show: true}},
}
sessionLineBar.AddXAxis(xTime).
AddYAxis("会话总数", sessionTotal, markPoints...).
AddYAxis("会话活动数", sessionActives, markPoints...).
AddYAxis("等待会话数", sessionWaits, markPoints...)
//processLineBar := charts.NewLine()
processLineBar := charts.NewLine()
processLineBar.SetGlobalOptions(charts.TitleOpts{Title: "进程数(单位:个)"})
processLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
processLineBar.ChartID = fmt.Sprintf("%v_%v_%v_processBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
processLineBar.Height = "300px"
processLineBar.TextStyle.FontSize = 18
//processLineBar.Width = "100%"
processLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
processLineBar.AddXAxis(xTime).AddYAxis("进程数", processes, markPoints...)
//主从延迟
dgDelayLineBar := charts.NewLine()
dgDelayLineBar.SetGlobalOptions(charts.TitleOpts{Title: "延迟时间(单位：秒)"})
dgDelayLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
dgDelayLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dgdelayBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
dgDelayLineBar.Height = "300px"
//dgDelayLineBar.Width = "100%"
dgDelayLineBar.TextStyle.FontSize = 18
dgDelayLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
dgDelayLineBar.AddXAxis(xTime).AddYAxis("延迟时间", dgDelay, markPoints...)
//dbSizeLineBar := charts.NewLine()
dbSizeLineBar := charts.NewLine()
dbSizeLineBar.SetGlobalOptions(charts.TitleOpts{Title: "数据库大小(单位:G)"})
dbSizeLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
dbSizeLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dbsizeBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
dbSizeLineBar.Height = "300px"
dbSizeLineBar.TextStyle.FontSize = 18
//dbSizeLineBar.Width = "100%"
dbSizeLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
dbSizeLineBar.AddXAxis(xTime).
AddYAxis("数据库大小", dbSize, markPoints...).
AddYAxis("数据库文件大小", dbFileSize, markPoints...)
tablespaceSql := fmt.Sprintf("select * from t_oracle_tablespace where f_instances_id=%v and f_check_time=(select max(f_check_time) from t_oracle_tablespace where f_instances_id=%v) group by f_tablespace_name order by f_used_rate desc", dbList[i]["f_id"], dbList[i]["f_id"])
tablespaceResult := Query(db, dbInfo, tablespaceSql)
var tablespaceName []string
var tablespaceUsedRate []string
//var Results [][] string
tablespaceTable := "<table ><tr><th>实例名</th><th>主机</th><th>端口</th><th>表空间名称</th><th>总大小(M)</th><th>已用空间(M)</th><th>可用空间(M)</th><th>使用率(%)</th><th>监控时间</th></tr>"
for k := 0; k < len(tablespaceResult); k++ {
//var Result [] string
//Result=append(Result,fmt.Sprintf("'%v','%v','%v','%v','%vM','%vM','%vM','%v%%','%v'",
//dbList[i]["f_class"],dbList[i]["f_ip"],dbList[i]["f_port"],tablespaceResult[k]["f_tablespace_name"],
//tablespaceResult[k]["f_max_size"],tablespaceResult[k]["f_used"],
//stringToInt(tablespaceResult[k]["f_max_size"])-stringToInt(tablespaceResult[k]["f_used"]),
//tablespaceResult[k]["f_used_rate"],
// time.Unix(stringToInt64(tablespaceResult[k]["f_check_time"]), 0).Format("2006-01-02 15:04:05")))
//Results=append(Results,Result)
tablespaceTable += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%vM</td><td>%vM</td><td>%vM</td><td>%v%%</td><td>%v</td></tr>",
dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], tablespaceResult[k]["f_tablespace_name"], tablespaceResult[k]["f_max_size"],
tablespaceResult[k]["f_used"], stringToInt(tablespaceResult[k]["f_max_size"])-stringToInt(tablespaceResult[k]["f_used"]),
tablespaceResult[k]["f_used_rate"], time.Unix(stringToInt64(tablespaceResult[k]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))
tablespaceName = append(tablespaceName, tablespaceResult[k]["f_tablespace_name"])
tablespaceUsedRate = append(tablespaceUsedRate, tablespaceResult[k]["f_used_rate"])
}
tablespaceTable += "</table>"
//展示tablespace 详细信息
//tablespaceBar := charts.NewBar()
tablespaceBar := charts.NewBar()
tablespaceBar.SetGlobalOptions(charts.TitleOpts{Title: "表空间使用率(单位:%)"})
tablespaceBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
tablespaceBar.ChartID = fmt.Sprintf("%v_%v_%v_tablespaceBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
tablespaceBar.Height = "300px"
tablespaceBar.TextStyle.FontSize = 18
//tablespaceBar.Width = "100%"
tablespaceBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
//bar.AddXAxis(nameItems).AddYAxis("商家A",[]int{20,30,40,10,24,60}).AddYAxis("商家B",[]int{35,14,25,60,44,23})
tablespaceBar.AddXAxis(tablespaceName).AddYAxis("表空间使用率", tablespaceUsedRate)
//tablespaceBar.SetGlobalOptions(charts.YAxisOpts{Type:"value",AxisLabel: charts.LabelTextOpts{Formatter: "{value}"}})
//tablespaceBar.SetGlobalOptions(charts.GlobalOptser())
//LabelTextOpts{Show:true,Color: "blue",Position: "top",Formatter: "%"}
tablespaceBar.SetSeriesOptions(charts.LabelTextOpts{Show: true, Position: "top", Formatter: "{c}" + "%"})
//tablespaceInfo:=charts.NewBar()
tablespaceInfo := charts.NewBar()
tablespaceInfo.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
//tablespaceInfo.Width = "100%"
tablespaceInfo.Height = "400px"
tablespaceChartID := fmt.Sprintf("%v_%v_%v_tablespaceInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
tablespaceInfo.ChartID = tablespaceChartID
tablespaceJs := fmt.Sprintf("document.getElementById('%v').innerHTML='%v';",
tablespaceChartID, tablespaceTable)
tablespaceInfo.AddJSFuncs(tablespaceJs)
sgaPgaLineBar := charts.NewLine()
sgaPgaLineBar.SetGlobalOptions(charts.TitleOpts{Title: "sga/pga使用率(单位:%)"})
sgaPgaLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
sgaPgaLineBar.ChartID = fmt.Sprintf("%v_%v_%v_sgapgaBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
sgaPgaLineBar.Height = "300px"
//sgaPgaLineBar.Width = "100%"
sgaPgaLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
sgaPgaLineBar.TextStyle.FontSize = 18
sgaPgaLineBar.AddXAxis(xTime).
AddYAxis("sga", sgaPct, markPoints...).
AddYAxis("pga", pgaPct, markPoints...)
bufferHitLineBar := charts.NewLine()
bufferHitLineBar.SetGlobalOptions(charts.TitleOpts{Title: "buffer hit(命中率)(单位:%)"})
bufferHitLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
bufferHitLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bufferhitBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
bufferHitLineBar.Height = "300px"
bufferHitLineBar.TextStyle.FontSize = 18
//bufferHitLineBar.Width = "100%"
bufferHitLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
bufferHitLineBar.AddXAxis(xTime).
AddYAxis("buff_hit", bufferHit, markPoints...)
transactionLineBar := charts.NewLine()
transactionLineBar.SetGlobalOptions(charts.TitleOpts{Title: "Transactions(单位:次/s)"})
transactionLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
transactionLineBar.ChartID = fmt.Sprintf("%v_%v_%v_transactionBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
transactionLineBar.Height = "300px"
//transactionLineBar.Width = "100%"
transactionLineBar.TextStyle.FontSize = 18
transactionLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
transactionLineBar.AddXAxis(xTime).
AddYAxis("user_commits", userCommit, markPoints...).
AddYAxis("user_rollbacks", userRollback, markPoints...)
p1.Add(
hostBar,
sessionLineBar,
processLineBar,
)
if dbList[i]["f_role"] == "0" {
p1.Add(dgDelayLineBar)
}
p1.Add(
dbSizeLineBar,
tablespaceBar,
tablespaceInfo,
sgaPgaLineBar,
bufferHitLineBar,
transactionLineBar,
)
//log.Println(p)
}
}
if dbList[i]["f_type"] == "mysql"{
//nowTime,_:=time.Parse("2006-01-02 15:04:05", time.Now().Format("2006-01-02 15:04:05"))
//runTime,_:=time.Parse("2006-01-02 15:04:05",dbList[i]["f_startup_time"])
//diffTime:=nowTime.Sub(runTime)
//diffDay:=fmt.Sprintf("%v天",int(diffTime.Hours()/24))
diffDay:=dbList[i]["f_startup_time"]+"天"
mysqlSql := fmt.Sprintf("select * from t_mysql_monitor_status where f_instances_id=%v and f_check_time BETWEEN %v and %v", dbList[i]["f_id"],startUnixTime,endUinxTime)
result := Query(db, dbInfo, mysqlSql)
var xTime []string
var dgDelay [] string
var threadsRunning [] string
var threadsConnecteds [] string
var threadsCached [] string
var comInsert [] string
var comUpdate [] string
var comDelete [] string
var comSelect [] string
var qps [] string
var tps [] string
var slowQueries [] string
var bufferPoolHit [] string
var bytesReceived [] string
var bytesSent [] string
var timeDiff int
if len(result) >0 {
for j := 0; j < len(result)-1; j++ {
xTime = append(xTime, time.Unix(stringToInt64(result[j]["f_check_time"]), 0).Format("2006-01-02 15:04:05"))
if dbList[i]["f_role"] == "0" {
if result[j+1]["f_delay"] == "-1" {
dgDelay = append(dgDelay, "null")
} else {
dgDelay = append(dgDelay, result[j+1]["f_delay"])
}
}
timeDiff = stringToInt(result[j+1]["f_check_time"]) - stringToInt(result[j]["f_check_time"])
if result[j+1]["f_threads_running"] == "-1" {
threadsRunning = append(threadsRunning, "null")
} else {
threadsRunning = append(threadsRunning, result[j+1]["f_threads_running"])
}
if result[j+1]["f_threads_connected"] == "-1" {
threadsConnecteds = append(threadsConnecteds, "null")
} else {
threadsConnecteds = append(threadsConnecteds, result[j+1]["f_threads_connected"])
}
if result[j+1]["f_threads_cached"] == "-1" {
threadsCached = append(threadsCached, "null")
} else {
threadsCached = append(threadsCached, result[j+1]["f_threads_cached"])
}
if result[j+1]["f_com_insert"] == "-1" || result[j]["f_com_insert"] == "-1" {
comInsert = append(comInsert, "null")
} else {
comInsertValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_insert"]))-float64(stringToInt(result[j]["f_com_insert"])))/float64(timeDiff)), 64)
comInsert = append(comInsert, fmt.Sprintf("%v", comInsertValue))
}
if result[j+1]["f_com_update"] == "-1" || result[j]["f_com_update"] == "-1" {
comUpdate = append(comUpdate, "null")
} else {
comUpdateValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_update"]))-float64(stringToInt(result[j]["f_com_update"])))/float64(timeDiff)), 64)
comUpdate = append(comUpdate, fmt.Sprintf("%v", comUpdateValue))
}
if result[j+1]["f_com_delete"] == "-1" || result[j]["f_com_delete"] == "-1" {
comDelete = append(comDelete, "null")
} else {
comDeleteValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_delete"]))-float64(stringToInt(result[j]["f_com_delete"])))/float64(timeDiff)), 64)
comDelete = append(comDelete, fmt.Sprintf("%v", comDeleteValue))
}
if result[j+1]["f_com_select"] == "-1" || result[j]["f_com_select"] == "-1" {
comSelect = append(comSelect, "null")
} else {
comSelectValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_com_select"]))-float64(stringToInt(result[j]["f_com_select"])))/float64(timeDiff)), 64)
comSelect = append(comSelect, fmt.Sprintf("%v", comSelectValue))
}
if result[j+1]["f_queries"] == "-1" || result[j]["f_queries"] == "-1" {
qps = append(qps, "null")
} else {
qpsValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_queries"]))-float64(stringToInt(result[j]["f_queries"])))/float64(timeDiff)), 64)
qps = append(qps, fmt.Sprintf("%v", qpsValue))
}
if result[j+1]["f_com_commit"] == "-1" || result[j]["f_com_commit"] == "-1" || result[j+1]["f_com_rollback"] == "-1" || result[j]["f_com_rollback"] == "-1" {
tps = append(tps, "null")
} else {
tpsValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", ((float64(stringToInt(result[j+1]["f_com_commit"]))-float64(stringToInt(result[j]["f_com_commit"])))+(float64(stringToInt(result[j+1]["f_com_rollback"]))-float64(stringToInt(result[j]["f_com_rollback"]))))/float64(timeDiff)), 64)
tps = append(tps, fmt.Sprintf("%v", tpsValue))
}
if result[j+1]["f_slow_queries"] == "-1" || result[j]["f_slow_queries"] == "-1" {
slowQueries = append(slowQueries, "null")
} else {
slowQueriesValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_slow_queries"]))-float64(stringToInt(result[j]["f_slow_queries"])))/float64(timeDiff)), 64)
slowQueries = append(slowQueries, fmt.Sprintf("%v", slowQueriesValue))
}
if result[j+1]["f_buffer_pool_hit"] == "-1" {
bufferPoolHit = append(bufferPoolHit, "null")
} else {
bufferPoolHit = append(bufferPoolHit, result[j+1]["f_buffer_pool_hit"])
}
if result[j+1]["f_bytes_received"] == "-1" || result[j]["f_bytes_received"] == "-1" {
bytesReceived = append(bytesReceived, "null")
} else {
bytesReceivedValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_bytes_received"]))-float64(stringToInt(result[j]["f_bytes_received"])))/float64(timeDiff)/float64(1024)), 64)
bytesReceived = append(bytesReceived, fmt.Sprintf("%v", bytesReceivedValue))
}
if result[j+1]["f_bytes_sent"] == "-1" || result[j]["f_bytes_sent"] == "-1" {
bytesSent = append(bytesSent, "null")
} else {
bytesSentValue, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", (float64(stringToInt(result[j+1]["f_bytes_sent"]))-float64(stringToInt(result[j]["f_bytes_sent"])))/float64(timeDiff)/float64(1024)), 64)
bytesSent = append(bytesSent, fmt.Sprintf("%v", bytesSentValue))
}
}
var isRole []string
if dbList[i]["f_role"] == "1" {
isRole = append(isRole, "主库")
} else {
isRole = append(isRole, "从库")
}
hostJs := "<table><tr><th>类型</th><th>产品线</th><th>主机</th><th>端口</th><th>版本</th><th>主从</th><th>运行时间</th><th>开始时间</th><th>结束时间</th></tr>"
hostJs += fmt.Sprintf("<tr><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td><td>%v</td></tr>",
"mysql", dbList[i]["f_class"], dbList[i]["f_ip"], dbList[i]["f_port"], dbList[i]["f_version"], isRole[0], diffDay, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"))
hostJs += "</table>"
hostBar := charts.NewBar()
//echarts=append(echarts, hostBar)
//log.Println(hostBar)
//log.Println(echarts)
hostBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
//hostBar.Width = "100%"
hostBar.Height = "100px"
hostBarChartID := fmt.Sprintf("%v_%v_%v_serverInfo", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
hostBar.ChartID = hostBarChartID
//js:=fmt.Sprintf("let id = document.getElementById('%v'); let code=';\n let data=%v;for(let i=0;i!=data.length;i++){\n if (i==0){\n code+='<tr><td>'+data[i]+'</td>';\n }else if(i==data.length-1){\n code+='<td>'+data[i]+'</td></tr>';\n }else{\n code+='<td>'+data[i]+'</td>';\n }\n }\n id.innerHTML=code+'<table>';",hostBarChartID,hostInfo)
hostBar.AddJSFuncs(fmt.Sprintf("document.getElementById('%v').innerHTML='%v';", hostBarChartID, hostJs))
var mysqlDelay []string
maxConnections := stringToInt(result[len(result)-1]["f_max_connections"])
threadsConnected := stringToInt(result[len(result)-1]["f_threads_connected"])
openTables := stringToInt(result[len(result)-1]["f_open_tables"])
tableOpenCache := stringToInt(result[len(result)-1]["f_table_open_cache"])
openFilesLimit := stringToInt(result[len(result)-1]["f_open_files_limit"])
openFiles := stringToInt(result[len(result)-1]["f_open_files"])
connPie := charts.NewPie()
connPie.SetGlobalOptions(charts.TitleOpts{Title: "连接池使用"})
connPie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
connPieId := fmt.Sprintf("%v_%v_%v_connPie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
connPie.ChartID = connPieId
connPie.Height = "200px"
//connPie.MarginLeft="200px"
connPie.Width = "300px"
//connPie.Border="1px solid #ccc"
//connPieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",connPieId,"width:28%;margin-left:50px;",connPieId,"float:left;width:40%")
connPieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", connPieId, "border:1px solid #ccc;width:28%;margin-left:0px;")
connPie.AddJSFuncs(connPieJs)
connPie.Trigger = "item"
connPie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
connPie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})
connPie.Add("连接池使用", map[string]interface{}{"可用连接": maxConnections - threadsConnected, "已用连接": threadsConnected},
charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
)
tablePie := charts.NewPie()
tablePie.SetGlobalOptions(charts.TitleOpts{Title: "表缓存数"})
tablePie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
tablePieId := fmt.Sprintf("%v_%v_%v_tablePie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
tablePie.ChartID = tablePieId
tablePie.Height = "200px"
tablePie.Width = "300px"
//connPie.Border="1px solid #ccc"
//tablePieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",tablePieId,"border:1px solid #ccc;width:28%;margin-top:-200px;",tablePieId,"float:left;width:47%;margin:-47px")
tablePieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", tablePieId, "border:1px solid #ccc;width:28%;margin-top:-200px;")
tablePie.AddJSFuncs(tablePieJs)
tablePie.Trigger = "item"
tablePie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
tablePie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})
tablePie.Add("表缓存使用", map[string]interface{}{"可用表缓存": tableOpenCache - openTables, "已打开表数": openTables},
charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
)
filePie := charts.NewPie()
filePie.SetGlobalOptions(charts.TitleOpts{Title: "打开文件数"})
filePie.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
filePieId := fmt.Sprintf("%v_%v_%v_filePie", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
filePie.ChartID = filePieId
filePie.Height = "200px"
filePie.Width = "300px"
//connPie.Border="1px solid #ccc"
//filePieJs:=fmt.Sprintf("document.getElementById('%v').style.cssText='%v';document.getElementById('%v').parentNode.style.cssText='%v';",filePieId,"border:1px solid #ccc;width:28%;margin-top:-200px;margin-right:50px;",filePieId,"float:left;width:23%;margin:-23px")
filePieJs := fmt.Sprintf("document.getElementById('%v').style.cssText='%v';", filePieId, "border:1px solid #ccc;width:28%;margin-top:-200px;margin-right:0px;")
filePie.AddJSFuncs(filePieJs)
filePie.Trigger = "item"
filePie.Formatter = "{a} <br/>{b} : {c} ({d}%)"
filePie.SetGlobalOptions(charts.LegendOpts{Bottom: "20", Left: "10", Orient: "vertical"})
filePie.Add("打开文件数", map[string]interface{}{"可用文件句柄": openFilesLimit - openFiles, "已打开文件数": openFiles},
charts.LabelTextOpts{Show: true, Position: "inside", Formatter: "{d}%"},
)
var markPoints = []charts.SeriesOptser{
charts.MPNameTypeItem{Name: "最大值", Type: "max"},
charts.MPNameTypeItem{Name: "平均值", Type: "average"},
charts.MPNameTypeItem{Name: "最小值", Type: "min"},
charts.MPStyleOpts{Label: charts.LabelTextOpts{Show: true}},
}
threadLineBar := charts.NewLine()
threadLineBar.SetGlobalOptions(charts.TitleOpts{Title: "连接数(单位:个)"})
threadLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
threadLineBar.ChartID = fmt.Sprintf("%v_%v_%v_threadBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
threadLineBar.Height = "300px"
//threadLineBar.Width = "100%"
threadLineBar.TextStyle.FontSize = 18
threadLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
threadLineBar.TextStyle.FontSize = 18
threadLineBar.AddXAxis(xTime).
AddYAxis("threads_running", threadsRunning, markPoints...).
AddYAxis("threads_connect", threadsConnecteds, markPoints...).
AddYAxis("threads_cached", threadsCached, markPoints...)
//主从延迟
dgDelayLineBar := charts.NewLine()
dgDelayLineBar.SetGlobalOptions(charts.TitleOpts{Title: "延迟时间(单位：秒)"})
dgDelayLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
dgDelayLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dgdelayBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
dgDelayLineBar.Height = "300px"
//dgDelayLineBar.Width = "100%"
dgDelayLineBar.TextStyle.FontSize = 18
dgDelayLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
dgDelayLineBar.AddXAxis(xTime).AddYAxis("延迟时间", dgDelay, markPoints...)
//qps
qpsLineBar := charts.NewLine()
qpsLineBar.SetGlobalOptions(charts.TitleOpts{Title: "QPS(单位：次/秒)"})
qpsLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
qpsLineBar.ChartID = fmt.Sprintf("%v_%v_%v_qpsBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
qpsLineBar.Height = "300px"
qpsLineBar.TextStyle.FontSize = 18
//qpsLineBar.Width = "100%"
qpsLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
qpsLineBar.AddXAxis(xTime).
AddYAxis("qps", qps, markPoints...)
//tps
tpsLineBar := charts.NewLine()
tpsLineBar.SetGlobalOptions(charts.TitleOpts{Title: "TPS(单位：次/秒)"})
tpsLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
tpsLineBar.ChartID = fmt.Sprintf("%v_%v_%v_tpsBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
tpsLineBar.Height = "300px"
//tpsLineBar.Width = "100%"
tpsLineBar.TextStyle.FontSize = 18
tpsLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
tpsLineBar.AddXAxis(xTime).
AddYAxis("tps", tps, markPoints...)
//dml
dmlLineBar := charts.NewLine()
dmlLineBar.SetGlobalOptions(charts.TitleOpts{Title: "DML(单位：次/秒)"})
dmlLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
dmlLineBar.ChartID = fmt.Sprintf("%v_%v_%v_dmlBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
dmlLineBar.Height = "300px"
dmlLineBar.TextStyle.FontSize = 18
//dmlLineBar.Width = "100%"
dmlLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
dmlLineBar.AddXAxis(xTime).
AddYAxis("insert", comInsert, markPoints...).
AddYAxis("delete", comDelete, markPoints...).
AddYAxis("update", comUpdate, markPoints...).
AddYAxis("select", comSelect, markPoints...)
//慢查
slowQueriesLineBar := charts.NewLine()
slowQueriesLineBar.SetGlobalOptions(charts.TitleOpts{Title: "慢查数(单位：个/秒)"})
slowQueriesLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
slowQueriesLineBar.ChartID = fmt.Sprintf("%v_%v_%v_slowqueriesBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
slowQueriesLineBar.Height = "300px"
slowQueriesLineBar.TextStyle.FontSize = 18
//slowQueriesLineBar.Width = "100%"
slowQueriesLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
slowQueriesLineBar.AddXAxis(xTime).
AddYAxis("slow_queries", slowQueries, markPoints...)
//innodb buffer pool hit
bufferPoolHitLineBar := charts.NewLine()
bufferPoolHitLineBar.SetGlobalOptions(charts.TitleOpts{Title: "Innodb_buffer_pool_hit(单位：%)"})
bufferPoolHitLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
bufferPoolHitLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bufferpoolhitBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
bufferPoolHitLineBar.Height = "300px"
bufferPoolHitLineBar.TextStyle.FontSize = 18
//bufferPoolHitLineBar.Width = "100%"
bufferPoolHitLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
bufferPoolHitLineBar.AddXAxis(xTime).
AddYAxis("Innodb_buffer_pool_hit", bufferPoolHit, markPoints...)
//流量
bytesLineBar := charts.NewLine()
bytesLineBar.SetGlobalOptions(charts.TitleOpts{Title: "流量(单位：MB)"})
bytesLineBar.SetGlobalOptions(charts.InitOpts{AssetsHost: "http://49.233.181.63/assets/"})
bytesLineBar.ChartID = fmt.Sprintf("%v_%v_%v_bytesBar", strings.Replace(dbList[i]["f_ip"], ".", "", -1), dbList[i]["f_class"], dbList[i]["f_port"])
bytesLineBar.Height = "300px"
bytesLineBar.TextStyle.FontSize = 18
// bytesLineBar.Width = "100%"
bytesLineBar.SetGlobalOptions(charts.ToolboxOpts{Show: true})
bytesLineBar.AddXAxis(xTime).
AddYAxis("bytes_received", bytesReceived, markPoints...).
AddYAxis("bytes_sent", bytesSent, markPoints...)
p1.Add(
hostBar,
connPie,
filePie,
tablePie,
threadLineBar,
)
if dbList[i]["f_role"] == "0" {
p1.Add(dgDelayLineBar)
}
p1.Add(
qpsLineBar,
tpsLineBar,
dmlLineBar,
slowQueriesLineBar,
bufferPoolHitLineBar,
bytesLineBar,
)
for j := 0; j < len(result)-1; j++ {
if dbList[i]["f_role"] == "0" {
if result[j+1]["f_dg_delay"] == "-1" {
mysqlDelay = append(mysqlDelay, "null")
} else {
mysqlDelay = append(mysqlDelay, result[j+1]["f_dg_delay"])
}
}
//mysqlTimeDiff :=stringToInt(result[j+1]["f_check_time"])-stringToInt(result[j]["f_check_time"])
}
}
}
}
htmlName:=fmt.Sprintf("databases_report_%v.html",time.Now().Format("2006_01_02"))
f1, err1 := os.Create(htmlName)
checkErr(dbInfo, err1)
p1.Render(f1)
}