监控MySQL你还应该收集表信息

存储架构 2016-12-04 阅读原文

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

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

  1. 表数据大小(DATA_LENGTH)
  2. 索引大小(INDEX_LENGTH)
  3. 行数(ROWS)
  4. 当前自增值(AUTO_INCREMENT,如果有)

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

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

  • InfluxDB :时间序列数据库,存储监控数据
  • Grafana :数据展示面板
  • Telegraf :收集信息的agent
    看了下 telegraf 的最新的 mysql 插件 ,一开始很欣慰:支持收集 Table schema statistics 和 Info schema auto increment columns。试用了一下,有数据,但是如前面所说,除了自增值外其他都是预估值,telegraf收集频率过高没啥意义,也许一天2次就足够了,它提供的 IntervalSlow 选项固定写死在代码里,只能是放缓 global status 监控频率。不过倒是可以与其它监控指标分开成两份配置文件,各自定义收集间隔来实现。
    最后打算自己用python撸一个,上报到influxdb里 🙂

github项目地址在文后。

2. Concept

完整代码见 GitHub项目地址: DBschema_gather

实现也特别简单,就是查询 information_schema 库的 COLUMNSTABLES 两个表:

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
    
  2. 配置

    settings_dbs.py 配置文件

    • DBLIST_INFO :列表存放需要采集的哪些MySQL实例表信息,元组内分别是连接地址、端口、用户名、密码
      用户需要select表的权限,否则看不到对应的信息.
    • InfluxDB_INFO :influxdb的连接信息,注意提前创建好数据库名 mysql_info
      设置为 None 可输出结果为json.
  3. 创建influxdb上的数据库和存储策略

    存放2年,1个复制集:(按需调整)

    CREATE DATABASE "mysql_info"
    CREATE RETENTION POLICY "mysql_info_schema" ON "mysql_info" DURATION 730d REPLICATION 1 DEFAULT
    

看大的信息类似于:

  1. 放crontab跑

    可以单独放在用于监控的服务器上,不过建议在生产环境可以运行在mysql实例所在主机上,安全起见。

    一般库在晚上会有数据迁移的动作,可以在迁移前后分别运行 mysql_schema_info.py 来收集一次。不建议太频繁。

    40 23,5,12,18 * * * /opt/DBschema_info/mysql_schema_info.py >> /tmp/collect_DBschema_info.log 2>&1
    
  2. 生成图表

导入项目下的 grafana_table_stats.json 到 Grafana面板中。效果如下:

表数据大小和行数

每天行数变化增量,auto_increment使用率

责编内容by:Sean's Notes 【阅读原文】。感谢您的支持!

您可能感兴趣的

MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁... 浏览目录: 视图: 是一个虚拟表,其内容由查询定义 。同真实的表一样,视图包含一系列带有名称的列和行数据 视图有如下特点; 1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。 2. 视图是由基本表(实表)产生的表(虚表)。 3. 视图的建立和...
xtrabackup热备和恢复MySQL 热备份与恢复 3.1xtrabackup介绍: 热备份的方式也是直接复制数据物理文件,和冷备份一样,但是热备份可以不停机直接复制,一般用于7*24小时不间断的重要核心业务。MySQL的社区版本热备份工具InnoDB Hot Backup是付费的,只能试用30天,商业版才可以永久的使用, ...
MySQL连接远程数据库可能出现的问题 连接远程服务器超时 如果你连接远程服务器超时的话,那你需要做的是检查服务器是否开启了数据库端口(默认是3306)。 确认mysql是否允许远程访问 ss -tlnp 上面命令会列举出已经开启的网络接口。正常状态应该如图: 如果在你的服务器上,没有33...
MySQL Shell – Easy scripting With the introduction of MySQL InnoDB Cluster we also got the MySQL Shell (mysqlsh) interface. The shell offers scripting in Javascript (default), SQL...
记一次隐式转换引起的sql慢查询 前言: 实在很无语呀,遇到一个mysql隐式转换问题,问了周边的dba大拿该问题,他们居然反问我,你连这个也不知道?白白跟他们混了那么长 尼玛,我还真不知道。罪过罪过…. 该文章后续仍在不断的更新修改中, 请移步到原文地址 http://xiaorui.cc/?p=5147 ...