Status

Members

• Sushrut Borkar
• Glenn Galvizo
• Mike Carey

Motivation

Overview:

If the same query is run repeatedly in AsterixDB, it must be recompiled each time. A query plan cache can reduce this time by storing the job specifications for previously compiled queries. This allows for skipping both rewriting and compilation of an already seen query.

Design:

After the query string is parsed into an AST, we convert the AST into a string and use this for query plan cache lookups. In the case of a miss, we continue rewriting and compiling the query, and then cache the resulting job spec. In the case of a hit, we directly submit the cached job spec to Hyracks.

The query cache is implemented as a concurrent LRU hash map. To prevent the cache from growing indefinitely, an LRU replacement algorithm is used with a default size of 1024.

We have to include extra attributes in the cache key (besides the AST string) to avoid finding and using the wrong job spec from the cache. In total, 5 attributes are included in the cache key:

• AST string. We cache this instead of the original query string before parsing because it is resilient to minor changes in the query, such as adding spaces or empty lines.

• SessionConfig. For example, if the user runs a query, changes part of the session configuration (e.g. the preferred output format), and reruns the query, this prevents the second query from being served from the cache.

• Config, to capture the effects of used SET statements.

• Active dataverse, e.g., as defined in a USE statement.

• Result Set ID, which distinguishes among queries in multi-statement requests.

Similarly, 3 attributes are included in the cache value:

• Hyracks job spec to be submitted to Hyracks.

• Cached warnings. Since we skip compilation when serving queries from the cache, we cannot detect compile time warnings. To get around this, we cache warnings issued during rewriting and compilation, and then reissue them for cache hits. As a result, line numbers in warnings may be incorrect for queries answered using the cache.

• Lock. Since running the same job from multiple threads does not work, we include a lock in the cache value. To use a cached job spec, a thread must acquire this lock, and then release it after the job has finished running. If the lock is held by another thread, we recompile the query instead of blocking.

Cache invalidation:

When some event (for example, a new access method) changes the job spec that a query should produce, the cached job spec should be invalidated. We invalidate all cache entries on such events. A future improvement is to implement a fine-grained invalidation policy that keeps track of the objects that each cache entry depends on and invalidates only the relevant entries. Currently, these commands clear the cache:

• CREATE_INDEX
• DATAVERSE_DROP
• DATASET_DROP
• INDEX_DROP
• VIEW_DROP
• FUNCTION_DROP
• LIBRARY_DROP
• SYNONYM_DROP
• ANALYZE
• ANALYZE_DROP
• FULL_TEXT_FILTER_DROP
• FULL_TEXT_CONFIG_DROP
• NODEGROUP_DROP
• DROP_FEED
• DISCONNECT_FEED
• Rebalance

Caching conditions:

The cache is not accessed in any of the following conditions:

• The query issues out-of-band output. This is because OOB output is produced during compilation, which we skip for queries answered from the cache. Since the 19006 interface asks for OOB output, the cache is not used from there.
• No job spec is generated, or it is explain-only.
• There are any given query parameters.
• There are any runtime errors (to avoid caching queries that give runtime errors, we only insert into the cache after a query has run successfully).

Proposed changes

Interface:

We introduce two new statements for controlling cache access:

• “SET `compiler.querycache.bypass` "true";” forces the current query to ignore the cache.
• “SET `compiler.querycache.clear` "true";” clears all cache entries. The current query may still insert into the cache.

We also add a boolean HTTP API parameter bypass_cache which does the same thing as the first SET statement above. Finally, the parameter query.cache.capacity can be configured in the [cc] section of the cc.conf file to control the maximum cache size before replacement.

Changes:

• Compilation logic is changed in the source code since we skip rewriting and compilation for cache hits.
• Hints are now included in the AST string to prevent incorrect cache lookups that would otherwise miss the hints.
• A bug is fixed where the AST string of WINDOW expressions did not include FROM LAST or IGNORE NULLS.

Compatibility/Migration

The proposed changes don’t modify existing behavior besides incorrect line numbers in warnings for queries answered using the cache if the incoming query is not identical in its format to the one that led to the cache entry.

Testing

Correctness:

All the queries in SqlppExecutionTest were run 3 times each to check whether the queries were answered from the cache correctly during the second and third iterations. This was done in SqlppExecutionWithCacheTest so that the SqlppExecutionTest functionality is not affected. This test runs each query file 3 times, while running DDL files only once. Some query files actually contain DDLs, so the flag  “// loopIterations=1” is added to ensure that these files are run only once. Similarly, to prevent some tests failing due to incorrect cached line numbers, we have added “SET `compiler.querycache.bypass` "true";” to some query files.

We also tested multithreaded access from the HTTP API to ensure that the cache works when being accessed from many threads.

Performance:

We ran the CH2 benchmark with and without the cache to test mean response time and compile time improvements due to caching.