Recommended configurations in complex OLTP scenarios-OceanBase

To help users achieve optimal performance with OceanBase Database across various business scenarios, OceanBase has summarized some recommended configurations for key parameters and variables based on extensive tuning experience from numerous real-world scenarios. This topic introduces the recommended configurations for parameters in complex online transaction processing (OLTP) scenarios.

Background information

In a complex OLTP scenario, large amounts of data and intricate transactions are processed. It is commonly used in banking and insurance systems, where workloads often involve complex join operations, complex join subqueries, batch processing jobs compiled in Programming Language (PL), and long, large transactions, or large transactions. Sometimes, parallel execution strategies are employed to speed up short-duration queries.

Configuration template

Starting from V4.3.2, OceanBase Database provides recommended parameter configuration templates for different business scenarios in the installation package. After installing OceanBase RPM package, you can find these templates in the /home/admin/oceanbase/etc directory, where:

default_parameter.json stores recommended parameter configurations.
default_system_variable.json stores recommended variables configurations.

Recommended configurations for OceanBase Database V4.3.2

This section takes OceanBase Database V4.3.2 as an example to introduce the recommended configurations for V4.3.x. You can view the recommended configurations for a version later than V4.3.2 in the configuration template for the corresponding version.

In this scenario, there are currently no recommended variable configurations.

Recommended parameter configurations

The table below lists the recommended parameters and their descriptions for this scenario.

Parameter	Description	Default value	Value range	Recommended value	Applicable scope	Effective upon OBServer node restart?
large_query_threshold	The execution time threshold to identify a large query. A request may be suspended if its execution time exceeds this threshold. A suspended request is considered a large query and is processed by following the large query scheduling strategy.	5s	For OceanBase Database V4.x earlier than V4.3.0: [1ms, +∞) For OceanBase Database V4.3.0 and later: [0ms, +∞)	600s. The query execution may last a long time in a scenario that involves complex OLTP workloads.	Cluster	No
_enable_defensive_check	Specifies whether to perform defensive checks during the execution of a query.	1	True False	0. Disabling defensive checks in the production environment can increase the performance by 3%.	Cluster	No
enable_syslog_recycle	Specifies whether to record the logs generated before the startup of the OBServer node. You can use this parameter with `max_syslog_file_count` to specify whether to include earlier log files in the recycling logic.	False	True False	True. This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space.	Cluster	No
max_syslog_file_count	The maximum number of system log files that can be retained.	0	[0, +∞)	300. Set the parameter to an appropriate value when `enable_syslog_recycle` is set to `True`.	Cluster	No
_rowsets_max_rows	The maximum number of rows processed in a vectorized execution by the SQL engine.	256	[1, 65536]	4. This value can maximize the effectiveness for complex OLTP workloads.	Tenant	No
log_transport_compress_all	Specifies whether to compress logs for transmission.	False	`True`: specifies to compress logs for transmission. `False`: specifies not to compress logs for transmission.	True. Remote procedure call (RPC) compression can reduce the CPU overhead when the bandwidth resources are limited.	Tenant	No

Configuration examples

To configure cluster-level parameters in the sys tenant, execute the following statements:

obclient> ALTER SYSTEM SET large_query_threshold = '600s';
obclient> ALTER SYSTEM SET _enable_defensive_check = 0;
obclient> ALTER SYSTEM SET enable_syslog_recycle = True;
obclient> ALTER SYSTEM SET max_syslog_file_count = 300;

To configure tenant-level parameters in a user tenant, execute the following statements:

MySQL tenant

Oracle tenant

obclient> ALTER SYSTEM SET _rowsets_max_rows = 4;
obclient> ALTER SYSTEM SET log_transport_compress_all = True;

obclient> ALTER SYSTEM SET "_rowsets_max_rows" = 4;
obclient> ALTER SYSTEM SET log_transport_compress_all = True;

Recommended configurations for OceanBase Database V3.x

Recommended parameter configurations

Parameter	Description	Default value	Value range	Recommended value	Applicable scope	Effective upon OBServer node restart?
system_memory	The memory reserved for the `sys` tenant, namely, the sys500 tenant.	30G	[0M, +∞)	30G. Note The value of `system_memory` is not the upper limit on the memory available for the sys500 tenant. The memory for the sys500 tenant is subject to the total available memory of the OBServer node. In a standard production environment, the default value `30G` is recommended, or you can set the parameter to a larger value such as `50G` if needed. In a small-scale environment, we recommend that you set `system_memory` to a value at least 5% of the server memory.	Cluster	No
rootservice_memory_limit	The maximum memory available for RootService.	2G	[2G, +∞)	2G	Cluster	No
cpu_quota_concurrency	The maximum concurrency allowed for each CPU quota of a tenant, which is calculated by using the following formula: `cpu_quota_concurrency` × Minimum number of CPU cores for the tenant = Maximum number of worker threads available for the tenant.	4	[1,10]	4. Note When you specify this parameter, you need to consider the concurrency capacity design of the tenant and make sure that the actual available worker threads meet the business concurrency requirements and do not exceed the capacity of actual hardware resources. In multi-tenant scenarios, you also need to consider the CPU resource isolation requirements between the tenants to prevent them from affecting each other.	Cluster	No
enable_merge_by_turn	Specifies whether to enable the rotating major compaction strategy.	False	True False	False	Cluster	No
freeze_trigger_percentage	The memory threshold for triggering a minor compaction of the MemStore.	70	[1,99]	30. Note In batch write scenarios such as batch import, you can set `freeze_trigger_percentage` to a smaller value such as 30%, to increase the minor compaction frequency and reduce the amount of data compacted in each minor compaction, so as to prevent the memory from being used up due to quick data writes.	Cluster	No
clog_sync_time_warn_threshold	The warning threshold of time consumed for synchronizing transaction logs. When the consumed time reaches the threshold, a WARN-level log is generated.	1s	[1ms, 10000ms]	1s	Cluster	No
major_compact_trigger	The number of minor compactions that triggers a global major compaction. This parameter is equivalent to the `minor_freeze_times` parameter. The value `0` specifies to disable minor compactions.	5	[0, 65535]	50. Note Specify the parameter based on the actual amount of incremental data generated every day. Do not trigger major compactions during work hours or frequently.	Cluster	No
memstore_limit_percentage	The percentage of the memory that can be occupied by the MemStore to the total available memory of the tenant. The memory occupied by the MemStore is calculated as follows: min_memory × memstore_limit_percentage, where `min_memory` is specified when you create the tenant.	50	[1, 99]	80. Note For large-scale tenants, such as those with 32 CPU cores and 256 GB of memory, or tenants with more writes than reads, you can increase the value of `memstore_limit_percentage` to cache more updated data, so as not to frequently trigger minor compactions.	Cluster	No
minor_freeze_times	The number of minor compactions that triggers a global major compaction. This parameter is equivalent to the `major_compact_trigger` parameter. The value `0` specifies to disable minor compactions.	5	[0, 65535]	50. Note Specify the parameter based on the actual amount of incremental data generated every day. Do not trigger major compactions during work hours or frequently.	Cluster	No
trace_log_slow_query_watermark	The threshold of the query execution duration. A query whose execution duration exceeds this threshold is considered a slow query. Trace logs of slow queries are written to system logs.	1s	[1ms,+∞)	100ms	Cluster	No
enable_syslog_recycle	Specifies whether to record the logs generated before the startup of the OBServer node. You can use this parameter with `max_syslog_file_count` to specify whether to include earlier log files in the recycling logic.	False	True False	True. Note This value specifies to enable automatic system log recycling, so as to prevent log files from occupying disk space.	Cluster	No
max_syslog_file_count	The maximum number of system 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.	0	[0, +∞)	500. Note Specify the parameter based on the actual reserved space and log retention strategy. Each log file can occupy at most 256 MB of disk space. You need to specify the maximum log file size based on the actual disk capacity to avoid using up the disk space.	Cluster	No
enable_rebalance	Specifies whether to enable automatic load balancing.	True	True False	True	Cluster	No
large_query_threshold	The execution time threshold to identify a large query. A request may be suspended if its execution time exceeds this threshold. A suspended request is considered a large query and is processed by following the large query scheduling strategy.	5s	[1ms, +∞)	5s	Cluster	No
writing_throttling_trigger_percentage	The threshold of write speed that triggers throttling. When the memory used by the MemStore reaches the threshold, the write speed limit is triggered. If this parameter is set to `100`, write throttling is disabled.	100	[1, 100]	80. Note In a scenario with a small tenant memory size or a high write speed, we recommend that you set the parameter to `80` or `90` to prevent the space of the MemStore from being used up. In addition, the value of `writing_throttling_trigger_percentage` must be greater than that of `freeze_trigger_percentage`.	Tenant	No

Configuration examples

Configure cluster-level parameters in the sys tenant.

obclient> ALTER SYSTEM SET system_memory = '30G';
obclient> ALTER SYSTEM SET rootservice_memory_limit = '2G';
obclient> ALTER SYSTEM SET cpu_quota_concurrency = '4';
obclient> ALTER SYSTEM SET enable_merge_by_turn = FALSE;
obclient> ALTER SYSTEM SET freeze_trigger_percentage = '30';
obclient> ALTER SYSTEM SET clog_sync_time_warn_threshold = '1s';
obclient> ALTER SYSTEM SET major_compact_trigger = '50';
obclient> ALTER SYSTEM SET memstore_limit_percentage = '80';
obclient> ALTER SYSTEM SET minor_freeze_times = '50';
obclient> ALTER SYSTEM SET trace_log_slow_query_watermark = '100ms';
obclient> ALTER SYSTEM SET enable_syslog_recycle = TRUE;
obclient> ALTER SYSTEM SET max_syslog_file_count = '500';
obclient> ALTER SYSTEM SET enable_rebalance = TRUE;
obclient> ALTER SYSTEM SET large_query_threshold = '5s';

To configure tenant-level parameters in a user tenant, execute the following statements:

MySQL tenant

Oracle tenant

obclient> ALTER SYSTEM SET large_query_threshold = '5s';

obclient> ALTER SYSTEM SET large_query_threshold = '5s';

Recommended variable configurations

You also need to specify variables while specifying parameters. The following table lists the recommended variable configurations.

Variable	Description	Default value	Value range	Recommended value	Applicable scope
recyclebin	Specifies whether to enable the recycle bin. If the recycle bin is enabled, a deleted table and objects subordinated to the table are stored in the recycle bin. If the recycle bin is disabled, a deleted table and objects subordinated to the table are removed directly without being stored in the recycle bin.	OFF	`OFF` (0): specifies not to enable the recycle bin. `ON` (1): specifies to enable the recycle bin.	OFF	Global Session
ob_query_timeout	Specifies the timeout period in microseconds for SQL DML operations.	10000000	[0, 3216672000000000]	10000000	Global Session
ob_trx_idle_timeout	Specifies the idle timeout duration in microseconds in which no transactions are executed. A timeout occurs when the execution interval between two statements exceeds the value of this parameter.	120000000	[0,+∞)	120000000	Global Session
ob_trx_timeout	The transaction timeout period in microseconds.	100000000	[0, 3216672000000000]	100000000	Global Session
undo_retention	The time range in seconds of data versions to be retained by the system. This variable is used to control the collection of data of multiple versions in minor compactions. When `undo_retention` is set to `0`, multi-version minor compaction is disabled, which indicates that only the latest version of row data is retained in the minor compaction file. When `undo_retention` is set to a value greater than 0, multi-version minor compaction is enabled, and multiple versions of row data within the specified period in seconds are retained in the minor compaction file. Notice After multi-version minor compaction is enabled for the tenant, the corresponding incremental minor compaction files are retained in a major compaction, but multi-version data is not stored in the Major SSTable to prevent the number of retained SSTable files from exceeding the limit, we recommend that you do not set this parameter to an excessively large value.	1800	[0, 2^32)	0	Global
ob_sql_work_area_percentage	The percentage of the SQL workspace memory to the total memory of the tenant. The `ob_sql_work_area_percentage` parameter specifies the memory required by the blocking operators, such as SQL Sort, in the SQL statements that may consume a lot of memory.	5	[0, 100]	5. Note When the tenant memory is small in size, you can set this parameter to a greater value, such as `20`. You can also increase the parameter value in read-only or analytical processing (AP) scenarios.	Global
parallel_servers_target	Specifies the queuing condition for parallel queries on each server. If this variable is set to `0`, the queuing condition is calculated based on the maximum number of CPUs of the tenant. When the number of threads taken by Parallel eXecution (PX) of multiple SQL queries exceeds the specified queuing condition, subsequent SQL queries for PX must wait in a queue.	0	[0, 9223372036854775807]	Number of CPU cores of the tenant × 10	Global
max_allowed_packet	The maximum size in bytes of a network packet. Notice You can use the `SET GLOBAL` statement to modify the global value of the variable. However, you cannot use the `ALTER SESSION SET` statement to modify a session value of the variable. You can only view the session value. The session value must be the same as the global value. Generally, this variable must be adjusted both in the client and the server.	4194304	[1024,1073741824]	4194304. Note You can increase the value in scenarios where large amounts of data is to be updated, such as data migration and large queries.	Global Session
ob_timestamp_service	The timestamp service. Notice If this variable is set to `LTS` for a user tenant, the tenant does not support cross-server strong-consistency reads or distributed transactions.	The value is `LTS` for the `sys` tenant. The value is `GTS` for a user tenant.	`LTS`: During transaction execution, the local timestamp on the server is used as the snapshot for transactions. Access across partitions is not supported. `GTS`: During transaction execution, a global timestamp is obtained from GTS as the snapshot and commit version number of transactions. The use purpose is not limited.	GTS	Global
nls_date_format	The format of converting a date to a string and the format of implicitly converting a string to a date. This parameter applies only to the Oracle mode.	DD-MON-RR	N/A	DD-MON-RR	Global Session

Configuration examples

MySQL tenant

Oracle tenant

obclient> SET GLOBAL recyclebin=OFF;
obclient> SET GLOBAL ob_query_timeout=10000000;
obclient> SET GLOBAL ob_trx_idle_timeout=120000000;
obclient> SET GLOBAL ob_trx_timeout=100000000;
obclient> SET GLOBAL undo_retention=0;
obclient> SET GLOBAL ob_sql_work_area_percentage=5;
obclient> SET GLOBAL parallel_servers_target=<Number of CPU cores of the tenant × 10>;
obclient> SET GLOBAL max_allowed_packet=4194304;
obclient> SET GLOBAL ob_timestamp_service=GTS;
obclient> SET GLOBAL nls_date_format='DD-MON-RR';

obclient> SET GLOBAL recyclebin=OFF;
obclient> SET GLOBAL ob_query_timeout=10000000;
obclient> SET GLOBAL ob_trx_idle_timeout=120000000;
obclient> SET GLOBAL ob_trx_timeout=100000000;
obclient> SET GLOBAL undo_retention=0;
obclient> SET GLOBAL ob_sql_work_area_percentage=5;
obclient> SET GLOBAL parallel_servers_target=<Number of CPU cores of the tenant × 10>;
obclient> SET GLOBAL max_allowed_packet=4194304;
obclient> SET GLOBAL ob_timestamp_service=GTS;
obclient> SET GLOBAL nls_date_format='DD-MON-RR';

Recommended configurations in complex OLTP scenarios

Background information

Configuration template

Recommended configurations for OceanBase Database V4.3.2

Recommended parameter configurations

Configuration examples

Recommended configurations for OceanBase Database V3.x

Recommended parameter configurations

Note

Note

Note

Note

Note

Note

Note

Note

Note

Configuration examples

Recommended variable configurations

Notice

Note

Notice

Note

Notice

Configuration examples