Last Updated on
AUTO_INCREMENT primary keys are useful, because they save usthe 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
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
For example, PMM (Percona Monitoring and Management) does it well:
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
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
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
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.