Default production parameters

This topic provides recommended configurations of default production parameters for hardware configurations, 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 Root Service.

  • Recommended value: 2G

  • Value range: [2G,+∞)

  • Example:

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

• 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: (0, 100)

  • Example:

    obclient> ALTER SYSTEM SET memstore_limit_percentage=80;
    

• 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: 100ms

  • 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

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

  • Recommended value: 20

  • Value range: (0, 100)

  • Example:

    obclient> ALTER SYSTEM SET freeze_trigger_percentage=20;
    

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

• 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: 1800

  • Value range: [0, 4294967295]

  • Example:

    obclient> ALTER SYSTEM SET undo_retention=1800;
    

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

System variables

• 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: 86400000000

  • Value range: [100000000,+∞)

  • Example:

    obclient> SET GLOBAL ob_trx_idle_timeout=120000000;
    

• ob_trx_timeout: specifies the transaction timeout duration in microseconds.

  • Recommended value: 86400000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_trx_timeout=100000000;
    

• 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 queuing conditions for large queries on each server. When the number of threads occupied for parallel execution (PX) exceeds the specified value, the subsequent large queries must wait in a queue for parallel execution.

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

• 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

• 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';
    

• 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: (0, 100)

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

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

  • Recommended value: 24

  • Value range: [1,64]

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

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

• 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

• 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, 4294967295]

  • Example:

    obclient> ALTER SYSTEM SET undo_retention=900;
    

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: 86400000000

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

• 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 queuing conditions for large queries on each server. When the number of threads occupied for parallel execution (PX) exceeds the specified value, the subsequent large queries must wait in a queue for parallel execution.

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