Rumah  >  Artikel  >  pangkalan data  >  监控MySQL的同时收集表信息代码详解(图文)

监控MySQL的同时收集表信息代码详解(图文)

黄舟
黄舟asal
2017-03-18 14:39:542049semak imbas

1. Story

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

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

    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表的权限,否则看不到对应的信息.

  • InfluxDB_INFO:influxdb的连接信息,注意提前创建好数据库名 mysql_info
    设置为 None 可输出结果为json.

  • 创建influxdb上的数据库和存储策略
    存放2年,1个复制集:(按需调整)

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

    看大的信息类似于:
    监控MySQL的同时收集表信息代码详解(图文)

  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
  1. 生成图表

导入项目下的 grafana_table_stats.json 到 Grafana面板中。效果如下:
监控MySQL的同时收集表信息代码详解(图文)
表数据大小和行数

监控MySQL的同时收集表信息代码详解(图文)
每天行数变化增量,auto_increment使用率

4. More

  1. 分库分表情况下,全局唯一ID在表里无法计算 autoIncrUsage

  2. 实现上其实很简单,更主要的是唤醒收集这些信息的意识

  3. 可以增加 Graphite 输出格式

    Atas ialah kandungan terperinci 监控MySQL的同时收集表信息代码详解(图文). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!