MySQL Auto‑Increment Hits INT Limit: Diagnosis, Fixes, and Performance Lessons

Background

Operations reported a missing device record, leading to suspicion of data loss. The service writes a unique identifier to Redis; if the key is absent it performs an INSERT, otherwise an UPDATE, using Redis as a cache to accelerate bulk writes.

Problem Analysis

Inspection of a table with over 5 × 10⁹ rows showed that the AUTO_INCREMENT column had reached 2147483647, the maximum value for a signed 32‑bit INT. Although the column definition included a display width of 50, this does not affect the numeric range, so further INSERTs silently failed.

Solution Options

Convert the primary key column to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT. This requires a table lock for the duration of the ALTER.

Adopt a distributed ID generation scheme (e.g., Snowflake). This entails redesigning the table, migrating existing data, and adding supporting infrastructure such as Redis.

Sharding the table. This is the most complex approach and requires a sharding framework, Elasticsearch for cross‑shard queries, and middleware (e.g., Logstash) to sync MySQL and ES.

BIGINT Migration

The simplest path—changing the column type—was chosen. In a test environment with 100 million rows the ALTER completed in about one hour; production was estimated to take 6‑7 hours.

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Progress was monitored with the Performance Schema:

SELECT EVENT_NAME,
       WORK_COMPLETED,
       WORK_ESTIMATED,
       ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS "Progress (%)"
FROM performance_schema.events_stages_current;

The operation stalled below 50 % after many hours because the index tree grew large and CPU contention from other threads slowed the ALTER.

Root Cause of ID Gaps

MySQL’s high‑performance AUTO_INCREMENT algorithm can produce non‑contiguous IDs under heavy concurrency. With 30 consumer threads processing batches, the gap widened dramatically (e.g., 10 rows consumed used ~100 IDs). Reducing the thread count to 15 noticeably decreased the gaps.

Temporary Fix with Stored Procedure

A stored procedure was created to clean the last 1 million IDs, allowing the system to keep running while a permanent fix was prepared.

BEGIN
  DECLARE start_id INT DEFAULT 1;
  DECLARE end_id   INT DEFAULT 100000;
  WHILE start_id <= end_id DO
    UPDATE your_table
    SET id = start_id + 1
    WHERE id = (SELECT original_id FROM (
                  SELECT id AS original_id FROM your_table ORDER BY id DESC LIMIT 1
                ) AS t);
    SET start_id = start_id + 1;
  END WHILE;
END;

After cleaning, the AUTO_INCREMENT value was reset:

ALTER TABLE your_table AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM your_table);

Consumer Strategy Evolution

Single‑threaded batch of 500 records.

30 threads, each processing one record at a time.

30 threads, each processing batches of 50 records.

The final configuration—multi‑threaded batch processing—delivered the highest throughput, but excessive concurrency can increase context‑switch overhead and CPU contention.

Key Takeaways

Design schemas with a sufficiently large ID range (e.g., use BIGINT from the start) to avoid costly migrations.

High‑performance AUTO_INCREMENT may create large ID gaps under heavy load; monitor and tune concurrency.

Combining multi‑threaded asynchronous processing with batch writes improves performance, but must be balanced against CPU contention and locking.