Monitoring that AUTO_INCREMENT values won’t reach the limit

Last updated on 25 March 2021

AUTO_INCREMENT  primary keys are useful, because they save us the trouble to generate a unique value for every row, delegating this task to the database. But when we reach the limit we need to take some action, or we won’t be able to insert more rows.

On 5 May 2020, GitHub had a 2 hours and 24 minutes downtime caused by  AUTO_INCREMENT  values.

Gibraltar from above
Gibraltar from above

To increment or not to increment

Someone would argue that  AUTO_INCREMENT  comes with the so called Insert Locks, which can cause some contention for write-intensive workloads. On the other hand, generating a UUID will cause the primary key and all secondary indexes to be bigger, and slightly less efficient. So, most people prefer to use  AUTO_INCREMENT  primary keys.

Another problem is that all data types have a maximum value. When you reach the maximum allowed for your primary key, you will get an error:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

To make the problem worse,  AUTO_INCREMENT  values can only increase. It is not possible to decrease the counter to use lower values, even if they are available.

For MariaDB users, another workaround would be to use sequences, but this will be a topic for another article.

PMM and mysqld_exporter

Some monitoring software include graphs for  AUTO_INCREMENT  values.

For example, PMM (Percona Monitoring and Management) does it well:

PMM pane, with a list of tables and their % of used AUTO_INCREMENT values
A PMM pane shows the highest AUTO_INCREMENT values

It is actually a feature of Prometheus mysqld_exporter, which is included in PMM. If you use it but don’t use PMM, all you have to do is to invoke the exporter with the  --collect.auto_increment.columns  option.

Dear ol’ SQL

If your monitoring solution doesn’t include  AUTO_INCREMENT , or if it does but you’re curious to know how it does that, you may want to see the query that one can use to monitor the progress of  AUTO_INCREMENT  values:

SELECT
t.TABLE_SCHEMA AS `schema`,
t.TABLE_NAME AS `table`,
t.AUTO_INCREMENT AS `auto_increment`,
c.DATA_TYPE AS `pk_type`,
(
t.AUTO_INCREMENT /
(CASE DATA_TYPE
WHEN 'tinyint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
255,
127
)
WHEN 'smallint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
65535,
32767
)
WHEN 'mediumint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
16777215,
8388607
)
WHEN 'int'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
4294967295,
2147483647
)
WHEN 'bigint'
THEN IF(COLUMN_TYPE LIKE '%unsigned',
18446744073709551615,
9223372036854775807
)
END / 100)
) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE
t.AUTO_INCREMENT IS NOT NULL
AND c.COLUMN_KEY = 'PRI'
AND c.DATA_TYPE LIKE '%int'
;

Note that I made the assumption that  AUTO_INCREMENT  columns are always part of the primary key. I recommend to make sure to follow this good practice. But it’s not necessarily the case, as discussed here. Modify the query if you think you might have non-primary key  AUTO_INCREMENT  columns.

See also

Ebooks

Training

Conclusions

We discussed how to check if any of your tables has an  AUTO_INCREMENT  value that is close to its limit. In a separate article we will see how to fix this problem without causing a downtime.

Federico Razzoli

Photo credit

About Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems. Federico is also a free software supporter. He believes in information sharing and reciprocal help. He thinks that multiple organisations contributing the same repositoy and benefiting from each other's work are better than many companies re-inventing the wheel. He thinks this view is not just compatible with business, but it's today and tomorrow's way of doing business. Federico also loves beers from the North of Europe, wines from Italy, bagpipes, nature hiking and the history of computing.

4 Replies to “Monitoring that AUTO_INCREMENT values won’t reach the limit”

  1. That max_value field doesn’t look quite right to me – is it supposed to be percent_of_max_value or something? 2 issues, percentage calculation doesn’t look right, plus the hard coded unsigned bigint will get coerced into signed. Flipped it to this and it looked a bit straighter:

    (t.AUTO_INCREMENT/ CAST(
    (CASE DATA_TYPE
    WHEN ‘tinyint’
    THEN IF(COLUMN_TYPE LIKE ‘%unsigned’,
    ‘255’,
    ‘127’
    )
    WHEN ‘smallint’
    THEN IF(COLUMN_TYPE LIKE ‘%unsigned’,
    ‘65535’,
    ‘32767’
    )
    WHEN ‘mediumint’
    THEN IF(COLUMN_TYPE LIKE ‘%unsigned’,
    ‘16777215’,
    ‘8388607’
    )
    WHEN ‘int’
    THEN IF(COLUMN_TYPE LIKE ‘%unsigned’,
    ‘4294967295’,
    ‘2147483647’
    )
    WHEN ‘bigint’
    THEN IF(COLUMN_TYPE LIKE ‘%unsigned’,
    ‘18446744073709551615’,
    ‘9223372036854775807’
    )
    END) AS unsigned) * 100
    ) AS pct_used

    • Hi Glyn,
      Did you test your version? At a first glance, it seems to me that it gives exactly the same results as mine, the two formulas are equivalent. Though it is possible I didn’t test with BIGINT UNSIGNED.

Leave a Reply

Your email address will not be published. Required fields are marked *

*