Recommended configurations

OceanBase summarizes the recommended configurations of core parameters and variables for various business scenarios based on its tuning experience from a large number of real scenarios. OceanBase Database can provide high performance in various business scenarios based on the recommended configurations. OceanBase Cloud Platform (OCP) allows you to select a parameter configuration template during tenant creation. OCP reads parameter values from the RPM package of the corresponding kernel version based on the selected configuration template. For more information about how to create a tenant by using OCP, see Create a primary tenant.

This topic provides you with recommended configurations of core parameters and system variables for various business scenarios. You can use these configurations after you deploy OceanBase Database with a command-line interface (CLI) tool.

Configuration templates

You can find the templates of recommended configurations for diversified business scenarios under the /home/admin/oceanbase/etc directory after you install the RPM package of OceanBase Database.

• default_parameter.json: stores the recommended configurations of parameters.
• default_system_variable.json: stores the recommended configurations of variables.

The templates in parameter configuration files are classified as follows:

• express_oltp: This template is suitable for workloads like transaction, payment core systems, and high-throughput internet applications. It has no foreign key constraints, stored procedures, long transactions, large transactions, complex joins, or complex subqueries. For more information about the recommended configurations, see express_oltp.

• complex_oltp: This template is suitable for workloads like banking and insurance systems, usually involving complex joins, complex correlated subqueries, batch jobs written in PL, and long and large transactions. Parallel execution may be used for short-term queries. For more information about the recommended configurations, see complex_oltp.

• olap: This template is mainly used for real-time data warehouse analysis applications. For more information about the recommended configurations, see olap.

• htap: This template is suitable for mixed OLAP and OLTP workloads, including but not limited to real-time operational data analysis, fraud detection, and personalized recommendations. For more information about the recommended configurations, see htap.

• kv: This template is suitable for HBase-compatible and Key-Value table model access modes, with single-partition access and no SQL layer. This scenario typically involves very high data throughput demands and is very latency-sensitive. For more information about the recommended configurations, see kv.

express_oltp

No configurations are recommended for variables in this scenario.

Recommended values of the parameters in the express_oltp scenario are described as follows:

Example of an express OLTP scenario

1. Execute the following statement in the sys tenant to configure the cluster-level parameter.

   ALTER SYSTEM SET enable_record_trace_log = False;
   ALTER SYSTEM SET enable_syslog_recycle = True;
   ALTER SYSTEM SET max_syslog_file_count = 300;
   

2. Execute the following statement in the user tenant to configure the tenant-level parameter.

   • Here is an example of configuring a MySQL tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     ALTER SYSTEM SET _enable_filter_reordering = False;
     

   • Here is an example of configuring an Oracle tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     ALTER SYSTEM SET "_enable_filter_reordering" = False;
     

complex_oltp

No configurations are recommended for variables in this scenario.

Recommended values of the parameters in the complex_oltp scenario are described as follows:

Examples

1. Execute the following statement in the sys tenant to configure the cluster-level parameter.

   ALTER SYSTEM SET enable_record_trace_log = False;
   ALTER SYSTEM SET large_query_threshold = '600s';
   ALTER SYSTEM SET enable_syslog_recycle = True;
   ALTER SYSTEM SET max_syslog_file_count = 300;
   

2. Execute the following statement in the user tenant to configure the tenant-level parameter.

   • Here is an example for a MySQL tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     ALTER SYSTEM SET _enable_filter_reordering = False;
     

   • Here is an example for an Oracle tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     ALTER SYSTEM SET "_enable_filter_reordering" = False;
     

olap

Recommended values of the parameters in the olap scenario are described as follows:

The following table describes the recommended values of the variables in the olap scenario:

Examples

1. Execute the following statement in the sys tenant to configure the cluster-level parameter.

   ALTER SYSTEM SET enable_record_trace_log = False;
   ALTER SYSTEM SET trace_log_slow_query_watermark = '7d';
   ALTER SYSTEM SET large_query_threshold = '0ms';
   ALTER SYSTEM SET enable_syslog_recycle = True;
   ALTER SYSTEM SET max_syslog_file_count = 300;
   

2. Execute the following statement in the user tenant to configure the tenant-level parameter.

   • Here is an example of configuring a MySQL tenant:

     ALTER SYSTEM SET default_table_store_format = 'column';
     ALTER SYSTEM SET _io_read_batch_size = '128K';
     ALTER SYSTEM SET _io_read_redundant_limit_percentage = 50;
     ALTER SYSTEM SET default_load_mode = 'FULL_DIRECT_WRITE';
     ALTER SYSTEM SET direct_load_allow_fallback = False;
     ALTER SYSTEM SET _nested_loop_join_enabled = False;
     ALTER SYSTEM SET default_table_organization = 'HEAP';
     

   • Here is an example of configuring an Oracle tenant:

     ALTER SYSTEM SET default_table_store_format = 'column';
     ALTER SYSTEM SET "_io_read_batch_size" = '128K';
     ALTER SYSTEM SET "_io_read_redundant_limit_percentage" = 50;
     ALTER SYSTEM SET default_load_mode = 'FULL_DIRECT_WRITE';
     ALTER SYSTEM SET direct_load_allow_fallback = False;
     ALTER SYSTEM SET "_nested_loop_join_enabled" = False;
     ALTER SYSTEM SET default_table_organization = 'HEAP';
     

3. Execute the following statement in the user tenant to configure the tenant-level variable.

   • Here is an example of configuring a MySQL tenant:

     SET GLOBAL ob_query_timeout = 604800000000;
     SET GLOBAL ob_trx_timeout = 604800000000;
     SET GLOBAL parallel_degree_policy = AUTO;
     SET GLOBAL parallel_min_scan_time_threshold = 10;
     SET GLOBAL ob_sql_work_area_percentage = 30;
     SET GLOBAL collation_server = utf8mb4_bin;
     SET GLOBAL collation_connection = utf8mb4_bin;
     

   • Here is an example of configuring an Oracle tenant:

     SET GLOBAL ob_query_timeout = 604800000000;
     SET GLOBAL ob_trx_timeout = 604800000000;
     SET GLOBAL parallel_degree_policy = AUTO;
     SET GLOBAL parallel_min_scan_time_threshold = 10;
     SET GLOBAL ob_sql_work_area_percentage = 30;
     

htap

No configurations are recommended for variables in this scenario.

Recommended values of the parameters in the htap scenario are described as follows:

HTAP scenarios

1. Execute the following statement in the sys tenant to configure a cluster-level parameter.

   ALTER SYSTEM SET large_query_threshold = '600s';
   ALTER SYSTEM SET enable_record_trace_log = False;
   ALTER SYSTEM SET enable_syslog_recycle = True;
   ALTER SYSTEM SET max_syslog_file_count = 300;
   

2. Execute the following statement in the user tenant to configure a tenant-level parameter.

   • Here is an example of configuring a parameter in MySQL mode:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     

   • Here is an example of configuring a parameter in Oracle mode:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET default_load_mode = 'DISABLED';
     ALTER SYSTEM SET direct_load_allow_fallback = True;
     

kv

No configurations are recommended for variables in this scenario.

Recommended values of the parameters in the kv scenario are described as follows:

Examples

1. Execute the following statement in the sys tenant to configure the cluster-level parameters.

   ALTER SYSTEM SET enable_record_trace_log = False;
   ALTER SYSTEM SET large_query_threshold = '0ms';
   ALTER SYSTEM SET enable_syslog_recycle = True;
   ALTER SYSTEM SET max_syslog_file_count = 300;
   

2. Execute the following statement in the user tenant to configure the tenant-level parameters.

   • Here is an example for a MySQL tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET _enable_filter_reordering = False;
     

   • Here is an example for an Oracle tenant:

     ALTER SYSTEM SET log_transport_compress_all = True;
     ALTER SYSTEM SET "_enable_filter_reordering" = False;
     

Query parameter and variable information

• You can execute the SHOW PARAMETERS LIKE 'parameter_name'; statement to query the information about a parameter.

  Here is an example:

  SHOW PARAMETERS LIKE 'enable_syslog_recycle';
  

• You can execute the following statements to query the information about a variable.

  • MySQL mode: You can execute the SHOW [GLOBAL] VARIABLES LIKE 'variables_name'; statement.

    Here is an example:

    SHOW VARIABLES LIKE 'ob_query_timeout';
    

    SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
    

  • Oracle mode: You can query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE/SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view for the information about a variable.

    Here is an example:

    SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
    

    SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
    

References

• For more information about the parameters and variables, see Overview of parameters and system variables.
• For more information about how to set the parameters, see Set parameters.
• For more information about how to set the variables, see Set variables.