Setting the Idle Query and Idle Session Timeouts

To keep long-running queries or idle sessions from tying up cluster resources, you can set timeout intervals for both individual queries, and entire sessions.

  1. In Cloudera Manager, navigate to Impala service > Configuration.
  2. In the search field, type idle.
  3. In the Idle Query Timeout field, specify the time in seconds after which an idle query is cancelled.

    This could be a query whose results were all fetched but was never closed, or one whose results were partially fetched and then the client program stopped requesting further results. This condition is most likely to occur in a client program using the JDBC or ODBC interfaces, rather than in the interactive impala-shell interpreter. Once a query is cancelled, the client program cannot retrieve any further results from the query.

    You can reduce the idle query timeout by using the QUERY_TIMEOUT_S query option at the query level. Any non-zero value specified in this field serves as an upper limit for the QUERY_TIMEOUT_S query option.

    The value of 0 disables query timeouts.

  4. In the Idle Session Timeout field, specify the time in seconds after which an idle session expires.

    A session is idle when no activity is occurring for any of the queries in that session, and the session has not started any new queries. Once a session is expired, you cannot issue any new query requests to it. The session remains open, but the only operation you can perform is to close it.

    The default value of 0 specifies sessions never expire.

    You can override this setting with the IDLE_SESSION_TIMEOUT query option at the session or query level.

  5. Click Save Changes and restart Impala.
  6. Log in to the CDP web interface and navigate to the Data Warehouse service.
  7. In the Data Warehouse service, click Virtual Warehouses in the left navigation panel.
  8. Select the Impala Virtual Warehouse, click options for the warehouse you want to set the timeout and retry options.
  9. Click Edit and navigate to Impala Coordinatorunder Configurationstab.
  10. In the search field, type idle.
  11. In the Idle Query Timeout field, specify the time in seconds after which an idle query is cancelled.

    This could be a query whose results were all fetched but was never closed, or one whose results were partially fetched and then the client program stopped requesting further results. This condition is most likely to occur in a client program using the JDBC or ODBC interfaces, rather than in the interactive impala-shell interpreter. Once a query is cancelled, the client program cannot retrieve any further results from the query.

    You can reduce the idle query timeout by using the QUERY_TIMEOUT_S query option at the query level. Any non-zero value specified in this field serves as an upper limit for the QUERY_TIMEOUT_S query option.

    The value of 0 disables query timeouts.

  12. In the Idle Session Timeout field, specify the time in seconds after which an idle session expires.

    A session is idle when no activity is occurring for any of the queries in that session, and the session has not started any new queries. Once a session is expired, you cannot issue any new query requests to it. The session remains open, but the only operation you can perform is to close it.

    The default value of 0 specifies sessions never expire.

    You can override this setting with the IDLE_SESSION_TIMEOUT query option at the session or query level.

  13. Click Apply and restart Impala.
Impala checks periodically for idle sessions and queries to cancel. The actual idle time before cancellation might be up to 50% greater than the specified configuration setting. For example, if the timeout setting was 60, the session or query might be cancelled after being idle between 60 and 90 seconds.