Inconsistent Performance of Auto-Increment Field Data Insertion

Translated
1

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

Original topic: 自增字段写入数据表现不一致的情况

| username: ShawnYan

【TiDB Usage Environment】Production, Testing, Research
【TiDB Version】tidb v6.1
【Problem Encountered】
The performance of setting an auto-increment field as a primary key and adding an index is inconsistent, and it is also inconsistent with MySQL.
What is the reason for this difference in results? Is it controlled by parameters or is it a bug?

【Reproduction Path】What operations were performed to encounter the problem

drop table t6,t7;

create table t6 (
id int(1) not null auto_increment,
name varchar(64),
primary key(id)
) engine=innodb;

create table t7 (
id int(1) not null auto_increment,
name varchar(64),
key idx_id(`id`)
) engine=innodb;

insert into t6 values(1,'chen'),(2147483646,'wu');
insert into t7 values(1,'chen'),(2147483646,'wu');

insert into t6 (`name`) values('a47');
insert into t7 (`name`) values('a47');

【Problem Phenomenon and Impact】

tidb> insert into t6 (`name`) values('a47');
Query OK, 1 row affected (0.01 sec)

tidb> insert into t7 (`name`) values('a47');
ERROR 1690 (22003): constant 2147483649 overflows int

【Attachments】

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: wakaka | Original post link

Isn’t this exceeding the maximum value of int? Is there any problem?

3
| username: wakaka | Original post link

Auto-increment values are sharded across different TiDB nodes. The t6 table is allocated to one node, and the maximum value on that node has not exceeded the int range. The t7 table has exceeded it.

4
| username: Z六月星星 | Original post link

TiDB can ensure the monotonicity of auto-increment values, but it cannot guarantee their continuity.

5
| username: Z六月星星 | Original post link
6
| username: Tank001 | Original post link

Tried it, no issues with MySQL.
But TiDB does have problems, didn’t notice it before. :smiley:

7
| username: OnTheRoad | Original post link

I made a simple diagram to illustrate the implementation principle of AUTO_INCREMENT in TiDB. This is roughly what it means.

auto_increment
auto_increment1011×465 29.4 KB

8
| username: ShawnYan | Original post link

This case only uses one TiDB server, and the SQL is executed sequentially in a CLI.

9
| username: ShawnYan | Original post link

The definition is that if an insert operation is performed on another server, the order of AUTO_INCREMENT values may jump significantly because each server has its own cached AUTO_INCREMENT values.

However, this case is executed on a single TiDB server.

10
| username: OnTheRoad | Original post link

The official documentation does not explicitly state that AUTO_ID_CACHE is only requested during insertion, right?

11
| username: ShawnYan | Original post link

This is used to avoid conflicts when multiple TiDB servers are writing. It should not be involved in this case, and the auto-increment step is also 1.

tidb> show variables like 'auto_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | 1     |
+--------------------------+-------+
4 rows in set (0.00 sec)
12
| username: db_user | Original post link

The guess might be that the absence of a primary key causes the issue. It feels like with a primary key, it’s a set of key-value pairs with a step size of one. Without a primary key, it’s composed of two sets of key-value pairs, possibly calculated twice, resulting in a step size of 2.

create table t8 (a int(1) primary key, id int(1) not null auto_increment, name varchar(64), key idx_id(`id`) ) engine=innodb;
insert into t8 (a,`name`) values(1,'a47');
insert into t8 (a,`name`) values(2,'a47');
insert into t8 (a,`name`) values(5,'a47');

Try it this way and see.

13
| username: wuxiangdong | Original post link

Speculation:
For non-clustered tables, each time an auto-increment value is fetched, it is fetched in pairs. The first value is assigned to the id, and the second value is assigned to the hidden column.

14
| username: wuxiangdong | Original post link

In this way, the performance of t6 and t7 will be the same.

15
| username: ShawnYan | Original post link

Yes and no, the performance of t6.t7 will be different from MySQL.

16
| username: ShawnYan | Original post link

The magical performance is here, the auto-increment in TiDB didn’t change.

test case:

drop table t8;

create table t8 (
a int(1) primary key, -- diff
id int(1) not null auto_increment,
name varchar(64),
key idx_id(`id`)
) engine=innodb;

insert into t8 values(1,1,'chen'),(2147483646,2147483646,'wu');

show create table t8\G

insert into t8 (`a`,`name`) values(2147483647,'a47');

show create table t8\G

mariadb

AUTO_INCREMENT increases by 1

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)

tidb

AUTO_INCREMENT doesn’t change?

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)
17
| username: wuxiangdong | Original post link

tidb, non-restricted tables have a hidden column _tidb_rowid, while MySQL restricted tables have a hidden column _rowid.

18
| username: db_user | Original post link

Isn’t the result in TiDB consistent with that in MariaDB? The ID is an auto-increment column. I feel it is related to the number of key-value pairs, which affects the number of auto-increment calculations.

19
| username: ShawnYan | Original post link

Inconsistent, looking at the table definition, above is AUTO_INCREMENT=47,48, below is 47, 47

20
| username: db_user | Original post link

This is where TiDB and MySQL differ. TiDB records the current maximum value, while MySQL records the next value. You can check your t6 above; the primary key with auto-increment behaves the same way.