Experience parallel import and high compression

Data write efficiency and storage cost are two core requirements for databases in large-data scenarios. Based on the LSM-Tree storage architecture, OceanBase Database provides parallel data loading through direct load, which bypasses the SQL layer and writes data directly to the underlying storage. It also encodes and compresses data during compaction, significantly reducing storage usage without affecting write performance. Based on a sample dataset of one million rows, this topic helps you experience first-hand how OceanBase Database improves write efficiency and optimizes storage cost.

This topic covers the following experiences:

• Prepare batch sample data: Generate one million rows of test data in CSV format.
• Experience parallel import: Use direct load with parallelism for bulk data ingestion and compare the elapsed time with that of a regular import.
• Verify import results: Check the number of imported rows to confirm data is fully ingested.
• Experience data encoding and compression: Create tables with different compression configurations and compare storage usage before and after compression.
• Verify query availability: Confirm that compressed data can be queried normally and the results are consistent.

Prerequisites

Before you proceed, ensure the following conditions are met:

Introduction to parallel import and high compression

Direct load

Normal data import goes through parsing, optimization, and execution at the SQL layer, writing data to the MemTable, which is then flushed to static data (SSTable) by background tasks. Direct load is an import execution mode that can be enabled by adding the DIRECT(...) hint to statements like LOAD DATA or INSERT INTO SELECT. Direct load bypasses the SQL layer and MemTable, writing data directly to the underlying storage (SSTable), resulting in a shorter execution path. It is suitable for the initial import of large tables, data migration of 10 GB to TB scales, and scenarios with limited CPU and memory.

When you enable direct load, you can specify the degree of parallelism with the parallel(N) hint. The database splits the import task into multiple parallel subtasks, which further improves loading throughput.

Data encoding and compression

OceanBase Database's LSM-Tree architecture ensures that data compression occurs only during compaction, without affecting data writes. When data is persisted to disk as SSTables, it is compressed at the microblock granularity in two levels:

In MySQL-compatible mode, encoding and compression behavior is controlled by the ROW_FORMAT and COMPRESSION clauses during table creation. The main values are as follows:

In other words, new tables have data encoding enabled and use the zstd_1.3.8 compression algorithm by default. This topic compares storage usage between the "no encoding, no compression" and the "default encoding and compression" configurations.

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; obtain it as described in the prerequisites above):

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

2. Create a resource unit, resource pool, and tenant named load_tenant with specifications of 2C4G:

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

   Tenant creation takes tens of seconds (about 6 seconds in our test environment). No output is displayed during this period, which is normal; just wait for the command to return.

3. Log in to load_tenant (the default password for the root user of a newly created tenant is empty, so just press Enter when prompted). All subsequent operations are performed in this tenant:

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

Prepare batch sample data

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

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

   This command generates a CSV file containing the following fields: id (an incremental number), user_name (username), city (a cyclic value from 20 cities), and amount (amount). The city has only 20 values, user_name has the same user_ prefix, and the data contains a large amount of duplicate and similar content, making it easy to observe the compression effect.

2. Check the data file:

   head -3 /tmp/load_sample.csv
   wc -l /tmp/load_sample.csv
   ls -lh /tmp/load_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 confirms that the number of rows is correct, and ls -lh shows that the file size is about 29 MB. Record this file size, because it is compared with the actual storage usage in the database later.

Experience parallel import

This section creates three test tables with the same structure but different configurations, and imports the same dataset into each of them by using a different method:

Procedure

1. Log in to load_tenant and create three test tables:

   obclient [test]> CREATE TABLE load_normal (
                       id BIGINT PRIMARY KEY,
                       user_name VARCHAR(32),
                       city VARCHAR(32),
                       amount INT
                    );
   obclient [test]> CREATE TABLE load_direct (
                       id BIGINT PRIMARY KEY,
                       user_name VARCHAR(32),
                       city VARCHAR(32),
                       amount INT
                    );
   obclient [test]> CREATE TABLE load_none (
                       id BIGINT PRIMARY KEY,
                       user_name VARCHAR(32),
                       city VARCHAR(32),
                       amount INT
                    ) ROW_FORMAT=REDUNDANT COMPRESSION='none';
   

   load_none disables both data encoding and compression by setting ROW_FORMAT=REDUNDANT COMPRESSION='none'. The other two tables use the default configurations (ROW_FORMAT=DYNAMIC and COMPRESSION='zstd_1.3.8').

2. Set the import file path. For security reasons, the secure_file_priv system variable can only be modified via a local Unix Socket connection:

   # The socket path depends on the deployment method. For OBD demo deployment, you can find it using the following command:
   find / -name sql.sock 2>/dev/null
   # Connect using the found socket path (example):
   obclient -S /path/to/sql.sock -uroot@load_tenant -p
   

   When prompted for the password, press Enter directly (the default password for the newly created tenant's root user is empty).

   obclient [(none)]> SET GLOBAL secure_file_priv = "/tmp";
   Query OK, 0 rows affected
   

   secure_file_priv restricts the file access paths for statements like LOAD DATA INFILE. This is a global variable, so changes take effect on new connections. In the following steps, the import statements executed via obclient -e will each establish a new connection automatically, requiring no additional action. After setting it, you can open a new connection and run SHOW VARIABLES LIKE 'secure_file_priv'; to confirm the setting. A return value of /tmp indicates successful configuration.

   Note

   For detailed information about secure_file_priv, see secure_file_priv.

3. Execute the following command in the operating system terminal to import data into load_normal by using the regular import method, and record the elapsed time:

   time obclient -h127.0.0.1 -uroot@load_tenant -P2881 -A test -e "SET ob_query_timeout=3600000000; LOAD DATA INFILE '/tmp/load_sample.csv' INTO TABLE load_normal FIELDS TERMINATED BY ',';"
   

   After the command finishes, the time command outputs three statistics: real (actual elapsed time), user (CPU time in user space), and sys (CPU time in kernel space). Record the real value as the import time of the regular import.

4. Execute the following command to import data into load_direct by using the direct load method, and record the elapsed time:

   time obclient -h127.0.0.1 -uroot@load_tenant -P2881 -A test -e "SET ob_query_timeout=3600000000; LOAD DATA /*+ direct(true,1024,'full') parallel(4) */ INFILE '/tmp/load_sample.csv' INTO TABLE load_direct FIELDS TERMINATED BY ',';"
   

   Key parameters in the hint:

   Hint parameter	Description
   direct(true,1024,'full')	Enables full direct load: data is sorted before it is written, up to 1024 erroneous rows are tolerated, and the full mode is used (suitable for the initial loading of empty tables).
   parallel(4)	Sets the import parallelism to 4.

5. Use direct load to import data into load_none:

   time obclient -h127.0.0.1 -uroot@load_tenant -P2881 -A test -e "SET ob_query_timeout=3600000000; LOAD DATA /*+ direct(true,1024,'full') parallel(4) */ INFILE '/tmp/load_sample.csv' INTO TABLE load_none FIELDS TERMINATED BY ',';"
   

   Notice

   • The default password for the root user of a new tenant is empty. The preceding command can be executed directly. If a password has been set, append -ppassword to the command (for example, -p'******'). Writing the password on the command line avoids interactive password entry from affecting the timing results.
   • The SET ob_query_timeout=3600000000 in the command temporarily adjusts the statement timeout for this session to 1 hour, preventing execution failure due to batch imports exceeding the default statement timeout (10 seconds). For details, see ob_query_timeout.

6. Return to the obclient session and verify the number of rows imported for the three tables:

   obclient [test]> SELECT COUNT(*) FROM load_normal;
   obclient [test]> SELECT COUNT(*) FROM load_direct;
   obclient [test]> SELECT COUNT(*) FROM load_none;
   

   All three statements return 1000000, which matches the number of rows in the source data file, indicating that all data has been imported:

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

7. Compare the real time recorded in step 3 with that recorded in step 4. In this example, the data volume is one million rows (about 29 MB), and the two import methods usually take a similar amount of time (about 3 seconds each in this environment), so the difference is not significant. This is normal: for a small data volume, the SQL-layer overhead of a regular import is negligible, and the advantage of direct load becomes apparent only for large-scale data of several GBs or more. To observe the difference, you can increase the data volume and compare again.

Interpretation

Experience data encoding and compression

After the import is complete, the table's encoding and compression configurations are applied during compaction to generate SSTables. This section manually triggers a round of major compaction, then compares the actual storage usage of three tables under different compression configurations, and verifies that compressed data can be queried normally.

Procedure

1. Log in to the sys tenant of the cluster as the root user and initiate a major compaction for load_tenant:

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

   obclient [oceanbase]> ALTER SYSTEM MAJOR FREEZE TENANT = load_tenant;
   Query OK, 0 rows affected
   

   Note

   Before initiating a major compaction, ensure the global major compaction switch is enabled (cluster-level parameter enable_major_freeze, enabled by default). For details on how to trigger a major compaction, see Manually trigger a major compaction.

2. Query the compaction status in the session of load_tenant:

   obclient [test]> SELECT STATUS FROM oceanbase.DBA_OB_MAJOR_COMPACTION;
   

   The status returns COMPACTING (compacting) or VERIFYING (verifying checksum) during compaction, and IDLE after compaction is complete:

   +------------+
   | STATUS     |
   +------------+
   | COMPACTING |
   +------------+
   1 row in set
   

   Repeat the preceding query until STATUS changes to IDLE before proceeding to the next step. A major compaction typically takes several minutes (about 6 minutes in this environment); you can query every 10–30 seconds during this period. If the compaction is already complete when queried, IDLE is displayed directly.

3. Query the actual storage usage of the three tables:

   obclient [test]> SELECT t.TABLE_NAME, r.DATA_SIZE, r.REQUIRED_SIZE
       -> FROM oceanbase.DBA_OB_TABLE_LOCATIONS t
       -> JOIN oceanbase.DBA_OB_TABLET_REPLICAS r ON t.TABLET_ID = r.TABLET_ID
       -> WHERE t.TABLE_NAME IN ('load_normal', 'load_direct', 'load_none')
       -> ORDER BY t.TABLE_NAME;
   

   A sample return result is as follows (the actual size depends on the data content, database version, and cluster status, so refer to the actual query result. Focus on the relative relationship among the three DATA_SIZE values):

   +-------------+-----------+---------------+
   | TABLE_NAME  | DATA_SIZE | REQUIRED_SIZE |
   +-------------+-----------+---------------+
   | load_direct |   4057224 |       8114448 |
   | load_none   |  63569253 |     127138506 |
   | load_normal |   4184897 |       6040515 |
   +-------------+-----------+---------------+
   3 rows in set
   

   The query results are interpreted as follows:

   • DATA_SIZE: The actual size of the data, that is, the amount of data stored after compression. load_none disables encoding and compression, so its DATA_SIZE is significantly larger than those of the other two tables. load_direct and load_normal enable the default encoding and compression, so the storage usage is much smaller (about 1/15 of that of load_none in this example).
   • REQUIRED_SIZE: The allocated space for the data, including storage overhead such as alignment. Therefore, REQUIRED_SIZE >= DATA_SIZE.

4. Verify that compressed data can be queried normally. Execute the same query on both load_direct and load_none:

   obclient [test]> SELECT city, COUNT(*), SUM(amount) FROM load_direct WHERE city IN ('city_0', 'city_1') GROUP BY city;
   obclient [test]> SELECT city, COUNT(*), SUM(amount) FROM load_none WHERE city IN ('city_0', 'city_1') GROUP BY city;
   

   The results of the two tables are identical and consistent with the distribution pattern of the source data (one million rows are evenly distributed across 20 cities, with 50,000 rows per city value; the output order of GROUP BY is not guaranteed and may differ from the following example):

   +--------+----------+-------------+
   | city   | COUNT(*) | SUM(amount) |
   +--------+----------+-------------+
   | city_1 |    50000 |    24550000 |
   | city_0 |    50000 |    24500000 |
   +--------+----------+-------------+
   2 rows in set
   

5. Perform point queries to verify that row-level data can be decoded and read normally:

   obclient [test]> SELECT * FROM load_direct WHERE id = 500000;
   

   +--------+-------------+--------+--------+
   | id     | user_name   | city   | amount |
   +--------+-------------+--------+--------+
   | 500000 | user_500000 | city_0 |      0 |
   +--------+-------------+--------+--------+
   1 row in set
   

Interpretation

Next steps

After you complete the experiences in this topic, you can explore the following:

1. Experience data protection and recovery: Use flashback queries to restore data to its state before an accidental operation, and learn about comprehensive data protection capabilities such as backup and recovery. For more information, see Experience data protection and recovery.
2. Experience online DDL: Execute common DDL operations such as adding columns and creating indexes on a test table with continuous read and write activity, and observe business continuity. For more information, see Experience online DDL.
3. Deepen your understanding of data loading and stress testing: Learn about high-concurrency performance under Sysbench and TPC-C stress tests. For more information, see Experience high-concurrency OLTP scenarios.

References

• For more information about direct load, including its principles and full/incremental modes, see Overview of direct load.
• For more information about the LOAD DATA statement, see Import data by using the LOAD DATA statement.
• For more information about the architecture of compression and encoding, see Compression and encoding.
• For more information about how to configure data encoding and compression, see Use data encoding and compression.
• For more information about manually triggering compactions and viewing their progress, see Manually trigger a major compaction and View major compaction information.
• For more information about view fields, see DBA_OB_TABLET_REPLICAS and DBA_OB_TABLE_LOCATIONS.