Use OBDUMPER to export data from or OBLOADER to import data to OceanBase Database|V4.0.0| docs|Distributed Database

Use OBDUMPER to export data from OceanBase Database

We recommend that you export table schemas and table data separately from OceanBase Database, same as with MySQL Database.

The command help is as follows. You must specify the username and password for the business tenant. To export table schemas, you must enter the username and password of the root user or the proxyro user in the SYS tenant.

By default, OBProxy prohibits logon to an OceanBase cluster as the proxyro user for security considerations. You need to test the connectivity of the proxyro user. The root user has extensive privileges. Therefore, we recommend that you use the proxyro user to obtain the metadata information of tables.

bin/obdumper-h <host IP address> -P <port> -u <user> -p <password> --sys-user <root or proxyro user in the sys tenant> --sys-password <user password in the sys tenant> -c <cluster> -t <tenant> -D <Schema name> [--ddl] [--csv|--sql] [--all|--table 'table name'] -f<data file or directory>

Test connectivity of the `proxyro` user

The password of the proxyro user is specified in the cluster configuration file during cluster deployment by using OceanBase Deployer (OBD).

[root@obce00 ~]# mysql -h xxx.xxx.xxx.xxx -P 2883 -u proxyro@sys#obce-3zones -p****** -c -A
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 11

The password is correct. The logon fails because OBProxy prohibits logon as the proxyro user by default. Modify the OBProxy settings to skip logon check for the proxyro user.

mysql -h xxx.xxx.xxx.xxx -u root@proxysys -P 2883 -p******
alter proxyconfig set skip_proxyro_check=true;
MySQL [(none)]> show proxyconfig like '%skip_proxyro_check%';
+--------------------+-------+--------------------------------------------------+-------------+---------------+
| name               | value | info                                             | need_reboot | visible_level |
+--------------------+-------+--------------------------------------------------+-------------+---------------+
| skip_proxyro_check | True  | used for proxro@sys, if set false, access denied | false       | SYS           |
+--------------------+-------+--------------------------------------------------+-------------+---------------+
1 row in set (0.010 sec)

Test the connectivity of the proxyro user again.

[root@obce00 ~]# mysql -h xxx.xxx.xxx.xxx -P 2883 -u proxyro@sys#obce-3zones -p****** -c -A -Ns -e "show databases;"
oceanbase
information_schema
[root@obce00 ~]#

Export only the schema

Export tables in the test database in the obmysql tenant.

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# bin/obdumper -h xxx.xxx.xxx.xxx -P 2883 -u u_dumper -p ****** --sys-user=proxyro --sys-password=****** -c obce-3zones -t obmysql -D test --ddl --all -f /tmp/obdumper
2021-09-29 21:14:55 [INFO] Parsed args:
-h[--host] xxx.xxx.xxx.xxx
-P[--port] 2883
-u[--user] u_dumper
-p[--password] ******
[--sys-user] proxyro
[--sys-password] ******
-c[--cluster] obce-3zones
-t[--tenant] obmysql
-D[--database] test
[--ddl]
[--all]
-f[--file-path] /tmp/obdumper

obproxy Druid LogFactory, userDefinedLogType=null, logInfo=public com.alipay.oceanbase.obproxy.druid.support.logging.Log4j2Impl(java.lang.String)
2021-09-29 21:14:55 [INFO] {dataSource-1} inited
2021-09-29 21:14:55 [INFO] {dataSource-2} inited
2021-09-29 21:14:55 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-09-29 21:14:57 [WARN] No views are exist in the schema: "test"
2021-09-29 21:14:57 [INFO] Generate 1 dump tasks finished
2021-09-29 21:14:57 [INFO] Start 1 schema dump threads finished
2021-09-29 21:14:57 [INFO] Build direct com.alibaba.druid.pool.DruidDataSource finished
2021-09-29 21:14:57 [INFO] Build proxyro com.alibaba.druid.pool.DruidDataSource finished
2021-09-29 21:14:57 [INFO] Return the latest compatible version: 3.1.0 -> 2.2.71
2021-09-29 21:14:57 [INFO] DbType: OBMYSQL Version: 3.1.0
2021-09-29 21:14:57 [INFO] ObMySql(3.1.0) is older than 2.2.71 ? false
2021-09-29 21:14:57 [INFO] Load meta/mysql/mysql56.xml, meta/ob/obmysql14x.xml, meta/ob/obmysql22x.xml, meta/ob/obmysql2271.xml successed
2021-09-29 21:14:58 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireDependencies()
2021-09-29 21:14:58 [INFO] Query 0 dependencies elapsed 846.9 ms
2021-09-29 21:15:01 [INFO]
Finished Tasks: 0       Running Tasks: 1        Progress: 0.00%
2021-09-29 21:15:04 [INFO] Query table: "t1" attr finished. Remain: 0
2021-09-29 21:15:04 [INFO] Query 1 tables elapsed 5.775 s
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireTablespaceMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireSequenceMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireMaterializedViewMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireAliasMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireSynonymMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireTypeMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireTypeBodyMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquirePackageMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquirePackageBodyMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireTriggerMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireProcedureMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireFunctionMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireDatabaseLinkMapping()
2021-09-29 21:15:04 [WARN] c.o.o.d.m.o.ObMySql14xDatabase does't implement acquireDependencies()
2021-09-29 21:15:04 [INFO] Dump [TABLE] t1 to "/tmp/obdumper/data/test/TABLE/t1-schema.sql" finished
2021-09-29 21:15:04 [INFO] No.1 It has dumped 1 tables finished. Remain: 0
2021-09-29 21:15:04 [INFO] Total dumped 1 tables finished
2021-09-29 21:15:04 [INFO] Dump the ddl of schema: "test" finished
2021-09-29 21:15:04 [INFO] {dataSource-1} closing ...
2021-09-29 21:15:04 [INFO] {dataSource-1} closed
2021-09-29 21:15:04 [INFO] Close count: 7
2021-09-29 21:15:04 [INFO] {dataSource-2} closing ...
2021-09-29 21:15:04 [INFO] {dataSource-2} closed
2021-09-29 21:15:04 [INFO] Close count: 6
2021-09-29 21:15:04 [INFO] Shutdown task context finished
2021-09-29 21:15:04 [INFO]
Finished Tasks: 1       Running Tasks: 0        Progress: 100.00%
2021-09-29 21:15:04 [INFO]

All Dump Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
1          |       TABLE        |              t1              |              1               |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 1          End Time: 2021-09-29 21:15:04


2021-09-29 21:15:04 [INFO] Dump schema finished. Total Elapsed: 7.051 s
2021-09-29 21:15:04 [INFO] System exit 0
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#

We recommend that you familiarize yourself with the structure of the directory exported by OBDUMPER so that you can quickly locate the exported script or logs. The structure of the directory is as follows:

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# tree /tmp/obdumper/
/tmp/obdumper/
├── CHECKPOINT.bin
├── data
│   ├── MANIFEST.bin
│   └── test
│       └── TABLE
│           └── t1-schema.sql
└── logs
    ├── ob-loader-dumper.error
    ├── ob-loader-dumper.info
    └── ob-loader-dumper.warn

4 directories, 6 files
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#

If an error occurs during the export, you can view the logs in the logs/ directory.

Export data only

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# bin/obdumper -h xxx.xxx.xxx.xxx -P 2883 -u u_dumper -p ****** --sys-user=proxyro --sys-password=****** -c obce-3zones -t obmysql -D test --csv --all -f /tmp/obdumper
2021-09-29 21:16:54 [INFO] Parsed args:
-h[--host] xxx.xxx.xxx.xxx
-P[--port] 2883
-u[--user] u_dumper
-p[--password] ******
[--sys-user] proxyro
[--sys-password] ******
-c[--cluster] obce-3zones
-t[--tenant] obmysql
-D[--database] test
[--csv]
[--all]
-f[--file-path] /tmp/obdumper

obproxy Druid LogFactory, userDefinedLogType=null, logInfo=public com.alipay.oceanbase.obproxy.druid.support.logging.Log4j2Impl(java.lang.String)
2021-09-29 21:16:54 [INFO] {dataSource-1} inited
2021-09-29 21:16:54 [INFO] {dataSource-2} inited
2021-09-29 21:16:54 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-09-29 21:16:55 [INFO] Generate 1 csv dump tasks for non-partitioned table(without macro): t1. Remain: 0
2021-09-29 21:16:55 [INFO] Generate 1 dump tasks finished
2021-09-29 21:16:55 [INFO] Start 16 record dump threads finished
2021-09-29 21:16:55 [INFO] Dump 8 rows test.t1 to "/tmp/obdumper/data/test/TABLE/t1.csv" finished
2021-09-29 21:16:56 [INFO] {dataSource-1} closing ...
2021-09-29 21:16:56 [INFO] {dataSource-1} closed
2021-09-29 21:16:56 [INFO] Close count: 1
2021-09-29 21:16:56 [INFO] {dataSource-2} closing ...
2021-09-29 21:16:56 [INFO] {dataSource-2} closed
2021-09-29 21:16:56 [INFO] Close count: 1
2021-09-29 21:16:56 [INFO] Shutdown task context finished
2021-09-29 21:16:56 [INFO]
Finished Tasks: 1       Running Tasks: 0        Progress: 100.00%
2021-09-29 21:16:56 [INFO]

All Dump Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
        No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
         1          |       TABLE        |              t1              |              8               |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 8          End Time: 2021-09-29 21:16:56


2021-09-29 21:16:56 [INFO] You can't merge the data files for 1 tables. --file-name is missing
2021-09-29 21:16:56 [INFO] Dump record finished. Total Elapsed: 1.548 s
2021-09-29 21:16:56 [INFO] System exit 0
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#

The structure of the directory is as follows:

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# tree /tmp/obdumper
/tmp/obdumper
├── CHECKPOINT.bin
├── data
│   ├── MANIFEST.bin
│   └── test
│       └── TABLE
│           ├── t1.0.csv
│           └── t1-schema.sql
└── logs
    ├── ob-loader-dumper.error
    ├── ob-loader-dumper.info
    └── ob-loader-dumper.warn

4 directories, 7 files
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#

The exported data files in the CSV format are located in the same directory as the schema files.

View the exported files:

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# cat /tmp/obdumper/data/test/TABLE/t1-schema.sql
create table if not exists `t1` (
        `id` bigint(20) not null auto_increment,
        `c1` timestamp not null default CURRENT_TIMESTAMP,
        primary key (`id`)
)
default charset=utf8mb4
default collate=utf8mb4_general_ci;[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# cat /tmp/obdumper/data/test/TABLE/t1.0.csv
'id','c1'
1,'2021-09-29 21:16:03'
2,'2021-09-29 21:16:05'
3,'2021-09-29 21:16:05'
4,'2021-09-29 21:16:06'
5,'2021-09-29 21:16:06'
6,'2021-09-29 21:16:18'
7,'2021-09-29 21:16:18'
8,'2021-09-29 21:16:19'

Use OBLOADER to import data to OceanBase Database

Similarly, we recommend that you import table schemas and data separately to OceanBase Database.

Command-line options of OBLOADER:

bin/obloader -h <host IP address> -P <port> -u <user> -p <password> --sys-user <root or proxyro user in the sys tenant> --sys-password <user password in the sys tenant> -c <cluster> -t <tenant> -D <Schema name> [--ddl] [--csv|--sql] [--all|--table 'table name'] -f<data file or directory>

OBLOADER must obtain the metadata information of the table schemas during an import, which means that you need to log on to the sys tenant. We recommend that you log on as the proxyro user.

Import schemas

The following example imports a previously exported data file to the test2 database of the obmysql tenant.

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# bin/obloader -h xxx.xxx.xxx.xxx -P 2883 -u u_loader -p ****** --sys-user=proxyro --sys-password=****** -c obce-3zones -t obmysql -D test2 --ddl --all -f /tmp/obdumper
2021-09-29 21:26:05 [INFO] Parsed args:
-h[--host] xxx.xxx.xxx.xxx
-P[--port] 2883
-u[--user] u_loader
-p[--password] ******
[--sys-user] proxyro
[--sys-password] ******
-c[--cluster] obce-3zones
-t[--tenant] obmysql
-D[--database] test2
[--ddl]
[--all]
-f[--file-path] /tmp/obdumper

2021-09-29 21:26:05 [INFO] No control files were defined in the path: "/tmp/obdumper"
2021-09-29 21:26:05 [INFO] No control files were defined in the path: "/tmp/obdumper"
2021-09-29 21:26:05 [INFO] No mapping files were defined in the path: "/tmp/obdumper"
obproxy Druid LogFactory, userDefinedLogType=null, logInfo=public com.alipay.oceanbase.obproxy.druid.support.logging.Log4j2Impl(java.lang.String)
2021-09-29 21:26:05 [INFO] {dataSource-1} inited
2021-09-29 21:26:05 [INFO] {dataSource-2} inited
2021-09-29 21:26:05 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-09-29 21:26:05 [INFO] Init writer thread pool finished
2021-09-29 21:26:05 [INFO] No.1 sql of the file: "t1-schema.sql" exec success. Elapsed: 114.4 ms
2021-09-29 21:26:05 [INFO] Load file: "t1-schema.sql" finished
2021-09-29 21:26:06 [INFO] {dataSource-2} closing ...
2021-09-29 21:26:06 [INFO] {dataSource-2} closed
2021-09-29 21:26:06 [INFO] Close count: 0
2021-09-29 21:26:06 [INFO] {dataSource-1} closing ...
2021-09-29 21:26:06 [INFO] {dataSource-1} closed
2021-09-29 21:26:06 [INFO] Close count: 1
2021-09-29 21:26:06 [INFO] Shutdown task context finished
2021-09-29 21:26:06 [INFO]
Finished Tasks: 1       Running Tasks: 0        Progress: 100.00%
2021-09-29 21:26:06 [INFO]

All Load Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
        No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
         1          |       TABLE        |              t1              |              1               |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 1          End Time: 2021-09-29 21:26:06


2021-09-29 21:26:06 [INFO] Load schema finished. Total Elapsed: 1.070 s
2021-09-29 21:26:06 [INFO] System exit 0
[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]#

Import data

[root@obce00 ob-loader-dumper-2.1.13-SNAPSHOT]# bin/obloader -h xxx.xxx.xxx.xxx -P 2883 -u u_loader -p ****** --sys-user=proxyro --sys-password=****** -c obce-3zones -t obmysql -D test2 --csv --all -f /tmp/obdumper
2021-09-29 21:27:53 [INFO] Parsed args:
-h[--host] xxx.xxx.xxx.xxx
-P[--port] 2883
-u[--user] u_loader
-p[--password] ******
[--sys-user] proxyro
[--sys-password] ******
-c[--cluster] obce-3zones
-t[--tenant] obmysql
-D[--database] test2
[--csv]
[--all]
-f[--file-path] /tmp/obdumper

2021-09-29 21:27:53 [INFO] No control files were defined in the path: "/tmp/obdumper"
2021-09-29 21:27:53 [INFO] No control files were defined in the path: "/tmp/obdumper"
2021-09-29 21:27:53 [INFO] No mapping files were defined in the path: "/tmp/obdumper"
obproxy Druid LogFactory, userDefinedLogType=null, logInfo=public com.alipay.oceanbase.obproxy.druid.support.logging.Log4j2Impl(java.lang.String)
2021-09-29 21:27:53 [INFO] {dataSource-1} inited
2021-09-29 21:27:53 [INFO] {dataSource-2} inited
2021-09-29 21:27:53 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-09-29 21:27:54 [WARN] File: "/tmp/obdumper/data/MANIFEST.bin" is unmatched on the suffix[.csv], ignore it
2021-09-29 21:27:54 [WARN] File: "/tmp/obdumper/data/test/TABLE/t1-schema.sql" is unmatched on the suffix[.csv], ignore it
2021-09-29 21:27:54 [INFO] Binding table: "t1" to the file: "/tmp/obdumper/data/test/TABLE/t1.0.csv" finished
2021-09-29 21:27:54 [INFO] File: "/tmp/obdumper/data/test/TABLE/t1.0.csv" has not been splitted. 202 < 67108864
2021-09-29 21:27:54 [INFO] Splitted 1 csv subfiles by 64.0 MB. Elapsed: 10.49 ms
2021-09-29 21:27:54 [INFO] Generate 1 subfiles finished
2021-09-29 21:27:55 [INFO] Query table entry and primary key for table: t1 finished. Remain: 0
2021-09-29 21:27:55 [INFO] Query the leader location for the table: "t1". Remain: 0
2021-09-29 21:27:55 [INFO] Calculate leader: xxx.xxx.xxx.xxx:2881 of table: "t1", part: 0. Remain: 0
2021-09-29 21:27:55 [INFO] Waiting to refresh observer load status ......
2021-09-29 21:27:55 [INFO] Refresh the observer load status success. Table: "t1". Remain: 0
2021-09-29 21:27:55 [INFO] Refresh observer load status finished. Elapsed: 94.30 ms
2021-09-29 21:27:55 [INFO] Create 16384 slots for ring buffer finished. [xxx.xxx.xxx.xxx:2881]
2021-09-29 21:27:55 [INFO] Start 11 database writer threads finished. [xxx.xxx.xxx.xxx:2881]
2021-09-29 21:27:55 [INFO] Start 3 csv file reader threads successed
2021-09-29 21:27:55 [INFO] File: "/tmp/obdumper/data/test/TABLE/t1.0.csv" has been parsed finished
2021-09-29 21:27:56 [INFO] Wait for the all the workers to drain of published events then halt the workers
2021-09-29 21:27:56 [INFO] {dataSource-2} closing ...
2021-09-29 21:27:56 [INFO] {dataSource-2} closed
2021-09-29 21:27:56 [INFO] Close count: 5
2021-09-29 21:27:56 [INFO] {dataSource-1} closing ...
2021-09-29 21:27:56 [INFO] {dataSource-1} closed
2021-09-29 21:27:56 [INFO] Close count: 2
2021-09-29 21:27:56 [INFO] Shutdown task context finished
2021-09-29 21:27:56 [INFO]
Finished Tasks: 1       Running Tasks: 0        Progress: 100.00%
2021-09-29 21:27:56 [INFO]

All Load Tasks Finished:

----------------------------------------------------------------------------------------------------------------------------
        No.#        |        Type        |             Name             |            Count             |       Status
----------------------------------------------------------------------------------------------------------------------------
         1          |       TABLE        |              t1              |            8 -> 8            |      SUCCESS
----------------------------------------------------------------------------------------------------------------------------

Total Count: 8          End Time: 2021-09-29 21:27:56


2021-09-29 21:27:56 [INFO] Load record finished. Total Elapsed: 2.140 s
2021-09-29 21:27:56 [INFO] System exit 0

Check the imported table schemas and data.

MySQL [test]> show create table test2.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.012 sec)

MySQL [test]> select * from test2.t1;
+----+---------------------+
| id | c1                  |
+----+---------------------+
|  1 | 2021-09-29 21:16:03 |
|  2 | 2021-09-29 21:16:05 |
|  3 | 2021-09-29 21:16:05 |
|  4 | 2021-09-29 21:16:06 |
|  5 | 2021-09-29 21:16:06 |
|  6 | 2021-09-29 21:16:18 |
|  7 | 2021-09-29 21:16:18 |
|  8 | 2021-09-29 21:16:19 |
+----+---------------------+
8 rows in set (0.015 sec)

Common import and export issues

Unexpected results occur during data import because the same directory is used in multiple exports.

You must specify the directory when you use OBDUMPER to export data. If the same directory is used when different databases or tables are exported, the exported files are mixed together and the files with the same name are overwritten. When you use OBLOADER to import data from the directory, unexpected data may be imported. To import data, OBLOADER parses the structure of the directory to obtain the tables to be imported.

Therefore, if you need to export data multiple times, we recommend that you use a unique export directory each time. Alternatively, you can clear the export directory before each export.

External files fail to be imported due to invalid file formats

The solution varies with the type of the external file:

The external file is an SQL file.
- If the file content is DDL statements, the SQL file cannot contain comments or SET switch statements.
- If the file content is DML statements, the SQL file must contain only INSERT statements, and each statement must occupy one line without line breaks.

Note This file format is quite tricky. We recommend that you import files in this format directly in the MySQL command line instead of using OBLOADER.

The external file is a CSV file.

The content of the CSV file must be in the standard CSV format and contain column and row delimiters. Columns must be enclosed within specific strings, which are usually double quotation marks. In addition, double quotation marks within columns must be escaped.

Export performance issues

If you export the schemas of multiple tables, we recommend that you set the --threads option to a value that is not greater than 4. An excessively high concurrency increases the burden of accessing internal views for the sys tenant and causes export timeout errors.

You can enable concurrency for data export from multiple tables. In this case, the --threads option is dynamically adjusted based on the CPU utilization. You can also manually specify the number of concurrent threads to control the impact of export on the host performance.

When you export or import a large amount of data, a bottleneck may occur on OBLOADER or OBDUMPER itself. In this case, you can edit the obloader or obdumper file and increase the values of the Java parameters Xms and Xmx, which respectively specify the Java initial memory and the maximum available memory.

vim bin/obdumper or vim bin/obloader

 50 JAVA_OPTS="$JAVA_OPTS -server -Xms4G -Xmx4G -XX:MetaspaceSize=512M -XX:MaxMetaspaceSize=512M -Xss352K"

Import performance issues

When you import table schemas, set the concurrency parameter --threads to a value not greater than 2. Concurrent DDL operations may not speed up the data import. In OceanBase Database, DDL statements are executed in series. Each DDL statement takes 1 second to execute on average.

You can enable concurrency for the import of a large amount of data. In this case, the --threads option is dynamically adjusted based on the CPU utilization. You can also manually specify the number of concurrent threads to control the impact of import on the host performance.

The import performance is also affected by table indexes. We recommend that you set only the primary key index when you create a large table, and create unique and common indexes after data is imported.

The MemStore write speed of the tenant may also affect the data import performance. Insufficient MemStore space may trigger major or minor compactions. A major compaction hinders the system performance. We recommend that you avoid triggering a major compaction during data import. You can enable minor compaction of memory and set the number of minor compactions that triggers a major compaction to a value greater than 100. This way, major compaction will not be triggered in at least 24 hours. If the MemStore usage reaches the throttling threshold specified for the tenant, the import performance also deteriorates. If the MemStore space is exhausted, an import error may occur, which may lead to the failure of the entire data import process.

Therefore, we recommend that you set the tenant throttling threshold to a value no more than 90. The parameters of minor compaction are related to the size and write speed of the tenant memory, you need to optimize the parameters based on the actual situation.

Use OBDUMPER to export data from or OBLOADER to import data to OceanBase Database