Autonomous column selection for columnar cache
Aspects of the disclosure are directed to generating cache configurations for caching data for a database. A database management system (DBMS) can search for column data to cache in a database cache to improve performance of the DBMS in resolving queries. Column data selection can be performed automatically and in the background of a deployed DBMS. Periodically, the DBMS can assess the performance benefit of having certain data cached in the database cache and select data for caching based on the assessed performance benefit. The DBMS can also determine the performance benefit of cached data when not cached, as well as select some portions of data to cache over others. The DBMS can also select data for caching based on different degrees of compression, to further improve query resolution performance.
1 . A system comprising:
one or more processors configured to:
receive a query specifying one or more groups of data along a first dimension of a plurality of dimensions;
generate a query execution plan corresponding to the query, the query execution plan comprising query operations and an order in which the query operations are to be performed to execute the query;
generate values for one or more performance metrics based on the query operations within the query execution plan, wherein the values for the one or more performance metrics indicate an estimated change in processing performance prior to loading the one or more groups of data into a database cache, by modeling the one or more groups of data as being stored in the database cache as compared to the one or more groups of data being stored in one or more storage devices; and
load at least a portion of the one or more groups of data specified in the query into the database cache when the values of the one or more performance metrics satisfy one or more performance criteria.
2 . The system of claim 1 , wherein the one or more groups of data are part of a first set of data represented as a cache configuration; and
wherein the one or more processors are further configured to identify the cache configuration from candidate sets of data, each candidate cache configuration comprising respective one or more groups of data along the first dimension.
3 . The system of claim 2 , wherein in identifying the cache configuration, the one or more processors are further configured to search the candidate cache configurations for the first set of data subject to the one or more performance criteria.
4 . The system of claim 3 , wherein respective data represented by each of the candidate cache configurations, when cached in the database cache, consumes a respective amount of memory equal to or less than a memory capacity of the database cache.
5 . The system of claim 3 , wherein the one or more performance criteria comprise identifying a maximum- or a minimum-valued set of data based on respective values of the one or more performance metrics for each group of data in the identified set.
6 . The system of claim 1 ,
wherein the query received is part of a plurality of received queries; and
wherein the one or more processors are further configured to:
sample queries from the plurality of received queries;
receive respective first values for the one or more performance metrics for the sampled queries when respective one or more groups of data for the sampled queries are retrieved from a database;
generate, for the respective one or more groups of data for the sampled queries, respective second values for the one or more performance metrics for the sampled queries when the respective one or more groups of data specified by the sampled queries are cached in the database cache; and
determine, for the sampled queries, a respective performance benefit for the respective one or more groups of data specified in the sampled query based on the respective first and second values for the sampled queries.
7 . The system of claim 6 ,
wherein in loading at least a portion of the one or more groups of data specified in the query into the database cache, based on the values of the one or more performance metrics, the one or more processors are further configured to generate a cache configuration representing one or more selected groups of data, the groups of data selected based on the respective performance benefit for each of the one or more selected groups of data.
8 . The system of claim 7 ,
wherein the one or more groups of data are columns; and
wherein to generate the cache configuration, the one or more processors are configured to:
receive column data corresponding to the sampled queries;
generate distinct column sets from the received column data;
generate a membership map between column sets and columns in the column sets; and
use the membership map to search for a cache configuration with a highest overall performance benefit of columns in the cache configuration, subject to one or more constraints including a database cache memory constraint.
9 . The system of claim 1 , wherein the one or more performance metrics comprise at least one of:
an amount of time between receiving the query by the one or more processors and delivering a response to the query,
a number of processing cycles by the one or more processors to resolve the query, and
a number of operations performed by the one or more processors to resolve the query.
10 . The system of claim 1 , wherein in generating, for the query, the values for the one or more performance metrics, the one or more processors are further configured to:
maintain data corresponding to one or more invalidated blocks in a table of a database queried as part of the one or more groups of data;
estimate a refresh time for refreshing the one or more invalidated blocks in the database cache; and
generate, for the query, the values for the one or more performance metrics in accordance with the maintained data and the estimated refresh time.
11 . The system of claim 1 ,
wherein the first dimension is a column dimension for tables in a database,
wherein data in the database cache is stored along the column dimension, and
wherein data in the one or more storage devices is stored along a row dimension for the tables in the database.
12 . The system of claim 1 ,
wherein in loading the one or more groups of data into the database cache, the one or more processors are further configured to compress each of the one or more groups of data according to one or more compression levels before loading the one or more groups of data into the database cache; and
wherein the one or more processors are further configured to generate the one or more values for the one or more performance metrics in processing the query when each of one or more groups of data are cached in the database cache and compressed according to a respective compression level.
13 . The system of claim 1 , wherein in generating the values for the one or more performance metrics, the one or more processors are further configured to:
determine a first group of data of the one or more groups of data in the database cache; and
generate, for the query, values for the one or more performance metrics when the first group of data is not cached in the database cache.
14 . The system of claim 1 , wherein in loading at least the portion of the one or more groups of data, the one or more processors are further configured to:
determine, for a first group of data, whether the first group of data meets a threshold quantity of valid blocks of data; and
in response to the determination that the first group of data does meet the threshold quantity, load a portion of the first group of data to the database cache that does not include invalidated blocks.
15 . The system of claim 14 , wherein in generating the values for the one or more performance metrics, the one or more processors are further configured to:
track a respective rate of access of portions of the one or more groups of data in one or more storage devices; and
generate, for the query, the values for the one or more performance metrics in accordance with a rate of access of a portion of data specified in the query.
16 . A computer-implemented method, comprising:
receiving, by one or more processors, a query specifying one or more groups of data along a first dimension of a plurality of dimensions;
generating, by the one or more processors, a query execution plan corresponding to the query, the query execution plan comprising query operations and an order in which the query operations are to be performed to execute the query;
generating, by the one or more processors, values for one or more performance metrics based on the query operations within the query execution plan, wherein the values for one or more performance metrics indicate an estimated change in processing performance prior to loading the one or more groups of data into a database cache, by modeling the one or more groups of data as being stored in the database cache as compared to the one or more groups of data being stored in the one or more storage devices; and
loading, by the one or more processors, at least a portion of the one or more groups of data specified in the query into the database cache when the values of the one or more performance metrics satisfy one or more performance criteria.
17 . The computer-implemented method of claim 16 , wherein the one or more groups of data are part of a first set of data represented as a cache configuration; and
wherein the method further comprises identifying the cache configuration from candidate sets of data, each candidate cache configuration comprising respective one or more groups of data along the first dimension.
18 . The computer-implemented method of claim 17 , wherein respective data represented by each of the candidate cache configurations, when cached in the database cache, consumes a respective amount of memory equal to or less than a memory capacity of the database cache.
19 . One or more non-transitory computer-readable storage media storing instructions that when executed by a system comprising one or more processors, cause the system to perform operations comprising:
receiving a query specifying one or more groups of data along a first dimension of a plurality of dimensions;
generating a query execution plan corresponding to the query, the query execution plan comprising query operations and an order in which the query operations are to be performed to execute the query;
generating values for one or more performance metrics based on the query operations within the query execution plan, wherein the values for one or more performance metrics indicate an estimated change in processing performance prior to loading the one or more groups of data into a database cache, by modeling the one or more groups of data as being stored in the database cache as compared to the one or more groups of data being stored in the one or more storage devices; and
loading at least a portion of the one or more groups of data specified in the query into the database cache when the values of the one or more performance metrics satisfy one or more performance criteria.
20 . The system of claim 1 , wherein the one or more processors are further configured to select the query operations based on predetermined criteria, wherein the predetermined criteria corresponds to one or more of:
a use of computing resources;
a use of storage resources;
a number of operations to perform;
a total time needed to execute the query; or
user input that specifies particular query operations to perform.