Progress analyzer for database queries

Patent metadata

Field	Value
Publication number	US-9934261-B2
Application number	US-40150309-A
Country	US
Kind code	B2
Filing date	Mar 10, 2009
Priority date	Mar 10, 2009
Publication date	Apr 3, 2018
Grant date	Apr 3, 2018

Abstract

A data processing system includes a query progress analyzer. The database processing system comprises a receiver that receives database queries including a target query directed to a database and a query analyzer. The query analyzer is coupled to the receiver and analyzes progress status of the target query based on comparison of measured target query resource usage to resource usage predicted from a query plan of the target query and measured system resource usage.

First claim

What is claimed is:

1. A database processing system comprising:
a receiver to receive database queries including a target query directed to a database; and
a query analyzer in communication with the receiver and including a processor, the query analyzer to analyze a progress status of the target query at runtime by comparing measured target query resource usage to resource usage predicted from a query plan of the target query and to measured usage of resources by the database processing system, wherein the measured target query resource usage includes at least one of (1) an amount of central processing unit execution time; (2) a number of storage disk inputs; (3) a number of storage disk outputs; (4) an amount of memory used; or (5) a number of messages exchanged.

2. The system according to claim 1 wherein the query analyzer is to analyze the target query progress status by predicting performance characteristics based on the query plan, acquiring a snapshot of resource usage of the target query being executed for resources included in the prediction, and comparing the snapshot of resource usage to predicted total resource usage.

3. The system according to claim 1 wherein the query analyzer is to acquire a snapshot of performance statistics for the database while the database is running.

4. The system according to claim 1 wherein the query analyzer is to recommend an action based on the comparison of the measured target query resource usage to the resource usage predicted from the query plan of the target query.

5. The system according to claim 1 further including a database server to service the target query, and the query analyzer to execute on the database server.

6. The system according to claim 1 further including a database server to service the database queries.

7. The system according to claim 1 wherein the query analyzer is to analyze the target query in terms of a plurality of resource types.

8. The system according to claim 1 wherein the query analyzer is to predict performance characteristics of the target query when run in isolation on a selected database system based on the query plan of the target query.

9. The system according to claim 1 wherein the query analyzer is to measure statistics of resource usage by the database processing system and overall system usage statistics at runtime of the target query.

10. The system according to claim 1 wherein the query analyzer is to categorize executing queries in terms of a ratio of predicted to actual resource usage including consideration of elapsed time.

11. The system according to claim 1 wherein the query analyzer is to distinguish between metrics reflecting resources usable by multiple queries concurrently and resources with shared, alternate usage among multiple queries.

12. A method of operating a database processing system comprising:
comparing, via a processor, measured target query resource usage to resource usage predicted from a query plan of a target query directed to a database;
analyzing, via the processor, a progress status of the target query based on the comparison; and
distinguishing, via the processor, between metrics reflecting resources usable by multiple queries concurrently and resources with shared, alternate usage among multiple queries based on the analysis of the progress status, wherein the measured target query resource usage includes at least one of (1) an amount of central processing unit execution time; (2) a number of storage disk inputs; (3) a number of storage disk outputs; (4) an amount of memory used; or (5) a number of messages exchanged.

13. The method according to claim 12 wherein the analyzing of the progress status of the target query includes:
predicting a total resource usage based on the query plan and measured usage of resources by the database processing system;
acquiring a snapshot of the measured target query resource usage for resources included in the prediction, the snapshot to be acquired during runtime of the target query; and
comparing the snapshot of the measured target query resource usage to the predicted total resource usage.

14. A tangible machine readable storage medium comprising machine-readable instructions that, when executed, cause a machine to at least:
compare measured target query resource usage to resource usage predicted from a query plan of a target query and to measured usage of resources by a database processing system to analyze progress status of the target query during execution of the target query; and
distinguish between metrics reflecting resources usable by multiple queries concurrently and resources with shared, alternate usage among multiple queries, wherein the measured target query resource usage includes at least one of (1) an amount of central processing unit execution time; (2) a number of storage disk inputs; (3) a number of storage disk outputs; (4) an amount of memory used; or (5) a number of messages exchanged.

15. A machine readable storage medium as defined in claim 14 , wherein the machine-readable instructions, when executed, further cause the machine to:
predict a total resource usage based on the query plan;
acquire a snapshot of resource usage of the target query during execution for resources included in the prediction; and
compare the snapshot of the resource usage of the target query during execution to predicted total resource usage.

16. The system according to claim 1 , wherein the progress status includes at least one of an elapsed time since execution of the target query began, a predicted time remaining before query completion, or a total resource usage by the target query relative to a predicted total resource usage for the target query.

17. The system according to claim 1 , wherein the query analyzer is to estimate a remaining central processing unit time for the target query by comparing a number of tuples emitted by the database processing system to an estimated output cardinality of the target query.

18. The system according to claim 1 , wherein the query analyzer is to estimate a remaining central processing unit time for the target query by converting an estimated execution time from a query optimizer into an estimate of central processing unit time and comparing the estimate of central processing unit time to the actual amount of central processing unit time consumed by the target query.

19. The system according to claim 1 , wherein the query analyzer is to estimate a remaining central processing unit time for the target query by relating a number of tuples processed by the database processing system to central processing unit time and determining the remaining central processing unit time for the target query based on the relationship and the number of tuples processed by the database processing system.

Assignees

• Kuno Harumi
• Wiener Janet L
• Dayal Umeshwar
• Krompass Stefan
• Hewlett Packard Entpr Dev Lp

Inventors

• Kuno Harumi
• Wiener Janet L
• Dayal Umeshwar
• Krompass Stefan

Classifications

• G06F16/217 Primary

  Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409) · CPC title

• G06F17/30306 Primary

  Physics · mapped topic