Skip to content

# Checking for AUTO_INCREMENT capacity with single query

MySQL Analysis INFORMATION_SCHEMA openark kit April 5, 2011April 5, 2011

Darn! This means oak-show-limits becomes redundant. Am I not supposed to speak about it on my coming presentation? Bad timing!

You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT?

The answer is all there in INFORMATION_SCHEMA. The TABLES table shows the current AUTO_INCREMENT value per table, and the COLUMNS table tells us all about a column’s data type.

It takes some ugly code to deduce the maximum value per column type, what with signed/unsigned and data type, but then its very simple. Here is the query:

>     SELECT
>       TABLE_SCHEMA,
>       TABLE_NAME,
>       COLUMN_NAME,
>       DATA_TYPE,
>       COLUMN_TYPE,
>       IF(
>         LOCATE('unsigned', COLUMN_TYPE) > 0,
>         1,
>         0
>       ) AS IS_UNSIGNED,
>       (
>         CASE DATA_TYPE
>           WHEN 'tinyint' THEN 255
>           WHEN 'smallint' THEN 65535
>           WHEN 'mediumint' THEN 16777215
>           WHEN 'int' THEN 4294967295
>           WHEN 'bigint' THEN 18446744073709551615
>         END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
>       ) AS MAX_VALUE,
>       AUTO_INCREMENT,
>       AUTO_INCREMENT / (
>         CASE DATA_TYPE
>           WHEN 'tinyint' THEN 255
>           WHEN 'smallint' THEN 65535
>           WHEN 'mediumint' THEN 16777215
>           WHEN 'int' THEN 4294967295
>           WHEN 'bigint' THEN 18446744073709551615
>         END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
>       ) AS AUTO_INCREMENT_RATIO
>     FROM
>       INFORMATION_SCHEMA.COLUMNS
>       INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
>     WHERE
>       TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA', 'performance_schema')
>       AND EXTRA='auto_increment'
>     ;

There’s one row in the result set for each AUTO_INCREMENT column. since at most one AUTO_INCREMENT column can exist for any given table, each row also identifies a unique table. Resulting columns are mostly self-explanatory, but here’s some details on some of the columns:

- IS_UNSIGNED: 1 when the column is UNSIGNED, 0 otherwise.
- MAX_VALUE: maximum value that can be contained within column.
- AUTO_INCREMENT: current AUTO_INCREMENT value for table.
- AUTO_INCREMENT_RATIO: value in the range [0..1], where 1 means “100% full”.

A sample output:

>     +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+
>     | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME  | DATA_TYPE | COLUMN_TYPE           | IS_UNSIGNED | MAX_VALUE  | AUTO_INCREMENT | AUTO_INCREMENT_RATIO |
>     +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+
>     | sakila       | actor      | actor_id     | smallint  | smallint(5) unsigned  |           1 |      65535 |            201 |               0.0031 |
>     | sakila       | address    | address_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            606 |               0.0092 |
>     | sakila       | category   | category_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |             17 |               0.0667 |
>     | sakila       | city       | city_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |            601 |               0.0092 |
>     | sakila       | country    | country_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |            110 |               0.0017 |
>     | sakila       | customer   | customer_id  | smallint  | smallint(5) unsigned  |           1 |      65535 |            600 |               0.0092 |
>     | sakila       | film       | film_id      | smallint  | smallint(5) unsigned  |           1 |      65535 |           1001 |               0.0153 |
>     | sakila       | inventory  | inventory_id | mediumint | mediumint(8) unsigned |           1 |   16777215 |           4582 |               0.0003 |
>     | sakila       | language   | language_id  | tinyint   | tinyint(3) unsigned   |           1 |        255 |              7 |               0.0275 |
>     | sakila       | payment    | payment_id   | smallint  | smallint(5) unsigned  |           1 |      65535 |          16050 |               0.2449 |
>     | sakila       | rental     | rental_id    | int       | int(11)               |           0 | 2147483647 |          16050 |               0.0000 |
>     | sakila       | staff      | staff_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |
>     | sakila       | store      | store_id     | tinyint   | tinyint(3) unsigned   |           1 |        255 |              3 |               0.0118 |
>     +--------------+------------+--------------+-----------+-----------------------+-------------+------------+----------------+----------------------+

#### Bonus: free advice on increasing your AUTO_INCREMENT capacity

Make it UNSIGNED. No, really. Check your definitions now.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
11
Leave a Reply

9 Comment threads
2 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Daniël van Eeden

Nice one!!

Maybe it’s a good idea to also exclude performance_schema?

Author
shlomi

@Daniel,
thanks, updated.

Guest
Cédric

Nice query, just add “ORDER BY 9 DESC” in my case.
Thx !

Author
shlomi

ORDER BY is on you 🙂

common_schema over traditional scripts | code.openark.org

[…] It turns out it takes very little effort to build a query which does the same. In which case it is also easy to build a view which provides the […]

Guest
Martin

Hi 10x for the great article.But I have a question I have a result like this from running this query against one of the DB-s: | ussd_ubrowser | ubrowser_phone_producers | id | int | int(11) | 0 | 2147483647 | 18446744073709551615 | 8589934596.0000 | How is that possible,the Signed int to have more than 2147483647 and the output in AUTO_INCREMENT_RATIO is as seen above 8589934596.The version of MySQL that I use is quite old: 5.1.73 64Bit BR Martin

Author
shlomi

@martin, I guess the answer is “this is a bug” and that I should be casting along the way. But in order to fix it, I would like to see the data you were working on. Nothing special, just the table definition (only PK is of interest) and the current PK value, etc.

Thanks!

Guest
Martin

Hi Shlomi, 10x for the fast response.Here is the requested information: mysql> show create table ussd_ubrowser.ubrowser_phone_producers \G; *************************** 1. row *************************** Table: ubrowser_phone_producers Create Table: CREATE TABLE `ubrowser_phone_producers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `ordClmn` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show table status from ussd_ubrowser like 'ubrowser_phone_producers' \G; *************************** 1. row *************************** Name: ubrowser_phone_producers Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 6 Avg_row_length: 21 Data_length: 128 Max_data_length: 281474976710655 Index_length: 2048 Data_free: 0 Auto_increment: 18446744073709551615 Create_time: 2013-02-22 20:42:54 Update_time: 2013-02-22 20:42:54 Check_time: 2015-11-27 14:57:07… Read more »

Author
shlomi

@Martin,

The answer lies in the strange output you got from your `SHOW CREATE TABLE`. How is it possible you have `AUTO_INCREMENT=18446744073709551615` for an INT column? There is nothing the query can do about this: I suggest reporting this as a bug to MySQL.

Guest
M

Hi Shlomi,

great 10x for looking into it.And thank you for the great utilities that you have made.

BR
Martin

Using Prometheus to Check for auto_increment Value Exhaustion - Percona Database Performance Blog

[…] are several ways to check for auto_increment capacity; this is just one of them. But what really matters is how PMM […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.