This topic describes the information about parameters and system variables, such as their modification and query, and the differences in using parameters and system variables in OceanBase Database.
Parameters
You can set these parameters to control the load balancing, major compaction time, major compaction mode, resource allocation, and module switches of a cluster.
Categories of parameters
OceanBase Database provides cluster-level and tenant-level parameters.
Cluster-level parameters specify the basic information and performance and security options of an entire OceanBase cluster. Typical cluster-level parameters are those used for global tasks, such as data backup and restore, and load balancing. Usually, cluster-level parameters are specified during cluster startup and are seldom modified.
Tenant-level parameters specify feature options of one or more tenants to optimize specific configurations of the tenants. Typical tenant-level parameters are those used for the storage engine, SQL execution strategies, and access control. Usually, tenant-level parameters are specified when you create and manage a tenant, and can be modified as needed at any time.
Note
Most parameters take effect immediately after you set or modify their values, while some parameters take effect after you restart the OBServer node.
Query parameters
To query whether a parameter is a cluster-level or tenant-level parameter, use the following statement:
obclient> SHOW PARAMETERS LIKE 'max_syslog_file_count';
The return result is as follows:
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault |
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
| zone1 | observer | 172.xx.xxx.xxx | 2882 | max_syslog_file_count | INT | 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 | 0 | 1 |
+-------+----------+----------------+----------+-----------------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+---------+-------------------+---------------+-----------+
1 row in set
If the value in the scope column is CLUSTER, the parameter is a cluster-level parameter. If the value in the scope column is TENANT, the parameter is a tenant-level parameter.
System variables
System variables allow you to control behaviors of a database system, such as the cache size, number of concurrent connections, CPU utilization, and memory usage. You can also use system variables to configure various features of the database system.
Categories of system variables
The system variables of OceanBase Database can be categorized into global variables and session-level variables.
A global variable is used to implement a global modification. Different users of the same database tenant share the settings of global variables. Modification to global variables remains effective after you exit the session. In addition, modification to a global variable does not take effect on the currently open session and takes effect only after a new session is established.
A session-level variable is used to implement a session modification. When a client is connected to the database, the database copies global variables to automatically generate session-level variables. Modifications made to session-level variables apply to the current session only.
Query system variables
You can query a system variable by using the following methods:
To query the current values of system variables, use the following statements:
obclient [oceanbase]> SHOW VARIABLES LIKE 'ob_query_timeout';
obclient [oceanbase]> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';
To query the modification records and default values of global variables, use the following statement:
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
To query the current values of system variables, use the following statements:
obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_SESSION_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
obclient [SYS]> SELECT * FROM SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE WHERE VARIABLE_NAME = 'ob_query_timeout';
To query the modification records and default values of global variables, use the following statement:
obclient [SYS]> SELECT * FROM SYS.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';
Differences between parameters and system variables
| Comparison item | Parameter | System variable |
|---|---|---|
| Effective scope | Effective in a cluster, zone, server, or tenant. | Effective globally or at the session level in a tenant. |
| Effective mode |
|
|
| Modification |
|
Modification can only be performed by using SQL statements. Here are some examples:
|
| Query method | You can query a parameter by using the SHOW PARAMETERS statement, for example, SHOW PARAMETERS LIKE 'schema_history_expire_time';. |
You can query a variable by using the SHOW [GLOBAL] VARIABLES statement. Here are some examples:
|
| Persistence | Parameters are persisted into internal tables and configuration files, and can be queried from the /home/admin/oceanbase/etc/observer.config.bin and /home/admin/oceanbase/etc/observer.config.bin.history files. |
Only global variables are persisted. |
| Lifecycle | Long. A parameter remains effective for the entire duration of a process. | Short. A system variable takes effect only after the tenant schema is created. |
References
For more information about parameters, see Overview of parameters.
For more information about how to query and set parameters, see Set parameters.
For information about system variables, see Overview of system variables.
For more information about how to query and set system variables, see Set variables.