The performance of OBLOADER can be tuned from three aspects: command-line options, virtual machine (VM) parameters, and database kernel.
Command-line option tuning
If a wide table is to be imported or if the column values are long, reduce the value of the
--batchoption. Increase the value of this option for the opposite case.Indexes affect the data import performance. In addition to the primary and unique keys, regular indexes can be created only after the data is imported.
If the server load and network bandwidth are low, you can adjust the value of the
--threadoption.Note
When tuning, take into account the resource utilization on the server where OBLOADER runs, on the OceanBase Database cluster nodes, and on the OceanBase Database Proxy (ODP) service nodes.
OBLOADER V4.3.2 and later versions support the
--memparameter for specifying the VM memory size. Supported units are K, M, G, and T. The default value is 4G.
Virtual machine parameter tuning
Set the virtual machine 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 optimization
The performance of importing data is significantly affected by the tenant's incremental memory write speed.
If the incremental memory is insufficient, the database may trigger a major or minor compaction. Major compactions consume a lot of resources, so try to avoid them. You can enable memory-based minor compactions and set the minor_freeze_times parameter to a value greater than 100.
If the incremental memory usage reaches the tenant's throttling threshold, the import performance will decrease.
If the incremental memory usage reaches its maximum, data import may fail. We recommend that you set the tenant's throttling threshold to a value higher than 90. You need to adjust the minor_freeze parameter based on the tenant's memory size and write speed. The following table describes the relevant parameters for tuning the database kernel.
| Parameter | Default value | Description |
|---|---|---|
| set global ob_sql_work_area_percentage=20; | 5 | The percentage of memory used during SQL execution. Value range: [0, 100]. |
| set global max_allowed_packet=1073741824; | 130023424 | The maximum size of a network data packet that the server can receive. |
| alter system set freeze_trigger_percentage=30; | 70 | The threshold of memory used by tenants for triggering a major freeze. major_freeze_trigger_percent = major_freeze trigger threshold / MemStore capacity. MemStore capacity is calculated based on 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 that trigger a major compaction. If the value is 0, minor compactions are disabled. If the memory usage reaches a predefined limit, minor_freeze or major_freeze will be triggered. This parameter specifies the number of minor freezes between two consecutive major freezes. If the value is 0, automatic minor freezes are disabled. Value range: [0, 65536). |
| alter system set minor_compact_trigger=16; | - | The threshold determining whether to trigger the next layer of minor compaction. When the total number of mini SSTables reaches this value, all SSTables are compacted into a new minor SSTable. |
| alter system set merge_thread_count=32; | 0 | The number of worker threads for daily major compactions. If this parameter is set to 0, the number of worker threads of the compaction process is calculated as min{10,cpu_cnt}, where cpu_cnt is the number of CPU cores of the system. This parameter takes effect immediately without the need for a restart. Value range: [0, 256]. NoticeThis parameter is deprecated in OceanBase Database V4.0.0 and later versions. |
| alter system set minor_merge_concurrency=48; | 0 | The number of concurrent threads for minor compactions. Value range: [0, 64]. NoticeThis parameter is deprecated in OceanBase Database V4.0.0 and later versions. |
| alter system set writing_throttling_trigger_percentage=100; | 100 | The server memory throttling threshold. We recommend that you disable server throttling.
NoticeThis parameter is supported in OceanBase Database V2.2.30 and later versions. It requires the tools to have the capability to prevent server memory exhaustion. <main |