Configure general parameters

This topic describes the general system parameters and variables in OceanBase Database.

Cluster-level parameters

• memory_limit: specifies the maximum memory that the OBServer node can use. The value of memory_limit is unlimited. If memory_limit is set to a value greater than the total physical memory, other values calculated based on memory_limit will be excessively large.

  • Default value: 0.

  • Value range: [0M, +∞)

  • Example:

    obclient> ALTER SYSTEM SET memory_limit='50G';
    

  Note

  You must modify memory_limit in conjunction with memory_limit_percentage.

• system_memory: specifies the size of the system memory space reserved for virtual tenants.

  • Default value: 30G

  • Value range: [0M, +∞)

  • Example:

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

  Note

  For more information, see system_memory.

• rootservice_memory_limit: specifies the maximum memory available to RootService.

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

  • Default value: 4.

  • Value range: [1, 10]

  • Example:

    obclient> ALTER SYSTEM cpu_quota_concurrency=4;
    

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

  • Default value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM enable_merge_by_turn = True;
    

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

  • Default value: 70

  • Value range: [1, 99]

  • Example:

    obclient> ALTER SYSTEM freeze_trigger_percentage=70;
    

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

  • Default value: 5.

  • Value range: [0, 65535]

  • Example:

    obclient> ALTER SYSTEM major_compact_trigger=5;
    

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

  • Default value: 50

  • Value range: [1, 99]

  • Example:

    obclient> ALTER SYSTEM memstore_limit_percentage=50;
    

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

  • Default value: 5.

  • Value range: [0, 65535]

  • Example:

    obclient> ALTER SYSTEM 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.

  • Default value: 1s

  • Value range: [1ms, +∞)

  • Example:

    obclient> ALTER SYSTEM trace_log_slow_query_watermark='100ms';
    

• enable_syslog_recycle: specifies whether to enable system log recycling.

  • Default value: False

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM enable_syslog_recycle=False;
    

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

  • Default value: 0.

  • Value range: [0, +∞)

  • Example:

    obclient> ALTER SYSTEM max_syslog_file_count=0;
    

• enable_sql_audit: specifies whether to enable SQL Audit.

  • Default value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM enable_sql_audit=True;
    

• clog_disk_usage_limit_percentage: specifies the maximum disk I/O usage for transaction logs.

  • Default value: 95

  • Value range: [80, 100]

  • Example:

    obclient> ALTER SYSTEM clog_disk_usage_limit_percentage=95;
    

• datafile_disk_percentage: specifies the percentage of occupied space of the disk where the data_dir directory resides. The disk of the data_dir directory will be initialized by OceanBase Database to store data.

  • Default value: 90

  • Value range: [5, 99]

  • Example:

    obclient> ALTER SYSTEM datafile_disk_percentage=90;
    

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

  • Default value: 0.

  • Value range: [0, 256]

  • Example:

    obclient> ALTER SYSTEM merge_thread_count=0;
    

• enable_rebalance: specifies whether to enable automatic load balancing.

  • Default value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM enable_rebalance=True;
    

• resource_hard_limit: specifies how resource units are allocated.

  • Default value: 100

  • Value range: (0, 10000]

  • Example:

    obclient> ALTER SYSTEM resource_hard_limit=100;
    

• resource_soft_limit: specifies whether to enable automatic unit balancing. When the value of resource_soft_limit is less than 100, unit balancing is enabled. When the value of resource_soft_limit is greater than or equal to 100, unit balancing is disabled.

  • Default value: 50

  • Value range: (0, 10000]

  • Example:

    obclient> ALTER SYSTEM resource_soft_limit=50;
    

• auto_leader_switch_interval: specifies the working interval of the background thread after an automatic switchover to the leader.

  • Default value: 30s

  • Value range: [1s, +∞).

  • Example:

    obclient> ALTER SYSTEM auto_leader_switch_interval='30s';
    

• enable_auto_leader_switch: specifies whether to enable automatic switchover to the leader.

  • Default value: True

  • Valid values: True and False

  • Example:

    obclient> ALTER SYSTEM enable_auto_leader_switch=True;
    

• stack_size: specifies the size of the function call stack for programs.

  • Default value: 1M

  • Value range: [512K, 20M]

  • Example:

    obclient> ALTER SYSTEM stack_size='1M';
    

• syslog_level: specifies the level of system logs to be printed.

  • Default value: WDIAG

  • Valid values: DEBUG, TRACE, WDIAG, EDIAG, INFO, WARN, ERROR, and PERF.

  • Example:

    obclient> ALTER SYSTEM enable_auto_leader_switch=INFO;
    

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.

  • Default value: 100

  • Value range: [0, 100].

  • Example:

    obclient> ALTER SYSTEM SET writing_throttling_trigger_percentage=100;
    

• audit_trail: specifies whether to enable database auditing.

  • Default value: NONE

  • Valid values:

    • NONE

    • OS

    • DB

    • DB, EXTENDED

    • DB_EXTENDED

  • Example:

    obclient> ALTER SYSTEM SET audit_trail=NONE;
    

System variable

• recyclebin: specifies whether to enable the recycle bin.

  • Default value: OFF

  • Valid values:

    • 1 or ON

    • 0 or OFF

  • Example:

    obclient> SET GLOBAL recyclebin=OFF;
    

    or

    obclient> SET GLOBAL recyclebin=0;
    

• ob_query_timeout: specifies the query timeout duration in microseconds.

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

  • Default value: 120000000

  • Value range: [100000000, +∞)

  • Example:

    obclient> SET GLOBAL ob_trx_idle_timeout=120000000;
    

• ob_trx_timeout: specifies the transaction timeout duration in microseconds.

  • Default value: 100000000

  • Value range: N/A

  • Example:

    obclient> SET GLOBAL ob_trx_timeout=100000000;
    

• ob_read_consistency: specifies the read consistency level.

  • Default value: STRONG

  • Valid values:

    • 0: An empty string

    • 1: FROZEN

    • 2: WEAK

    • 3: STRONG

  • Example:

    obclient> SET GLOBAL ob_read_consistency=STRONG;
    

    or

    obclient> SET GLOBAL ob_read_consistency=3;
    

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

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

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

  • Default value: 0.

  • Value range: [0, 9223372036854775807]

  • Example:

    obclient> SET GLOBAL parallel_servers_target=0;
    

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

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

  • Default value: DD-MON-RR

  • Value range: N/A

  • Example:

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

• autocommit: specifies whether to automatically commit transactions.

  • Default value: ON

  • Valid values:

    • 1 or ON

    • 0 or OFF

  • Example:

    obclient> SET GLOBAL autocommit=ON;
    

    or

    obclient> SET GLOBAL autocommit=1;
    

• ob_enable_plan_cache: specifies whether to enable the plan cache.

  • Default value: ON

  • Valid values:

    • 1 or ON

    • 0 or OFF

  • Example:

    obclient> SET GLOBAL ob_enable_plan_cache=ON;
    

    or

    obclient> SET GLOBAL ob_enable_plan_cache=1;
    

• ob_plan_cache_percentage: specifies the percentage of the tenant memory that can be used by the plan cache.

  • Default value: 5.

  • Value range: [0, 100].

  • Example:

    obclient> SET GLOBAL ob_plan_cache_percentage=5;
    

• ob_sql_audit_percentage: specifies the maximum percentage of the SQL audit memory to the total memory of the current tenant.

  • Default value: 3.

  • Value range: [0, 80].

  • Example:

    obclient> SET GLOBAL ob_sql_audit_percentage=3;
    

• ob_enable_sql_audit: specifies whether to enable SQL auditing for the current tenant. When the parameter is set to False, SQL audit is disabled for all tenants.

  • Default value: True

  • Valid values: True and False

  • Example:

    obclient> SET GLOBAL ob_enable_sql_audit=True;
    

• transaction_isolation: specifies the isolation level of transactions.

  • Default value: READ-COMMITTED

  • Valid values: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, and SERIALIZABLE

  • Example:

    obclient> SET GLOBAL transaction_isolation='READ-COMMITTED';
    

• validate_password_check_user_name: specifies whether the user account password can be the same as the username. When the value is ON, a password cannot be the same as the username. When the value is OFF, a password can be the same as the username.

  • Default value: OFF

  • Valid values: ON and OFF

  • Example:

    obclient> SET GLOBAL validate_password_check_user_name=ON;
    

• validate_password_length: specifies the minimum length of a user account password.

  • Default value: 0.

  • Value range: [0, 2147483647]

  • Example:

    obclient> SET GLOBAL validate_password_length=0;
    

• validate_password_mixed_case_count: specifies the minimum number of uppercase letters and the minimum number of lowercase letters that a user account password must contain.

  • Default value: 0.

  • Value range: [0, 2147483647]

  • Example:

    obclient> SET GLOBAL validate_password_mixed_case_count=0;
    

• validate_password_number_count: specifies the minimum number of digits that a user account password must contain.

  • Default value: 0.

  • Value range: [0, 2147483647]

  • Example:

    obclient> SET GLOBAL validate_password_number_count=0;
    

• validate_password_policy: specifies the password check strategy. The value LOW indicates to implement only general password checks. The value Medium indicates to check the password length, the number of uppercase letters, lowercase letters, digits, special characters, and the password resemblance to the username.

  • Default value: LOW

  • Valid values: LOW and MEDIUM

  • Example:

    obclient> SET GLOBAL validate_password_policy=LOW;
    

• validate_password_special_char_count: specifies the minimum number of special characters that a user account password must contain.

  • Default value: 0.

  • Value range: [0, 2147483647]

  • Example:

    obclient> SET GLOBAL validate_password_special_char_count=0;
    

• resource_manager_plan: specifies whether to activate the resource management plan. The CPU quota for limiting the user activities in a resource group varies based on the resource management plan.

  Regular and parallel execution threads within a tenant compete for CPU resources. By specifying resource isolation plans for tenants, you can control the strategies for resource contention and limit resource usage.

  • Default value: ""

  • Value range: N/A

  • Example:

    Create multiple resource management plans for each tenant, such as DAYTIME and NIGHT. Then, you can activate different plans for different periods or application scenarios to maximize system performance.

    delimiter//
    
    # Define the DAYTIME resource management plan
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN(
      PLAN => 'DAYTIME');
    end; //
    
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN => 'DAYTIME',
      GROUP_OR_SUBPLAN => 'interactive_group' ,
      COMMENT => 'Prioritize transaction processing (TP)',
      MGMT_P1 => 30);
    end; //
    
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN => 'DAYTIME',
      GROUP_OR_SUBPLAN => 'batch_group' ,
      COMMENT => 'Allocate no more than 40% of CPU resources for analytical processing (AP), even with zero TP load.',
      MGMT_P1 => 20,
      UTILIZATION_LIMIT => 40);
    end; //
    
    
    # Define the NIGHT resource management plan
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN(
      PLAN => 'NIGHT');
    end; //
    
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN => 'NIGHT',
      GROUP_OR_SUBPLAN => 'interactive_group' ,
      COMMENT => 'Minimize resources for TP',
      MGMT_P1 => 10);
    end; //
    
    begin DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN => 'NIGHT',
      GROUP_OR_SUBPLAN => 'batch_group' ,
      COMMENT => 'Allocate no more than 90% of CPU resources for AP, even with zero TP load.',
      MGMT_P1 => 90,
      UTILIZATION_LIMIT => 90);
    end; //
    
    # Letters in the username are automatically capitalized. To use lowercase letters, enclose the username in quotation marks. For example, specify it in the '"test001"' format.
    BEGIN
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
         (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'test001', 'batch_group');
    END;//
    
    select * from DBA_RSRC_GROUP_MAPPINGS;//
    
    
    # Execute the following statement to activate the DAYTIME plan for the daytime to prioritize TP tasks:
    SET GLOBAL resource_manager_plan='DAYTIME';  /MySQL mode
    ALTER SYSTEM SET resource_manager_plan = 'DAYTIME';  /Oracle mode
    
    # Execute the following statement to activate the NIGHT plan for the night time to prioritize batch/AP tasks:
    SET GLOBAL resource_manager_plan='NIGHT';  /MySQL mode
    ALTER SYSTEM SET resource_manager_plan = 'NIGHT'; /Oracle mode
    
    # Disable all resource plans and limits.
    SET GLOBAL resource_manager_plan=''; //MySQL mode
    ALTER SYSTEM SET resource_manager_plan = '';   /Oracle mode