Cache Debugging

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.

Show queryinfo

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.

General Debugging

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):

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.