Query Plan Cloning via Global Cache Coordination
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database server systems face performance issues due to excessive use of access spinlocks when reusing query plans across client connections, leading to inefficient CPU cycles and wasted resources as they fail to identify and reuse identical query plans across multiple clients.
Innovation Solution
Implementing a system with global and private cache storage to share lightweight stored procedures and query plans across client connections, while avoiding spinlock access by using a coordination module to determine existing query plans and clone them when necessary, thereby optimizing query plan reuse.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database server system uses access spinlocks to access global memory cache for query plans, then query plan reuse is enabled, but CPU cycles are wasted and performance decreases due to excessive serialization
Solution Approach 1:
The patent segments the memory cache into client-specific local memory caches and a shared global memory cache. Each client has its own local cache for storing query plans, while the global cache serves as a shared repository. This segmentation allows clients to access their own query plans without contending for the global cache lock, reducing spinlock usage while maintaining query plan reuse capability across clients.
Solution Approach 2:
The patent introduces a coordinator as an intermediary component that manages query plan storage and retrieval between clients and the global memory cache. The coordinator handles the coordination of query plan access, allowing multiple clients to access the global cache without direct spinlock contention, thereby improving CPU cycle efficiency while maintaining reliable query plan reuse.
2Ease of operation
If the database server system stores query plan objects in local memory cache per client, then client-specific query plans are preserved, but query plans cannot be reused across multiple clients
Solution Approach 1:
The patent implements a dual-cache system where local memory caches serve the specific function of storing query plans for individual clients, while the global memory cache provides universal access for all clients. The coordinator enables the system to maintain client-specific storage convenience while achieving cross-client query plan sharing, making the system both client-aware and universally adaptable.
Solution Approach 2:
The patent adds a new dimension to the memory cache architecture by introducing a global memory cache layer above the client-specific local caches. This dimensional addition allows query plans to be stored locally for ease of operation while simultaneously being accessible globally for sharing across clients, resolving the contradiction between client-specific storage and cross-client versatility.
3Manufacturing precision
If the database server system builds and compiles query plans frequently, then query execution is optimized for each client, but system efficiency decreases due to expensive query plan generation
Solution Approach 1:
The patent implements preliminary action by pre-compiling and storing query plans in the global memory cache before they are needed by multiple clients. The coordinator manages a registry of pre-compiled query plans, allowing subsequent client requests to retrieve optimized query plans directly from the global cache without re-compilation, thereby maintaining query execution optimization while significantly improving system efficiency.
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for optimizing a query plan reuse in a database server system accessible by a plurality of client connections. An embodiment comprises determining if a query plan in a global cache storage is reserved by a client connection of a plurality of client connections, generating a cloned query plan from the query plan based on the determining, and associating the cloned query plan with a second client connection of the plurality of client connections.