Something you need to monitor

There is a decent chance that I can guess one thing you are not monitoring on your server. And it is something that can stop insertions to a table. Guess what it is yet? 

 

The majority of tables in a database typically use what is called an "auto_increment" column. This means that when you insert a row into a table a column will be automatically populated with a number that is a fixed increment from the previously incremented column. The amount of increment can be configured by the auto_increment_increment variable. The problem is that these auto_increment columns have maximum values that they may hold. Once a auto_increment column reaches this maximum value it won't insert any more until the situation is resolved.

 

A good DBA is planning ahead -- averting problems before they ever happen. And this can be done here. Information about the auto_increment columns for a table are stored in the information_schema database. Specifically, the TABLES table contains the current value for each auto_increment value and the COLUMNS table contains the data type information. Combining these you can determine how close to maximum capacity your auto_increment columns. Shlomi Noach had a great article (http://code.openark.org/blog/mysql/checking-for-auto_increment-capacity-...) back in 2011 on this problem. He published some SQL code that you can use to determine if any the auto_increment values in your tables are nearing capacity.

 

A word about data types for auto_increment. The auto_increment column must be an integer data type. It can be either a signed or unsigned integer. A signed integer is one that can be positive, negative or zero. An unsigned integer is one that can only be zero or greater. There is no reason for an auto_increment column to be signed as you are just reducing your possible range of values by approximately one-half. When evaluating the data type for your auto_increment column it will be a trade-off between expected range of values for the auto_increment  column and storage. This is simply because the data types that can hold larger values (say bigint for example) take up more bytes of disk space. Oracle has the exact range of values and storage requirements for the various integer data types here: http://dev.mysql.com/doc/refman/5.6/en/integer-types.html

 

There are multiple ways to monitor these values. I prefer to have a bash wrapper script that is run from cron periodically. If it finds one or more tables that have reached a perdetermined percentage level of capacity then I am emailed. For example, I might have it set to alert me if it reaches 80% of the maximum possible values for that auto_increment column. Something similar could be done from icinga or other monitoring systems. It could be done by hand, but in my opinion this is something that should be automated. People forget things. This is something that if you forget could potentially cause a site-outage situation.

 

I hope this helps and possibly gives you something to think about. Minimally I would recommend that you run Shlomi's SQL on your servers to determine if you are near capacity for any of your auto_increment columns. Ideally you should take the time to develope a long-term monitoring solution that will alert you when columns are reaching their maximum auto_increment numbers.

 

keith

Comments

This is certainly a good thing to monitor but be aware of servers with a large number of databases, tables or columns. INFORMATION_SCHEMA does not scale well so doing queries like this can take a very long time as the number of objects being queried grows. So if scanning the whole Mysql instance pay attention to run time and locking this may trigger.
When monitoring just table names on a server with more than 100,000 tables I had to switch to generating the info outside of It's just to get reasonable query times. So take care when looking for auto - increment values on such servers.

Simon, 

That's an excellent point. I have seen those situations handled several ways. In theory the table structures would usually be the same in a replicaiton topology so I might run something like this on a backup server in order to keep it from interfering with production. I have also also seen where the information_schema data was queried late at night/early in the morning from the prod servers and the results where stored in a central monitoring server / db where it could be queried "at leisure". Or you might have servers that are in a "hot standby" format (ie..a Master-Master configuration) this is easily done by querying the offline servers. 

keith

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.