Examples of parameter configuration in a production environment

This topic describes the recommended configuration of default production parameters in terms of hardware, transaction processing (TP) scenarios, and analytical processing (AP) scenarios.

Typical hardware configuration

The server configurations vary by users based on different production standards. This topic mainly uses the hardware configurations listed in the following table as the typical configuration.

TP-based scenarios

Cluster-level parameters

• system_memory: specifies the size of the system memory space reserved for the sys500 tenant.

  • Recommended value: 30G

  • Value range: [0M, +∞)

  • Example:

    obclient> ALTER SYSTEM SET system_memory='30G';
    

• rootservice_memory_limit: specifies the maximum memory available to RootService.

  • Recommended value: 2G

  • Value range: [2G, +∞)

  • Example:

    obclient> ALTER SYSTEM SET rootservice_memory_limit='2G';
    

• cpu_quota_concurrency: specifies the maximum concurrency allowed for each CPU quota of a tenant.

  • Recommended value: 4

  • Value range: [1, 10]

  • Example:

    obclient> ALTER SYSTEM SET cpu_quota_concurrency=4;
    

• enable_merge_by_turn: specifies whether to enable the rotating compaction strategy.

  • Recommended value: False

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_merge_by_turn=False;
    

• freeze_trigger_percentage: specifies the threshold of memory used by tenants for triggering a global freeze.

  • Recommended value: 30

  • Value range: [1, 99]

  • Example:

    obclient> ALTER SYSTEM SET freeze_trigger_percentage=30;
    

• major_compact_trigger: specifies the number of minor compactions for triggering a global major compaction.

  • Recommended value: 50

  • Value range: [0, 65535]

  • Example:

    obclient> ALTER SYSTEM SET major_compact_trigger=50;
    

• memstore_limit_percentage: specifies the percentage of the memory occupied by the MemStore among the total available memory of a tenant.

  • Recommended value: 80

  • Value range: [1, 99]

  • Example:

    obclient> ALTER SYSTEM SET memstore_limit_percentage=80;
    

• minor_freeze_times: specifies the number of minor compactions for triggering a global major compaction. If the value is 0, minor compaction is disabled.

  • Recommended value: 50

  • Value range: [0, 65535]

  • Example:

    obclient> ALTER SYSTEM SET minor_freeze_times=50;
    

• trace_log_slow_query_watermark: specifies the query execution time threshold. A query whose execution time exceeds this threshold is considered a slow query. Trace logs of slow queries are written to system logs.

  • Recommended value: 1s

  • Value range: [1ms, +∞)

  • Example:

    obclient> ALTER SYSTEM SET trace_log_slow_query_watermark='100ms';
    

• enable_syslog_recycle: specifies whether to enable system log recycling.

  • Recommended value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_syslog_recycle=True;
    

• max_syslog_file_count: specifies the maximum number of log files that can be retained.

  • Recommended value: 500

  • Value range: [0, +∞)

  • Example:

    obclient> ALTER SYSTEM SET max_syslog_file_count=500;
    

• enable_rebalance: specifies whether to enable automatic load balancing.

  • Recommended value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_rebalance=True;
    

Tenant-level parameters

• writing_throttling_trigger_percentage: specifies the upper limit of the write speed. If the value of this parameter is set to 100, write throttling is disabled.

  • Recommended value: 80

  • Value range: [0, 100].

  • Example:

    obclient> Alter SYSTEM SET writing_throttling_trigger_percentage=80;
    

System variable

• recyclebin: specifies whether to enable the recycle bin.

  • Recommended value: OFF

  • Valid values: ON and OFF

  • Example:

    obclient> SET GLOBAL recyclebin=OFF;
    

• ob_query_timeout: specifies the query timeout duration in microseconds.

  • Recommended value: 10000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_query_timeout=10000000;
    

• 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.

  • Recommended value: 120000000

  • Value range: [100000000, +∞)

  • Example:

    obclient> SET GLOBAL ob_trx_idle_timeout=120000000;
    

• ob_trx_timeout: specifies the transaction timeout duration in microseconds.

  • Recommended value: 100000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_trx_timeout=100000000;
    

• undo_retention: specifies 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.

  • Recommended value: 0

  • Value range: [0, 2^32^)

  • Example:

    obclient> SET GLOBAL undo_retention=0;
    

• ob_sql_work_area_percentage: specifies the maximum percentage of tenant memory for SQL execution.

  • Recommended value: 5

  • Value range: [0, 100].

  • Example:

    obclient> SET GLOBAL ob_sql_work_area_percentage=5;
    

• parallel_servers_target: specifies the queue condition for parallel queries on each server. When the number of threads taken by parallel eXecution (PX) of multiple SQL queries exceeds the specified queue condition, subsequent SQL queries for PX must wait in a queue.

  • Recommended value: Number of tenant CPU cores × 10

  • Value range: [0, 9223372036854775807]

  • Example:

    obclient> SET GLOBAL parallel_servers_target=30;
    

• max_allowed_packet: specifies the maximum size in bytes of a network packet.

  • Recommended value: 4194304

  • Value range: [1024, 1073741824]

  • Example:

    obclient> SET GLOBAL max_allowed_packet=4194304;
    

• ob_timestamp_service: specifies the timestamp service.

  • Recommended value: GTS

  • Valid values: GTS, LTS, and HA_GTS

  • Example:

    obclient> SET GLOBAL ob_timestamp_service=GTS;
    

• nls_date_format: specifies the format of converting a date value to a string and the format of implicitly converting a string to a date value.

  Note

  This parameter applies only to the Oracle mode.

  • Recommended value: DD-MON-RR

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL nls_date_format='DD-MON-RR';
    

AP-based scenarios

Cluster-level parameters

• server_data_copy_out_concurrency: specifies the maximum number of concurrent tasks of migrating data out of a single node.

  • Recommended value: 1000

  • Value range: [1, 1000]

  • Example:

    obclient> ALTER SYSTEM SET server_data_copy_out_concurrency=1000;
    

• server_data_copy_in_concurrency: specifies the maximum number of concurrent tasks of migrating data a single node.

  • Recommended value: 1000

  • Value range: [1, 1000]

  • Example:

    obclient> ALTER SYSTEM SET server_data_copy_in_concurrency=1000;
    

• minor_freeze_times: specifies the number of minor compactions for triggering a global major compaction. If the value is 0, minor compaction is disabled.

  • Recommended value: 5

  • Value range: [0, 65535]

  • Example:

    obclient> ALTER SYSTEM SET minor_freeze_times=5;
    

• trace_log_slow_query_watermark: specifies the query execution time threshold. A query whose execution time exceeds this threshold is considered a slow query. Trace logs of slow queries are written to system logs.

  • Recommended value: 10s

  • Value range: [1ms, +∞)

  • Example:

    obclient> ALTER SYSTEM SET trace_log_slow_query_watermark='10s';
    

• merger_switch_leader_duration_time: specifies the interval of follower-to-leader switchover in a daily compaction.

  • Recommended value: 0s

  • Value range: [0s, 30m]

  • Example:

    obclient> ALTER SYSTEM SET trace_log_slow_query_watermark='0s';
    

• memstore_limit_percentage: specifies the percentage of the memory occupied by the MemStore among the total available memory of a tenant.

  • Recommended value: 50

  • Value range: [1, 99]

  • Example:

    obclient> ALTER SYSTEM SET memstore_limit_percentage=50;
    

• large_query_worker_percentage: specifies the percentage of worker threads reserved for large queries.

  • Recommended value: 10

  • Value range: [0, 100].

  • Example:

    obclient> ALTER SYSTEM SET large_query_worker_percentage=10;
    

• enable_merge_by_turn: specifies whether to enable the rotating compaction strategy.

  • Recommended value: False

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_merge_by_turn=False;
    

• election_blacklist_interval: specifies the time interval in which the leader replica cannot be re-elected as the leader after it is dismissed.

  • Recommended value: 15s

  • Value range: [0s, 24h]

  • Example:

    obclient> ALTER SYSTEM SET election_blacklist_interval='15s';
    

• net_thread_count: specifies the number of network I/O threads.

  • Recommended value: 24

  • Valid range: [1, 100]

  • Example:

    obclient> ALTER SYSTEM SET net_thread_count=24;
    

• rpc_timeout: specifies the timeout duration of internal requests of the cluster in microseconds.

  • Recommended value: 1000000000

  • Value range: [0, +∞)

  • Example:

    obclient> ALTER SYSTEM SET rpc_timeout=1000000000;
    

• micro_block_merge_verify_level: specifies the validation level of macro blocks in a major compaction.

  • Recommended value: 0

  • Valid values:

    • 0: No verification is performed.

    • 1: The encoding algorithm is verified, and the encoded microblocks are read to ensure data accuracy.

    • 2: The encoding and compression algorithms are verified. In addition, the compressed blocks are decompressed to ensure data accuracy.

    • 3: The encoding and compression algorithms are verified, and lost write protection is implemented.

  • Example:

    obclient> ALTER SYSTEM SET micro_block_merge_verify_level=0;
    

• enable_sql_audit: specifies whether to enable SQL Audit.

  • Recommended value: False

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_sql_audit=False;
    

• enable_perf_event: specifies whether to enable the information collection feature for performance events.

  • Recommended value: False

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_perf_event=False;
    

• merge_thread_count: specifies the number of worker threads for a daily compaction.

  • Recommended value: 20

  • Value range: [0, 256]

  • Example:

    obclient> ALTER SYSTEM SET merge_thread_count=20;
    

• cache_wash_threshold: specifies the threshold that triggers cache clean-up. If the available memory is less than the specified value, the memory will be cleared.

  • Recommended value: 30GB

  • Value range: [0, +∞)

  • Example:

    obclient> ALTER SYSTEM SET cache_wash_threshold='30GB';
    

Tenant-level parameters

• enable_clog_persistence_compress: specifies whether to compress transaction logs for storage.

  • Recommended value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM SET enable_clog_persistence_compress=True;
    

• clog_persistence_compress_func: specifies the algorithm for compressing transaction logs for storage.

  • Recommended value: zstd_1.0

  • Valid values: none, lz4_1.0, zstd_1.0, and zstd_1.3.8

  • Example:

    obclient> ALTER SYSTEM SET clog_persistence_compress_func='zstd_1.0';
    

System variable

You may configure the basic variables for the sys tenant based on the following description:

• ob_query_timeout: specifies the query timeout duration in microseconds.

  • Recommended value: 10000000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_query_timeout=10000000000;
    

• ob_trx_timeout: specifies the transaction timeout duration in microseconds.

  • Recommended value: 100000000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_trx_timeout=100000000000;
    

You may configure the general variables for a user tenant based on the following description:

• undo_retention: specifies 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.

  • Recommended value: 900

  • Value range: [0, 2^32^)

  • Example:

    obclient> SET GLOBAL undo_retention=900;
    

• ob_sql_work_area_percentage: specifies the maximum percentage of tenant memory for SQL execution.

  • Recommended value: 70

  • Value range: [0, 100].

  • Example:

    obclient> SET GLOBAL ob_sql_work_area_percentage=70;
    

• parallel_servers_target: specifies the queue condition for parallel queries on each server. When the number of threads taken by parallel eXecution (PX) of multiple SQL queries exceeds the specified queue condition, subsequent SQL queries for PX must wait in a queue.

  • Recommended value: Number of tenant CPU cores × 30

  • Value range: [0, 9223372036854775807]

  • Example:

    obclient> SET GLOBAL parallel_servers_target=90;
    

• max_allowed_packet: specifies the maximum size in bytes of a network packet.

  • Recommended value: 67108864

  • Value range: [1024, 1073741824]

  • Example:

    obclient> SET GLOBAL max_allowed_packet=67108864;