Reduced usability and blocked transactions in case of query cache garbage collection

Detail name	Value
Changelog Number	7787
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0
Fix Versions	Exasol 6.2.0, Exasol 6.0.16, Exasol 6.1.5
Resolution Date	2019-07-04

Background

The query cache stores result sets of frequent queries. As it has a limited capacity, the least beneficial result sets are removed from it during query cache garbage collection.

To determine beneficial queries the query cache collects and maintains statistics about queries (e.g. result set size, query duration, frequency). Those statistics are also collected for queries currently not in cache.

Problem

The query cache garbage collection may take seconds up to even minutes if there are large amounts of access statistics to be maintained. During this time transactions are blocked (no commits/rollbacks) and system usability may suffer (hangups).

The likelihood of above problem increases with the amount of different cachable queries on the system.

Workaround

As this performance bug is directly related to query cache, disabling the query cache will avoid the problem. This can be achieved by SQL

ALTER SYSTEM SET QUERY_CACHE = 'OFF';
COMMIT;

Sometimes system performance depends on query cache why disabling it is no good option. In this special case here, increasing the query cache size will postpone the issue as query cache garbage collection will not be needed / invoked so often.

Increasing the default query cache size of 256 MiB by a factor of 100 can be facilitated using the following extra database parameter:

-queryCacheSize=25600