(2021-8-20后)
有开发web端能力的志愿者可以留言参与开发,大概4个静态页可以搞定
所需表在这里获取并在原型库创建
https://www.yuque.com/ccazhw/ml3nkf/lb0fbe
zabbix监控
安装zabbix_server
/etc/zabbix/zabbix_server.conf
添加下面配置
JavaGateway=127.0.0.1JavaGatewayPort=10052StartJavaPollers=5
安装zabbix_java_gateway
/etc/zabbix/zabbix_java_gateway.conf 参考配置
LISTEN_IP="127.0.0.1"LISTEN_PORT=10052PID_FILE="/var/run/zabbix/zabbix_java.pid"START_POLLERS=5
zabbix web添加jmx监控
配置->主机->创建主机
主机配置页
JMX接口
IP地址: Mycat或者其他java程序的IP端口:jmx开启的端口
模板配置页
添加Template App Generic Java JMX模板
查看监控
监测->最新数据
日志文件
/var/log/zabbix/zabbix_server.log
JVM启动参数参考
-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=1984
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false
该参数可以在wrapper.conf里面配置,或者在java命令行上加上上述参数
Prometheus与Grafana监控
server配置
{
....
"properties":{"prometheusPort":7066},
....
}
此配置开启7066端口.并提供以下url供查询监控信息
http://127.0.0.1:7066/metrics
供Prometheus查询
Mycat2可视化监控,使用Grafana和prometheus实现:
https://github.com/MyCATApache/Mycat2/blob/master/Mycat2-monitor.json
可配合模板JVM dashboard
![~[YH%KCVOQ0]RHEJAL3UMZ0.png](/uploads/projects/ccazhw@ml3nkf/528361315a632991f46dd75612b0094b.png)
Mycat UI(监控页, 测试阶段,2021-8-21后)
Mycat2服务器开启9066端口http服务,该服务供Mycat2 UI或者其他服务获取Mycat2内部的监控信息
对响应时间,qps这类性能监控项,会在记录一段时间的操作,然后重置参数值,再次计数,然后除以时间,得到性能参数
而对于关键值,比如连接数量,并不会重置.
对于慢SQL监控,则是把慢SQL持久化到(原型库)数据库里面,Mycat不提供界面查询,可以直接在原型库mycat.sql_log表里面查看
实例监控
其中关于数据库的响应时间,物理SQL的,qps暂时不准确(1.21完善),它们可以使用第三方监控Mysql的工具监控
lqps是逻辑SQL的每秒查询率
pqps是物理SQL的每秒查询率(不准确)
lrt是逻辑SQL的平均响应时间,毫秒
prt是物理SQL的平均响应时间(不准确),毫秒
thread是mycat内用于处理SQL的线程数
con是连接Mycat的客户端的连接数
数据源连接监控,暂时qps,thread监控不准,这里的thread是上述实例监控的thread的一部分(1.21完善)
con是数据源的连接数量
集群监控
master是一段时间内,主节点的选择计数
slave是一段时间内,从节点的选择计数
status是集群状态,当不是ok的时候,可能出现过主从切换等情况,需要检查
查询慢SQL
查询原型库中的mycat.sql_log表
server.json配置
{
"loadBalance":{
"defaultLoadBalance":"BalanceRandom",
"loadBalances":[]
},
"mode":"local",
"monitor":{
"databaseInstanceMonitor":{
"initialDelay":30,
"period":30,
"timeUnit":"SECONDS"
},
"instanceMonitor":{
"initialDelay":30,
"period":30,
"timeUnit":"SECONDS"
},
"ip":"localhost",
"open":true,
"port":9066,
"clusterMonitor":{
"initialDelay":30,
"period":30,
"timeUnit":"SECONDS"
},
"sqlLog":{
"clazz":"io.mycat.exporter.MySQLLogConsumer",
"open":true,
"sqlTimeFilter":30000,
"sqlTypeFilter":["SELECT"]
}
},
"properties":{},
"server":{
"bkaJoin":true,
"bufferPool":{
},
"forcedPushDownBroadcast":false,
"idleTimer":{
"initialDelay":3,
"period":15,
"timeUnit":"SECONDS"
},
"ignoreCast":false,
"ip":"localhost",
"joinClustering":true,
"mergeUnionSize":5,
"mycatId":1,
"port":8066,
"proxy":true,
"reactorNumber":8,
"serverVersion":"5.7.33-mycat-2.0",
"tempDirectory":"xxxxxx",
"timeWorkerPool":{
"corePoolSize":0,
"keepAliveTime":1,
"maxPendingLimit":65535,
"maxPoolSize":2,
"taskTimeout":1,
"timeUnit":"MINUTES"
},
"workerPool":{
"corePoolSize":8,
"keepAliveTime":1,
"maxPendingLimit":65535,
"maxPoolSize":1024,
"taskTimeout":1,
"timeUnit":"MINUTES"
}
}
}
其中monitor是监控配置
"monitor":{
"databaseInstanceMonitor":{//数据库监控
"initialDelay":30,//开始延迟时间
"period":30,//刷新周期
"timeUnit":"SECONDS"
},
"instanceMonitor":{//实例
"initialDelay":30,
"period":30,
"timeUnit":"SECONDS"
},
"ip":"localhost",//绑定地址
"open":true,//开启或关闭监控
"port":9066,//绑定端口
"clusterMonitor":{//集群监控
"initialDelay":30,
"period":30,
"timeUnit":"SECONDS"
},
"sqlLog":{//慢sql记录实现类,该实现记录到原型库的`mycat`.`sql_log`
"clazz":"io.mycat.exporter.MySQLLogConsumer",
"open":true,
"sqlTimeFilter":30000,大于3000毫秒才会记录
"sqlTypeFilter":["SELECT"]//只有SELECT类型语句才记录
}
},
支持语句类型
SELECT,
UPDATE,
INSERT_SELECT,
INSERT_VALUES,
INSERT,
DELETE,
MERGE,
//触发条件
@Override
protected void pushSqlLog(SqlEntry sqlEntry) {
SqlLogConfig sqlLog = monitorConfig.getSqlLog();
if (sqlLog != null && logConsumer != null) {
if (sqlLog.getSqlTimeFilter() <= sqlEntry.getSqlTime()) {
//永远触发可以把sqlTimeFilter设置成-1,
if (sqlLog.getSqlTypeFilter().contains(sqlEntry.getSqlType())) {
//按需设置审计sql类型
logConsumer.accept(sqlEntry);
}
}
}
}
可以把安装包作为jar引入项目来开发以下例子
MySQLLogConsumer例子
public class MySQLLogConsumer implements Consumer<SqlEntry> {
private static final Logger LOGGER = LoggerFactory.getLogger(MySQLLogConsumer.class);
boolean init = false;
boolean initFail = false;
public MySQLLogConsumer() {
}
private void init() throws SQLException {
JdbcConnectionManager jdbcConnectionManager = MetaClusterCurrent.wrapper(JdbcConnectionManager.class);
MetadataManager metadataManager = MetaClusterCurrent.wrapper(MetadataManager.class);
try (DefaultConnection connection = jdbcConnectionManager.getConnection(metadataManager.getPrototype())) {
Connection rawConnection = connection.getRawConnection();
rawConnection.setSchema("mycat");
JdbcUtils.execute(connection.getRawConnection(),
" create table if not exists `sql_log` (\n" +
" `instanceId` bigint(20) DEFAULT NULL,\n" +
" `user` varchar(64) DEFAULT NULL,\n" +
" `connectionId` bigint(20) DEFAULT NULL,\n" +
" `ip` varchar(22) DEFAULT NULL,\n" +
" `port` bigint(20) DEFAULT NULL,\n" +
" `traceId` varchar(22) NOT NULL,\n" +
" `hash` varchar(22) DEFAULT NULL,\n" +
" `sqlType` varchar(22) DEFAULT NULL,\n" +
" `sql` longtext,\n" +
" `transactionId` varchar(22) DEFAULT NULL,\n" +
" `sqlTime` time DEFAULT NULL,\n" +
" `responseTime` datetime DEFAULT NULL,\n" +
" `affectRow` int(11) DEFAULT NULL,\n" +
" `result` tinyint(1) DEFAULT NULL,\n" +
" `externalMessage` tinytext,\n" +
" PRIMARY KEY (`traceId`)\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", Collections.emptyList());
}
}
@Override
@SneakyThrows
public void accept(SqlEntry sqlEntry) {
if (!init){
init = true;
try {
init();
}catch (Exception e){
LOGGER.error("",e);
initFail = true;
}
}
if (initFail){
return;
}
IOExecutor ioExecutor = MetaClusterCurrent.wrapper(IOExecutor.class);
JdbcConnectionManager jdbcConnectionManager = MetaClusterCurrent.wrapper(JdbcConnectionManager.class);
MetadataManager metadataManager = MetaClusterCurrent.wrapper(MetadataManager.class);
ioExecutor.executeBlocking(new Handler<Promise<Void>>() {
@Override
public void handle(Promise<Void> event) {
try {
try (DefaultConnection connection = jdbcConnectionManager.getConnection(metadataManager.getPrototype())) {
JdbcUtils.execute(connection.getRawConnection(), "INSERT INTO `mycat`.`sql_log` (" +
"`instanceId`," +
"user," +
"connectionId," +
"ip," +
"port," +
"traceId," +
"hash," +
"sqlType," +
"`sql`," +
"transactionId," +
"sqlTime," +
"responseTime," +
"affectRow," +
"result," +
"externalMessage)" +
"values(?,?,?,?,?," +
"?,?,?,?,?," +
"?,?,?,?,?)",
Arrays.asList(sqlEntry.getInstanceId(),
sqlEntry.getUser(),
sqlEntry.getConnectionId(),
sqlEntry.getIp(),
sqlEntry.getPort(),
sqlEntry.getTraceId(),
sqlEntry.getHash(),
Objects.toString(sqlEntry.getSqlType()),
sqlEntry.getSql(),
sqlEntry.getTransactionId(),
sqlEntry.getSqlTime(),
sqlEntry.getResponseTime(),
sqlEntry.getAffectRow(),
sqlEntry.isResult(),
sqlEntry.getExternalMessage()
));
}
} catch (Exception e) {
LOGGER.warn("", e);
}finally {
event.tryComplete();
}
}
});
}
}
若有收获，就点个赞吧
0 人点赞
