1. Story

也許你經(jīng)常會被問到，庫里某個表最近一年的內(nèi)每個月的數(shù)據(jù)量增長情況。當(dāng)然如果你有按月分表比較好辦，挨個 show table status，如果只有一個大表，那估計要在大家都休息的時候，寂寞的夜里去跑sql統(tǒng)計了，因為你只能獲取當(dāng)前的表信息，歷史信息追查不到了。

除此以外，作為DBA本身也要對數(shù)據(jù)庫空間增長情況進行預(yù)估，用以規(guī)劃容量。我們說的表信息主要包括：

1. 表數(shù)據(jù)大小（DATA_LENGTH）

2. 索引大小(INDEX_LENGTH)

3. 行數(shù)（ROWS）

4. 當(dāng)前自增值（AUTO_INCREMENT，如果有）

目前是沒有看到哪個mysql監(jiān)控工具上提供這樣的指標(biāo)。這些信息不需要采集的太頻繁，而且結(jié)果也只是個預(yù)估值，不一定準(zhǔn)確，所以這是站在一個全局、長遠(yuǎn)的角度去監(jiān)控(采集)表的。

本文要介紹的自己寫的采集工具，是基于組內(nèi)現(xiàn)有的一套監(jiān)控體系：

• InfluxDB：時間序列數(shù)據(jù)庫，存儲監(jiān)控數(shù)據(jù)

• Grafana：數(shù)據(jù)展示面板

• Telegraf：收集信息的agent
  看了下 telegraf 的最新的 mysql 插件，一開始很欣慰：支持收集 Table schema statistics 和 Info schema auto increment columns。試用了一下，有數(shù)據(jù)，但是如前面所說，除了自增值外其他都是預(yù)估值，telegraf收集頻率過高沒啥意義，也許一天2次就足夠了，它提供的 IntervalSlow選項固定寫死在代碼里，只能是放緩 global status 監(jiān)控頻率。不過倒是可以與其它監(jiān)控指標(biāo)分開成兩份配置文件，各自定義收集間隔來實現(xiàn)。

最后打算自己用python擼一個，上報到influxdb里 :)

2. Concept

完整代碼見 GitHub項目地址：DBschema_gather
實現(xiàn)也特別簡單，就是查詢 information_schema 庫的 COLUMNS、TABLES 兩個表：
<!-- 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'

關(guān)于 auto_increment，我們除了關(guān)注當(dāng)前增長到哪了，還會在意相比 int / bigint 的最大值，還有多少可用空間。于是計算了 autoIncrUsage 這一列，用于保存當(dāng)前已使用的比例。

然后使用 InfluxDB 的python客戶端，批量存入influxdb。如果沒有InfluxDB，結(jié)果會打印出json —— 這是Zabbix、Open-Falcon這些監(jiān)控工具普遍支持的格式。

最后就是使用 Grafana 從 influxdb 數(shù)據(jù)源畫圖。

3. Usage

1. 環(huán)境
   在 python 2.7 環(huán)境下編寫的，2.6，3.x沒測。

運行需要MySQLdb、influxdb兩個庫：

$ sudo pip install mysql-python influxdb

1. 配置
   settings_dbs.py 配置文件

2. 
   

• DBLIST_INFO：列表存放需要采集的哪些MySQL實例表信息，元組內(nèi)分別是連接地址、端口、用戶名、密碼
  用戶需要select表的權(quán)限，否則看不到對應(yīng)的信息.

• InfluxDB_INFO：influxdb的連接信息，注意提前創(chuàng)建好數(shù)據(jù)庫名 mysql_info
  設(shè)置為 None 可輸出結(jié)果為json.

• 創(chuàng)建influxdb上的數(shù)據(jù)庫和存儲策略
  存放2年，1個復(fù)制集：（按需調(diào)整）

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

看大的信息類似于：


1. 放crontab跑
   可以單獨放在用于監(jiān)控的服務(wù)器上，不過建議在生產(chǎn)環(huán)境可以運行在mysql實例所在主機上，安全起見。

一般庫在晚上會有數(shù)據(jù)遷移的動作，可以在遷移前后分別運行 mysql_schema_info.py 來收集一次。不建議太頻繁。

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

1. 生成圖表

導(dǎo)入項目下的 grafana_table_stats.json 到 Grafana面板中。效果如下：

表數(shù)據(jù)大小和行數(shù)


每天行數(shù)變化增量,auto_increment使用率

4. More

1. 分庫分表情況下，全局唯一ID在表里無法計算 autoIncrUsage

2. 實現(xiàn)上其實很簡單，更主要的是喚醒收集這些信息的意識

3. 可以增加 Graphite 輸出格式

以上就是監(jiān)控MySQL的同時收集表信息代碼詳解（圖文）的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！