Variables of query timeout

The query timeout feature of OceanBase Database avoids a query task from occupying worker thread and CPU resources for a long period.

The number of worker threads for performing a query in an OceanBase Database tenant depends on the number of CPU cores in the tenant. Therefore, worker threads are a limited resource. If a query does not return results for a long period, the worker thread remains occupied.

By default, the query timeout value of OceanBase Database is controlled by the tenant variable ob_query_timeout. The default value of this variable is 100000000, in microseconds. When the query time exceeds this value, the error -4012(HY000): Timeout is returned.

The default value of the timeout variable is proper for the online transaction processing (OLTP) business but may be improper for the online analytical processing (OLAP). In this case, you can adjust the value of the timeout variable for sessions in the tenant or use SQL hints to set the timeout value.

Example 1: Obtain the timeout period of the current query

obclient> SHOW VARIABLES LIKE 'ob_query_timeout';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| ob_query_timeout | 60000000 |
+------------------+----------+
1 row in set

Example 2: Adjust the query timeout period for sessions in a tenant

obclient> SET SESSION ob_query_timeout=10000000;
Query OK, 0 rows affected 

obclient> SELECT SLEEP(11);
ERROR 4012 (HY000): Timeout

Example 3: Use SQL hints to set the timeout period

obclient> SELECT /*+ QUERY_TIMEOUT(100000000) */ SLEEP(11);
+-----------+
| SLEEP(11) |
+-----------+
|         0 |
+-----------+
1 row in set