System variables in OceanBase AI Database can be configured to meet business requirements. This topic describes how to modify the system variables of a tenant.
You can use the SET statement to modify system variables of a tenant.
Setting a session-level system variable takes effect only for the current session and does not affect other sessions.
Setting a global-level system variable does not take effect for the current session. You must log in again and establish a new session for the change to take effect.
Notice
To set a global-level variable, the current user must have the
SUPERorALTER SYSTEMprivilege. Make sure that you have the required privilege before you set the variable. For more information about user privileges, see Overview of users and privileges.
The following sections use ob_query_timeout to describe how to modify a system variable. ob_query_timeout specifies the timeout period for SQL queries, in microseconds. By increasing the value of ob_query_timeout, you can avoid SQL query failures caused by query timeouts during large-scale data queries.
Modify session-level system variables
Log in to a user tenant of the cluster as the
rootuser.For example, to log in to the
mq_t1tenant, run the following command:obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -AQuery the session-level system variables of the tenant.
obclient [(none)]> SHOW VARIABLES LIKE 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | Variable_name | Value | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setModify the value of the session-level system variable by using the
SETstatement.obclient [oceanbase]> SET ob_query_timeout = 20000000;Query the modification result.
obclient [(none)]> SHOW VARIABLES WHERE variable_name LIKE 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | Variable_name | Value | +------------------+----------+ | ob_query_timeout | 20000000 | +------------------+----------+ 1 row in set
Modify global-level system variables
Log in to a user tenant of the cluster as the
rootuser.For example, for the
mq_t1tenant:obclient -h172.30.xx.xx -P2883 -uroot@mq_t1#cluster -p**** -AQuery the global-level system variables of the tenant.
obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | Variable_name | Value | +------------------+----------+ | ob_query_timeout | 10000000 | +------------------+----------+ 1 row in setModify the value of the global-level system variable by using the
SETstatement.obclient [oceanbase]> SET GLOBAL ob_query_timeout = 20000000;Query the modification result.
obclient [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name LIKE 'ob_query_timeout';The query result is as follows:
+------------------+----------+ | Variable_name | Value | +------------------+----------+ | ob_query_timeout | 20000000 | +------------------+----------+ 1 row in setAfter the modification, you can execute the following statement to query the
DBA_OB_SYS_VARIABLESview for the modification status and default value of theob_query_timeoutvariable:obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE NAME='ob_query_timeout';The query result is as follows:
+----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | CREATE_TIME | MODIFY_TIME | NAME | VALUE | MIN_VALUE | MAX_VALUE | SCOPE | INFO | DEFAULT_VALUE | ISDEFAULT | +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ | 2024-05-08 14:20:28.885780 | 2024-05-09 15:11:11.654128 | ob_query_timeout | 20000000 | | | GLOBAL | SESSION | Query timeout in microsecond(us) | 10000000 | NO | +----------------------------+----------------------------+------------------+----------+-----------+-----------+------------------+----------------------------------+---------------+-----------+ 1 row in set
Modify a Boolean variable
For variables displayed as ON/OFF in the SHOW VARIABLES command, you can modify the variable value by using any of the following methods:
SET foreign_key_checks = ON;
SET foreign_key_checks = TRUE;
SET foreign_key_checks = 1;
SET GLOBAL foreign_key_checks = OFF;
SET GLOBAL foreign_key_checks = FALSE;
SET GLOBAL foreign_key_checks = 0;
Note
Setting Boolean variables by using ON/OFF, TRUE/FALSE, or 1/0 is equivalent.
References
For more information about system variables, see: