1

I've been assigned to create a bash script that checks when an AUTO_INCREMENT column is getting close to its limit. I figured the best way to do this is to get the "Current Auto Increment Value" and compare to "maximum value supported column's data type" (say 255 for tinyint).

We have about 2Tb of data in our databases, I dont want to query the INFORMATION SCHEMA table to get the AUTO_INCREMENT because it will take too long.

Therefore I can't use (SHOW TABLE STATUS LIKE 'table_name') nor (SELECT AUTO_INCREMENT FROM information_schema.tables) because both query INFORMATION SCHEMA table.

I've been told there might be a workaround to get the current AUTO_INCREMENT without hitting the information schema table but I can't see how. Any ideas?

This is how I get the data type

DATA_TYPE="`$MYSQLCONNECT -D $DATABASE --batch -N -e "SELECT DATA_TYPE FROM         
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$DATABASE' AND TABLE_NAME='$TABLE' AND 
EXTRA='auto_increment'"`"

This is how I get the max value that column can handle:

MAX_VALUE="`$MYSQLCONNECT -D $DATABASE --batch -N -e "SELECT ( 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 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$DATABASE' AND TABLE_NAME 
= '$TABLE' AND EXTRA='auto_increment'"`"
Captain Obvlious
  • 19,754
  • 5
  • 44
  • 74
andresg3
  • 343
  • 2
  • 6
  • 20

1 Answers1

1

Querying the INFORMATION_SCHEMA does take a long time, because a query against TABLES or STATISTICS or some other tables causes MySQL to analyze the data distribution in the table, calculate average row size, etc.

The amount of overhead of this analysis has more to do with the number of tables, not the size of tables. It basically reads 8 random pages from each table.

You can disable the feature of the automatic statistics analysis so the I_S queries run without so much overhead. 

SET GLOBAL innodb_stats_on_metadata=0

In MySQL 5.6, this is configured by default, but you can also do it yourself starting in MySQL 5.1.7.

See also: Solving INFORMATION_SCHEMA slowness

FWIW, I also created a script to do what you're doing. I offer it under the GPL, the same license used by MySQL. Here's a link to it:
https://github.com/billkarwin/bk-tools/blob/master/pk-full-ratio.sql

I created a similar script for all integer columns, not only auto-increment primary key columns:
https://github.com/billkarwin/bk-tools/blob/master/int-full-ratio.sql

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Please help: http://stackoverflow.com/questions/21393700/the-variable-inside-while-loop-is-not-remembered-mysql-bash-script-auto-increme – andresg3 Jan 27 '14 at 23:16