数据库监控物理内存
思路
最近接到一个新的需求,要对数据库的物理内存进行监控,超过阈值就要进行记录。其中要监控的维度包括:
1.整个mysql中所有数据库占用的物理内存
2.每个数据库的占用内存
3.每个数据表的物理内存
MySQL在初始化数据库的时候,有一个information_schema库,里边存储了很多的数据库的信息,其中就包括整个MySQL中所有的数据表信息,存在TABLES表中。
TABLES表中有数据库名(TABLE_SCHEMA),数据表名(TABLE_NAME),整个数据表的大小(DATA_LENGTH)。
执行SQL语句:
select TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH from information_schema.`TABLES`;
查询结果如下:
注意上边红框的部分:DATA_LENGTH 默认是字节,所以我们要转换一下 ,转为M为单位——DATA_LENGTH /1024/1024,并且要对DATA_LENGTH 降序排序,再给每一个字段取一个别名,优化后的SQL就是:
select TABLE_SCHEMA dbName, TABLE_NAME tableName, ROUND((DATA_LENGTH/1024/1024), 3) tableSize from information_schema.`TABLES`;
现在拿到的是表的大小,然后每个数据库中表的大小之和就是数据库占用的大小,所有数据库之和就是整个mysql中所有数据库占用的物理内存大小。
现在数据有了,我们可以通过定时任务来拉取数据存在数据库中。
但是我们是要把Java项目部署到和MySQL一起的服务器上,所以,我们需要获取部署到机器上的IP,并且有一个数据库的配置表,要包括数据库的ip,端口,账号,密码,连接上之后才可以进行监控。
我们使用SpringBoot进行搭建项目。
所以项目的架构是:SpringBoot + xxlJob + MyBatisPlus + JDK1.8快速项目搭建请参考这篇文章:Spring Boot 项目通用架构搭建
定时任务XXL-JOB集成
按照上边的快速搭建好项目之后,我们第一步先来集成定时任务XXL-JOB。
XXL-JOB使用SpringBoot开发,我们需要先下载到本地,启动项目,然后和我们的项目集成就可以了。
官网地址
码云下载
GitHub下载
下载好的xxl-job下边有一个doc文件夹,里边存放的是XXL-JOB所使用的表,我们先在数据库执行一下脚本。会生成数据库和表:
下载好之后导入我们的开发工具,我使用的是IDEA。
导入之后我们找到xxl-job-admin这个项目,修改applicaiton.properties文件中数据库的配置为上边我们导入xxl-job的数据库配置:
然后启动xxl-job-admin项目,访问:http://ip:port/xxl-job-admin,账号/密码:admin/123456
在我们创建的项目下添加xxl-job的依赖:
<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-job-core</artifactId>
<version>2.3.1</version>
</dependency>
修改 application.yml,添加xxl-job的配置:
# xxl-job配置
xxl:
job:
admin:
# 调度中心部署跟地址 [选填]:如调度中心集群部署存在多个地址则用逗号分隔。执行器将会使用该地址进行"执行器心跳注册"和"任务结果回调";为空则关闭自动注册;xxl-job后台管理界面的地址
addresses: http://127.0.0.1:8080/xxl-job-admin
executor:
# 执行器注册 [选填]:优先使用该配置作为注册地址,为空时使用内嵌服务 ”IP:PORT“ 作为注册地址。从而更灵活的支持容器类型执行器动态IP和动态映射端口问题。
address:
# 执行器AppName [选填]:执行器心跳注册分组依据;为空则关闭自动注册
appname: test-job
# 执行器IP [选填]:默认为空表示自动获取IP,多网卡时可手动设置指定IP,该IP不会绑定Host仅作为通讯实用;地址信息用于 "执行器注册" 和 "调度中心请求并触发任务";
ip:
# 执行器端口号 [选填]:小于等于0则自动获取;默认端口为9999,单机部署多个执行器时,注意要配置不同执行器端口;
port: 0
# 执行器运行日志文件存储磁盘路径 [选填] :需要对该路径拥有读写权限;为空则使用默认路径;
logpath: logs/xxl-job/test-job
# 执行器日志文件保存天数 [选填] : 过期日志自动清理, 限制值大于等于3时生效; 否则, 如-1, 关闭自动清理功能;
logretentiondays: 15
# 执行器通讯TOKEN [选填]:非空时启用;
accessToken: default_token
在这里一定要注意:在我们下载的xxl-job的配置中是有xxl.job.accessToken的,所以在我们的项目中的xxl.job.accessToken要和xxl-job-admin中的token一致,否则我们的项目集成xxl-job就会失败,无法访问。
编写一个测试job: XxlJobTest
@Slf4j
@Component
@RequiredArgsConstructor
public class XxlJobTest {
@XxlJob("xxlJobTest")
public ReturnT<String> xxlJobTest(String date) {
XxlJobContext xxlJobContext = XxlJobContext.getXxlJobContext();
String jobParam = xxlJobContext.getJobParam();
log.info("---------xxlJobTest定时任务执行成功--------{}",jobParam);
return ReturnT.SUCCESS;
}
}
添加执行器:注意appname要和我们在项目中配置的一致。
添加任务:
JobHandler和注解@XxlJob中保持一致,启动我们的项目,定时任务打印,就算是集成完了。
获取目标机器IP
由于我们的应用和Mysql部署在同一个服务器,所以我们应该获取服务的IP,根据IP获取到我们的配置,然后才能进行连接数据库。但是我们的IP不仅仅是一个,分为很多类型,所以我们写一个公共类来获取IP,使用以下代码获取:
public class IPUtil {
public static InetAddress getIPAddress() throws BindException {
try {
InetAddress inetAddress = null;
for (Enumeration<NetworkInterface> interfaces = NetworkInterface.getNetworkInterfaces(); interfaces.hasMoreElements(); ) {
NetworkInterface element = interfaces.nextElement();
for (Enumeration<InetAddress> addresses = element.getInetAddresses(); addresses.hasMoreElements(); ) {
InetAddress address = addresses.nextElement();
if (!address.isLoopbackAddress()) {
if (address.isSiteLocalAddress()) {
return address;
} else if (inetAddress == null) {
inetAddress = address;
}
}
}
}
if (inetAddress != null) {
return inetAddress;
}
InetAddress jdkAddress = null;
jdkAddress = inetAddress.getLocalHost();
if (jdkAddress == null) {
throw new BindException("无法获取IP");
}
return jdkAddress;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
数据库表建立
配置表:
CREATE TABLE `monitor_db_config` (
`id` int(11) NOT NULL,
`ip` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 'ip',
`port` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '端口',
`user` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '用户',
`password` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
`table_max_size` int(255) DEFAULT NULL COMMENT '表最大占用空间M',
`db_max_size` int(255) DEFAULT NULL COMMENT '单个数据库最大占用空间M',
`link_max_size` int(255) DEFAULT NULL COMMENT '整个数据库最大占用空间M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
还有一个超过大小的记录表:
CREATE TABLE `monitor_over_size` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`config_id` int(11) DEFAULT NULL COMMENT '数据库配置id',
`biz_type` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '业务类型',
`biz_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '业务名称',
`size` int(11) DEFAULT NULL COMMENT '大小M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
使用代码生成对应的controller,service,dao.
获取物理内存信息
由于我们是根据ip读取数据库连接在进行查询,所以我们不能使用mybatis这中形式,所以我们可以使用DataSource+JdbcTemplate来执行我们想要的sql,写一个执行sql的工具类:
public class DataBaseUtil {
public static List execute(MonitorDbConfig config, String sql, Class clazz) {
String url = "jdbc:mysql://"+ config.getIp()+":" + config.getPort() +":/boot";
DataSource dataSource = new DriverManagerDataSource(url, config.getUser(), config.getPassword());
try {
dataSource.getConnection();
}catch (SQLException e) {
return new ArrayList();
}
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(clazz));
}
}
clazz是返回的List的反省类型。
新建Job:
@Slf4j
@Component
@RequiredArgsConstructor
public class MonitorDBSIzeJob {
@Autowired
private MonitorDbConfigBiz configBiz;
@XxlJob("monitorDBSIzeJob")
public void xxlJobTest(String date) throws BindException {
configBiz.monitorDBSize();
}
}
MonitorDbConfigBiz:
public class MonitorDbConfigBiz {
@Autowired
private MonitorDbConfigService dbConfigService;
@Autowired
private MonitorOverSizeService overSizeService;
public void monitorDBSize() throws BindException {
// 获取本机ip
String ip = IPUtil.getIPAddress().getHostAddress();
// 根据ip获取配置
MonitorDbConfig config = dbConfigService.getDataByIP(ip);
if (ObjectUtils.isEmpty(config)) {
return;
}
List<MonitorOverSize> overSizes = new ArrayList<>();
// 获取数据库大小的sql
String sql = "select TABLE_SCHEMA dbName, TABLE_NAME tableName, ROUND((DATA_LENGTH/1024/1024), 3) tableSize from information_schema.`TABLES`;";
//执行sql
List<DBSizeVO> dbSizeVOS = DataBaseUtil.execute(config, sql, DBSizeVO.class);
if (!CollectionUtils.isEmpty(dbSizeVOS)) {
// 过滤数据
dbSizeVOS = dbSizeVOS.stream().filter(vo -> !ObjectUtils.isEmpty(vo.getTableSize())).collect(Collectors.toList());
// 获取所有表的大小作为整个链接的数据大小
BigDecimal totalSize = dbSizeVOS.stream().map(DBSizeVO::getTableSize).reduce(BigDecimal.ZERO, BigDecimal::add);
// 和数据库配置比较,超过就添加到数据库
if (!ObjectUtils.isEmpty(config.getLinkMaxSize()) && config.getLinkMaxSize().compareTo(totalSize) <= 0) {
overSizes.add(new MonitorOverSize().setBizType("all")
.setSize(totalSize)
.setConfigId(config.getId())
.setBizName(config.getIp()));
}
// 拿到每个表超过配置的数据,添加到数据库
dbSizeVOS.stream().filter(m -> m.getTableSize().compareTo(config.getTableMaxSize()) >= 0)
.forEach(p ->{
overSizes.add(new MonitorOverSize().setBizType("table")
.setSize(p.getTableSize())
.setConfigId(config.getId())
.setBizName(config.getIp()));
});
// 以数据库名字分组,数据库名字作为map的key,以数据作为value
Map<String, List<DBSizeVO>> dbDataMap = dbSizeVOS.stream().collect(Collectors.groupingBy(DBSizeVO::getDbName));
// 循环每个数据库的数据
dbDataMap.keySet().forEach(o ->{
// 每个数据库的数据表大小累加
BigDecimal dbSize = dbDataMap.get(o).stream().map(DBSizeVO::getTableSize).reduce(BigDecimal.ZERO, BigDecimal::add);
if (dbSize.compareTo(config.getDbMaxSize()) >= 0) {
overSizes.add(new MonitorOverSize().setBizType("db")
.setSize(dbSize)
.setConfigId(config.getId())
.setBizName(config.getIp()));
}
});
}
// 批量保存数据
overSizeService.saveBatch(overSizes);
}
}
配置XXL_JOB:
然后运行就可以了。
今天的内容就到这里了,创作不易,希望大家给个一键三连哦!