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 !
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.
Thank you Rick for that extra information
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)
Is there a question in your comment ?
Dear lefred，
I added an example where Max (ID) is larger than the actual number of rows.
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
[…] I always recommend to use auto_increment integers (or bigint…) as primary key but don’t forget to monitor them ! (see this post) […]