Mysql – Why is InnoDB auto_increment column skipping over large blocks of unused values

auto-incrementinnodbMySQL

We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time.

I turned on query logging while watching for a jump and found no deletions from the table during the time. (We don't often delete historical data, anyway.)

MySQL version: mysql Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (i386) using readline 5.1

OS: Centos 6.3

Any ideas how or why this could happen?
Any other information you might need to assess the situation?

Thanks!

Best Answer

This would be expected behavior in a few scenarios that come to mind:

  • If you you ROLLBACK transactions where you have inserted records into the table, the auto_increment values transiently assigned to those rows you ultimately did not COMMIT the insertion of will not be reused.

  • If you are using INSERT IGNORE and you have another unique key (in addition to the primary key). For every row you attempt to INSERT without explicitly specifying the primary key value in the VALUES, the auto_increment gets incremented, and if that row happens to be a duplicate of an existing unique key value, and therefore discarded because of INSERT IGNORE, the auto_increment counter will still have been incremented and those values will never be used.

  • Similarly, if you are using INSERT ... ON DUPLICATE KEY UPDATE and you have another unique key, the same behavior is observed. For those rows where a duplicate key value is found and the query is performed as an update, the auto_increment value still increments if the primary key column is not part of the original INSERT and the duplicate key detection results from a conflict against a unique key on another column.

Related Question