[
https://issues.apache.org/jira/browse/DRILL-8555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125083#comment-18125083
]
ASF GitHub Bot commented on DRILL-8555:
---------------------------------------
letian-jiang commented on PR #3086:
URL: https://github.com/apache/drill/pull/3086#issuecomment-6062435453
> **Suggestion: make the plan cache size and expiration configurable**
>
> `PlanCache` hard-codes its limits (`PlanCache.java:98-102`): a 32 MB
maximum weight and `expireAfterWrite(10, MINUTES)`. Operators can't tune either
one. A deployment with many distinct query templates may want a larger cache.
One where tables change often, or where memory on the planning Drillbit is
tight, may want a smaller cache or a shorter lifetime.
>
> Could these be boot options in `drill-module.conf`, read in
`DrillbitContext` and passed to the `PlanCache` constructor? The generated-code
cache already does this with `drill.exec.compile.cache_max_size`. For example:
>
> ```
> drill.exec.plan_cache: {
> # Upper bound on the total size of cached plan JSON, per Drillbit
> max_size_bytes: 33554432,
> # How long an entry may live after it is written; 0 disables time-based
expiry
> expire_after_write: 10m,
> # Optional: evict entries that have not been used for this long; 0
disables
> expire_after_access: 0
> }
> ```
>
> A few related points:
>
> * The weigher uses `value.json.length()`, which counts characters, not
bytes. If the setting is named in bytes, the weigher should measure bytes (or
the setting should say characters).
> * `expireAfterAccess` would let rarely used templates age out sooner,
while a hot query isn't replanned every 10 minutes just because it was written
10 minutes ago.
> * It would help to log the settings in use when the Drillbit starts, and
to document them in the plan-cache design doc.
> * Startup settings are a good fit because the cache is created once per
Drillbit, and Guava can't change these limits on a cache that's already built.
`planner.enable_plan_cache` stays the runtime on/off switch.
I’ve made the cache size and both expiration policies configurable.
1. The defaults are 32 MiB, expire_after_access = 10m, and
expire_after_write = 0 (disabled). This keeps actively used plans available
while allowing idle entries to expire. Since either expiration condition can
expire an entry, retaining a 10-minute write expiry would still force hot plans
to expire regardless of access. Operators who want periodic re-optimization can
enable write-based expiration.
2. The size limit now counts the combined UTF-8 byte lengths of the cache
key, physical-plan JSON, and optional text plan, rather than Java character
counts. This bounds cached text content; it does not include context metadata,
Java object overhead, or Guava’s internal structures, so it is not an exact
heap-memory limit.
> Physical plan cache for parameterized SQL queries
> -------------------------------------------------
>
> Key: DRILL-8555
> URL: https://issues.apache.org/jira/browse/DRILL-8555
> Project: Apache Drill
> Issue Type: New Feature
> Components: Query Planning & Optimization
> Reporter: Letian Jiang
> Priority: Major
>
> h2. Motivation
> Repeated queries that differ only in literals are a common workload, yet each
> query currently requires full planning. This adds unnecessary latency and
> wastes CPU time.
> h2. Brief Design
> Add a per-Drillbit, in-memory plan cache. Drill automatically parameterizes
> eligible SQL queries and reuses a cached physical plan with the current
> literal values, without changes to client queries.
> h2. Plan reuse safety
> * *Engine:* matches the SQL template and checks relevant option changes,
> plugin configuration changes, and whether base table versions still match.
> * *Storage plugin:* explicitly opts in, provides a plan-compatible table
> version, and is responsible for rebuilding its predicate-dependent scan work
> from the rebound expressions.
> h2. Expected benefit
> Short, frequently executed queries such as HBase reads can benefit
> substantially. Analytical queries with significant planning cost can benefit
> as well.
> h2. Implementation status
> A working implementation is available, including HBase, Paimon and Iceberg
> integrations. A pull request will follow.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
