Use prometheus to count the remaining available percentage of MySQL auto-increment primary keys

Recently, a database in the production environment wrote log data crazily, causing primary key value overflow, so it is necessary to monitor this indicator.

mysqld_exporter comes with this feature. Here are the startup parameters I use:

nohup ./mysqld_exporter --config.my-cnf="./my.cnf" --web.listen-address=":9104" --collect.heartbeat --collect.auto_increment.columns --collect.binlog_size --collect.engine_innodb_status --collect.engine_tokudb_status --collect.slave_hosts --collect.slave_status --collect.info_schema.processlist --collect.info_schema.innodb_metrics > /dev/null 2>&1 &

The parameters highlighted in red are used to collect the usage of the auto-increment ID.

The SQL actually executed is similar to this:

SELECT 
 table_schema,
 table_name,
 column_name,
 AUTO_INCREMENT,
 POW(2, CASE data_type
   WHEN 'tinyint' THEN 7
   WHEN 'smallint' THEN 15
   WHEN 'mediumint' THEN 23
   WHEN 'int' THEN 31
   WHEN 'bigint' THEN 63
   END+(column_type LIKE '% unsigned'))-1 AS max_int 
  FROM information_schema.tables t
   JOIN information_schema.columns c USING (table_schema,table_name)
  WHERE
   c.extra = 'auto_increment' 
  AND
   t.TABLE_SCHEMA NOT IN ('information_schema','mysql', 'sys','test','performance_schema') 
  AND
   t.auto_increment IS NOT NULL; 

In the Prometheus web interface, we can test and write the following promql to find the library + table name of the instance with the remaining auto-increment ID rate less than 40%

(mysql_info_schema_auto_increment_column_max{schema!~'test|mysql'} - mysql_info_schema_auto_increment_column{schema!~'test|mysql'})/mysql_info_schema_auto_increment_column_max{schema!~'test|mysql'}*100 < 40

After obtaining the data, we can configure relevant alarms in the alertmanager, or draw a graph on grafana, as follows:

This is the end of this article about using prometheus to count the remaining available percentage of MySQL auto-increment primary keys. For more relevant prometheus statistics on MySQL auto-increment primary keys, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!