MySQL – Keep an eye on your auto_increment values

In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.

However that integer can have different types each having their size limitation, and they can also be signed or not.

Many people when creating a table use by default the following syntax do define their auto_increment primary key:

id int auto_increment primary key

This is a signed integer where the maximum value is 2,147,483,647.

It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.

And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)

MySQL Shell Plugin

Once again, MySQL Shell comes to the rescue with an easy plugin: ext.innodb.getAutoincFill()

The plugin returns the information related to the auto_increments fields.

Of course it’s recommended to not use the information_schema cache in the session.

It provides live information, as when the auto_increment values are exhausted…

Conclusion

As you can see, it’s very important to monitor the tables using auto_increments and it’s also very important to choose a right data type for them. Using unsigned is always a good practice !

MySQL Shell plugins are available here on github and contributions are welcome !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

7 Comments

  1. INSERT IGNORE, IODKU, and many other forms of INSERT can “burn” ids. If you see that MAX(id) is a lot higher than COUNT(*), you may be having that problem.

    Normalization is a common place where you might be burning ids rapidly. To avoid it, you essentially must do a SELECT before the INSERT. At that point, “batching” the normalizations could help efficiency.

  2. innodb_autoinc_lock_mode =2

    mysql> create table test(id int primary key auto_increment,a int);
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into test(a) values (1);
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into test(a) select a from test;
    Query OK, 1 row affected (0.01 sec)
    Records: 1 Duplicates: 0 Warnings: 0

    mysql> insert into test(a) select a from test;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2 Duplicates: 0 Warnings: 0

    mysql> insert into test(a) select a from test;
    Query OK, 4 rows affected (0.01 sec)
    Records: 4 Duplicates: 0 Warnings: 0

    mysql> select * from test;
    +—-+——+
    | id | a |
    +—-+——+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 4 | 1 |
    | 6 | 1 |
    | 7 | 1 |
    | 8 | 1 |
    | 9 | 1 |
    +—-+——+
    8 rows in set (0.00 sec)

    • OK, thx 😉

      Yes the max(id) and the count(*) differ often in production and this is yet another reason to keep an eye on these values 😉

      Cheers

Leave a Reply to fanderchanCancel Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.