Schedule Meeting

a

How to monitor that AUTO_INCREMENT columns won’t reach their limit

by | Nov 13, 2020 | MariaDB, MySQL

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 than the highest value in the column, even if there are “holes”.

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 old 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

Did you like this article?

Photo credit

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

MariaDB Catalogs: some use cases

MariaDB Catalogs: some use cases

This month I attended Monty’s talk about Catalogs at MariaDB Server Fest 2023 in Helsinki. He described this nice feature, which is still under development, and how it will work. But when he talked about the use cases, I was under the impression that catalogs have...

MariaDB/MySQL: working with storage engines

MariaDB/MySQL: working with storage engines

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines. Here we'll see how to work with them. We'll see how to obtain information about storage engines,...

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...

Services

2 Comments

  1. Martins

    The article seems to be missing information of when do we know, that the usage is reaching its limit ? Is it when the column max_value is reaching 1 ?

    Reply
    • Federico Razzoli

      Hi Martins. In the article you’ll see a query that shows if some columns are approaching the limit.
      Federico

      Reply

Submit a Comment

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