小花狸监控V1.0已经实现了通过一个统一的界面,查看服务器信息.
但是如何报警呢?我们单位有一个内部的HTTP发送邮件的接口.
小花狸报警器每分钟扫描数据库,如果有异常情况,则通过HTTP发送邮件告警.
代码如下
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
"os/exec"
)
func main() {
db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/probe?charset=utf8")
if err != nil {
log.Fatalf("Open database error: %s\n", err)
}
defer db.Close()
mysqlCursor, _ := db.Query(`
select
g.generator_room,g.h,g.port,
case
when g.mysqlts is null then 'MySQL未采集'
when g.hostts is null then '主机未采集'
when g.Slave_IO_Running!='Yes' and g.Master_Host !='' then 'IO线程停止'
when g.Slave_SQL_Running!='Yes' and g.Master_Host !='' then 'SQL线程停止'
when g.mysqlts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts mysqlts,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value,
a.*,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg,e.port p,e.host h
from
cat_mysql_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_mysql_probe_history a
inner join
(select host,port,max(id) id from cat_mysql_probe_history group by host,port) b
on(a.host=b.host and a.port=b.port and a.id=b.id)
) a on (e.host=a.host and e.port=a.port)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d
on (c.host=d.host and c.id=d.id)
)c on(c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,a.host,a.port
) g
`)
mongodbCursor, _ := db.Query(`
select
g.generator_room,g.host,g.port,
case
when g.mongodbts is null then 'mongodb未采集'
when g.hostts is null then '主机未采集'
when g.member1_health!='1' then 'member1异常'
when g.member2_health!='1' then 'member2异常'
when g.member3_health!='1' then 'member3异常'
when g.mongodbts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts mongodbts,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value
, e.host
, e.port
, a.member1_name
, a.member1_health
, a.member1_StateStr
, a.member1_OptimeDate
, a.member2_name
, a.member2_health
, a.member2_StateStr
, a.member2_OptimeDate
, a.member3_name
, a.member3_health
, a.member3_StateStr
, a.member3_OptimeDate
, a.insert_per_second
, a.query_per_second
, a.update_per_second
, a.delete_per_second
, a.getmore_per_second
, a.command_per_second
, concat(round(a.res/1024),'G') res
, concat(round(a.Virtual/1024),'G') Virtual
, concat(round(a.Mapped/1024),'G') Mapped
, a.CurrentConnections
, a.BytesIn_per_second
, a.BytesOut_per_second
, a.NumRequests_per_second
, a.ts
,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg
from
cat_mongodb_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_mongodb_probe_history a
inner join (select host,port,max(id) id from cat_mongodb_probe_history group by host,port) b on(a.host=b.host and a.port=b.port and a.id=b.id)
)a on(a.port=e.port and a.host=e.host)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d on (c.host=d.host and c.id=d.id)
) c on (c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,a.host,a.port
) g
`)
redisCursor, _ := db.Query(`
select
g.generator_room,g.host,g.port,
case
when g.redists is null then 'redis未采集'
when g.hostts is null then '主机未采集'
when g.redists
when g.used_memory_value>redis_max_mem then 'redis内存超过阈值'
when g.redis_cpu_ratio>redis_max_cpu then 'redisCPU超过阈值'
when g.hostts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts redists,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value,
e.host
, e.port
, a.connected_clients
, a.blocked_clients
, concat(round(a.used_memory/1024/1024,0),'M') used_memory
,round(a.used_memory/1024/1024,0) used_memory_value
, a.role
, a.instantaneous_ops_per_sec
, a.rejected_connections
, a.cpu_ratio redis_cpu_ratio
, a.master_host
, a.master_port
, a.master_link_status
, a.master_last_io_seconds_ago
, a.connected_slaves
, a.SentinelInfo
, a.ts
,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg,e.redis_type,e.max_mem redis_max_mem,e.cpu_target redis_max_cpu
from
cat_redis_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_redis_probe_history a
inner join (select host,port,max(id) id from cat_redis_probe_history group by host,port) b
on(a.host=b.host and a.port=b.port and a.id=b.id)
) a on (a.host=e.host and a.port=e.port)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d
on (c.host=d.host and c.id=d.id)
) c on (c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,redis_type,a.host,a.port
) g order by g.generator_room,g.redis_type,g.host,g.port
`)
defer mysqlCursor.Close()
defer mongodbCursor.Close()
defer redisCursor.Close()
var generator_room string
var host string
var port string
var info string
for mysqlCursor.Next() {
err := mysqlCursor.Scan(&generator_room, &host, &port, &info)
if err != nil {
log.Println(err.Error())
}
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
for mongodbCursor.Next() {
mongodbCursor.Scan(&generator_room, &host, &port, &info)
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
for redisCursor.Next() {
redisCursor.Scan(&generator_room, &host, &port, &info)
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
}
func sendmail(generator_room, host, port, info *string) {
cmdStr := "curl 'http://xxx.com:8080/report?to=dba&title="
cmd := exec.Command("/bin/bash", "-c", cmdStr+*generator_room+"_"+*host+"_"+*port+"&content="+*info+"'")
cmd.Start()
}
经过实际测试测试,还是发现一些问题
Mongodb副本集,如果我停止了一个副本,所有的监控Agent均不能正常上报数据.
但是Mongodb本身的信息来看,是正常的.这是为什么呢..原因还没有查明.
上述报表SQL的优化
create index inx_1 on cat_mysql_probe_history(host,port);
create index inx_2 on cat_host_probe_history(host);
create index inx_3 on cat_host_probe(host);
create index inx_4 on cat_mongodb_probe_history(host,port);
create index inx_5 on cat_redis_probe_history(host,port);
每个报表SQL都可以在0.01s内返回
但是如何报警呢?我们单位有一个内部的HTTP发送邮件的接口.
小花狸报警器每分钟扫描数据库,如果有异常情况,则通过HTTP发送邮件告警.
代码如下
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
"os/exec"
)
func main() {
db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/probe?charset=utf8")
if err != nil {
log.Fatalf("Open database error: %s\n", err)
}
defer db.Close()
mysqlCursor, _ := db.Query(`
select
g.generator_room,g.h,g.port,
case
when g.mysqlts is null then 'MySQL未采集'
when g.hostts is null then '主机未采集'
when g.Slave_IO_Running!='Yes' and g.Master_Host !='' then 'IO线程停止'
when g.Slave_SQL_Running!='Yes' and g.Master_Host !='' then 'SQL线程停止'
when g.mysqlts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts mysqlts,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value,
a.*,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg,e.port p,e.host h
from
cat_mysql_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_mysql_probe_history a
inner join
(select host,port,max(id) id from cat_mysql_probe_history group by host,port) b
on(a.host=b.host and a.port=b.port and a.id=b.id)
) a on (e.host=a.host and e.port=a.port)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d
on (c.host=d.host and c.id=d.id)
)c on(c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,a.host,a.port
) g
`)
mongodbCursor, _ := db.Query(`
select
g.generator_room,g.host,g.port,
case
when g.mongodbts is null then 'mongodb未采集'
when g.hostts is null then '主机未采集'
when g.member1_health!='1' then 'member1异常'
when g.member2_health!='1' then 'member2异常'
when g.member3_health!='1' then 'member3异常'
when g.mongodbts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts mongodbts,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value
, e.host
, e.port
, a.member1_name
, a.member1_health
, a.member1_StateStr
, a.member1_OptimeDate
, a.member2_name
, a.member2_health
, a.member2_StateStr
, a.member2_OptimeDate
, a.member3_name
, a.member3_health
, a.member3_StateStr
, a.member3_OptimeDate
, a.insert_per_second
, a.query_per_second
, a.update_per_second
, a.delete_per_second
, a.getmore_per_second
, a.command_per_second
, concat(round(a.res/1024),'G') res
, concat(round(a.Virtual/1024),'G') Virtual
, concat(round(a.Mapped/1024),'G') Mapped
, a.CurrentConnections
, a.BytesIn_per_second
, a.BytesOut_per_second
, a.NumRequests_per_second
, a.ts
,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg
from
cat_mongodb_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_mongodb_probe_history a
inner join (select host,port,max(id) id from cat_mongodb_probe_history group by host,port) b on(a.host=b.host and a.port=b.port and a.id=b.id)
)a on(a.port=e.port and a.host=e.host)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d on (c.host=d.host and c.id=d.id)
) c on (c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,a.host,a.port
) g
`)
redisCursor, _ := db.Query(`
select
g.generator_room,g.host,g.port,
case
when g.redists is null then 'redis未采集'
when g.hostts is null then '主机未采集'
when g.redists
when g.used_memory_value>redis_max_mem then 'redis内存超过阈值'
when g.redis_cpu_ratio>redis_max_cpu then 'redisCPU超过阈值'
when g.hostts
when g.cpuratio>g.cpu_target then 'cpu超过阈值'
when g.rootratio_value>g.space_target then 'root空间超过阈值'
when g.dataratio_value>g.space_target then 'data空间超过阈值'
when g.dbdataratio_value>g.space_target then 'dbdata空间超过阈值'
else
'正常'
end info
from
(
select
round(100-cpu_idle,2) cpuratio,a.ts redists,c.ts hostts,
concat(round(mem_used/1024),'G') mem_used,
concat(round(mem_idle/1024),'G') mem_idle,
concat(round(mem_used/(mem_used+mem_idle),2)*100,'%') mem_ratio,
round(mem_used/(mem_used+mem_idle),2)*100 mem_ratio_value,
concat(round(root_used_space/1024/1024/1024,2),'G') root使用,
concat(round(root_idle_space/1024/1024/1024,2),'G') root空闲,
concat(round(root_used_space/(root_used_space+root_idle_space),2)*100,'%') rootratio,
round(root_used_space/(root_used_space+root_idle_space),2)*100 rootratio_value,
concat(round(data_used_space/1024/1024/1024,2),'G') data使用,
concat(round(data_idle_space/1024/1024/1024,2),'G') data空闲,
concat(round(data_used_space/(data_used_space+data_idle_space),2)*100,'%') dataratio,
round(data_used_space/(data_used_space+data_idle_space),2)*100 dataratio_value,
concat(round(dbdata_used_space/1024/1024/1024,2),'G') dbdata使用,
concat(round(dbdata_idle_space/1024/1024/1024,2),'G') dbdata空闲,
concat(round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100,'%') dbdataratio,
round(dbdata_used_space/(dbdata_used_space+dbdata_idle_space),2)*100 dbdataratio_value,
e.host
, e.port
, a.connected_clients
, a.blocked_clients
, concat(round(a.used_memory/1024/1024,0),'M') used_memory
,round(a.used_memory/1024/1024,0) used_memory_value
, a.role
, a.instantaneous_ops_per_sec
, a.rejected_connections
, a.cpu_ratio redis_cpu_ratio
, a.master_host
, a.master_port
, a.master_link_status
, a.master_last_io_seconds_ago
, a.connected_slaves
, a.SentinelInfo
, a.ts
,f.generator_room,f.cpu_target,f.mem_target,f.space_target,
e.admin,e.msg,e.redis_type,e.max_mem redis_max_mem,e.cpu_target redis_max_cpu
from
cat_redis_probe e
left join cat_host_probe f on (e.host=f.host)
left join
(
select a.* from cat_redis_probe_history a
inner join (select host,port,max(id) id from cat_redis_probe_history group by host,port) b
on(a.host=b.host and a.port=b.port and a.id=b.id)
) a on (a.host=e.host and a.port=e.port)
left join
(
select c.* from cat_host_probe_history c
inner join (select host,max(id) id from cat_host_probe_history group by host) d
on (c.host=d.host and c.id=d.id)
) c on (c.host=e.host)
where e.status=1 and f.status=1
order by f.generator_room,redis_type,a.host,a.port
) g order by g.generator_room,g.redis_type,g.host,g.port
`)
defer mysqlCursor.Close()
defer mongodbCursor.Close()
defer redisCursor.Close()
var generator_room string
var host string
var port string
var info string
for mysqlCursor.Next() {
err := mysqlCursor.Scan(&generator_room, &host, &port, &info)
if err != nil {
log.Println(err.Error())
}
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
for mongodbCursor.Next() {
mongodbCursor.Scan(&generator_room, &host, &port, &info)
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
for redisCursor.Next() {
redisCursor.Scan(&generator_room, &host, &port, &info)
if info != "正常" {
sendmail(&generator_room, &host, &port, &info)
}
}
}
func sendmail(generator_room, host, port, info *string) {
cmdStr := "curl 'http://xxx.com:8080/report?to=dba&title="
cmd := exec.Command("/bin/bash", "-c", cmdStr+*generator_room+"_"+*host+"_"+*port+"&content="+*info+"'")
cmd.Start()
}
经过实际测试测试,还是发现一些问题
Mongodb副本集,如果我停止了一个副本,所有的监控Agent均不能正常上报数据.
但是Mongodb本身的信息来看,是正常的.这是为什么呢..原因还没有查明.
create index inx_1 on cat_mysql_probe_history(host,port);
create index inx_2 on cat_host_probe_history(host);
create index inx_3 on cat_host_probe(host);
create index inx_4 on cat_mongodb_probe_history(host,port);
create index inx_5 on cat_redis_probe_history(host,port);
每个报表SQL都可以在0.01s内返回
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-1587668/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Go语言交互执行系统命令
下一篇：
Oracle将结果竖向显示
请登录后发表评论
登录
全部评论
