One of the most common issues when implementing Heimdall is the lack of cache hits. There are many different reasons why with a default cache policy as generated by the configuration Wizard, cache hits will not be observed. Each of these will be detailed below and how to diagnose them.
When using a tool that performs simple queries (i.e. not a prepared statement query) such as psql, after a query is executed, the command "show queryinfo" can be executed, which will provide information about the last query, and how it was processed, specifically the properties that were attributed to that command. An example output:
gpadmin=> show queryinfo; Attribute | Value ----------------------+---------------------- cache:ttl | 3600000 all:stop | false all:resultQueryTrans | true all:querytimeout | 0 all:printtiming | false all:printtables | false all:printresults | false all:printmeta | false all:printmatch | false all:printcapture | false all:onlytrans | false all:olderthan | 0 all:notrack | false all:maxburst | 10 all:logger | hdlog all:log | true all:invalidate | true all:capture | true nocache reason | cache is not enabled connid | 13 command | select 1 cache | true Autocommit | true | (24 rows)
Here, we see the "nocache reason" is set to "cache is not enabled", which is the first reason encountered that prevented caching of this query. If we resolve this issue, then we get:
gpadmin=> show queryinfo; Attribute | Value ----------------------+------------------ ... nocache reason | empty table list ...
Here, an empty table list is encountered, which indicates that caching was not enabled because no table names were extracted from the query, which defaults to disabling caching. See below for a list of nocache reasons.
First off, when debugging caching, the following steps should be followed:
1) In the vdb tab, enable the "verbose debugging" option:
2) Next, pass traffic you expect to be cached, and find the query hash value in the log tab (each unique query will have a unique hash):
3) Finally, check in the logs tab (or the log files on disk), and search for "nocache reason", i.e. using a regex filter of "3730569B89BF4674DE5549AF0C82A083.*nocache reason":
The nocache reason will provide a simple explanation in most cases of why a particular request was not served from cache. Below are some of the cases and details on how to trigger caching, as appropriate (this is not guaranteed to be a comprehensive list):
- No "no-cache" reason provided at all: If no cache rule is present or matching, than no no-cache reason will be provided
- multiple result sets: In this case, a single query is generating multiple results, which is currently non-cachable. Resolution: none
- Output Parameters returned: A called stored procedure is called with a return result. As this is likely to have side-effects, it is non-cachable. Resolution: none
- cursor execute|fetch|open...: SQL Server cursor execute result. As this may be scrollable, it is non-cachable. Resolution: none
- Explicitly nocache per rule: A no-cache rule is configured. Resolution: Remove or adjust the nocache rule
- System table, without cache override: A Database internal table has been detected, and is deemed to be non-cachable, as it may change behind the scenes without notice. Resolution: On the cache rule, you can specify the "cachesystem" property with a value of true to enable caching.
- empty table list: If a query doesn't have a detected table associated with it, then it won't be considered cachable. Example "select 1". Resolution: To enable caching such queries, create a rule with the property of "tables" and specify "none", which will provide a special behavior of instructing the system to allow caching anyway.
- dml: If Heimdall detects DML or otherwise doesn't flag it as NOT DML, then this will be set. This may be due to it being a "select...for update" query, for example. Resolution: To override the DML flag, the property of "update" and with true or false, depending on if it should be considered a DML operation or not.
- Result-set properties disallow caching (not forward only or (scroll-insensitive && read_only)): Certain result-sets may not be compatible with caching, due to scrolling or updatable flags. Resolution: none
- cache is not enabled: The cache is not enabled at the VDB level. Resolution: Enable the cache
- vdb reset timer: Via the UI or API, a timer was set to disable caching until a particular time. Resolution: Wait until the time expires
- table reset timer (internal): A DML has triggered a cache invalidation interval on this proxy. If a table is written to more than once every two seconds, this may be a continuous issue for the table. Resolution: Wait until the time expires or stop writes to the table at issue.
- table reset timer (external): Like with the internal invalidation, but due to invalidations from an external source, i.e. another proxy.
- SQL contains (), use unconditional flag to override this logic: Detected () in the SQL query. To override this logic, select the action cache and set unconditional flag as true for the given regex.
- sequence usage detected: Sequence usage has been detected. This logic can be overridden with unconditional flag in rules configuration.
- temp table: A temporary table was detected as part of the query. Resolution: Avoid using temporary tables.
- non-deterministic function call: A function was detected as part of the query that was determined to be non-deterministic by the database or cache engine. Resolution: Avoid using non-deterministic functions for queries that need to be cached.
- nocache reason
in the query, use unconditional flag to override: Certain patterns in a query will trigger nocache behavior, such as "sql_calc_found_rows" and "()". To cache such queries, the property of "unconditional" can be set to true to bypass these checks.
- exception creating fixed cached rowset impl: An unexpected error was encountered creating a copy of the result-set
- grid cache is null: For some reason, the grid cache is not enabled. This may indicate a bug or other error. Consult Heimdall support for assistance
- grid cache is not ready: This may indicate that something is wrong with the cache engine selected, i.e. Redis is selected, but the cache server is unavailable.
- no cached object found for key: In general, this indicates that there was no cached object for the query. Resolution: retry the query again
- No matching cache rule or nocache rule was applied: This may indicate that there are no matching rules that will allow caching. Resolution: Create and apply caching rules or modify an existing one to match the requirements.
- Cached object removed due to table eviction of table, ts=: There was a cached object, but the table has had an eviction event, and is being evicted.
- stale entry: The cached object has surpassed it's allowed TTL.
- global cache reset since stored: On the UI or via API, the clear-cache call was made, triggering a global eviction of objects. Any objects pre-dating this event will be considered stale.
- ttl value set to 0: A ttl value was set to zero for a cache rule
Queries in Transactions
As queries in a transaction may have different side-effects than those that are not, so, by default, the wizard creates a rule that does not trigger caching or cache retrieval while in a transaction.
This cause will NOT have a nocache reason (it simply won't be shown), as it is triggered in the rule match process itself.
As queries in a transaction may have different side-effects than those that are not, by default, the wizard creates a rule that does not trigger caching or cache retrieval while in a transaction.
System Table Detected
Certain table names such as pg_* will be flagged as being system tables, and which may be changed behind the scenes by default. In these cases, the default behavior is to disable caching. This can be overridden with the cache parameter of "cachesystem".
No Tables Detected
As invalidation is tracked at the table level, if no table is detect in a query, the default behavior is to disable caching. This includes for queries such as "Select 1". This can be overridden by tagging the query with a table of "none". This is a special value, in that it explicitly removes this tracking behavior.
Table Was Written To
As a DML is detected, the table will enter an invalidation window, which is a period of two seconds during which neither cache hits nor cache stores will occur. This includes for the entire duration of a transaction the DML is detected within, until a commit or rollback. Once the DML is completed, the invalidation window will no longer be in effect, however, for remote nodes, this window will occur for at least the full two second period. This is to reduce the number of messages traveling between nodes. Updates to refresh the invalidation window will occur every second until no dml is pending against the table. Updates can be made that don't trigger an invalidation window by using the parameter "invalidate" with a value of false.
Temporary Table Detected
When a temporary table is detected in a query, this will suppress caching as well.
Non-Deterministic Function Call Detected
With SQL Server, function calls are parsed, and non-deterministic functions are detected. When present in the query, this will trigger cache suppression.