How to Solve the Issue of Auto-Increment ID Skipping

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 自增ID跳跃增加,如何解决

| username: TiDBer_XowwRd4a

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]

The entire database uses TiDB’s auto-increment ID, but for some tables with frequent writes, the auto-increment ID increases particularly fast. With 20 million records, the auto-increment ID has already reached over 4 billion. The auto-increment IDs for other tables are normal. The default configuration is being used. How can this situation be resolved?
I know that in version 6.4, the auto-increment ID has been upgraded to a globally unique ID auto-increment, but this requires time for verification. Based on my observation of the ID data, it seems that out of the default 30,000 IDs, only 1-2,000 are used before it jumps directly to the next 30,000 IDs.

| username: xfworld | Original post link

When describing the problem, please follow this format:
【TiDB Usage Environment】Production / Testing / PoC
【TiDB Version】
【Problem Phenomenon and Impact】
【Reproduction Path】What operations were performed that led to the problem
【Resource Configuration】

Provide as much effective background information as possible. Many issues may have different suggestions under different scenarios and business contexts. If you don’t explain clearly, it will be difficult for others to help you.


Don’t leave it blank, fill in the necessary parts…
There are several types of auto-increment IDs, which one are you using?

| username: TiDBer_XowwRd4a | Original post link

Auto increment, the current issue is that some tables with high write volumes have this problem, while the auto-increment IDs of other tables do not have this issue.

| username: xfworld | Original post link

What about auto random?

| username: tidb菜鸟一只 | Original post link

Set the AUTO_ID_CACHE for the corresponding table to 100:
ALTER TABLE t AUTO_ID_CACHE 100;
If the length of consecutive IDs required in a batch INSERT statement exceeds the length of AUTO_ID_CACHE, TiDB will appropriately increase the cache to ensure the statement can be inserted normally.

| username: TiDBer_XowwRd4a | Original post link

I have considered this approach, but I’m not sure if reducing this cache will affect performance?

| username: tidb菜鸟一只 | Original post link

Normally, it won’t affect performance. If you have concerns, you can first adjust it to 10,000 for testing. If there are no issues, then you can adjust it to a smaller value.

| username: TiDBer_XowwRd4a | Original post link

Sure, I’ll give it a try later. Thank you.

| username: xfworld | Original post link

You only need to consider the amount brought by business concurrency for cache allocation intervals.
There have been significant optimizations after version 6.X.

However, hotspot issues may still occur.

| username: Jellybean | Original post link

If the auto-increment field uses the bigint type (which is also recommended), the maximum signed value can reach 9,223,372 trillion, and the maximum unsigned value can reach 18,446,744 trillion, which should be sufficient for use.

| username: wuxiangdong | Original post link

The request rate exceeds the speed of ID generation.

| username: TiDBer_XowwRd4a | Original post link

It has already been changed to bigint. The previous int type part has overflowed.