When MySQL Auto‑Increment Hits Its Limit: Diagnosis and Fixes

Background

Operations reported that a device could not be found in the backend, prompting an investigation that initially suspected corrupted data. Log inspection showed no errors; the service first stores a unique ID in Redis, inserting when the key is absent and updating otherwise, using the cache to speed up bulk inserts and updates.

Problem Analysis

The table contains over 5 × 10⁹ rows, raising concerns about table size limits. Examination of the auto‑increment column revealed the value 2147483647, which is the maximum for a signed 32‑bit integer. The column was defined as INT, and although the column length was set to 50, that setting has no effect on the numeric range.

The overflow caused INSERT statements to be generated but no rows were actually added, indicating that the primary key could no longer increase.

Proposed Solutions

Three remediation paths were suggested:

Change the column type to BIGINT. This requires no data migration but locks the table for the duration of the alteration.

Introduce a distributed ID scheme, which entails redesigning the table, migrating existing data to a new structure, and adding supporting services such as Redis.

Apply sharding (database‑and‑table partitioning). This approach requires a sharding framework, and if queries are not routed by shard, an additional Elasticsearch layer and synchronization middleware (e.g., Logstash) are needed.

https://juejin.cn/post/7444014749321461811

Switching to BIGINT was initially favored, but the lock time was expected to be long. The team also noted that the ID gaps were unusually large, likely due to high concurrency.

Implementation Details

A stored procedure was written to clean up the last one million IDs, providing a temporary relief window.

BEGIN
DECLARE start_id INT DEFAULT 1;
DECLARE end_id INT DEFAULT 100000;
DECLARE current_batch INT DEFAULT 0;
WHILE start_id <= end_id DO
  -- Update temporary table ID
  UPDATE table
  SET id = start_id + 1
  WHERE id = (
    SELECT original_id FROM (
      SELECT id AS original_id FROM table ORDER BY id DESC LIMIT 1
    ) AS test
  );
  SET start_id = start_id + 1;
END WHILE;
END

After cleaning, the auto‑increment value was reset:

ALTER TABLE your_table AUTO_INCREMENT = max(id) + 1;

To monitor the progress of the alteration, the following query was used:

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

Performance Observations

The alteration on a production table with hundreds of millions of rows took over ten hours and still showed less than 50 % progress, slowing down as it proceeded. The slowdown was attributed to the growing B‑tree index, increased I/O, and CPU contention from other concurrent threads.

Statistical sampling indicated that roughly 1 % of the data was processed per hour, confirming the lengthy duration.

Final Adjustments

The team experimented with consumer thread counts. Reducing the number of Java consumers from 30 to 15 decreased the ID gap size, confirming that high concurrency amplified the ID consumption rate. Ultimately, they switched to batch consumption (e.g., 30 threads each processing 50 records) and observed a significant throughput increase.

Key consumption strategies:

Single‑thread batch of 500 records.

30‑thread single‑record consumption.

30‑thread batch of 50 records.

Conclusion

The incident highlights three critical lessons:

Design databases with sufficient primary‑key range; using INT for tables expected to exceed billions of rows is unsafe.

High‑concurrency workloads can cause large gaps in auto‑increment values when the database employs a high‑performance ID generation strategy.

Multithreaded asynchronous processing must be balanced against context‑switch overhead; batch processing and careful thread tuning are essential for performance.