Overview of parameters and system variables|V4.2.1|OceanBase Database| docs|Distributed Database

Overview of parameters and system variables

Last Updated：2026-07-15 03:49:09 Updated

This topic describes how to modify and query parameters and system variables in OceanBase Database, and explains the differences between their usage.

Parameters

You can use parameters to control load balancing, major compaction time, major compaction mode, resource allocation, and module switches for a cluster.

Parameter types

Parameters in OceanBase Database are divided into cluster-level parameters and tenant-level parameters.

Cluster-level parameters: These parameters apply to the entire OceanBase cluster. They are global in nature and are used to configure basic information, performance parameters, security options, and so on for the entire cluster. These parameters typically include options for data backup and recovery, load balancing, and other aspects. Cluster-level parameters are usually configured when the cluster starts and are not modified frequently after configuration.
Tenant-level parameters: These parameters apply to a single tenant or multiple tenants. They are used to configure and optimize a single tenant or multiple tenants specifically. These parameters typically include options for the storage engine, SQL execution strategy, access control, and other aspects. Tenant-level parameters can usually be configured during tenant creation and management and can be modified as needed at any time.

Note

Parameters generally take effect either dynamically or after a restart. Most parameters take effect dynamically, meaning they do not require a restart of the OBServer node to take effect.

Query parameters

To query whether a parameter is cluster-level or tenant-level, use the following method:

obclient> SHOW PARAMETERS LIKE 'max_syslog_file_count';
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone  | svr_type | svr_ip         | svr_port | name                  | data_type | value | info                                                                                                                            | section  | scope   | source  | edit_level        |
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone2 | observer | xx.xx.xx.xx    |     2882 | max_syslog_file_count | NULL      | 0     | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | xx.xx.xx.xx    |     2882 | max_syslog_file_count | NULL      | 0     | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | xx.xx.xx.xx    |     2882 | max_syslog_file_count | NULL      | 0     | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | xx.xx.xx.xx    |     2882 | max_syslog_file_count | NULL      | 0     | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | xx.xx.xx.xx    |     2882 | max_syslog_file_count | NULL      | 0     | specifies the maximum number of the log files that can co-exist before the log file recycling kicks in. Each log file can occupy at most 256MB disk space. When this value is set to 0, no log file will be removed. Range: [0, +∞) in integer   | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+
5 rows in set (0.02 sec)

Here, if the value in the scope column is CLUSTER, the parameter is cluster-level; if the value in the scope column is TENANT, the parameter is tenant-level.

System variables

You can use system variables to control various behaviors of the database system, such as cache size, concurrent connections, CPU usage, memory usage, and so on. System variables can also be used to configure various features of the database system.

System variable types

System variables in OceanBase Database are divided into global variables and session variables.

Global variables: Modifications made to global variables are shared by different users within the same tenant of the database. Modifications to global variables do not become invalid upon session exit. Furthermore, changes to global variables do not take effect immediately for currently open sessions; you must establish a new session for the changes to take effect.
Session variables: Modifications made to session variables affect only the current session. When a client connects to the database, the database automatically generates session variables based on global variables. Changes to session variables only take effect for the current session.

Query system variables

To query a system variable, use the following method:

In MySQL-compatible mode

obclient> SHOW VARIABLES LIKE 'ob_query_timeout';
obclient> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';

In Oracle-compatible mode

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

Distinguish between parameters and system variables

Comparison Items

Parameter

System Variables

Effective Scope It is divided into clusters, zones, servers, and tenants. Divided into global and session levels for tenants.

Effective Date

Dynamic effective: edit_level is dynamic_effective
Effective after restart: edit_level is static_effective

Setting a variable at the session level only takes effect for the current session and does not apply to other sessions.
Setting a variable at the global level does not take effect for the current session. It requires logging out and logging back in to establish a new session for the change to take effect.

How to modify

You can modify the value by using an SQL statement. Example:
ALTER SYSTEM SET schema_history_expire_time='1h';
You can also modify the value by using a startup parameter. Example:
cd /home/admin/oceanbase && ./bin/observer -o "schema_history_expire_time=1h"; </ li>

Notice

The syntax for setting ALTER SYSTEM SET XXX='True'/'False' is modified in Oracle-compatible mode. The following table describes the modifications.

Parameter	Value
True/False	Must be enclosed in single quotes in earlier versions than V4.2.1 BP5. In V4.2.1 BP5 and later versions, it can be enclosed in single quotes or not.

It can only be modified by using SQL statements. Example:

MySQL-compatible mode
SET ob_query_timeout = 20000000;
SET GLOBAL ob_query_timeout = 20000000;
Oracle-compatible mode
ALTER SESSION SET ob_query_timeout = 20000000;
ALTER SYSTEM SET ob_query_timeout = 20000000;
or
SET ob_query_timeout = 20000000;
SET GLOBAL ob_query_timeout = 20000000;

Query method

You can useSHOW PARAMETERSStatement query. Example:SHOW PARAMETERS LIKE 'schema_history_expire_time';

You can useSHOW [GLOBAL] VARIABLESStatement query. Example:

MySQL-compatible mode
SHOW VARIABLES LIKE 'ob_query_timeout';
SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
Oracle-compatible mode
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';

Persistence The configuration is persisted to internal tables and the configuration file. You can query this parameter in the /home/admin/oceanbase/etc/observer.config.bin and /home/admin/oceanbase/etc/observer.config.bin.history files. Only variables at the global level are persisted; session-level variables are not persisted.

Lifecycle Long, from process startup to exit. Short, and it takes effect only after the tenant's schema is successfully created.

References

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

OceanBase

Customer Stories

Documentation