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
}