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
You can use the following method to query whether a parameter is a cluster-level or tenant-level parameter:
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)
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:
In MySQL mode
obclient> SHOW VARIABLES LIKE 'ob_query_timeout'; obclient> SHOW GLOBAL VARIABLES LIKE 'ob_query_timeout';In Oracle 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';
Differences between parameters and system variables
| Comparison item | Parameter | System variable |
|---|---|---|
| Effective scope | Affects a cluster, zone, server, or tenant. | Affects a tenant globally or at the session level. |
| Effective mode |
|
|
| Modification |
|
Modification can only be performed by using SQL statements. For example:
|
| Query method | You can query a system 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. For example:
|
| 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 system 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 Parameter summary.
For more information about how to view and set parameters, see Set parameters.
For information about system variables, see System variable summary.
For more information about how to view and set system variables, see Set variables.