Automated design of multidimensional clustering tables for relational databases

Abstract

The ability to physically cluster a database table on multiple dimensions is a powerful technique that offers significant performance benefits in many OLAP, warehousing, and decision-support systems. An industrial implementation of this technique for the DB2® Universal Database™ (DB2 UDB) product, called multidimensional clustering (MDC), which co-exists with other classical forms of data storage and indexing methods, was described in VLDB 2003. This paper describes the first published model for automating the selection of clustering keys in single-dimensional and multidimensional relational databases that use a cell/block storage structure for MDC. For any significant dimensionality (3 or more), the possible solution space is combinatorially complex.The automated MDC design model is based on whatif query cost modeling, data sampling, and a search algorithm for evaluating a large constellation of possible combinations. The model is effective at trading the benefits of potential combinations of clustering keys against data sparsity and performance. It also effectively selects the granularity at which dimensions should be used for clustering (such as week of year versus month of year). We show results from experiments indicating that the model provides design recommendations of comparable quality to those made by human experts. The model has been implemented in the IBM® DB2 UDB for Linux®, UNIX® and Windows® Version 8.2 release.

MLPPI Wizard: An Automated Multi-level Partitioning Tool on Analytical Workloads

“… The tool's predicate-driven method for search space enumeration can be applied to any clustering or partitioning scheme in a relational DBMS, allowing general expressions for partitioning definitions; note also that any complex query workload, giving many different partitioning options, can benefit from the proposed tool to find almost an optimal partitioning in an automated fashion.  Our tool is totally outside the database server, thereby incurring no overhead of instrumenting query optimizer's code, which is required by some existing tools [4][5][6].  To the best of our knowledge, our tool is the very first physical database design tool to address the multi-level partitioning problem on analytical workloads.…”

“…Physical database design [4][5][6][7][8][9][10][11][12][13][14][15][16][17][18][19][20][21][22] has been discussed in academic research and industrial communities in the past decades. The major DBMS vendors have led much of the work.…”

“…The major DBMS vendors have led much of the work. Their specific interests have been around automating the physical design for table partitioning [4][5][6][12][13][14], indexes/materialized views [8,[15][16][17][18][19][20][21], and integration [22].…”

“…Some of the tools in IBM DB2 [5], Oracle [12], and MS SQL Server [4] appear to be similar to our MLPPI wizard. With respect to problem scope and approach, however, our wizard is fundamentally different from the existing tools.…”

Regularized Cost-Model Oblivious Database Tuning with Reinforcement Learning

“…That task can be done either manually or with the help of sophisticated tools provided by database vendors. Based on these representative workloads, new database configurations are realized: for example, new beneficial indexes to be created [1,20,45], smart vertical partitioning for reducing I/O costs [17,22,33], or possibly a combination of index selection, partitioning and replication for both stand-alone databases [11,13,27] and parallel databases [2,32].…”