監控MySQL的同時收集表格資訊代碼詳解（圖文）

1. Story

也許你常常會被問到，庫裡某個表最近一年的內每個月的資料量成長情況。當然如果你有按月分錶比較好辦，挨個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裡:)

2. Concept

完整程式碼見GitHub專案位址：DBschema_g​​ather

實作也特別簡單，就是查詢
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，結果會列印出

json —— 這是Zabbix、Open-Falcon這些監控工具普遍支援的格式。

最後就是使用 Grafana 從 influxdb 資料來源畫圖。

3. Usage

1. 環境

   在 python 2.7 環境下寫的，2.6，3.x沒測。
   

運行需要

MySQLdb、influxdb兩個函式庫：

$ sudo pip install mysql-python influxdb

1. 設定

   
   #settings_dbs.py 設定檔

2. 
   

• 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

看大的信息類似：

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面板中。效果如下：

表格資料大小與行數

#每天行數變更增量,auto_increment使用率

4. More

1. 分庫分錶情況下，全域唯一ID在表裡無法計算autoIncrUsage

2. 實作其實很簡單，更主要的是喚醒收集這些資訊的意識

3. 可以增加Graphite 輸出格式

以上是監控MySQL的同時收集表格資訊代碼詳解（圖文）的詳細內容。更多資訊請關注PHP中文網其他相關文章！