1. Story
也許你經常會被問到，庫里某個表最近一年的內每個月的數據量增長情況。當然如果你有按月分表比較好辦，挨個 show table status，如果只有一個大表，那估計要在大家都休息的時候，寂寞的夜里去跑sql統計了，因為你只能獲取當前的表信息，歷史信息追查不到了。
除此以外，作為DBA本身也要對數據庫空間增長情況進行預估，用以規劃容量。我們說的表信息主要包括：
-
表數據大?。―ATA_LENGTH）
-
索引大小(INDEX_LENGTH)
-
行數（ROWS）
-
當前自增值（AUTO_INCREMENT，如果有）
目前是沒有看到哪個mysql監控工具上提供這樣的指標。這些信息不需要采集的太頻繁，而且結果也只是個預估值，不一定準確，所以這是站在一個全局、長遠的角度去監控(采集)表的。
本文要介紹的自己寫的采集工具，是基于組內現有的一套監控體系：
-
InfluxDB：時間序列數據庫，存儲監控數據
-
Grafana：數據展示面板
-
Telegraf：收集信息的agent
?看了下 telegraf 的最新的 mysql 插件，一開始很欣慰：支持收集 Table schema statistics 和 Info schema auto increment columns。試用了一下，有數據，但是如前面所說，除了自增值外其他都是預估值，telegraf收集頻率過高沒啥意義，也許一天2次就足夠了，它提供的 IntervalSlow選項固定寫死在代碼里，只能是放緩 global status 監控頻率。不過倒是可以與其它監控指標分開成兩份mysql，各自定義收集間隔來實現。
最后打算自己用python擼一個，上報到influxdb里 ??
2. Concept
完整代碼見 GitHub項目地址：DBschema_gather
實現也特別簡單，就是查詢 information_schema 庫的 COLUMNS、TABLES 兩個表：
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，結果會打印出mysql —— 這是Zabbix、Open-Falcon這些監控工具普遍支持的格式。
最后就是使用 Grafana 從 influxdb 數據源畫圖。
3. Usage
-
環境
在 python 2.7 環境下編寫的，2.6，3.x沒測。
運行需要MySQLdb、influxdb兩個庫：
$?sudo?pip?install?mysql-python?influxdb
-
配置
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
看大的信息類似于：
-
放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
-
生成圖表
導入項目下的 grafana_table_stats.json 到 Grafana面板中。效果如下：
表數據大小和行數
每天行數變化增量,auto_increment使用率
4. More
-
分庫分表情況下，全局唯一ID在表里無法計算 autoIncrUsage
-
實現上其實很簡單，更主要的是喚醒收集這些信息的意識
-
可以增加 Graphite 輸出格式
