Best Practices of Database SEQUENCE

Creating a Sequence

In GaussDB(DWS), the Global Transaction Manager (GTM) generates and maintains the global unique information about a transaction, such as the global transaction ID, transaction snapshot, and sequence. The following figure shows the process of creating a sequence in GaussDB(DWS).

Figure 1 Creating a sequence

The specific process is as follows:

1. The CN that receives the SQL command requests a UUID from the GTM.
2. The GTM returns a UUID.
3. The CN binds the obtained UUID to the sequenceName created by the user.
4. The CN delivers the binding relationship to other nodes, and other nodes create the sequence metadata table synchronously.
5. The CN sends the UUID and startID of the sequence to the GTM for permanent storage.

Therefore, sequence maintenance and request are actually completed on the GTM. When requesting nextval, each instance obtains a sequence value from the GTM using the sequence's UUID. The number of values requested correlates with the cache size. An instance will only request a new sequence value from the GTM once its cache is depleted. Thus, enlarging the sequence's cache minimizes the communication frequency between the CN/DN and the GTM.

Two Methods of Creating a Sequence

Method 1: Run the CREATE SEQUENCE statement to create a sequence and use nextval to invoke the sequence in the new table.

1
2
3
4
5

CREATE SEQUENCE seq_test increment by 1 minvalue 1 no maxvalue start with 1;
CREATE SEQUENCE

CREATE TABLE table_1(id int not null default nextval('seq_test'), name text);
CREATE TABLE

Method 2: If the serial type is used during table creation, a sequence is automatically created and the default value of the column is set to nextval.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE mytable(a int, b serial) distribute by hash(a);
NOTICE:  CREATE TABLE will create implicit sequence "mytable_b_seq" for serial column "mytable.b"
CREATE TABLE

 \d+ mytable
                                            Table "dbadmin.mytable"
 Column |  Type   |                      Modifiers                      | Storage | Stats target | Description
--------+---------+-----------------------------------------------------+---------+--------------+-------------
 a      | integer |                                                     | plain   |              |
 b      | integer | not null default nextval('mytable_b_seq'::regclass) | plain   |              |
Has OIDs: no
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
Options: orientation=row, compression=no

Common Usage of Sequences in Services

Sequences are commonly used to generate primary keys or unique columns during data import, a frequent practice in data migration scenarios. Different migration tools or service import scenarios use different import methods. Common import methods are classified into copy and insert. For sequences, the processing in the two scenarios is slightly different.

• Scenario 1: Insert pushdown

  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

  CREATE TABLE test1(a int, b serial) distribute by hash(a); NOTICE: CREATE TABLE will create implicit sequence "test1_b_seq" for serial column "test1.b" CREATE TABLE CREATE TABLE test2(a int) distribute by hash(a); CREATE TABLE EXPLAIN VERBOSE INSERT INTO test1(a) SELECT a FROM test2; QUERY PLAN ------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+-------------------------------------+--------+------------+----------+---------+--------- 1 | -> Streaming (type: GATHER) | 1 | | | 4 | 16.34 2 | -> Insert on dbadmin.test1 | 30 | | | 4 | 16.22 3 | -> Seq Scan on dbadmin.test2 | 30 | | 1MB | 4 | 14.21 RunTime Analyze Information ---------------------------------------------------- "dbadmin.test2" runtime: 9.586ms, sync stats Targetlist Information (identified by plan id) --------------------------------------------------------- 1 --Streaming (type: GATHER) Node/s: All datanodes 3 --Seq Scan on dbadmin.test2 Output: test2.a, nextval('test1_b_seq'::regclass) Distribute Key: test2.a ====== Query Summary ===== ------------------------------- System available mem: 1351680KB Query Max mem: 1351680KB Query estimated mem: 1024KB Parser runtime: 0.076 ms Planner runtime: 12.666 ms Unique SQL Id: 831364267 (26 rows)

  During an INSERT operation, nextval is executed on the DNs. This occurs whether nextval is called with its default value or invoked explicitly. The execution plan confirms that nextval operates at the sequence layer on the DNs. In this scenario, DNs obtain sequence values directly from the GTM and execute the request simultaneously, resulting in a relatively high level of efficiency.

  

Typical Optimization Scenarios Related to Sequences

Service scenarios:

Service scenario: In a service scenario, the CDM data synchronization tool is used to transfer data and import data from the source to the target GaussDB(DWS). Despite changing the CDM concurrency from 1 to 5, the synchronization rate remains unchanged, and there is a significant difference between the import rate and the expected value. Apart from data copying, all other services run smoothly without any performance or resource issues. Thus, it is likely that a bottleneck exists within the service. You are advised to review the job queue specifically for the COPY operation.

As shown in the preceding figure, five CDM jobs are executed concurrently. You can see five COPY statements in the active view. Check the waiting view based on query_id corresponding to the five COPY statements. Out of the five COPY operations, only one requests a sequence value from the GTM concurrently, while the rests wait for a lightweight lock. As a result, enabling five concurrent jobs does not substantially enhance performance compared to just running a single job.

Causes:

The serial type is used when the target table is created. By default, the cache of the created sequence is 1. As a result, when data is concurrently copied to the database, the CN frequently establishes connections with the GTM, and lightweight lock contention exists between multiple concurrent jobs, resulting in low data synchronization efficiency.

Solutions:

In this scenario, increase the cache value of the sequence to prevent bottlenecks caused by frequent GTM connection establishment. In this service scenario example, about 100,000 data records are synchronized each time. Based on service evaluation, change the cache value to 10,000. (In practice, set a proper cache value based on services to ensure quick access and avoid sequence number waste.)

In cluster versions 8.2.1.100 and later, you can use ALTER SEQUENCE to change the cache value.

GaussDB(DWS) clusters of version 8.2.1 or earlier do not allow for the modification of cache values through ALTER SEQUENCE. To change the cache value of an existing sequence, follow these steps (the mytable table is used as an example):

1. Remove the association between the current sequence and the target table.

   1 2	ALTER SEQUENCE mytable_b_seq owned by none; ALTER TABLE mytable alter b drop default;

2. Record the current sequence value as the start value of the new sequence.

   1	SELECT nextval('mytable_b_seq');

   Delete the sequence.

   1	DROP SEQUENCE mytable_b_seq;

3. Create a sequence and bind it to the target table. Replace xxx with the value of nextval obtained in the previous step.

   1 2 3

   CREATE SEQUENCE mytable_b_seq START with xxx cache 10000 owned by mytable.b; ALTER SEQUENCE mytable_b_seq owner to u1;--u1 is the owner of the mytable table. The owner does not need to run this statement. ALTER TABLE mytable alter b set default nextval('mytable_b_seq');