Monitor MySQL auto_increment on a table

Monitor MySQL auto_increment

We had a situation where we hit the max allowed number on one of our tables with auto_incremented id with an INT type (this rarely happens, but it did). We were caught unaware when this happened, and hence spent some time trying to resolve this issue.

After the incident, we decided that we start to monitor our tables with auto_increment IDs to better prepare for this situation before it hits the max allowed number. One of the methods demonstrated here are the ones we currently use on our databases.

Method 1

Monitor how close an auto_increment is to its maximum value. The idea is that we get a warning using some db cron scripts we have written to send an email notification when this happens.

## First, place the scripts below into a file auto_incr_query.sql
create temporary table max_int_values
(
int_type varchar(10) not null,
extra varchar(8) not null default '',
max_value bigint unsigned not null,
primary key (int_type,max_value),
key int_type (int_type),
key max_value (max_value)
);

insert into max_int_values(int_type,extra,max_value) values ('tinyint','',~0 >> 57);
insert into max_int_values(int_type,extra,max_value) values ('tinyint','unsigned',~0 >> 56);
insert into max_int_values(int_type,extra,max_value) values ('smallint','',~0 >> 49);
insert into max_int_values(int_type,extra,max_value) values ('smallint','unsigned',~0 >> 48);
insert into max_int_values(int_type,extra,max_value) values ('mediumint','',~0 >> 41);
insert into max_int_values(int_type,extra,max_value) values ('mediumint','unsigned',~0 >> 40);
insert into max_int_values(int_type,extra,max_value) values ('int','',~0 >> 33);
insert into max_int_values(int_type,extra,max_value) values ('int','unsigned',~0 >> 32);
insert into max_int_values(int_type,extra,max_value) values ('bigint','',~0 >> 1);
insert into max_int_values(int_type,extra,max_value) values ('bigint','unsigned',~0);

select t.table_Schema,t.table_name,c.column_name,c.column_type,
    t.auto_increment,m.max_value,
    round((t.auto_increment/m.max_value)*100,2) as pct_of_values_used,
     m.max_value - t.auto_increment as values_left,
     CONCAT(t.table_name,': ', IF(t.auto_increment > round((0.8)*(m.max_value)),'auto_incr over 90% threshold','aut_incr below 90% threshold')) as auto_incr_status
from information_schema.tables t
    inner join information_schema.columns c
    on c.table_Schema = t.table_Schema and c.table_name = t.table_name
    inner join max_int_values m
    on m.int_type = substr(c.column_type,1,length(m.int_type))
    and ((m.extra like '%unsigned') = (c.column_type like '%unsigned'))
where c.extra = 'auto_increment'
order by pct_of_values_used\G


Run the query from the script like this:
    mysql -ukkuser -pkkpass < /path/to/auto_incr_query.sql |grep 'over 80% threshold' > auto_incr_output.txt

Method 2

auto_incrment check
SELECT @@global.innodb_stats_on_metadata INTO @prev;
SET GLOBAL innodb_stats_on_metadata=0;
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IF(
    LOCATE('unsigned', COLUMN_TYPE) > 0,
    1,
    0
  ) AS IS_UNSIGNED,
  (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS MAX_VALUE,
  AUTO_INCREMENT,
  AUTO_INCREMENT / (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) AS AUTO_INCREMENT_RATIO,
   CONCAT(TABLE_SCHEMA,'.',TABLE_NAME,': ',IF(AUTO_INCREMENT / (
    CASE DATA_TYPE
      WHEN 'tinyint' THEN 255
      WHEN 'smallint' THEN 65535
      WHEN 'mediumint' THEN 16777215
      WHEN 'int' THEN 4294967295
      WHEN 'bigint' THEN 18446744073709551615
    END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
  ) > 0.8, 'WARNING: auto_incr is over 80% threshold','auto_incr is below 80% threshold')) AS AUTO_INCREMENT_PERC
FROM
  INFORMATION_SCHEMA.COLUMNS
  INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
  TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
  AND EXTRA='auto_increment'\G
SET GLOBAL innodb_stats_on_metadata=@prev;


Then run the following query to filter only auto_increment in danger of going over the max value

     mysql -ukkuser -pkkpass < /path/to/auto_incr_query.sql |grep 'over 80% threshold' >
     auto_incr_output.txt