Methods and systems for making effective use of system resources
Methods and systems for making effective use of system resources. A plurality of requests for access to a resource are received. Each request has an associated group of features. The group of features for each request is analyzed to collect observations about the plurality of requests. A function to predict an outcome of a subsequent request is generated based on the observations. Resources are allocated to service the subsequent request based on the function.
1. A method for determining the best-performing query access path by leveraging machine learning technology comprising:
receiving, with one or more hardware processing devices, a plurality of database queries to be performed using resources associated with a database, each database query having an associated group of features;
analyzing, utilizing machine learning techniques, the group of features for each database query to collect query execution data observations about the plurality of database queries;
creating, utilizing machine learning techniques, a model comprising a function to predict a resource allocation of a subsequent database query based on the observations, wherein the resource allocation comprises at least a predicted number of rows in a result set that an application will actually fetch for the subsequent database query and database resources to be adjusted accordingly for the subsequent database query; and
allocating, with the one or more hardware processing devices, resources to service the subsequent database query based on the predicted resources to be adjusted.
2. The method of claim 1 wherein the predicted resource allocation further comprises at least one query path.
3. The method of claim 1 wherein the group of features comprises:
a number of nested joins in the request;
a number of hash joins in the request; and
a sum of cardinal ties for hash joins in the request.
4. The method of claim 1 wherein the group of features comprises:
a cardinality estimate of a main table;
a storage count of the main table; and
a selectivity estimate of the main table.
5. The method of claim 1 wherein the group of features comprises a number of secondary queries in the request.
6. The method of claim 1 wherein the group of features comprises:
an application server processor state; and
an application server memory state.
7. The method of claim 1 wherein the group of features comprises:
a database processor state; and
a number of tables used.
8. An article comprising a computer readable medium having stored there on instructions for determining the best-performing query access path by leveraging machine learning technology that, when executed, are configurable to cause one or more processors to:
receive a plurality of database queries to be performed using resources associated with a database, each database query having an associated group of features;
analyze the group of features for each database query to collect query execution data observations about the plurality of database queries;
create a model comprising a function to predict a resource allocation of a subsequent database query based on the observations, wherein the resource allocation comprises at least a predicted number of rows in a result set that an application will actually fetch for the subsequent database query and database resources to be adjusted accordingly for the subsequent database query; and
allocate resources to service the subsequent database query based on the predicted resources to be adjusted.
9. The article of claim 8 wherein the predicted resource allocation further comprises at least one query path.
10. The article of claim 8 wherein the group of features comprises:
a number of nested joins in the request; a number of hash joins in the request; and
a sum of cardinalities for hash joins in the request.
11. The article of claim 8 wherein the group of features comprises:
a cardinality estimate of a main table;
a storage count of the main table; and
a selectivity estimate of the main table.
12. The article of claim 8 wherein the group of features comprises a number of secondary queries in the request.
13. The article of claim 8 wherein the group of features comprises:
an application server processor state; and
an application server memory state.
14. The article of claim 8 wherein the group of features comprises:
a database processor state; and
a number of tables used.
15. An apparatus for determining the best-performing query access path by leveraging machine learning technology, the apparatus comprising:
a storage system having one or more storage devices;
one or more hardware processors coupled with the storage system, the one or more hardware processors to receive a plurality of database queries to be performed using resources associated with a database, each database query having an associated group of features, to analyze the group of features for each database query to collect query execution data observations about the plurality of database queries, to create a model comprising a function to predict a resource allocation of a subsequent database query based on the observations, wherein the resource allocation comprises at least a predicted number of rows in a result set that an application will actually fetch for the subsequent database query and database resources to be adjusted accordingly for the subsequent database query, and to allocate resources to service the subsequent database query based on the predicted resources to be adjusted.
16. The apparatus of claim 15 wherein the predicted resource allocation further comprises at least one query path.
17. The apparatus of claim 15 wherein the group of features comprises:
a number of nested joins in the request; a number of hash joins in the request; and
a sum of cardinalities for hash joins in the request.
18. The apparatus of claim 15 wherein the group of features comprises:
a cardinality estimate of a main table;
a storage count of the main table; and a selectivity estimate of the main table.
19. The apparatus of claim 15 wherein the group of features comprises a number of secondary queries in the request.
20. The apparatus of claim 15 wherein the group of features comprises:
an application server processor state; and
an application server memory state.