Tuning OBServer

Adjust OBServer parameters to improve the OceanBase performance.

Method

This section provides tuning suggestions for the CPU, I/O, memory, and network transmission parameters of OBServer.

**Table 1** Tuning suggestions for CPU parameters
Parameter	Description	Suggestion
workers_per_cpu_quota	The maximum number of working threads that a tenant can allocate on each CPU.	This parameter specifies the maximum number of threads that the system allocates to a program, not the maximum number of threads that a program runs concurrently. Generally, adjustment is not required.
net_thread_count	The number of network I/O threads.	Run the top -H command to view the MySQL I/O thread usage. If the usage of a thread exceeds 90%, the thread may become a bottleneck. Increase the value of this parameter to improve performance. If the usage of all threads is under 50%, decrease the value of this parameter to reduce the overhead of thread switchover. NOTICE: Restart the OceanBase cluster for the modification to take effect.
autoinc_cache_refresh_interval	The interval for automatically refreshing background threads, in seconds.	Automatic refresh of background threads may cause unstable performance. Increase the value of this parameter to reduce the frequency of the automatic refresh.
enable_early_lock_release	Specifies whether rows locked in a transaction can be released before the transaction is committed. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is true for hotspot scenarios.
enable_monotonic_weak_read	Specifies whether to enable weak consistency read. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is false for performance scenarios.
weak_read_version_refresh_interval	The refresh interval of the weak consistency read version, in milliseconds. This parameter affects the latency of weak consistency read data.	When the value is 0, the weak consistency read version is not refreshed and the monotonic read function is disabled. Recommended value is 0 for performance scenarios.
enable_sql_audit	Enables or disables the SQL audit function. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Mandatory value is true in the production environment. Recommended value is false in the performance test scenario.
enable_perf_event	Enables or disables the function of collecting performance event information. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Mandatory value is true in the production environment. Recommended value is false in the performance test scenario.
enable_record_trace_log	Enables or disables the function of recording trace logs. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is false in the production environment where enable_perf_event and enable_sql_audit are enabled.

**Table 2** Tuning suggestions for I/O parameters
Parameter	Description	Suggestion
syslog_io_bandwidth_limit	The bandwidth limit for syslog-ng to write log files. The default value is 30 MB.	Recommended value: 10 MB.
clog_sync_time_warn_threshold	Records a warning message when the clog synchronization time exceeds the threshold.	Increase the value of this parameter. However, increasing the value may affect the investigation of slow clog synchronization.
trace_log_slow_query_watermark	Records a query in slow query logs if the query time exceeds the threshold specified by this parameter.	Increase the value of this parameter to avoid printing slow query tracing logs.
max_syslog_file_count	Maximum number of the observer.log files. If the number of observer.log files exceeds the maximum, the extra files are automatically deleted.	Adjust the value of this parameter based on your drive capacity.
enable_sql_operator_dump	Specifies whether to allow intermediate results of SQL processing to be written to drives to release memory. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is true during the test in the OLAP scenario to prevent errors caused by excessive memory.
builtin_db_data_verify_cycle	The bad block self-check period. The unit is day. Self-check is disabled when the value is 0.	Recommended value is 0 for performance scenarios.
disk_io_thread_count	The number of drive I/O threads. NOTICE: The value of this parameter must be an even number.	Adjust the value of this parameter based on the number of I/O threads.
enable_async_syslog	Enables or disables asynchronous syslog logging. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is true for performance scenarios.
fuse_row_cache_priority	The priority of the fuse row cache in the cache system. In the multi-level storage architecture of OceanBase database, if multiple columns in a row exist in MemStore, mini SSTable, minor SSTable, and SSTable, the columns need to be fused when the row is queried. In this case, the fuse row cache can be used to cache the row to prevent repetitive operations in the next query.	A value greater than 1 is recommended to prevent the row cache from being replaced too early.
syslog_level	The system log level.	Recommended value is PERF for performance scenarios.
merge_thread_count	The number of merged scheduling threads. The default value is 10.	Increase the value of this parameter based on the system load and log processing requirements.

**Table 3** Tuning suggestions for memory parameters
Parameter	Description	Suggestion
memory_limit_percentage	The percentage of the available memory for the OceanBase database to the total memory of the system.	Increase the value of this parameter to increase the available memory for the OceanBase database.
memstore_limit_percentage	The percentage of the MemStore memory used by a tenant to its total available memory.	Increase the MemStore space. If the value of this parameter is too large, the memory usage may exceed the threshold because the dump speed cannot keep up with the write speed.
freeze_trigger_percentage	The memory usage threshold for triggering global freezing.	For a system with high write pressure, a recommended value of this parameter is from 30 to 50 to trigger dump as soon as possible and prevent insufficient memory. Dumping causes extra CPU and I/O overhead. Frequent dumping results in an increase in the number of mini SSTable, which increases the query path and affects the performance. This parameter is changed to a tenant-level parameter in OceanBase V4.0 and later versions.
use_large_pages	Enables or disables huge page memory. NOTICE: This function takes effect only after the huge page memory function is enabled on the OS.	Recommended value is true to enable huge page memory to improve the query efficiency of the memory page table.

**Table 4** Tuning suggestions for network transmission parameters
Parameter	Description	Suggestion
__easy_memory_limit	The maximum memory size of RPC packets sent to a single OBServer. The default value is 4 GB.	Increase the value of this parameter if a large amount of data needs to be queried.
ob_proxy_readonly_transaction_routing_policy	Specifies whether the transaction routing by OceanBase Database Proxy (ODP) is affected by read-only statements. Boolean type. The function is enabled when the value is true, and the function is disabled when the value is false.	Recommended value is false, indicating that ODP routes transactions based on the first statement that actually starts the transaction.

Viewing and Modifying OBServer Parameters

Use OBClient to connect to OceanBase.
```
obclient -h127.0.0.1 -P2883 -uroot@sysbench_tenant -Doceanbase -A
```
Modify the following parameters as required:
- 127.0.0.1 and 2883 are the IP address and port number of the OceanBase server respectively.
- root is the database user, and sysbench_tenant is the database tenant.
- oceanbase is the name of the OceanBase database.
View OBServer parameters. For example, view workers_per_cpu_quota:
```
SHOW PARAMETERS LIKE 'workers_per_cpu_quota';
```
Set OBServer parameters. For example, set workers_per_cpu_quota to 20:
```
alter system set workers_per_cpu_quota = 20;
```

Parent topic: Database Parameter Tuning