Estimation of query input/output (I/O) cost in database

Patent metadata

Field	Value
Publication number	US-9594781-B2
Application number	US-201414301160-A
Country	US
Kind code	B2
Filing date	Jun 10, 2014
Priority date	Nov 27, 2013
Publication date	Mar 14, 2017
Grant date	Mar 14, 2017

Abstract

Disclosed herein are system, method, and computer program product embodiments for calibrating and using a stable storage model. An embodiment operates by generating, by a central computer, an access request for a stable storage, wherein the access request comprises a plurality of page accesses; measuring a cost to execute the access request on the stable storage; amortizing the cost over the plurality of page accesses; and calibrating, by the central computer, a stable storage model based on the amortized cost.

First claim

What is claimed is:

1. A method comprising:
generating, by a central computer, an access request comprising a plurality of page accesses for a stable storage, including at least a first page access at a first queue depth and a second page access at a second queue depth;
measuring a cost to execute the access request on the stable storage at both the first queue depth and the second queue depth, wherein the second queue depth is greater than the first queue depth;
amortizing the cost over the plurality of page accesses;
calibrating, by the central computer, a stable storage model based on the amortized cost; and
comparing a first calibration point for the first queue depth to a second calibration point for the second queue depth to determine a calibration improvement measure, wherein a third page access at a third queue depth greater than the second queue depth is measured when the calibration improvement measure does not exceed a threshold.

2. The method of claim 1 , wherein the access request includes a request for a plurality of queue depths at a plurality of band sizes.

3. The method of claim 1 , wherein calibrating the stable storage model further comprises using interpolation to estimate the access cost for a queue depth/band size combination of non-calibrated points.

4. The method of claim 1 , wherein calibrating the stable storage model further comprises using bilinear interpolation to estimate the access cost for a queue depth/band size combination of non-calibrated points.

5. The method of claim 1 , wherein calibrating the stable storage model comprises selecting a default stable storage model from a plurality of default stable storage models.

6. The method of claim 5 , wherein the plurality of default stable storage models includes a model for a solid state device (SSD) and a model for a hard disk drive (HDD).

7. The method of claim 1 , wherein the comparing comprises: determining if the amortized cost including the third queue depth improved by comparing the amortized cost to the previously calculated amortized cost for the first and second queue depths.

8. The method of claim 1 , wherein the third calibration request is not measured when the calibration does not exceed the threshold and the calibrating completes.

9. The method of claim 1 , wherein the measuring is performed when no other background processes are running.

10. A computer-readable device having instructions stored thereon, execution of which, by a computing device, causes the computing device to perform operations comprising:
receiving a queue depth and a band size associated with an access request comprising a plurality of page accesses for a stable storage, including at least a first page access at a first queue depth and a second page access at a second queue depth;
measuring a cost to execute the access request at both the first queue depth and the second queue depth, wherein the second queue depth is greater than the first queue depth;
amortizing the cost over the plurality of page accesses for the first and second queue depths;
comparing a first calibration point for the first queue depth to a second calibration point for the second queue depth to determine a calibration improvement measure, wherein a third page access at a third queue depth greater than the second queue depth is measured when the calibration improvement measure does not exceed a threshold;
calibrating a stable storage model based on the amortized cost; and
determining an estimated access cost for the access request based on the amortized cost for the first and second access requests and the stable storage model.

11. The computer-readable device of claim 10 , wherein the determining comprises identifying the estimated access cost in a table.

12. The computer-readable device of claim 10 , wherein the determining comprises interpolating calibrated estimate access costs to determine the estimated access cost.

13. The computer-readable device of claim 12 , wherein the interpolation is bilinear interpolation.

14. The computer-readable device of claim 10 , wherein the device is further configured to perform operations comprising: calibrating a model based on the amortized cost, wherein the calibrating comprises comparing a first calibration point for the first access request to a second calibration point for the second access request to determine a calibration improvement measure, wherein a third access request at a third queue depth greater than the second queue depth is measured when the calibration improvement does not exceed a threshold.

15. A system comprising:
a stable storage configured to store a database file;
an optimization module, in communication with the stable storage, configured to:
determine an amortized cost of an access request made to the database file comprising a plurality of page accesses for a stable storage, including at least a first page access at a first queue depth and a second page access at a second queue depth;
measure a cost to execute the access request at both the first queue depth and the second queue depth, wherein the second queue depth is greater than the first queue depth;
amortize the cost over the plurality of page accesses for the first and second queue depths;
compare a first calibration point for the first queue depth to a second calibration point for the second queue depth to determine a calibration improvement measure, wherein a third page access at a third queue depth greater than the second queue depth is measured when the calibration improvement measure does not exceed a threshold; and
calibrate a stable storage model based on the amortized cost; and

an access module, in communication with the optimization module and the stable storage, configured to submit Input/Output (I/O) requests to the database file based on the amortized cost.

16. The system of claim 15 , wherein the access module is configured to maintain a plurality of execution plans indicating how I/O requests are to be submitted to the database file.

17. The system of claim 16 , wherein the access module is further configured to select an execution plan based on the amortized cost.

18. The system of claim 15 , wherein the access model is configured to access a plurality of default stable storage models.

19. The system of claim 18 , wherein the access model is further configured to select a stable storage model from the plurality of default stable storage modes based on the amortized cost and to select an execution plan based on the selected stable storage model.

20. The system of claim 15 , further comprising a calibration unit configured to: calibrate a model based on the amortized cost, wherein the calibrating comprises comparing a first calibration point for a first access request to a second calibration point for a second access request to determine a calibration improvement measure, wherein a third access request at a third queue depth greater than the second queue depth is measured when the calibration improvement does not exceed a threshold.

Assignees

• Ghodsnia Pedram
• Bowman Ivan
• Sybase Inc

Inventors

• Ghodsnia Pedram
• Bowman Ivan

Classifications

• G06F16/217 Primary

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

• G06F17/30306 Primary

  Physics · mapped topic