Workload-driven recommendations for Columnstore and Rowstore indexes in relational databases
Provided are methods and systems for generating physical database design tuning recommendations. Given a workload, the system analyzes the workload to identify and recommend a set of rowstore and columnstore indexes optimal for the performance of the workload. The system is designed to estimate the size of the columnstore index (at the granularity of each column) without actually building the index, estimate the improvement in query performance that each columnstore index would result in when built, and automatically derive the workload used for the physical design tuning task by analyzing stored query execution history data. This automatic workload derivation is orthogonal to columnstores and can be used even when columnstore indexes are not being used.
1. A computer-implemented method for tuning a database, the method comprising:
receiving, by a computer, an input workload, the workload including a plurality of queries;
identifying, by the computer, for each query in the workload, candidate indexes for the query, wherein the candidate indexes include rowstore indexes and columnstore indexes;
for each candidate columnstore index of the candidate indexes:
estimating, by the computer, a size of the candidate columnstore index, wherein the estimating of the size of the candidate columnstore index includes:
collecting a sample of data from a table of the database to create statistics corresponding to the candidate columnstore index;
estimating, for each column of a plurality of columns in the sample of data, a number of distinct values in the column;
estimating, for each column of the plurality of columns in the sample of data, a number of runs in the column; and
generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns;
creating, by the computer, a corresponding hypothetical index based on the estimated size of the candidate columnstore index, and
generating, by the computer, a cost estimate for the candidate columnstore index based on the corresponding hypothetical index created for the candidate columnstore index;
for each query in the workload, determining, by the computer, one or more of the candidate indexes to recommend for the query based on the generated cost estimates; and
generating, by the computer, a tuning recommendation for performance of the workload based on the one or more candidate indexes determined for each query in the workload.
2. The method of claim 1 , wherein creating a hypothetical index includes:
creating an index (i) with metadata and sampled statistics and (ii) without actual data.
3. The method of claim 1 , wherein, for each candidate columnstore index, a hypothetical index is created using an application programming interface (API) of a database server.
4. The method of claim 1 , wherein generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns includes:
determining a per-column size of the columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns; and
scaling-up the determined per-column size of the columnstore index using a sampling rate associated with the sample of data.
5. The method of claim 1 , wherein collecting a sample of data from a table of the database includes sampling a number of rows in the table.
6. The method of claim 1 , wherein, for a candidate columnstore index, estimating a size of the candidate columnstore index includes:
building a columnstore index on the sample of data collected from the table of the database;
determining a per-column size of the columnstore index built on the sample of data; and
scaling-up the determined per-column size of the columnstore index using a sampling rate associated with the sample of data.
7. A system for tuning a database, the system comprising:
one or more processors; and
a non-transitory computer-readable medium coupled to the one or more processors having instructions stored thereon that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
receiving an input workload, the workload including a plurality of queries;
identifying, for each query in the workload, candidate indexes for the query, wherein the candidate indexes include rowstore indexes and columnstore indexes;
for each candidate columnstore index of the candidate indexes:
estimating a size of the candidate columnstore index, wherein the estimating of the size of the candidate columnstore index includes:
collecting a sample of data from a table of the database to create statistics corresponding to the candidate columnstore index;
estimating, for each column of a plurality of columns in the sample of data, a number of distinct values in the column;
estimating, for each column of the plurality of columns in the sample of data, a number of runs in the column; and
generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns;
creating a corresponding hypothetical index based on the estimated size of the candidate columnstore index, and
generating a cost estimate for the candidate columnstore index based on the corresponding hypothetical index created for the candidate columnstore index;
for each query in the workload, determining one or more of the candidate indexes to recommend for the query based on the generated cost estimates; and
generating a tuning recommendation for performance of the workload based on the one or more candidate indexes determined for each query in the workload.
8. The system of claim 7 , wherein the one or more processors are caused to perform further operations comprising:
creating an index (i) with metadata and sampled statistics and (ii) without actual data.
9. The system of claim 7 , wherein, for each candidate columnstore index, a hypothetical index is created using an application programming interface (API) of a database server.
10. The system of claim 7 , wherein the one or more processors are caused to perform further operations comprising: determining a per-column size of the columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns; and scaling-up the determined per-column size of the columnstore index using a sampling rate associated with the sample of data.
11. The system of claim 7 , wherein collecting a sample of data from a table of the database includes sampling a number of rows in the table.
12. The system of claim 7 , wherein the one or more processors are caused to perform further operations comprising: building a columnstore index on the sample of data collected from the table of the database determining a per-column size of the columnstore index built on the sample of data; and scaling-up the determined per-column size of the columnstore index using a sampling.
13. A tangible, non-transitory computer readable medium, or media, storing machine readable instructions that, when executed by one or more processors, cause the one or more processors to perform operations comprising:
receiving an input workload, the workload including a plurality of queries;
identifying, for each query in the workload, candidate indexes for the query, wherein the candidate indexes include rowstore indexes and columnstore indexes;
for each candidate columnstore index of the candidate indexes:
estimating a size of the candidate columnstore index, wherein the estimating of the size of the candidate columnstore index includes:
collecting a sample of data from a table of the database to create statistics corresponding to the candidate columnstore index;
estimating, for each column of a plurality of columns in the sample of data, a number of distinct values in the column;
estimating, for each column of the plurality of columns in the sample of data, a number of runs in the column; and
generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns;
creating a corresponding hypothetical index based on the estimated size of the candidate columnstore index, and
generating a cost estimate for the candidate columnstore index based on the corresponding hypothetical index created for the candidate columnstore index;
for each query in the workload, determining one or more of the candidate indexes to recommend for the query based on the generated cost estimates; and
generating a tuning recommendation for performance of the workload based on the one or more candidate indexes determined for each query in the workload.
14. The tangible, non-transitory computer-readable medium or media of claim 13 , wherein the machine readable instructions, when executed by the one or more processors, cause the one or more processors to perform further operations comprising:
creating an index (i) with metadata and sampled statistics and (ii) without actual data.
15. The tangible, non-transitory computer-readable medium or media of claim 13 , wherein, for each candidate columnstore index, a hypothetical index is created using an application programming interface (API) of a database server.
16. The tangible, non-transitory computer-readable medium or media of claim 13 , wherein the machine readable instructions, when executed by the one or more processors, cause the one or more processors to perform further operations comprising: determining a per-column size of the columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns; and scaling-up the determined per-column size of the columnstore index using a sampling rate associated with the sample of data.
17. The tangible, non-transitory computer-readable medium or media of claim 13 , wherein the machine readable instructions, when executed by the one or more processors, cause the one or more processors to perform further operations comprising:
building a columnstore index on the sample of data collected from the table of the database;
determining a per-column size of the columnstore index built on the sample of data; and
scaling-up the determined per-column size of the columnstore index using a sampling rate associated with the sample of data.