数据库服务器资源使用周报

最新推荐文章于 2023-12-21 21:53:00 发布

hwtl070359898

最新推荐文章于 2023-12-21 21:53:00 发布

阅读量332

点赞数

文章标签： java mysql github

本文链接：https://blog.csdn.net/qq_38082146/article/details/118438218

版权

本文介绍了一个旨在提高服务器资源利用率的项目，通过监控和统计DB Server的CPU、内存、磁盘使用情况及慢查询次数，生成周报并发送给相关人员。项目使用Python脚本从InfluxDB和Elasticsearch收集数据，存储到MySQL，并通过邮件发送报告。文章详细阐述了表结构、统计代码以及实现效果。

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

一.项目说明

1.1 项目目的

1.盘活服务器资源，提高资源的使用率；资源是公司的资产，只有尽可能发挥其价值，才能创造更多的价值。所以，有必要知道，公司整体（或某业务、产品）所属的 DB Server的资源使用情况。主要从CPU、内存、Disk的平均数和中位数来反映。实现更合理的资源分配和集中性的管理，节省资源成本。

2.慢查询的次数，既可以说明程序的性能和Server的压力，说明了待确认和优化的情况，也说明了资源的紧张性。

3.此类历史数据的积累，可以生成一个变化趋势图，说明资源使用趋势。

4.之前的监控大部分诊断具体的一个DB Server或应用，这个是针对公司整体（或某业务、产品）所属的 DB Server；是监控体系的一个完善和补充。

即:资源盘活、充分利用、降本增效、监控补充。

1.2 部署环境及架构

现有的监控数据已收集到InfluxDB 和 elasticsearch 中，本次要实现的功能是将数据计算聚合到MySQL中，然后通过邮件发送给相关人员。存储到MySQL 数据库中，一是因为此类数据有一定的价值（具有追溯性和便于历史趋势分析），二是 InfluxDB 、elasticsearch 数据都有过期时间，数据保留的天数不是太长。

二.表的创建

2.1 存储DB资源使用情况的表

表名定义为weekly_dbperformance，具体的脚本如下：

复制代码

CREATE TABLE `weekly_dbperformance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cpu_mean` varchar(255) NOT NULL DEFAULT '',
  `cpu_median` varchar(255) NOT NULL DEFAULT '',
  `mem_mean` varchar(255) NOT NULL DEFAULT '',
  `mem_median` varchar(255) NOT NULL DEFAULT '',
  `disk_mean` varchar(255) NOT NULL DEFAULT '',
  `disk_median` varchar(255) NOT NULL DEFAULT '',
  `datetime_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据行创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3740 DEFAULT CHARSET=utf8mb4;

复制代码

在记录数据生成的时间字段添加个索引

create index idx_datetime on weekly_dbperformance (datetime_created);

2.2 存储DB 实例慢查询情况的表

表名定义为weekly_dbslowqty，具体的脚本如下：

复制代码

CREATE TABLE `weekly_dbslowqty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `qindex_name` varchar(50) NOT NULL DEFAULT '',
  `qstartdate` varchar(50) NOT NULL DEFAULT '',
  `qenddate` varchar(50) NOT NULL DEFAULT '',
  `slowqty` varchar(20) NOT NULL DEFAULT '',
  `datetime_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据行创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3740 DEFAULT CHARSET=utf8mb4;

复制代码

在记录查询的开始时间的字段上添加个索引：

https://www.jianshu.com/p/77550dab31c2

三.主要功能代码

3.1 统计DB Server资源使用率

可执行文件为collect_dbperformance.py

从InfluxDB中查询DB Server的资源使用情况。包括CPU的平均数、CPU的中位数、内存使用的平均数、内存使用的中位数、磁盘平均使用率、磁盘使用的中位数。

拉取计算的是过去7天的数据。

复制代码

#!/usr/bin/python
# -*- coding: UTF-8 -*-



from influxdb import InfluxDBClient

import pytz
import time
import dateutil.parser
import datetime

import db_monitor_conn
mysqldb = db_monitor_conn.db
# use cursor
cursor = mysqldb.cursor()

class DBApi(object):
    """
    通过infludb获取数据
    """

    def __init__(self, ip, port):
        """
        初始化数据
        :param ip:influxdb地址
        :param port: 端口
        """
        self.db_name = 'telegraf'
        self.use_cpu_table = 'cpu' # cpu使用率表
        self.phy_mem_table = 'mem'# 物理内存表
        self.disk_table = 'disk'# 磁盘表
        self.client = InfluxDBClient(ip, port, '用*户*名', '密*码', self.db_name)  # 连接influxdb数据库
        print ('test link influxdb')


    def get_use_dbperformance(self, s_time, e_time):
        """
        获取磁盘io使用率
        :param host: 查询的主机host (telegraf 配置参数中的host栏位)
        :param s_time: 开始时间
        :param e_time: 结束时间
        :return:
        """

        response = {}
        ### 时间还需转换，否则报错 TypeError: Tuple or struct_time argument required

        #s = time.strptime(s_time, '%Y-%m-%d %H:%M:%S')
        #e = time.strptime(e_time, '%Y-%m-%d %H:%M:%S')
        s = time.strptime(s_time, '%Y-%m-%d')
        e = time.strptime(e_time, '%Y-%m-%d')
        start_time = int(time.mktime(s