You can tune the performance of OBLOADER from three aspects: command-line options, virtual machine (VM) parameters, and database kernel.
Command-line options-based performance tuning
When you import wide tables or tables with columns that contain long values, decrease the value of the
--batchoption. When you import narrow tables or tables containing only columns with short values, increase the value.Indexes affect the performance of data import. We recommend that you set only the primary key and unique key and create common indexes after data is imported.
You can adjust the value of the
--threadoption when the server load and network workload are relatively low.Note
During performance tuning, take into account the resource utilization on the server where OBLOADER runs, on the ODP service nodes, and on the nodes in the OceanBase cluster,
OBLOADER V4.3.2 and later versions support the
--memparameter for specifying the VM memory size. Valid units are K, M, G, and T. The default value is 4G.
VM parameter-based performance tuning
Set the VM parameter in the import script to 60% of the available physical memory. Default value: -Xms4G -Xmx4G.
vim bin/obloader
JAVA_OPTS="$JAVA_OPTS -server -Xms4G -Xmx4G -XX:MetaspaceSize=128M -XX:MaxMetaspaceSize=128M -Xss352K"
You can also use the --mem parameter to specify the VM memory size instead of modifying the VM parameter. For example, --mem 4G.
Database kernel-based performance tuning
The data import performance is subject to the speed of writing incremental data to the memory of the tenant.
Insufficient incremental memory may trigger a major or minor compaction. Prevent major compaction because it greatly affects import performance. You can enable minor compaction for memory and set the number of minor compactions to more than 100.
If the incremental memory usage reaches the throttling threshold specified for the tenant, the import performance falls.
If the incremental memory usage reaches the upper limit, data import may fail. We recommend that you set the tenant throttling threshold to a value higher than 90. As the parameters of minor compaction are related to the size and write speed of the tenant memory, you need to optimize the parameters as needed. The following table describes the parameters for tuning the database kernel.
Parameter |
Default value |
Description |
|---|---|---|
| set global ob_sql_work_area_percentage=20; | 5 | The memory usage during the execution of SQL statements. Value range: [0,100]. |
| set global max_allowed_packet=1073741824; | 130023424 | The maximum size of network data packets that can be received by the server. |
| alter system set freeze_trigger_percentage=30; | 70 | The threshold of memory used by tenants for triggering a global freeze. 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: [1,99]. |
| alter system set minor_freeze_times=500; | 5 | The number of minor compactions for triggering a global major compaction. If the value is 0, minor compaction is disabled. 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=16; | - | The threshold for triggering the next-level compaction in hierarchical minor compactions. When the total number of mini SSTables in the current level reaches this threshold, all SSTables are compacted to the next level to form a new minor SSTable. |
| alter system set merge_thread_count=32; | 0 | The number of worker threads for daily major compactions. If the value of this parameter is 0, the formula for calculating the number of worker threads for a compaction is min{10,cpu_cnt}, where cpu_cnt indicates the number of CPU cores in the system. This parameter takes effect immediately after modification and does not require a restart. Value range: [0,256]. NoticeThis parameter has been deprecated in OceanBase V4.0.0 and later versions. |
| alter system set minor_merge_concurrency=48; | 0 | The number of concurrent threads in a minor compaction. Value range: [0,64]. NoticeThis parameter has been deprecated in OceanBase V4.0.0 and later versions. |
| alter system set writing_throttling_trigger_percentage=100; | 100 | The threshold of server memory usage that triggers write throttling. We recommend that you disable throttling for the server.
NoticeThis parameter is supported in OceanBase V2.2.30 and later versions. The tool must have the memory exhaustion prevention capability. |