Prometheus监控mysql auto_increment值消耗情况，防止耗尽

如果你用过或了解过MySQL，那你一定知道自增主键了。每个自增id都是定义了初始值，然后按照指定步长增长（默认步长是1）。

虽然，自然数是没有上限的，但是我们在设计表结构的时候，通常都会指定字段长度，那么，这时候id就有上限了。

我们一般会将自增键的类型设置为int，数据范围为2的31次方， 即为负21亿到正21亿，对于一个频繁插入删除数据的表来说，21亿是可能会被用完的，可能引发业务无法正常写入。

因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成  bigint unsigned，那将允许最大使用 2的64次方，即为18446744073709552000。

在这篇博文中，我们将看看如何使用Prometheus来监控auto_increment值使用情况，防止值耗尽，影响业务。

一、mysqld exporter

MySQL (https://github.com/percona/mysqld_exporter), 需要开启采集指标项  collect.auto_increment.columns ，export 参考如下

/opt/exporter/bin/mysqld_exporter_3306 --web.listen-address=0.0.0.0:51230 --config.my-cnf=/opt/exporter/conf/my3306.cnf --exporter.lock_wait_timeout=1 --collect.info_schema.processlist.processes_by_user --collect.info_schema.processlist.processes_by_host --collect.info_schema.tables.databases=* --collect.info_schema.tables --collect.info_schema.innodb_metrics --collect.global_status --collect.global_variables --collect.slave_status --collect.info_schema.processlist --collect.perf_schema.tablelocks --collect.binlog_size --collect.perf_schema.indexiowaits --collect.perf_schema.tableiowaits --collect.info_schema.clientstats --collect.perf_schema.replication_group_member_stats --collect.perf_schema.replication_applier_status_by_worker --collect.engine_innodb_status --collect.slave_hosts --collect.info_schema.query_response_time --collect.info_schema.tablestats --collect.auto_increment.columns

二、Prometheus query

Prometheus不仅存储auto_increment列的当前值(mysql_info_schema_auto_increment_column)，而且还具有每个int类型允许的最大值(mysql_info_schema_auto_increment_column_max).我们可以通过一个查询获得用于该列的值的百分比，如下所示:

mysql_info_schema_auto_increment_column*100/mysql_info_schema_auto_increment_column_max

三、Grafana-MySQL统计仪表板

Panel JSON 见文尾

四、定义Prometheus 监控项阈值

当表字段的自增id使用率达85%触发告警

 - alert: IOV-MySQL_auto_increment
    expr: mysql_info_schema_auto_increment_column*100/mysql_info_schema_auto_increment_column_max >= 85
    labels:
      level: 3
    annotations:
      cur_value: '{{ $value }}'
      description: The mysql auto_increment_column 使用率已达 {{$value}},注意防止值耗尽

附上：Panel JSON

{
  "columns": [
    {
      "$$hashKey": "object:108",
      "text": "Current",
      "value": "current"
    }
  ],
  "editable": true,
  "error": false,
  "fieldConfig": {
    "defaults": {
      "custom": {}
    },
    "overrides": []
  },
  "fontSize": "100%",
  "gridPos": {
    "h": 7,
    "w": 24,
    "x": 0,
    "y": 3
  },
  "height": "",
  "hideTimeOverride": true,
  "id": 65,
  "links": [],
  "pageSize": null,
  "scroll": false,
  "showHeader": true,
  "sort": {
    "col": 5,
    "desc": true
  },
  "styles": [
    {
      "$$hashKey": "object:443",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "node_name",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:444",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "Time",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:445",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "agent_type",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:446",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "az",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:447",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "/.*_id/",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:448",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "custom_label",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:449",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "node_model",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:450",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "job",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:451",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "instance",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:452",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "region",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:453",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "service_type",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:454",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "cluster",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:455",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "node_type",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:456",
      "alias": "",
      "align": "auto",
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "mappingType": 1,
      "pattern": "replication_set",
      "thresholds": [],
      "type": "hidden",
      "unit": "short"
    },
    {
      "$$hashKey": "object:457",
      "colors": [
        "rgba(50, 172, 45, 0.97)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(245, 54, 54, 0.9)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "pattern": "/.*/",
      "thresholds": [
        "95",
        "98"
      ],
      "type": "number",
      "unit": "percentunit"
    }
  ],
  "targets": [
    {
      "datasource": {
        "type": "prometheus",
        "uid": "W0luofAGk"
      },
      "expr": "topk(10, avg by (alias,instance,schema,table) (mysql_info_schema_auto_increment_column{instance=~\"$instance\"} / mysql_info_schema_auto_increment_column_max{instance=~\"$instance\"}))\n",
      "format": "table",
      "instant": true,
      "interval": "5m",
      "intervalFactor": 1,
      "legendFormat": "{{ schema }}.{{ table }}",
      "refId": "A",
      "step": 300
    }
  ],
  "timeFrom": "5m",
  "title": "Top Tables by Auto Increment Usage",
  "transform": "table",
  "type": "table-old",
  "datasource": null
}