Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb表级设置AUTO_ID_CACHE=1后，id自增异常
|
username: TiDBer_hxqv9az1
To improve efficiency, please provide the following information. A clear problem description can lead to a quicker resolution:
[Overview] Sysbench 1.20 mixed read-write stress test. After preparing the data, max(id) is approximately 1.5 times the count of rows. After inserting 2688 rows of data, the id of the 2689th row jumps by 1313, causing a huge id gap.
[Application Framework and Development Adaptation Business Logic]
sysbench 1.20
[Background] Operations performed
vim oltp_common.lua modified the create_table function to add AUTO_ID_CACHE=1
time sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=xxx.xxx.16.65 --mysql-port=6000 --mysql-user=root --mysql-password=‘xxx’ --mysql-db=sbtest --table_size=1000000 --tables=10 --threads=10 --time=300 prepare
[Phenomenon] Business and database phenomenon
Default table creation SQL, prepare data as follows:
CREATE TABLE sbtest4 (
id int(11) NOT NULL AUTO_INCREMENT,
k int(11) NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ‘’,
pad char(60) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY k_4 (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1221704
select max(id), min(id), count(1) from sbtest.sbtest4;
±--------±--------±---------+
| max(id) | min(id) | count(1) |
±--------±--------±---------+
| 1003089 | 1 | 1000000 |
±--------±--------±---------+
After setting AUTO_ID_CACHE=1 at the table level, the table creation SQL and prepare data are as follows:
CREATE TABLE sbtest2.sbtest4 (
id int(11) NOT NULL AUTO_INCREMENT,
k int(11) NOT NULL DEFAULT ‘0’,
c char(120) NOT NULL DEFAULT ‘’,
pad char(60) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY k_4 (k)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1486753 /*T![auto_id_cache] AUTO_ID_CACHE=1 */
select max(id), min(id), count(1) from sbtest2.sbtest4;
±--------±--------±---------+
| max(id) | min(id) | count(1) |
±--------±--------±---------+
| 1486752 | 1 | 1000000 |
±--------±--------±---------+
[Problem] Current issue encountered
AUTO_ID_CACHE=1 compatible with MySQL auto-increment, id has a huge gap.
[Business Impact]
Data exceeding 2688 rows does not meet the requirement of continuous auto-increment id.
[TiDB Version]
7.5.1
[Attachments] Relevant logs and monitoring
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: