饱和度 - 监控MySQL的InnoDB Buffer pool 相关的指标

1. 对于 MySQL 而言，用什么指标来反映资源有多“满”呢？首先我们要关注 MySQL 所在机器的 CPU、内存、硬盘 I/O、网络流量这些基础指标

2. MySQL 本身也有一些指标来反映饱和度，比如刚才我们讲到的连接数，当前连接数（Threads_connected）除以最大连接数（max_connections）    可以得到连接数使用率

是一个需要重点监控的饱和度指标。

 

3. InnoDB Buffer pool 相关的指标，一个是 Buffer pool 的使用率，一个是 Buffer pool 的内存命中率。Buffer pool 是一块内存，专门用来缓存 Table、Index 相关的数据，提升查询性能。对 InnoDB 存储引擎而言，Buffer pool 是一个非常关键的设计。我们查看一下 Buffer pool 相关的指标。

MariaDB [(none)]> show global status like '%buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_pages_free         | 1036                                             |
| Innodb_buffer_pool_pages_total        | 8191                                             |
+---------------------------------------+--------------------------------------------------+
19 rows in set (0.001 sec)

Innodb_buffer_pool_pages_total 表示 InnoDB Buffer pool 的页总量，页（page）是 Buffer pool 的一个分配单位，默认的 page size 是 16KiB，可以通过  show variables like "innodb_page_size" 拿到。

 

Innodb_buffer_pool_pages_free 是剩余页数量，通过 total 和 free 可以计算出 used

用 used 除以 total 就可以得到使用率

当然，使用率高并不是说有问题，因为 InnoDB 有 LRU 缓存清理机制，只要响应得够快，高使用率也不是问题。

 

Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads 是另外两个关键指标。read_requests 表示向 Buffer pool 发起的查询总量，如果 Buffer pool 缓存了相关数据直接返回就好，如果 Buffer pool 没有相关数据，就要穿透内存去查询硬盘了。有多少请求满足不了需要去查询硬盘呢？

 

看 Innodb_buffer_pool_reads 指标统计的数量。所以，reads 这个指标除以 read_requests 就得到了穿透比例，这个比例越高，性能越差，一般可以通过调整 Buffer pool 的大小来解决。

 

4. Prometheus的配置

# A scrape configuration containing exactly one endpoint to scrape:
# Here it's Prometheus itself.
scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: "gauge_exporter"
    scrape_interval: 30s  # 采集频率为30s抓取一次
    static_configs:
    - targets:  # 配置采集任务
      - 127.0.0.1:5004

5.  自定义监控业务

[root@yld-db 0211]# cat flask_counter_gauge.py 
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
from flask import Flask, Response
from prometheus_client import Counter, generate_latest, CollectorRegistry, Gauge

def base(cmd):
    if subprocess.call(cmd, shell=True):
        raise Exception("{} 执行失败".format(cmd))


app = Flask(__name__)

registry = CollectorRegistry()
counter = Counter('innodb_pool_counter', 'an example showed how to use counter', ['machine_ip'], registry=registry)
gauge = Gauge('innodb_pool_gauge', 'an example showed how to use gauge', ['machine_ip'], registry=registry)


@app.route('/metrics')
def hello():
    counter.labels('127.0.0.1').inc(1)
    # get Innodb_buffer_pool_pages_free
    conn = pymysql.connect(host="127.0.0.1",user='root',password='do1admin',port=13307)
    cursor = conn.cursor()
    cursor.execute('show global status like "Innodb_buffer_pool_pages_free";')
    Innodb_buffer_pool_pages_free_rows = cursor.fetchall()
    Innodb_buffer_pool_pages_free = Innodb_buffer_pool_pages_free_rows[0][1]

    print Innodb_buffer_pool_pages_free

    # get Innodb_buffer_pool_pages_total
    cursor_all = conn.cursor()
    cursor_all.execute('show global status like "Innodb_buffer_pool_pages_total";')
    Innodb_buffer_pool_pages_free_all = cursor_all.fetchall()
    Innodb_buffer_pool_pages_total = Innodb_buffer_pool_pages_free_all[0][1]

    print Innodb_buffer_pool_pages_total
    # get Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total
    gauge.labels('127.0.0.1').set(float(Innodb_buffer_pool_pages_free)/float(Innodb_buffer_pool_pages_total))

    return Response(generate_latest(registry), mimetype='text/plain')
    #return Response(generate_latest(gauge), mimetype='text/plain')


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5004)

[root@yld-db 0211]#