About query timeout variables

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 executing a query in an OceanBase Database tenant depends on the number of CPU cores in the tenant. Therefore, worker threads are scarce resources. If a query does not return results for a long period, it continues to occupy worker threads.

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 execution time of a query 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) business. In this case, you can adjust the value of the timeout variable for the current session in the tenant or use SQL hints to set the timeout value.

View the current query timeout period

obclient> SHOW VARIABLES LIKE 'ob_query_timeout';
+------------------+-----------+
| VARIABLE_NAME    | VALUE     |
+------------------+-----------+
| ob_query_timeout | 100000000 |
+------------------+-----------+
1 row in set

Adjust the query timeout period for sessions in a tenant

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

obclient> SELECT COUNT(*) FROM h1, h2 , h3;
ERROR 4012 (HY000): Timeout

Use SQL hints to set the timeout period

obclient> SELECT /*+ QUERY_TIMEOUT(100000000) */ COUNT(*) FROM h1, h2 , h3;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set