Record metadata, when queries are using cache

[flamber]

Is your feature request related to a problem? Please describe. The query_cache.query_hash is not the same as query.query_hash or query_execution.hash since x.37.6 (#14388). This means it's not possible to correlate if cache is being used for queries.

Describe the solution you'd like

• Another column in query_execution, which tells if cached results was used - ex. boolean column cached_results, or something like cache_hash which would get the value of query_cache.query_hash
• Making it more clear in the log, when cached results are used.
• Some way of connecting query_cache to query or query_execution
• Having some way of viewing this information in Admin > Audit

Describe alternatives you've considered

1. Doing estimates of query_execution.running_time, which should be a lot faster than queries without cache.
2. Storing the log and checking all request lines, which has a line before the request, which indicates that cache is being used:

2021-06-09 15:14:54,293 INFO api.card :: Question's average execution duration is 373.0 ms; using 'magic' TTL of 10.4 hours 💾
2021-06-09 15:14:54,507 DEBUG middleware.log :: POST /api/card/114/query 202 [ASYNC: completed] 217.3 ms (24 DB calls) App DB connections: 0/10 Jetty threads: 3/50 (2 idle, 0 queued) (78 total active threads) Queries in flight: 0 (0 queued)

Additional context Adding Regression, since it now makes it close to impossible to figure out cache-hit-rate.

:arrow_down: Please click the :+1: reaction instead of leaving a +1 or update? comment

[Rayyan98]

@flamber is the hash intended to be different or it a bug as the caching seems to we working as expected as far as i can see

[flamber]

@Rayyan98 Caching works as intended, but it is not possible to correlate the hashes manually.

[Rayyan98]

@flamber Will that change in future versions

[flamber]

@Rayyan98 That's why there's an issue for this. Please use the forum for questions and troubleshooting: https://discourse.metabase.com/

[maxzheng]

For insurance purpose, we want to be able to find out the # of records stored in cache. Query execution has the record_rows, but doesn't indicate if result is cached to disk or not.