Prediction of buffer pool size for transaction processing workloads
Techniques are described herein for prediction of an buffer pool size (BPS). Before performing BPS prediction, gathered data are used to determine whether a target workload is in a steady state. Historical utilization data gathered while the workload is in a steady state are used to predict object-specific BPS components for database objects, accessed by the target workload, that are identified for BPS analysis based on shares of the total disk I/O requests, for the workload, that are attributed to the respective objects. Preference of analysis is given to objects that are associated with larger shares of disk I/O activity. An object-specific BPS component is determined based on a coverage function that returns a percentage of the database object size (on disk) that should be available in the buffer pool for that database object. The percentage is determined using either a heuristic-based or a machine learning-based approach.
1. A computer-executed method comprising:
predicting a size for a buffer pool that is used to cache a set of database objects managed by a database management system, comprising:
identifying each of one or more database objects, of the set of database objects, for optimal buffer pool size prediction analysis based, at least in part, on historical utilization data that comprise a number of requests to read from disk for said each database object,
for each database object, of the one or more identified database objects, determining an object-specific buffer pool size component, wherein determining a particular object-specific buffer pool size component for a particular database object of the one or more identified database objects comprises basing the particular object-specific buffer pool size component on a pre-determined percentage of an on-disk size of the particular database object, wherein the particular database object of the one or more identified database objects comprises one selected from a group consisting of a table column, a tablespace, a materialized view, and an index, and
calculating a predicted size for maximizing a hit rate of the buffer pool based on the one or more object-specific buffer pool size components for the one or more identified database objects; and
adjusting the size of the buffer pool in memory to be the predicted size;
wherein the method is performed by one or more computing devices.
2. The computer-executed method of claim 1 , wherein identifying the particular database object comprises:
determining whether a cumulative disk I/O share value exceeds a threshold I/O value;
wherein the cumulative disk I/O share value represents a cumulative percentage of total disk I/O requests in the historical utilization data attributed to any database objects previously identified for optimal buffer pool size prediction analysis;
responsive to determining that the cumulative disk I/O share value does not exceed the threshold I/O value, identifying the particular database object for optimal buffer pool size prediction analysis based on the particular database object having a largest object-specific disk I/O share value among database objects, of the set of database objects, that have not yet been analyzed for optimal buffer pool size prediction;
wherein the object-specific disk I/O share value of the particular database object represents a percentage of the total disk I/O requests in the historical utilization data attributed to the particular database object.
3. The computer-executed method of claim 1 , wherein calculating the predicted size for the buffer pool based on the object-specific buffer pool size components for the one or more identified database objects comprises summing the object-specific buffer pool size components for the one or more identified database objects.
4. The computer-executed method of claim 1 , wherein determining the particular object-specific buffer pool size component comprises:
using a trained machine learning model to predict a percentage of an on-disk size of the particular database object based, at least in part, on a feature vector comprising one or more of:
buffer pool utilization during a particular time period,
a rate of pages not made young per thousand disk reads during the particular time period,
disk write count per second during the particular time period,
buffer pool hit rate during the particular time period,
a current coverage for the particular database object, or
percentage of the total number of disk I/O requests for the particular database object during the particular time period; and
determining the particular object-specific buffer pool size component based on the predicted percentage of the on-disk size of the particular database object.
5. The computer-executed method of claim 4 , wherein the feature vector comprises:
buffer pool utilization during the particular time period,
a rate of pages not made young per thousand reads during the particular time period,
disk write count per second during the particular time period, and
current coverage for the particular database object.
6. The computer-executed method of claim 1 , wherein:
the buffer pool is allocated for a particular workload that is configured to access the set of database objects;
the method further comprises, prior to predicting the size for the buffer pool, determining whether the particular workload is in a steady state;
wherein said predicting the size for the buffer pool is performed in response to determining that the particular workload is in a steady state.
7. The computer-executed method of claim 1 , wherein:
the buffer pool is allocated for a particular workload that is configured to access the set of database objects;
the set of database objects is a set of active database objects for the particular workload; and
the historical utilization data further comprises one or more of:
an amount of space in the buffer pool occupied by each database object of the set of database objects,
a total size of index and data pages on disk that belong to each database object of the set of database objects, and
for each database object of the set of database objects, a corresponding percentage of a total number of disk I/O requests for the particular workload;
buffer pool utilization;
a rate of pages not made young per thousand reads;
disk write count per second; or
buffer pool hit rate.
8. The computer-executed method of claim 1 , further comprising:
receiving a request for a buffer pool size prediction for the set of database objects of a database;
wherein said predicting the size for the buffer pool maintained for the set of database objects is performed in response to receiving the request; and
returning, as a response to the request, the predicted size for the buffer pool.
9. The computer-executed method of claim 1 , further comprising automatically setting the size of the buffer pool to be the predicted size.
10. The computer-executed method of claim 1 , wherein determining the particular object-specific buffer pool size component comprises:
generating a predicted object-specific buffer pool size component;
determining whether the predicted object-specific buffer pool size component is less than a size of a current allocation of buffer pool memory to the particular database object;
responsive to determining that the predicted object-specific buffer pool size component is less than the size of the current allocation of buffer pool memory to the particular database object, determining the particular object-specific buffer pool size component to be the size of the current allocation of buffer pool memory to the particular database object.
11. One or more non-transitory computer-readable media storing one or more sequences of instructions that, when executed by one or more processors, cause:
predicting a size for a buffer pool that is used to cache a set of database objects managed by a database management system, comprising:
identifying each of one or more database objects, of the set of database objects, for optimal buffer pool size prediction analysis based, at least in part, on historical utilization data that comprise a number of requests to read from disk for said each database object,
for each database object, of the one or more identified database objects, determining an object-specific buffer pool size component, wherein determining a particular object-specific buffer pool size component for a particular database object of the one or more identified database objects comprises basing the particular object-specific buffer pool size component on a pre-determined percentage of an on-disk size of the particular database object, wherein the particular database object of the one or more identified database objects comprises one selected from a group consisting of a table column, a tablespace, a materialized view, and an index, and
calculating a predicted size for maximizing a hit rate of the buffer pool based on the one or more object-specific buffer pool size components for the one or more identified database objects; and
adjusting the size of the buffer pool in memory to be the predicted size.
12. The one or more non-transitory computer-readable media of claim 11 , wherein identifying the particular database object of the one or more database objects for optimal buffer pool size prediction analysis comprises:
determining whether a cumulative disk I/O share value exceeds a threshold I/O value;
wherein the cumulative disk I/O share value represents a cumulative percentage of total disk I/O requests in the historical utilization data attributed to any database objects previously identified for optimal buffer pool size prediction analysis;
responsive to determining that the cumulative disk I/O share value does not exceed the threshold I/O value, identifying the particular database object for optimal buffer pool size prediction analysis based on the particular database object having a largest object-specific disk I/O share value among database objects, of the set of database objects, that have not yet been analyzed for optimal buffer pool size prediction;
wherein the object-specific disk I/O share value of the particular database object represents a percentage of the total disk I/O requests in the historical utilization data attributed to the particular database object.
13. The one or more non-transitory computer-readable media of claim 11 , wherein calculating the predicted size for the buffer pool based on the object-specific buffer pool size components for the one or more identified database objects comprises summing the object-specific buffer pool size components for the one or more identified database objects.
14. The one or more non-transitory computer-readable media of claim 11 , wherein determining the particular object-specific buffer pool size component comprises:
using a trained machine learning model to predict a percentage of an on-disk size of the particular database object based, at least in part, on a feature vector comprising one or more of:
buffer pool utilization during a particular time period,
a rate of pages not made young per thousand disk reads during the particular time period,
disk write count per second during the particular time period,
buffer pool hit rate during the particular time period,
a current coverage for the particular database object, or
percentage of the total number of disk I/O requests for the particular database object during the particular time period; and
determining the particular object-specific buffer pool size component based on the predicted percentage of the on-disk size of the particular database object.
15. The one or more non-transitory computer-readable media of claim 14 , wherein the feature vector comprises:
buffer pool utilization during the particular time period,
a rate of pages not made young per thousand reads during the particular time period,
disk write count per second during the particular time period, and
current coverage for the particular database object.
16. The one or more non-transitory computer-readable media of claim 11 , wherein:
the buffer pool is allocated for a particular workload that is configured to access the set of database objects;
the one or more sequences of instructions further comprise instructions that, when executed by one or more processors, cause, prior to predicting the size for the buffer pool, determining whether the particular workload is in a steady state;
wherein said predicting the size for the buffer pool is performed in response to determining that the particular workload is in a steady state.
17. The one or more non-transitory computer-readable media of claim 11 , wherein:
the buffer pool is allocated for a particular workload that is configured to access the set of database objects;
the set of database objects is a set of active database objects for the particular workload; and
the historical utilization data further comprises one or more of:
an amount of space in the buffer pool occupied by each database object of the set of database objects,
a total size of index and data pages on disk that belong to each database object of the set of database objects, and
for each database object of the set of database objects, a corresponding percentage of a total number of disk I/O requests for the particular workload;
buffer pool utilization;
a rate of pages not made young per thousand reads;
disk write count per second; or
buffer pool hit rate.
18. The one or more non-transitory computer-readable media of claim 11 , wherein the one or more sequences of instructions further comprise instructions that, when executed by one or more processors, cause automatically setting the size of the buffer pool to be the predicted size.