In this blog post, we’ll look at how to use Prometheus to check for auto_increment value exhaustion.

One of the proactive tasks DBAs perform is checking if a field defined as auto_increment is about to reach the maximum allowed value of the int definition. For example, if a field is defined as smallint unsigned and your next auto_increment value is 65535 (the maximum possible value for smallint), you are in trouble. You should modify the column definition to at least mediumint unsigned, (which has a maximum value is 16777215) before you start to silently lose data or are not able to insert more rows.

Prometheus and the mysqld exporter

When you deploy the Percona Monitoring and Management (PMM) platform, you’re not only getting a state-of-the-art visualization tool, but you also get the Prometheus time-series database with it as part of the package. Prometheus connects to exporters running on a PMM Client and aggregates metrics collected by those exporters. For this case, the exporter that we care about is for MySQL (https://github.com/percona/mysqld_exporter), and specifically the metrics gathered by the collect.auto_increment.columns collector.

Tablestats

To know if your PMM installation is collecting this data, you should check with pmm-admin. If your output comes with tablestats=OFF, you are not collecting this metric:

By default, the tablestats feature is enabled (unless your MySQL server has more than 1000 tables). To enable it beyond that limit, re-add the metrics using the  --disable-tablestats-limit parameter with a larger value than the number of your tables (described in https://www.percona.com/doc/percona-monitoring-and-management/pmm-admin.html):

Prometheus query

Prometheus not only stores the current value of the auto_increment column (mysql_info_schema_auto_increment_column), but also has the maximum allowed value per int type on it (mysql_info_schema_auto_increment_column_max). We can get the percentage of values used for the column with a single query, like:

mysql_info_schema_auto_increment_column*100/mysql_info_schema_auto_increment_column_max (available thanks to Max Bubenick)

Now, where do I execute the query? There are three options: the Prometheus GUI, using the API or the MySQL Table Statistics dashboard in PMM.

GUI

Every PMM installation allows you to access Prometheus by going to the URL http://<serverIP>/prometheus. Put the query in the text field, and hit Execute. The results look like this:

Prometheus GUI

Prometheus GUI

 

You can see a row per existent table. In this case we can see that the table “sbtest20” is using the 95% of the available values. This makes it a candidate for a column definition modification. The remaining tables are using around 0.0002% of the values, so there’s plenty room to grow.

API

This is probably the best approach for checking this regularly. You can use the REST API to query metrics (as explained in https://prometheus.io/docs/querying/api/), like this:

See the new data? If you want to query the metrics for a specific table like I do, then just add the schema and table name: {schema=”percona”,table=”sbtest20″}:

The part of the output that matters here is:

As we saw in the GUI output, the table sbtest20 already used 95% of the available values for the column int definition. In this case, it is tinyint signed and the current maximum value is 120 (out of 127):

PMM’s MySQL Table Statistics dashboard

This information is also available on PMM, just go to the dashboard called MySQL Table Statistics and look for the section “Top Tables by Auto Increment Usage”:

The above graph is from Percona’s PMM demo setup, which is available publicly to everyone. You can access that particular chart by going to https://pmmdemo.percona.com/graph/dashboard/db/mysql-table-statistics?refresh=1m&panelId=53&fullscreen&orgId=1.

Conclusion

There are several ways to check for auto_increment capacity; this is just one of them. But what really matters is how PMM collects a rich set of information, and that’s is available for just one API request of distance. Note that each additional metric series collected by Prometheus adds additional work on the PMM Server, so watch that your server doesn’t enter what is known as Rushed Mode. This is a condition where the PMM Server is experiencing a high volume of writes and is struggling to keep up. You can identify this condition (since version 1.4) by examining the Prometheus dashboard, and in particular the graph Prometheus Problems. Check for the metric series called “Has been in Rushed Mode” and see if is greater than 0.

Enjoy PMM!

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Wagner Bianchi

Awesomesauce!

0
Vinoth Kanna

Indeed, It’s very much helpful, But I still tend to use the tweaked version of common_schema query to quickly check through the server where we don’t have PMM.

select tab as ‘Table’, col as ‘Column’, cv as ‘Current Value’, ud as ‘Appox. Usage Period (Days)’, round(usg,2) as ‘Usage %’ from (select concat(table_schema,’.’,table_name) as tab, concat(column_name,’ ‘,column_type,’ ‘,if(locate(‘unsigned’, column_type)=0,’signed’,”)) as col, auto_increment as cv, datediff(now(),create_time) as ud, ((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)))*100) as usg 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 table_type = ‘base table’ and extra = ‘auto_increment’ order by 5 desc, 3 desc) as get_auto_inc_usage where usg >= limit 10;

USER_INPUT -> In % (Auto Increment Usage)


Sample Output:
+---------------------------------+-------------------------------+---------------+----------------------------+---------+
| Table | Column | Current Value | Appox. Usage Period (Days) | Usage % |
+---------------------------------+-------------------------------+---------------+----------------------------+---------+
| test.tickets | id tinyint(4) signed | 100 | 0 | 78.74 |
| helpdesk.hd_ticket_priority | priority_id tinyint(4) signed | 5 | 406 | 3.94 |
| helpdesk.hd_syslog | log_id int(11) unsigned | 439118 | 198 | 0.01 |

0
Vinoth Kanna

* where usg >= USER_INPUT limit 10;

I marked the text “USER_INPUT” between , It disappeared

0
ChenGuangjing(Andy)

I also have a query to get the result

SELECT table_schema, table_name, column_name, column_type, auto_increment,max_int,ROUND(auto_increment/max_int*100,2) AS “USED %” FROM
(SELECT table_schema, table_name, column_name, column_type, auto_increment,
pow(2, case data_type
when ‘tinyint’ then 7
when ‘smallint’ then 15
when ‘mediumint’ then 23
when ‘int’ then 31
when ‘bigint’ then 63
end+(column_type like ‘% unsigned’))-1 as max_int
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,table_name)
WHERE t.table_schema not in (‘mysql’,’information_schema’,’performance_schema’) and t.table_type = ‘base table’
and c.extra = ‘auto_increment’ AND t.auto_increment IS NOT NULL order by auto_increment desc limit 10) TMP;
Here is my test result

+————–+————+————-+——————+—————-+————+——–+
| table_schema | table_name | column_name | column_type | auto_increment | max_int | USED % |
+————–+————+————-+——————+—————-+————+——–+
| edxapp | andy | id | int(10) unsigned | 4294967295 | 4294967295 | 100.00 |
| examlogin | andy | id | int(11) | 2147483641 | 2147483647 | 100.00 |
+————–+————+————-+——————+—————-+————+——–+
2 rows in set (0.11 sec)

1