明辉站/应用软件/内容

监控MySQL的一起收集表信息代码详细说明(图文)

应用软件2023-09-02 阅读
[摘要]1. Story也许你经常会被问到,库里某个表最近一年的内每个月的数据量增长情况。当然如果你有按月分表比较好办,挨个 show table status,如果只有一个大表,那估计要在大家都休息的时候,寂寞的夜里去跑sql统计了,因为你只能获取当前的表信息,历史信息追查不到了。除此以外,作为DBA本...

1. Story

也许你经常会被问到,库里某个表最近一年的内每个月的数据量增长情况。当然如果你有按月分表比较好办,挨个 show table status,如果只有一个大表,那估计要在大家都休息的时候,寂寞的夜里去跑sql统计了,因为你只能获取当前的表信息,历史信息追查不到了。

除此以外,作为DBA本身也要对数据库空间增长情况进行预估,用以规划容量。我们说的表信息主要包括:

  1. 表数据大小(DATA_LENGTH)

  2. 索引大小(INDEX_LENGTH)

  3. 行数(ROWS)

  4. 当前自增值(AUTO_INCREMENT,如果有)

目前是没有看到哪个mysql监控工具上提供这样的指标。这些信息不需要采集的太频繁,而且结果也只是个预估值,不一定准确,所以这是站在一个全局、长远的角度去监控(采集)表的。

本文要介绍的自己写的采集工具,是基于组内现有的一套监控体系:

最后打算自己用python撸一个,上报到influxdb里 :)

2. Concept

完整代码见 GitHub项目地址:DBschema_gather
实现也特别简单,就是查询 information_schema 库的 COLUMNSTABLES 两个表:
<!-- more -->

SELECT
    IFNULL(@@hostname, @@server_id) SERVER_NAME,
    %s as HOST,
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.TABLE_ROWS,
    t.DATA_LENGTH,
    t.INDEX_LENGTH,
    t.AUTO_INCREMENT,
  c.COLUMN_NAME,
  c.DATA_TYPE,
  LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED
  # CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned'))
FROM
    information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND c.EXTRA = 'auto_increment'
WHERE
    t.TABLE_SCHEMA NOT IN (
        'mysql',
        'information_schema',
        'performance_schema',
        'sys'
    )
AND t.TABLE_TYPE = 'BASE TABLE'

关于 auto_increment,我们除了关注当前增长到哪了,还会在意相比 int / bigint 的最大值,还有多少可用空间。于是计算了 autoIncrUsage 这一列,用于保存当前已使用的比例。

然后使用 InfluxDB 的python客户端,批量存入influxdb。如果没有InfluxDB,结果会打印出json —— 这是Zabbix、Open-Falcon这些监控工具普遍支持的格式。

最后就是使用 Grafana 从 influxdb 数据源画图。

3. Usage

  1. 环境
    在 python 2.7 环境下编写的,2.6,3.x没测。

运行需要MySQLdbinfluxdb两个库:

$ sudo pip install mysql-python influxdb
  1. 配置
    settings_dbs.py 配置文件


    • DBLIST_INFO:列表存放需要采集的哪些MySQL实例表信息,元组内分别是连接地址、端口、用户名、密码
      用户需要select表的权限,否则看不到对应的信息.

以上就是监控MySQL的同时收集表信息代码详解(图文)的详细内容,更多请关注php中文网其它相关文章!


学习教程快速掌握从入门到精通的SQL知识。

……

相关阅读