The Evolution of OceanBase Auto-increment Columns and Best Practices

Posted on In Views: Word count in article: 4.2k Reading time ≈ 15 mins.
This article traces the evolution of OceanBase auto-increment columns from 4.0.0 to 4.2.3, compares the principles and jump behavior of the ORDER and NOORDER modes, summarizes best practices for bigint/int selection, mode switching, and cache size tuning, and introduces the Sequence extension in MySQL mode.

If, when creating a table, you need a numeric column whose values are unique and increasing, that is an auto-increment column.

The column type of an auto-increment column must be defined as AUTO_INCREMENT.

Outline of This Article

  • The evolutionary history of auto-increment columns in OceanBase
  • Best practices for using OceanBase auto-increment columns
  • Appendix
    • The mystery of auto-increment jumps (Rethinking Auto Increment)
    • The extension in MySQL mode — Sequence

We recommend reading the parts that interest you.

You’re also welcome to leave comments to critique and correct the content of this article.

The Evolutionary History of Auto-increment Columns in OceanBase

Version 4.0.0

In OceanBase’s MySQL mode, auto-increment columns support two different auto-increment modes. You can control the default mode via the tenant-level configuration item default_auto_increment_mode, or specify auto_increment_mode when creating a table. The default is order.

  • ORDER: Based on a centralized cache for the auto-increment column. The auto-increment column is globally increasing, providing better compatibility with MySQL behavior.

Diagram of ORDER mode

  • NOORDER: A distributed cache for the auto-increment column. It only guarantees global uniqueness and gives partitioned tables better performance (it only guarantees increment within a partition, not global increment).

Diagram of NOORDER mode

Version 4.2.2

INTEGER column type growth supports the Online approach:

For a primary key column / partition key / index column / a column that a generated column depends on / a column with a Check constraint, if the column type is integer, when the column type is modified to an integer type with a larger value range (e.g., INT -> BIGINT), in V4.2.1 this was implemented via dual-table dual-write Offline DDL, and the conversion process would take a table lock, blocking reads and writes.

But starting from V4.2.2, the Offline DDL was improved to Online DDL, so growing an integer column type no longer affects business writes.

Version 4.2.3 and Above

The auto-increment starting value can be reduced

When reducing the value of a table’s auto-increment field, note the following:
If the table already contains data and the maximum value in the auto-increment column is not less than the newly specified AUTO_INCREMENT value, the new AUTO_INCREMENT value will be automatically adjusted to the next value after the current maximum value in the auto-increment column.

For example, if the current maximum value of the auto-increment column is 5 and the current AUTO_INCREMENT value is 8, then setting AUTO_INCREMENT to any value between 0 and 6 will, after the statement executes successfully, actually adjust the AUTO_INCREMENT value to 6. This is compatible with native MySQL behavior.

The auto-increment cache size can be set at the table level

In versions before 4.2.3, the cache size of all tables used the value set by auto_increment_cache_size as the number of auto-increment values cached per node.

To flexibly control different caching strategies for different tables, a table-level option auto_increment_cache_size was added, allowing you to specify a table’s auto-increment cache size when creating or altering the table.

Scenario NOORDER mode (best performance) ORDER mode (maximum compatibility)
Multiple machines and partitions generating auto-increment values Different machines each cache their own auto-increment ranges, so the order in which data is inserted across the table will jump. But the overall auto-increment values of the table are not “wasted” due to jumps. All nodes request auto-increment values from the leader, so there are no jumps, but performance is worse than noorder.
Explicitly specifying auto-increment values (insert, insert on duplicate, replace) If you explicitly insert a specified value into the auto-increment column, the insert refreshes each node’s cached auto-increment range to ensure subsequent generated values are never smaller than that value. There can be auto-increment jumps and “waste” caused by the cache refresh. No jumps.
insert on duplicate / replace into scenarios without specifying the auto-increment column Early versions needed to refresh the global cache. The latest versions of all branches no longer need to refresh. No special jumps occur in this scenario. Early versions needed to refresh the global cache. The latest versions of all branches no longer need to refresh. No special jumps occur in this scenario.
Machine restart / crash After a machine restarts or crashes, the remaining cached value range from before the crash cannot be reused and must be re-fetched. There can be auto-increment jumps and “waste” caused by the machine restart/crash. The cache range is maintained on the leader node. When the leader node restarts/crashes, the unused auto-increment values in that range will not continue to be used, causing jumps and “waste.” Note that jumps here only occur on the leader node; the other follower nodes, which do not store the cache, will not affect the continuity of auto-increment value generation even if they crash.
Active leader switch (e.g., scaling up/down, upgrading OBServer) In older versions, each leader switch discarded the current cache range and re-fetched it, causing auto-increment jumps and “waste.” Starting from 4.2.3, this was optimized so that a normal leader switch does not cause jumps, but note: a leader switch affects the server’s availability, and right after the switch some insert requests may need to retry, which can cause a small range of data jumps. In older versions, a leader switch cleared the original leader’s cache range, causing jumps and “waste.” Starting from 4.2.3, this was optimized so that a normal leader switch does not cause jumps, but note: a leader switch affects the server’s availability, and right after the switch some insert requests may need to retry, which can cause a small range of data jumps.

Best Practices for Using OceanBase Auto-increment Columns

When should you set the auto-increment column’s data type to bigint?

  • The business itself grows quickly and retains data for a long period.
  • The customer doesn’t care whether bigint or int is used when creating the table.
  • Leaders are spread out, so the probability of machine leader switches increases (crashes, random load balancing, etc.), increasing the probability of jumps.
  • In noorder mode, you need to explicitly specify auto-increment values.

When is it acceptable to keep the auto-increment column’s data type as int?

  • The business’s data volume is far below the int limit.
  • Migrating from MySQL, where the customer insists on using the int type, otherwise the application has compatibility issues.
  • Standalone scenarios with very few leader switches.
  • You have decent monitoring and operations capabilities and can take action when the number of auto-increment values approaches the limit—such as rebuilding the table and re-importing data, or changing int to bigint (online since 4.2.2).

When can you change the auto-increment column to noorder?

  • When the user has no need for table-level ordering of the auto-increment column and wants to optimize the performance of high-concurrency operations, you can change order to noorder.
  • When the user does need table-level ordering of the auto-increment column but all leaders are on a single OBServer, and wants to optimize the performance of high-concurrency operations, you can change order to noorder.

When can you reduce the auto-increment cache?

  • The jump problem is fairly prominent.
  • Business traffic is very low.
  • Performance is not a sensitive concern.
  • Performance requirements are somewhat high, but it’s a standalone mode with the leader concentrated on a single node.

Auto-increment Column Configuration

GLOBAL system variable Meaning Default
auto_increment_cache_size Sets the number of cached auto-increment values Defaults to 1M since 4.0
auto_increment_increment Sets the auto-increment step Defaults to 1; also supports session-level setting
auto_increment_offset Determines the starting value of the auto-increment column Defaults to 1; also supports session-level setting
Tenant-level configuration item Meaning Default
default_auto_increment_mode Sets the default auto-increment mode. order: auto-increment data stays continuous and increasing; noorder: only guarantees the auto-increment value is unique Before 4.0, only noorder was supported; in 4.0 and later, this configuration item was added and order mode was supported, with order also being the default.
Table option Meaning Default
AUTO_INCREMENT_MODE Whether the auto-increment column is in order or noorder mode. When unspecified, takes the value set by the default_auto_increment_mode configuration item.
auto_increment_cache_size Controls the number of auto-increment values cached per memory request. When unspecified, takes the value set by the auto_increment_cache_size system variable.
SQL MODE Meaning Default
NO_AUTO_VALUE_ON_ZERO When this SQL MODE is specified, inserting 0 into the auto-increment column sets it to 0 rather than taking the next auto-increment value Not a default SQL MODE

Appendix

The Mystery of Auto-increment Jumps (Rethinking Auto Increment)

OceanBase’s auto-increment columns in MySQL mode are designed to be as compatible with MySQL as possible, providing users with the following characteristics:

  • After you actively insert a value i into an auto-increment column, all subsequently auto-generated values on this table must be greater than i.
  • The values auto-generated on each partition are always monotonically increasing.

When an auto-increment column from a standalone database (such as MySQL) is ported directly into a distributed database (such as OceanBase), you will observe “jumps” during use.

The following content describes the principle behind OceanBase auto-increment jumps. Interested readers may read it selectively.

Content from: OceanBase official documentation “Auto-increment Column Jumps”.

In MySQL, the auto-increment column is a column attribute of a database table that automatically generates a unique, increasing value used to identify the row uniquely. As a distributed database, OceanBase typically distributes its database tables across multiple different machines. While being as compatible with MySQL as possible, it must also guarantee the performance of generating auto-increment values in a distributed, multi-machine scenario, which leads to the jump problem during auto-increment value generation.

In OceanBase, auto-increment columns support two auto-increment modes—NOORDER mode and ORDER mode—with ORDER mode as the default. Where:

  • ORDER mode: an auto-increment column based on a centralized cache. Once set to this mode, the auto-increment column’s values increase globally.
  • NOORDER mode: an auto-increment column based on a distributed cache. Once set to this mode, only global uniqueness of the auto-increment column’s values is guaranteed.

Below, we describe how auto-increment values jump during generation in each of these two modes.

NOORDER Mode

Tables created in OceanBase V4.x by specifying AUTO_INCREMENT_MODE = 'NOORDER', as well as all tables with auto-increment columns created in versions below V4.0.0 (exclusive), are NOORDER-mode auto-increment tables.

The internal principle of a NOORDER-mode auto-increment column is shown in the figure below.

Internal principle of NOORDER mode

As the figure shows, the data structure of a NOORDER-mode auto-increment column has two parts:

  • Internal table: responsible for persisting the position of auto-increment values already used.
  • Cache: a range of auto-increment values recorded in the internal structure, obtained by requesting from the internal table.

Each OBServer node in a NOORDER-mode auto-increment column remains independent; each node can autonomously fetch an auto-increment range from the internal table and record it in the machine’s cache, thereby accelerating auto-increment value generation. Below, we use several typical scenarios as examples to explain why auto-increment values jump in NOORDER mode.

Scenario 1: Multiple machines and partitions generating auto-increment values

Assume auto_increment_cache_size is 100. When the OBServer nodes OBServer1, OBServer2, and OBServer3 where the partitioned table resides receive insert into values (null) requests in the following order, their internal processing logic is as follows:

  1. OBServer1 finds it has no cache, requests an auto-increment range [1,100] from the internal table, and generates an auto-increment value of 1.
  2. OBServer2 finds it has no cache, requests an auto-increment range [101,200] from the internal table, and generates an auto-increment value of 101.
  3. OBServer3 finds it has no cache, requests an auto-increment range [201,300] from the internal table, and generates an auto-increment value of 201.
  4. OBServer1 uses the cache [2,100] to generate the auto-increment value 2.
  5. OBServer2 uses the cache [102,200] to generate the auto-increment value 102.

……

Thus, the order in which data is inserted into the table is 1, 101, 201, 2, 102, .... As you can see, the auto-increment values keep jumping.

Scenario 2: Inserting a specified maximum value via an INSERT statement

In MySQL, if you explicitly insert a specified value into an auto-increment table, subsequently generated auto-increment values will not be smaller than that value.

In OceanBase’s distributed scenario, when you insert a specified value that is larger than all other values in the auto-increment table (i.e., the maximum value), not only must the OBServer node itself know that a maximum value was just inserted, it must also synchronize this to the other OBServer nodes and the internal table. This synchronization is very time-consuming. To avoid performing synchronization every time a maximum value is specified, the system discards the current cache when a maximum value is inserted, so no further synchronization is needed from the current value up to the next cached value.

For example, when OBServer1, OBServer2, and OBServer3—where the partitioned table resides—receive requests explicitly specifying an increasing sequence (1, 2, 3, ...) in the following order, and assuming all these machines hold caches:

  1. OBServer1 receives the value 1, discards the cache [1,100], re-fetches a new cache range [301,400] from the internal table, and synchronizes 101 as a sync value to the internal table and the other OBServer nodes.
  2. OBServer2 receives the value 2, finds it is smaller than the values in its current cache range [101,200], and does nothing.
  3. OBServer3 receives the value 3, finds it is smaller than the values in its current cache range [201,300], and does nothing.
  4. OBServer1 receives the value 4, finds it is smaller than the values in its current cache range [301,400], and does nothing. …

Thus, if after inserting some values you continue using the auto-increment column to generate a sequence, auto-increment value jumps occur. For example, OBServer1’s first range [1,100] was never used and it jumped straight to 301.

Besides multi-machine environments, jumps can also occur in a single-machine environment when inserting a specified maximum value. An example follows:

  1. Create a table t1 with an auto-increment column.
1
obclient> CREATE TABLE t1 (c1 int not null auto_increment) AUTO_INCREMENT_MODE='NOORDER';

At the same time, auto_increment_cache_size is 100.

  1. Insert data into the table multiple times.
1
obclient> INSERT INTO t1 VALUES(null);
1
obclient> INSERT INTO t1 VALUES(3);
1
obclient> INSERT INTO t1 VALUES(null);
  1. After the inserts succeed, view the data in the table.
1
obclient> SELECT * FROM t1;

The query result is as follows:

1
2
3
4
5
6
7
+-----+
| c1  |
+-----+
|   1 |
|   3 |
| 101 |
+-----+

Based on the query result, the auto-increment column jumped from 3 to 101.

Scenario 3: Machine restart or crash

The auto-increment cache is an in-memory structure. If an OBServer node’s machine restarts or crashes, the unused cache range on that machine is not written back to the internal table, which causes that unused portion of the range to never be used again. For example, assume OBServer1’s initial auto-increment cache range is [1,100] and it has already generated the auto-increment values 1 and 2. If OBServer1 then crashes, after the restart the machine’s cache range becomes a new range [101,200], and the next auto-increment value is 101, so the final order of auto-increment values is 1, 2, 101, ...—a jump has occurred.

ORDER Mode

To avoid the auto-increment jump problem in the fairly common scenarios mentioned in NOORDER mode—multiple machines and partitions generating auto-increment values and inserting a specified maximum value via an INSERT statement—OceanBase added the ORDER-mode auto-increment column starting in V4.x, and made it the default mode after creating a table, providing better compatibility with MySQL.

The internal principle of an ORDER-mode auto-increment column is shown in the figure below.

Internal principle of ORDER mode

Compared with NOORDER mode, an ORDER-mode auto-increment column selects the current cluster’s leader among all OBServer nodes as the leader of the auto-increment service. The other OBServer nodes, acting as followers, must send RPC requests to request auto-increment values from the leader OBServer node, while the leader OBServer node requests auto-increment ranges from the internal table to use as its auto-increment cache.

For example, again in the multi-machine, multi-partition scenario, assume the auto-increment column’s auto_increment_cache_size is 100. When the OBServer nodes OBServer1, OBServer2, and OBServer3 where the partitioned table resides receive insert into values (null) requests in the following order, their internal processing logic is as follows:

  1. OBServer1 finds it is not the leader and sends an RPC request to OBServer2. OBServer2 requests an auto-increment range [1,100] from the internal table and returns an auto-increment value of 1 to OBServer1.
  2. OBServer2 finds it is the leader, and since it has the cache range [2,100], it directly generates an auto-increment value of 2.
  3. OBServer3 finds it is not the leader and sends an RPC request to OBServer2. OBServer2 finds it has the cache range [3,100] and returns an auto-increment value of 3 to OBServer3.

……

As you can see, in ORDER mode, because all OBServer nodes request auto-increment values from the leader, in most cases—just like in the single-machine scenario—the system can always generate a continuous sequence of auto-increment values. However, in high-concurrency multi-machine scenarios, ORDER mode performs worse than NOORDER mode.

For an ORDER-mode auto-increment column, although it has solved the auto-increment jump problem in scenarios like multiple machines and partitions generating auto-increment values and inserting a specified maximum value via an INSERT statement, jumps can still occur when the leader OBServer node’s machine restarts or crashes, or when a leader switch happens.

Scenario 1: Machine restart or crash

In ORDER mode, the leader OBServer node stores the in-memory cache range. When the leader OBServer node’s machine restarts or crashes, the unused auto-increment values in that range will not continue to be used; instead, a new cache range is used, causing auto-increment value jumps.

Note

In this scenario, the auto-increment jump problem only occurs when the leader OBServer node restarts or crashes. The other follower OBServer nodes, which do not store the cache, will not affect the continuity of auto-increment value generation even if they crash.

Scenario 2: Leader switch

Assume OBServer2’s initial auto-increment cache range is [1,100] and it has already generated the auto-increment values 1 and 2. When a leader switch occurs within the cluster, by the normal processing logic:

  1. The leader switches to OBServer1, which requests a new auto-increment range [101,200] from the internal table and continues generating the auto-increment values 101 and 102.
  2. After OBServer2’s machine restarts successfully, the leader switches back to OBServer2, which continues using its previous cache range [3,100] to generate the auto-increment values 3 and 4.

As you can see, the auto-increment values went from 101 to 3, a non-increasing problem.

To avoid this non-increasing problem caused by switching the leader back and forth, OceanBase clears the cache range on the original leader OBServer node when a leader switch occurs, which causes auto-increment value jumps.

The Extension in MySQL Mode — Sequence

Definition

In OceanBase, a Sequence is a unique—and usually increasing—numeric value generated by the database according to certain rules. It is typically used to generate unique identifiers.

Reason for Introduction

OceanBase has many users whose business originally ran on DB2 / Oracle but who later plan to adopt the MySQL technology path, requiring migration from DB2 / Oracle to OceanBase MySQL-mode tenants.

To reduce the complexity of reworking business that previously made heavy use of sequences in DB2 / Oracle, OceanBase added Oracle-behavior-compatible sequence functionality in MySQL mode.

See the Create and Manage Sequences chapter in OceanBase’s official documentation. The syntax stays compatible with Oracle and is not repeated here.

Applicable Scenarios

  1. Scenarios migrating from DB2 / Oracle to OceanBase MySQL-mode tenants.
  2. Scenarios where the auto-increment column’s binding to a table cannot meet business requirements. A sequence is not bound to a table; it can be created independently and used across tables.
  3. Scenarios where the lack of CYCLE capability in auto-increment columns—where they stop working after reaching MAXVALUE—cannot meet business requirements. Sequences support cyclic sequences and have CYCLE capability.

FAQ

What are the similarities and differences between a sequence and an auto-increment column?

  1. An auto-increment column is bound to a table. A sequence is not bound to a table; it can be created independently and used across tables.
  2. An auto-increment column has no CYCLE capability. A sequence supports cyclic sequences and has CYCLE capability.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- Create a table with an auto-increment column id; the auto-increment column is tightly bound to the table
obclient [test]> CREATE TABLE t1(id bigint not null auto_increment primary key, name varchar(50));
Query OK, 0 rows affected (0.489 sec)

obclient [test]> INSERT INTO t1(name) VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.036 sec)

obclient [test]> SELECT * FROM t1;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.021 sec)


-- Create a sequence with start value 1, min value 1, max value 5, step 2, and non-cyclic values
obclient [test]> CREATE SEQUENCE seq1 START WITH 1 MINVALUE 1 MAXVALUE 5 INCREMENT BY 2 NOCYCLE;
Query OK, 0 rows affected (0.073 sec)

obclient [test]> SELECT seq1.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       1 |
+---------+
1 row in set (0.012 sec)

obclient [test]> SELECT seq1.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       3 |
+---------+
1 row in set (0.004 sec)

obclient [test]> SELECT seq1.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       5 |
+---------+
1 row in set (0.004 sec)

-- With NOCYCLE set, no larger sequence value can be generated after reaching MAXVALUE
obclient [test]> SELECT seq1.nextval FROM DUAL;
ERROR 4332 (HY000): sequence exceeds MAXVALUE and cannot be instantiated


-- Create another sequence with start value 1, min value 1, max value 5, step 2, and cyclic values (2 auto-increment values pre-allocated in memory)
obclient [test]> CREATE SEQUENCE seq7 START WITH 1 MINVALUE 1 MAXVALUE 5 INCREMENT BY 2 CYCLE CACHE 2;
Query OK, 0 rows affected (0.095 sec)

obclient [test]> SELECT seq7.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       1 |
+---------+
1 row in set (0.009 sec)

obclient [test]> SELECT seq7.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       3 |
+---------+
1 row in set (0.005 sec)

obclient [test]> SELECT seq7.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       5 |
+---------+
1 row in set (0.005 sec)

obclient [test]> SELECT seq7.nextval FROM DUAL;
+---------+
| nextval |
+---------+
|       1 |
+---------+
1 row in set (0.001 sec)


-- Besides being usable in a top-level SELECT, a sequence can also be used in INSERT and UPDATE

obclient [test]> create table t2(c1 int);
Query OK, 0 rows affected (0.192 sec)

obclient [test]> insert into t2 values(seq7.nextval);
Query OK, 1 row affected (0.009 sec)

obclient [test]> select * from t2;
+------+
| c1   |
+------+
|    3 |
+------+
1 row in set (0.001 sec)

obclient [test]> update t2 set c1 = seq7.nextval;
Query OK, 1 row affected (0.010 sec)
Rows matched: 1  Changed: 1  Warnings: 0

obclient [test]> select * from t2;
+------+
| c1   |
+------+
|    5 |
+------+
1 row in set (0.001 sec)

Note:

There is one more difference between sequences and auto-increment columns

  • When creating a sequence, the default is the NOORDER attribute (for compatibility with Oracle behavior).
  • When creating an auto-increment column, the default is the ORDER attribute (for compatibility with MySQL behavior).

When creating a sequence, if you set the ORDER attribute, then to guarantee global ordering, every NEXTVALUE operation must go to the central node to update a specific internal table, which can cause heavy lock contention under high concurrency. If you don’t require sequence values to increase—only to be unique—we recommend setting the sequence’s attribute to NOORDER.

At the same time, when performance requirements are high, you should also pay attention to the CACHE / NOCACHE attribute.

  • NOCACHE: means the OBServer does not cache auto-increment values. In this mode, every NEXTVAL call triggers an internal-table SELECT and UPDATE, which affects database performance.
  • CACHE: specifies the number of auto-increment values cached in each OBServer’s memory; the default value is 20.

Note:

When creating a sequence, because the default CACHE value is too small, you need to declare it manually. With a single-machine TPS of 100, we recommend setting the CACHE SIZE to 360000.