We also faced the same situations last year which we weren’t aware of and never expected but it happened and it took us one hour to identify the problem. It was embarrassing moment as service was down for almost 1 hour.

Thanks Matthew for more insight.

Sorry – my bad. I seem to have skipped your second line in your original comment.

http://en.wikipedia.org/wiki/Red_herring

“… something that misleads or distracts from the relevant or important issue”

I think you’re confusing the x in INT(x) for the number of bits/bytes. You can read more about this here: http://stackoverflow.com/questions/7552223/int11-vs-intanything-else

@Matthew,

You are probably the first person I know of that uses Go for something that is not experimental. Oh, and congratulations on your new post. I love this blog and I visit it regularly.

sweet, but the numbers in parentheses are huge red herrings, especially as you seem to want to make the number smaller by 1

you could just as easily have said INT(937) UNSIGNED for all the difference it makes

I also blogged about this a while back: http://mechanics.flite.com/blog/2012/07/16/monitoring-auto-increment-capacity-in-mysql/

You are absolute correct, the “Procedure Analyse” route is horribly slow due to the full table scans. Definitely not something you want to use on production systems. You method is more suited for your described work case.

Additionally, in the environment for my client, with 200+ MySQL servers, deploying a single binary to a central “admin server” and executing multiple copies was far easier than having to deploy a stored procedure to each system. Going the SP route would constitute “a change” and thus the client’s change management policies would have to be followed with approvals and meetings etc etc. My script wasn’t a change and thus easier to be implemented.

On this topic it is also worth mentioning that MySQL has this obscure feature it called “Procedure Analyse”. It gives you current data type usage min and max values, null frequency ect. as well as optimal suggested field size.
http://dev.mysql.com/doc/refman/5.6/en/procedure-analyse.html

You could very easily use this to get this information without any external scripting. Like:
SELECT * FROM table1 PROCEDURE ANALYSE(10, 2000);

Or this stored proc that does it for a whole db:

CREATE PROCEDURE table_field_size_analyse(IN $database VARCHAR(255))
BEGIN

DECLARE $table_name VARCHAR(255);

DECLARE $table_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = $database;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET $table_name = NULL;

OPEN $table_cursor;
table_loop: LOOP

FETCH $table_cursor INTO $table_name;

IF $table_name IS NULL
THEN
LEAVE table_loop;
END IF;

SET @dsql = CONCAT(‘SELECT * FROM ', $database, '.', $table_name , ' PROCEDURE ANALYSE(10, 2000);’);

PREPARE stmt1 FROM @dsql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

END LOOP;
CLOSE $table_cursor;

END

or otherwise use this openark-kit tool: http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-show-limits.html

We’re using the common_schema view when monitoring our servers. We’re checking this view on a couple slaves (they all resolve the same obviously, and we don’t want to do I_S queries on the master if we don’t have to). We have threshold alerts on the AUTO_INCREMENT capacity and live quietly ever after.