You can tune the performance of OBLOADER from three aspects: command-line options, 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.As the import performance is affected by the number of indexes on the table, 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 run, on the ODP service nodes, and on the nodes in the OceanBase cluster, especially the network of the ODP nodes.
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=512M -XX:MaxMetaspaceSize=512M -Xss352K"
Database kernel-based performance tuning
The incremental memory write speed of the tenant may affect the data import performance.
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=30; | 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 enable_syslog_recycle='True'; | False | Specifies whether to enable recycling for system logs. This parameter takes effect when max_syslog_file_count is specified. |
| alter system set max_syslog_file_count=100; | 0 | The maximum number of log files that can be retained. Each log file can occupy at most 256 MB of disk space. If this parameter is set to 0, no log files are 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). | 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=5; | - | 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=45; | 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*0.3}, 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=20; | 0 | The number of concurrent threads in a minor compaction. Value range: [0,64]. |
| alter system set writing_throttling_trigger_percentage=100; | 100 | The threshold of server memory usage that will trigger write throttling.
NoticeThis parameter is supported in OceanBase V2.2.30 and later versions. The tool must have the memory exhaustion prevention capability. |