Main SQL Executor Thread Throttling for Responsive Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face challenges in optimizing query performance due to complex queries and high transaction volumes, leading to inefficiencies in response times.
Innovation Solution
Implementing a database execution engine that limits the execution time of a main executor thread and employs worker threads to manage query execution, with restrictions on thread usage to prevent overloading, and employs a shared lock mechanism to synchronize thread activity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the main executor thread is allowed to run without time restrictions to complete complex query execution, then query completion reliability is improved, but thread activity remains high and system responsiveness deteriorates
Solution Approach 1:
The main executor thread operates in periodic cycles: an active execution period followed by a waiting period. During the active period, the thread executes query tasks; when the time limit is reached, it enters a waiting phase where it yields the CPU and allows other threads to execute. This periodic action pattern ensures that no single thread monopolizes system resources indefinitely, thereby maintaining system responsiveness while still completing complex queries over time.
2Stability of the object's composition
If worker threads are limited to prevent overloading, then system stability is improved, but query execution speed deteriorates
Solution Approach 1:
The system dynamically adjusts thread behavior based on workload conditions. The main executor thread transitions between active and waiting states based on time limits, and worker threads are dynamically created and terminated based on query requirements. This dynamic adaptation allows the system to maintain stability under heavy load while still achieving high execution speeds when resources are available, resolving the contradiction between stability and speed.
3Productivity
If the main executor thread is restricted to a time limit, then system responsiveness is improved, but query execution completeness may deteriorate
Solution Approach 1:
The query execution process maintains continuity through the alternating active and waiting phases. During active periods, the main executor thread makes progress on query execution. When the time limit is reached and the thread enters the waiting phase, the query state is preserved and can be resumed in subsequent active periods. This continuous action across multiple cycles ensures both system responsiveness (through timely yielding) and query execution completeness (through resumption capability).
Data Source
AI summary
A database execution engine generates a first query execution plan in response to receiving a first query, where a thread limit is specified for worker threads launched by the database execution engine. A first main executor thread is launched to process the first query and a first plurality of tasks are created to be performed in response to the first query. Then, a first plurality of worker threads are launched to perform the first plurality of tasks, where the first plurality of worker threads is less than or equal to the thread limit. In response to parallelizing processing of the first query execution plan, the first main executor thread is restricted to a first period of execution time before entering a waiting phase. The first main executor thread is woken up after the first plurality of worker threads have completed the first plurality of tasks.