Performance tuning|V3.0.0|OceanBase Loader and Dumper|docs|OceanBase Documentations

You can tune the performance of OBLOADER from three aspects: command-line options, virtual machine (VM) memory, and database kernel.

Command-line options-based performance tuning

Decrease the value of the --batch option when you import wide tables or tables with columns that contain long values. Default value: 200.
Increase the value of the --batch option when you import narrow tables or tables with columns that contain short values. Default value: 200.
You can adjust the value of the --thread option when the server load and network workload are relatively low.

Note

During performance tuning, take into account the resource usage on the server of OBLOADER, the OBProxy server, and OBServers in the OceanBase cluster. You only need to consider the network condition of the OBProxy server.

VM memory-based performance tuning

We recommend that you set the VM memory parameter in the script to 60% of the available physical memory.

The following table describes the VM memory parameter.

Parameter tuning	Default value
-Xms4G -Xmx4G	4G

Database kernel-based performance tuning

You can tune the performance of OBLOADER by adjusting the following database kernel parameters:

-- Required system variables and parameters
set global ob_sql_work_area_percentage=30; -- Default value: 5
alter system set freeze_trigger_percentage=30; -- Default value: 70
set global max_allowed_packet=1073741824; -- Set it to 1 G

-- Optional system variables and parameters
alter system set enable_syslog_recycle='True'; -- Default value: false
alter system set max_syslog_file_count=100; -- Default value: 0
alter system set minor_freeze_times=500; -- Default value: 5
alter system set minor_compact_trigger=5; -- Default value: 5
alter system set merge_thread_count=45; -- Default value: 0
alter system set minor_merge_concurrency=20; -- Default value: 0
alter system set writting_throttling_trigger_percentage=85; -- Default value: 10

Parameter	Default value	Description
set global ob_sql_work_area_percentage=30;	5	The memory utilization during the execution of SQL statements.
alter system set freeze_trigger_percentage=30;	70	major_freeze_trigger_percent = major_freeze trigger threshold/Memstore capacity. The memstore capacity is calculated based on the value of memstore_lmt_percent by using the following formula: memstore_lmt_percent=memstore_limit/min_memory. Value range: (0, 100).
alter system set enable_syslog_recycle='True';	False	Specifies whether to recycle old system logs. This parameter takes effect when max_syslog_file_count is specified.
alter system set max_syslog_file_count=100;	0	Specifies the maximum number of system log files that can exist at the same time. Each system log file is 256 MB in size. If you set this parameter to 0, system log files will not be deleted. Value range: [0, + ∞).
alter system set minor_freeze_times=5; alter system set minor_freeze_times=500; (The memory size of the tenant is sufficient).	3	If the memory usage reaches a predefined threshold, a minor freeze or a major freeze will be triggered. This parameter specifies the number of minor freezes triggered between two consecutive major freezes. If you set this parameter to 0, automatic triggering of minor freezes is disabled. Value range:[0, 65536).
alter system set minor_compact_trigger=5;	-	Specifies the number of mini SSTables in a layer to trigger the compaction of all mini SSTables in this layer. When this value is reached, mini SSTables are compacted into new SSTables of the next layer.
alter system set merge_thread_count=45;	0	Specifies the number of worker threads for daily major compactions. When the default value is used, the number of threads for major compaction is MIN[Number of logical CPUs * 30%, 10]. This parameter takes effect immediately after modification and does not require a restart. Value range: [0, 64].
alter system set minor_merge_concurrency=20;	0	The number of concurrent threads for a minor compaction.
alter system set writting_throttling_trigger_percentage=85;	10	Specifies the threshold of server memory usage that will trigger write throttling. This system parameter is supported in OceanBase Database V2.2.30 and later. Therefore, OBLOADER must be designed with the mechanism of preventing full disk usage in the destination database during data importing.