Experience online DDL

Adding columns and creating indexes are common schema changes as an application evolves. In traditional databases, such DDL operations may hold long table locks, forcing the business to stop reading and writing, so the changes can often only be scheduled during off-peak hours. Most common DDL operations in OceanBase Database are online DDL, which means users can continue to read from and write to the table while the operation runs. Operations such as adding a column at the end only modify metadata. Index creation requires data reorganization and takes time that depends on the data volume, but business read/write can continue while it runs.

In this topic, you will run common DDL operations, such as adding a column and creating an index, on a 1-million-row test table that carries continuous read/write traffic. You will check whether the traffic continues during DDL execution, and verify the new table structure and query results.

This topic covers the following steps:

• Prepare a test table for continuous read/write: Create a test tenant, import 1 million rows of sample data, and use Shell loops to simulate continuous read/write traffic.
• Experience online column addition: Add a column at the end while the workload is continuously reading and writing, and verify that the workload is not interrupted during DDL.
• Experience online index creation: Create an index while the workload is continuously reading and writing, and observe that the workload continues during data reorganization.
• Verify online execution and the change results: Confirm that the DDL ran online by comparing table_id before and after, and verify the new table structure and query results.

Prerequisites

Before you perform the operations described in this topic, ensure that the following conditions are met:

Introduction to Online DDL

OceanBase Database classifies DDL operations into online and offline categories:

• Online DDL: During an online DDL operation, users can still access the database, including for read and write operations. Such operations have little impact on the business, support concurrent access, and require fewer locks, which reduces downtime.
• Offline DDL: An offline DDL operation locks the affected tables and blocks user access to their data. This causes downtime while the operation runs, which affects normal queries and transaction processing.

The following table lists some common DDL operations and whether they are online (excerpted from the official MySQL mode support list):

How offline DDL works: Offline DDL rebuilds the table. It creates a hidden temporary table that is invisible to users, migrates the data from the original table into it, renames the tables, and then drops the original table. As a result, the table_id of the table changes after an offline DDL operation completes, and no DML operation is allowed while it runs.

How to tell whether a DDL operation is online: Query the table_id before and after the operation. If the value is unchanged, the operation is online; if it changes, the operation is offline:

SELECT DISTINCT(table_id) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'xxx';

Create a test tenant

1. Log in to the sys tenant of the cluster as the root user (the password is randomly generated during deployment. For information about how to obtain it, see the Prerequisites section above):

   obclient -h127.0.0.1 -uroot@sys -P2881 -p -A oceanbase
   

2. Create a 2C4G resource unit, a resource pool, and a tenant named ddl_tenant:

   obclient [oceanbase]> CREATE RESOURCE UNIT ddl_unit MAX_CPU=2, MIN_CPU=2, MEMORY_SIZE='4G';
   obclient [oceanbase]> CREATE RESOURCE POOL ddl_pool UNIT='ddl_unit', UNIT_NUM=1, ZONE_LIST=('zone1');
   obclient [oceanbase]> CREATE TENANT IF NOT EXISTS ddl_tenant CHARSET='utf8mb4', PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('ddl_pool') SET ob_tcp_invited_nodes='%';
   

   Tenant creation takes tens of seconds (about 6 seconds in the environment used for this topic). No output is displayed on the command line during this period, which is normal. Just wait for the command to return.

3. Log in to ddl_tenant. The root user of a newly created tenant has an empty default password, so just press Enter when prompted. All subsequent operations are performed in this tenant:

   obclient -h127.0.0.1 -uroot@ddl_tenant -P2881 -p -A test
   

Prepare test tables and sample data

1. Create a test table named ddl_tbl in the session of ddl_tenant:

   obclient [test]> CREATE TABLE ddl_tbl (
       ->     id BIGINT PRIMARY KEY,
       ->     user_name VARCHAR(32),
       ->     city VARCHAR(32),
       ->     amount INT
       -> );
   

2. On the machine where OceanBase is deployed, run the following command to generate 1 million rows of test data:

   seq 1 1000000 | awk '{printf "%d,user_%06d,city_%d,%d\n", $1, $1, $1%20, $1%1000}' > /tmp/ddl_sample.csv
   

   This command generates a CSV file containing the following fields: id (incremental number), user_name (username), city (circularly values from 20 cities), and amount (amount).

3. Check the data file:

   head -3 /tmp/ddl_sample.csv
   wc -l /tmp/ddl_sample.csv
   

   The first 3 rows of data returned by the head command are as follows:

   1,user_000001,city_1,1
   2,user_000002,city_2,2
   3,user_000003,city_3,3
   

   wc -l returning 1000000 indicates the correct number of data rows.

4. In the operating system terminal, run the following command to import the data into ddl_tbl. The --local-infile parameter enables client-side local file import, so no server-side file access path needs to be configured:

   obclient -h127.0.0.1 -uroot@ddl_tenant -P2881 --local-infile -A test -e "SET ob_query_timeout=3600000000; LOAD DATA LOCAL INFILE '/tmp/ddl_sample.csv' INTO TABLE ddl_tbl FIELDS TERMINATED BY ',';"
   

5. Return to the obclient session and verify the number of imported rows:

   obclient [test]> SELECT COUNT(*) FROM ddl_tbl;
   

   The result 1000000 matches the number of rows in the source data file, indicating that all data has been imported:

   +----------+
   | COUNT(*) |
   +----------+
   |  1000000 |
   +----------+
   1 row in set
   

Simulate continuous read/write business

The subsequent DDL experience requires a business load that continuously reads from and writes to the test table. This topic uses a Shell loop to simulate such a workload: each iteration writes one new row into ddl_tbl (INSERT) and then queries that row by primary key (SELECT).

1. Open a new operating system terminal (hereafter Terminal A) and run the following command to start the business simulation loop:

   i=1000001
   while true; do
       obclient -h127.0.0.1 -uroot@ddl_tenant -P2881 -A test -e \
         "INSERT INTO ddl_tbl(id, user_name, city, amount) VALUES($i, 'user_live', 'city_live', 500); SELECT id, user_name, amount FROM ddl_tbl WHERE id = $i;" \
         > /dev/null 2>&1 \
         && echo "$(date '+%H:%M:%S') Read and write succeeded id=$i" \
         || echo "$(date '+%H:%M:%S') Read or write failed id=$i"
       i=$((i+1))
       sleep 1
   done
   

   Command description:

   • obclient -e: Executes the statement and then exits. Each iteration writes data with an INSERT statement and reads it back with a primary key lookup. If both statements succeed, the loop prints "Read and write succeeded". If either of them fails, it prints "Read or write failed".
   • The query results are redirected to /dev/null. Success is determined solely by the command exit code, making the output more concise.
   • The loop starts writing from id=1000001, which does not conflict with the 1 million rows already imported.
   • To stop the business simulation, press Ctrl+C to end the loop.

   After the loop starts, Terminal A outputs one result line per second (the actual time and number may vary):

   10:30:01 Read and write succeeded id=1000001
   10:30:02 Read and write succeeded id=1000002
   10:30:03 Read and write succeeded id=1000003
   

2. Keep the loop running in Terminal A, then open another terminal (hereafter Terminal B) and log in to ddl_tenant. All subsequent DDL operations are executed in the obclient session of Terminal B:

   obclient -h127.0.0.1 -uroot@ddl_tenant -P2881 -p -A test
   

Experience online column addition

In this section, while the business loop on Terminal A is running continuously, a column is added to the end of ddl_tbl to verify that business read and write operations are not interrupted during DDL execution.

Procedure

1. In the session on Terminal B, query and record the table_id before DDL execution:

   obclient [test]> SELECT DISTINCT(table_id) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'ddl_tbl';
   

   A sample return result is as follows (table_id is an object number automatically assigned by the system. Record the actual returned value. This example uses 500002):

   +----------+
   | table_id |
   +----------+
   |   500002 |
   +----------+
   1 row in set
   

2. While confirming that the read and write loop on Terminal A is still running, execute the statement to add a column at the end in the session on Terminal B:

   obclient [test]> ALTER TABLE ddl_tbl ADD COLUMN remark VARCHAR(64) DEFAULT NULL;
   Query OK, 0 rows affected
   

3. Observe the output on Terminal A while the DDL runs. From the start of the column addition until the command returns, the read/write loop continuously prints "Read and write succeeded", and no iteration reports "Read or write failed", which indicates that business read and write operations were not interrupted.

4. Query the table_id again in the session on Terminal B and compare it with the value recorded in Step 1:

   obclient [test]> SELECT DISTINCT(table_id) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_name = 'ddl_tbl';
   

   The returned value is still 500002 (compare it with the value you recorded), the same as before the DDL ran. This indicates that the table was not rebuilt and that the column addition was performed online.

5. View the modified table structure:

   obclient [test]> DESCRIBE ddl_tbl;
   

   The returned result is as follows, with the new remark column appearing at the end of the table structure:

   +-----------+-------------+------+------+---------+-------+
   | Field     | Type        | Null | Key  | Default | Extra |
   +-----------+-------------+------+------+---------+-------+
   | id        | bigint(20)  | NO   | PRI  | NULL    |       |
   | user_name | varchar(32) | YES  |      | NULL    |       |
   | city      | varchar(32) | YES  |      | NULL    |       |
   | amount    | int(11)     | YES  |      | NULL    |       |
   | remark    | varchar(64) | YES  |      | NULL    |       |
   +-----------+-------------+------+------+---------+-------+
   5 rows in set
   

6. Query the new column for existing data. Adding a column at the end only modifies metadata, so existing rows return the column's default value:

   obclient [test]> SELECT id, remark FROM ddl_tbl WHERE id = 500000;
   

   +--------+--------+
   | id     | remark |
   +--------+--------+
   | 500000 | NULL   |
   +--------+--------+
   1 row in set
   

7. Verify that the new column takes effect immediately for new writes. Write a value to the remark column and query it:

   obclient [test]> INSERT INTO ddl_tbl(id, user_name, city, amount, remark) VALUES(2000001, 'user_check', 'city_1', 100, 'online ddl works');
   obclient [test]> SELECT id, user_name, remark FROM ddl_tbl WHERE id = 2000001;
   

   +---------+------------+------------------+
   | id      | user_name  | remark           |
   +---------+------------+------------------+
   | 2000001 | user_check | online ddl works |
   +---------+------------+------------------+
   1 row in set
   

Interpretation

Experience online index creation

This section goes on to create an index on the city column of ddl_tbl while the business loop keeps running. Unlike appending a column, creating an index requires data reorganization, which takes time that depends on the data volume. It is therefore a typical case for observing whether business operations can continue during data reorganization.

Procedure

1. Confirm that the read/write loop on Terminal A is running continuously. In the session on Terminal B, execute the following statement to create an index on the city column:

   obclient [test]> CREATE INDEX idx_ddl_city ON ddl_tbl(city);
   Query OK, 0 rows affected
   

2. Observe the output on Terminal A while the DDL runs. Index creation reorganizes 1 million existing rows into index data, and the time it takes depends on the data volume. From the start of creation until the command returns, the read/write loop continuously prints "Read and write succeeded", which indicates that business reads and writes are not interrupted.

   To confirm again that the operation runs online, query the table_id as described in the previous section. Before and after the index is created, the table_id of the table remains unchanged.

3. View the modified table definition. Adding \G at the end of the statement displays the query results vertically, row by row, which makes long table creation statements easier to read:

   obclient [test]> SHOW CREATE TABLE ddl_tbl\G
   

   The return result is as follows. The table attributes in the creation statement depend on your cluster configuration, so the actual output may differ. Focus on the newly added remark column and the idx_ddl_city index:

   *************************** 1. row ***************************
          Table: ddl_tbl
   Create Table: CREATE TABLE `ddl_tbl` (
     `id` bigint(20) NOT NULL,
     `user_name` varchar(32) DEFAULT NULL,
     `city` varchar(32) DEFAULT NULL,
     `amount` int(11) DEFAULT NULL,
     `remark` varchar(64) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_ddl_city` (`city`) BLOCK_SIZE 16384 LOCAL
   ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   1 row in set
   

4. Verify the query result. Count the number of rows with a specific value in the city column:

   obclient [test]> SELECT COUNT(*) FROM ddl_tbl WHERE city = 'city_5';
   

   The returned result is 50000, which matches the source data distribution (1 million rows evenly distributed across 20 city values, 50,000 rows each):

   +----------+
   | COUNT(*) |
   +----------+
   |    50000 |
   +----------+
   1 row in set
   

5. Press Ctrl+C on Terminal A to stop the business simulation loop, and return to the session on Terminal B to count the number of rows written during the loop:

   obclient [test]> SELECT COUNT(*) FROM ddl_tbl WHERE id > 1000000;
   

   The returned number of rows is the total number of rows written during the business simulation loop plus the row verified earlier with id=2000001. This number depends on how long the loop ran (in this experiment, the loop ran for about 100 seconds and wrote 100 rows, plus the verification row, for a total of 101 rows). All of these rows were written successfully while both the column addition and the index creation were running, which further confirms that business writes were not interrupted during the DDL operations.

Interpretation

What to do next

After you complete the online DDL experience in this topic, you can explore the following topics:

1. Review the Quick Start series: By now, you have completed the full experience, from deployment, high-concurrency OLTP, HTAP, AI scenarios, and multi-tenancy through high availability, horizontal scaling, data efficiency, data protection, and online schema changes. You can start the review from Deploy OceanBase.
2. Learn more about DDL management: For a complete list of online and offline DDL operations and how to tell them apart, see Online DDL and Offline DDL operations.
3. Learn more about index management: For operations such as viewing and dropping indexes, see View indexes.

References

• For a complete list of online and offline DDL operations and how to tell them apart, see Online DDL and Offline DDL operations.
• For operations that change a table structure, such as adding, modifying, and dropping columns, see Modify a table.
• For information about how to create various indexes, see Create an index.
• For the complete syntax of the CREATE INDEX statement, see CREATE INDEX.
• For the complete syntax of the ALTER TABLE statement, see ALTER TABLE.
• For information about how to view indexes, see View indexes.
• For usage of LOAD DATA LOCAL INFILE, see Import data by using the LOAD DATA statement.
• For field descriptions of the DBA_OB_TABLE_LOCATIONS view, see DBA_OB_TABLE_LOCATIONS.
• For usage of obclient command-line options, see the OBClient documentation.