Avoid getting auto id for each statement

[tiancaiamao]

Development Task

On the release-4.0 branch, add a print here

tidb/meta/autoid/autoid.go

Line 469 in 98820cf

alloc.mu.Lock()

fmt.Println("allocating. .. . . .")

mysql> create table t (id int key auto_increment);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t value ();
Query OK, 1 row affected (0.01 sec)

mysql> insert into t value ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t value ();
Query OK, 1 row affected (0.00 sec)

Each time, there is a log output:

allocating. .. . . . 
allocating. .. . . . 
allocating. .. . . . 

It means that TiDB is not batch allocating the auto-id, it may have some impact on the performance.

On the master branch:

mysql> create table t (id int key auto_increment);                                                                                                                                                                                                           
Query OK, 0 rows affected (0.01 sec)    

mysql> trace insert into t values ();
+---------------------------------------------------------+-----------------+------------+
| operation                                               | startTS         | duration   |
+---------------------------------------------------------+-----------------+------------+
| trace                                                   | 15:25:47.755482 | 1.658495ms |
|   └─session.Execute                                     | 15:25:47.755508 | 1.606023ms |
|     ├─session.ParseSQL                                  | 15:25:47.755519 | 25.546µs   |
|     └─session.ExecuteStmt                               | 15:25:47.755766 | 1.334715ms |
|       ├─executor.Compile                                | 15:25:47.755813 | 169.143µs  |
|       └─session.runStmt                                 | 15:25:47.756015 | 1.033428ms |
|         ├─executor.handleNoDelayExecutor                | 15:25:47.756051 | 222.45µs   |
|         │ └─*executor.InsertExec.Next                   | 15:25:47.756058 | 184.892µs  |
|         │   ├─table.AllocBatchAutoIncrementValue        | 15:25:47.756069 | 27.934µs   |
|         │   ├─autoid.Alloc                              | 15:25:47.756073 | 5.812µs    |
|         │   └─table.AddRecord                           | 15:25:47.756155 | 67.728µs   |
|         └─session.CommitTxn                             | 15:25:47.756296 | 698.142µs  |
|           └─session.doCommitWitRetry                    | 15:25:47.756301 | 668.702µs  |
|             └─tikvTxn.Commit                            | 15:25:47.756311 | 600.696µs  |
|               ├─twoPhaseCommitter.prewriteMutations     | 15:25:47.756331 | 159.735µs  |
|               │ └─regionRequest.SendReqCtx              | 15:25:47.756356 | 104.369µs  |
|               ├─tikvStore.getTimestampWithRetry         | 15:25:47.756514 | 8.061µs    |
|               └─twoPhaseCommitter.commitMutations       | 15:25:47.756549 | 329.029µs  |
|                 └─regionRequest.SendReqCtx              | 15:25:47.756563 | 231.084µs  |
+---------------------------------------------------------+-----------------+------------+
19 rows in set (0.01 sec)

It's also allocating every time. Both 4.0 and master have the problem.

See also #21617, #11158

[tiancaiamao]

Mislead by where I check the allocating code... run into Alloc() does not mean get the ID from TiKV.