autonomic physical database design - from indexing to
TRANSCRIPT
Autonomic Physical Database Design - FromIndexing to Multidimensional Clustering
Stephan Baumann, Kai-Uwe Sattler
Databases and Information Systems GroupTechnische Universitat Ilmenau, Ilmenau, Germany
Abstract—Database design is a well-studied and well-understood problem in developing database applications. How-ever, modern application requirements in terms of query responsetime, query throughput, database sizes, database maintenanceand database interactivity capabilities on the one hand, and themanifold features of modern DBMS for physical schema designsuch as numerous different index structures, materialized views,partitioning, and clustering schemes on the other hand makehigh demands on the database design process. With this growingnumber of impact factors on physical database design automaticsolutions have been developed and are provided in most systems,while research in this area still continues. In this paper, we givea survey on the state of the art in autonomic physical design.Starting with the classic index tuning problem and possiblesolutions, we describe further design problems such as choosingmaterializations of aggregations for OLAP and multidimensionalclustering schemes. Finally, we point out to most recent challengesresulting from a shift in requirements and give a direction onhow to tackle these.
I. INTRODUCTION
Modern enterprise applications are typically characterizedby high demands regarding data volume, query responsetime, and transaction throughput. In order to fulfill theserequirements database design and tuning play important roles.However, the complexity of modern DBMS with hundreds oftuning knobs require deep knowledge about system internals,workload, and data characteristics as well as expertise andtremendous effort by the DBA. This problem is even ag-gravated in dynamic scenarios with changing workloads andrequirements which would require a continuous adjustment ofthe system.
Studies from the year 2001 have shown [?] that approx. 80%of the total cost of ownership for database applications arespent on administration (training, deployment, DBA cost) – anobservation that has stimulated the research and developmentof autonomic (also known as self-*) techniques for databasesystems. Techniques considered so far cover different levelsranging from the physical level with indexing, clustering, andpartitioning over the conceptual schema level (with denormal-ization and materialization) and the system level (memoryand bufferpool tuning, statistics and reorganization) up tothe external application level. Features of these levels requiredifferent techniques, have different impact on the performanceand come at different costs and decision intervals: whereasadjusting the sizes of the memory pools of the DBMS canbe done at runtime in a couple of seconds with a direct
impact on the following queries, changing the physical schematakes typically much more time and requires higher effort.In addition, physical database design is a particular difficultproblem: modern systems provide numerous storage and in-dexing options with many parameters and complex databaseschemas offer many opportunities, e.g. for creating indexes.Furthermore, there is often a tradeoff, e.g. between perfor-mance and space consumption or between query performanceand update/maintenance costs.
Finally, more recent developments in the database fieldsuch as on column stores, in-memory processing, but also onNoSQL systems and large-scale parallel processing platformsseem to simplify physical database design, for example bymaking indexes obsolete for column stores. However, recentresearch works reintroduce access paths and storage optionseven in such systems: examples are min-max indexes forcolumn stores [?], indexing for Hadoop [?] or consideringother issues related to physical design such as memory layoutsmitigating NUMA effects [?]. All this together raises theconclusion that the problem of physical database design isstill an important problem requiring autonomous features.
In this paper we summarize contributions to autonomousdatabase design In Section II we formulate the general problemof database design and sketch the search space. In Section IIIwe first introduce the problem of index selection and pointto important work in this field. Section IV looks at theproblem of materialization in the context of database design. InSection V we cover the field of multidimensional clustering,in Section VI we take a look at benchmarking approachesfor automatic design and in Section VII we summarize ourobservations and point to future directions.
II. THE PHYSICAL DATABASE DESIGN PROBLEM
Physical database design is the process of defining thephysical part of a database schema. This includes storagestructures, storage parameters, but also additional access pathssuch as indexes, partitions, summary tables, etc. Though, thesestructures and their parameters are mostly transparent to theapplication level, typically they have a significant impact onthe performance of the applications and, therefore, are infact an important subject of database tuning. The problem ofphysical database design can be formulated as follows. Givena database schema D and a workload W of queries/updates,find a configuration C of physical database objects/parameters
for D which minimizes cost(W ) under some constraints, e.g.not exceeding a certain disk space.
However, the big variety of possible data structures, tech-niques, and parameters – even in commercial systems – makethis a complex and challenging task. Therefore, significantefforts have been made over the last years to automate physicaldesign.
Physical database design tasks can be classified roughlyaccording three dimensions:
• What? describes which database objects are considered.This includes indexes, table partitions, summary tables,etc. as well as combinations of them. Furthermore, in-teraction relationships exist between these objects whichhave to be taken into account.
• When? defines the time of creating or modifying a physi-cal schema. Possible solutions are either static approacheswhich create a schema only once or after explicit invoca-tion by the database administrator or dynamic approacheswhich adjust a schema contiunously (or periodicallly) atruntime.
• Why? specifies the reason for a design decision. This canbe based on heuristics or on cost models capturing theexpected benefit wrt. to a given workload. Furthermore,the necessary information used for this decision can beobtained by analyzing the workload W (or a sample),additional schema information (e.g. including integrityconstraints) or only the database schema D.
Based on these dimensions, physical database design is oftenmodeled as an optimization problem, either as static optimiza-tion, e.g. as a knapsack or integer programming problem, oran online optimization problem.
III. INDEX TUNING
One of the most prominent and also well-studied physicaldesign task is index tuning. Index tuning addresses the IndexSelection Problem (ISP) [?]. In a simplified and typically usedversion it can be formulated as follows. Given a set of queriesQ1, . . . Qm and a set of possible indexes (index candidates)I1, . . . , In where cost(Qk) denotes the execution costs forQk, cost(Qk, Ii) the execution costs for Qk when index Ii ispresent, mcost(Ii) denotes the maintenance costs for Ii (e.g.for updates) and size(Ii) the storage space occupied by Ii.Furthermore, the profit of index Ii for a given query Qk is thedifference between the query execution costs with and withoutthe index, i.e.
profit(Qk, Ii) = max{0, cost(Qk)− cost(Qk, Ii)}
Based on this, the ISP consists of finding a subset of allpossible indexes C ⊆ {I1, . . . , In} which is materialized(called an index configuration) and maximizes the profit forall given queries:
m∑i
max{profit(Qi, Ij) : Ij ∈ C} −∑Ij∈C
mcost(Ij)
while still satisfying given index space constraints S:∑Ij∈C
size(Ij) ≤ S
This problem can be seen as a special case of the knapsackproblem [?]. However, calculating the profit only for a specificindex per query is only an approximation of the real problem.Typically, there are dependencies between indexes that areused together in a certain query and, therefore the exact profitcan only be defined in terms of index sets I ⊆ {I1, . . . , In}.These dependencies are called index interactions and shouldbe taken into account, too.
Unfortunately, ISP is an NP problem [?]: the number ofpossible indexes for a table with n columns is [?]:
n∑k=1
(2k)n!
(n− k)!
Thus, solving the ISP manually is feasible only for very smalldatabase schemas.
However, various approaches to autonomously approximatethe best configuration have been proposed. These autonomousapproaches for index tuning can be classified into the followingcategories [?]:• static index management and recommendation,• online index management,• self-tuning index structures.Static index recommenders have been introduced around
the year 2000 by the major commercial DBMS vendors,e.g. in Microsoft SQL Server [?], [?], IBM DB2 [?], andOracle. These add-on tools analyze a given query workloadto derive recommandations for index creation together withthe estimated cost benefit. This is typically implemented using“hypothetical” objects – in this case virtual indexes – whichexist only in the catalog for the query optimizer but are notphysically created. Possible virtual indexes or index candidatesare identified from the relevant clauses of the SQL queries suchas WHERE, ORDER BY, GROUP BY, and SELECT. For the relevantcolumns and their combinations, virtual indexes are created.The profit of these indexes is estimated as shown above byoptimizing the query twice: with and without the index set.The profit is cumulated for all queries of the workload andthe ISP is solved, e.g. using a modified knapsack solution,integer linear programming or approximative approaches. Sev-eral approaches have been proposed to improve the accuracyof the estimations, e.g. bei taking index interactions [?] andinteractions with other physical structures [?] into account, toreduce the number of optimizer calls [?], or to consider furtherconstraints in addition to disk space [?].
However, though index recommenders exploit runtimestatistics of the given workload, they usually still work indesign mode: the DBA has to decide whether and when therecommended indexes are created. Furthermore, the recom-mended index configuration is optimal only for the givenworkload. When database use changes over time – which isoften the case, e.g caused by seasonal effects, changing interest
QueryOptimizer
QueryOptimizer
Profit?
QueryExecutionPlan Pwout
QueryExecutionPlan Pwith
Recommendation:= Hypothetical Objects in Pwith
Heuristics Hypothetical Objects I
Query Qk
cost(Qk) cost(Qk,Ii)
Fig. 1. Index recommendation using hypothetical objects
and new applications – the index configuration has likely to beadjusted. Physical Design Alerter as proposed in [?] can helpto mitigate this problem by determining when the physicaldesign tool should be invoked and the rather expensive tuningtask is performed again.
Dynamic or online index tuning aims at closing the loopand carrying out the whole process of index recommendationand index creation/dropping automatically. One of the firstsolutions in this category were described in [?], [?], similarapproaches have been presented e.g. in [?], [?]. The basic ideais to maintain the virtual indexes and update their cumulativeprofit over time while processing the queries. Based on thisinformation, decisions about changing the currently material-ized index configuration C can be made, e.g. if adding newindex Inew 6∈ C to C or replacing Iold ∈ C by Inew 6∈ C wouldincrease the total profit.
In this way, online tuning can be modeled as an onlineoptimization problem. Problems of this class can be solvedby online algorithms processing their input piece-by-piecewithout knowing the entire input. Well-known examples forsuch algorithms are the ski rental problem or the ice creamvendor problem [?]: under the assumption that the numberof days for skiing is not known in advance, the question iswhether to continue to pay a repeating cost for renting skis orto buy skis and just pay a one-time cost.
For online index tuning, the problem described abovecan be formulated as follows. Given a workload W =〈Q1, Q2, . . . Qm〉 as a sequence of queries, Ci an index con-figuration, and a configuration schedule S = 〈C0, C1, . . . Cm〉denoting that Qi is processed on Ci at cost cost(Qi, Ci). Now,starting with an initial configuration C0, the goal is to find anoptimal schedule S∗ that minimizes the costs:
S∗ = arg minS
cost(W,S)
where cost(W,S) represents the total execution cost for W inS as well as the transition costs between two configurations(i.e., materializing and/or remove indexes):
cost(W,S) =m∑i=1
cost(Qi, Ci) + transition(Ci−1, Ci)
However, estimating costs for alternative plans, maintainingperformance statistics for virtual indexes, and finding theoptimal configuration cause a significant overhead and it isnot suitable to perform these steps for every single query. Thework demonstrated in [?] addresses this issue by gatheringstatistics at several levels of detail and using heuristics toregulate the overhead, e.g. re-tune more frequently in case ofshifting workloads or reduce activities for stable workloads.Furthermore, issues like throttling and oscillation have tocarefully be taken into account [?].
Another problem is related to acceptance: transition to a newconfiguration means to create new indexes which could resultin unexpected delay of queries triggering the transition. Also,DBAs are typically very careful with changing configurationsof a production system and, therefore, accept fully autonomoustechniques only slowly [?].
One step further to address these problems is to push downthe task of index tuning from the level of index configurationsto the index structures themselves. This could mean to piggy-back index building with queries or even a more fine-grainedcontrol by adjusting the index structure to their usage, e.g.grow with increasing number of accesses.
In [?] we have presented the concept of index-buildingqueries. If a necessary change of the current index configura-tion was detected which pays off even by taking the transitioncost into account, the recommended indexes are created ina deferred mode. Now, as soon as a query is executed thatperforms a scan on the table underlying a deferred index, thequery execution plan is modified to populate the index whilescanning the table and make it available for usage. Howeverat this point, the index-building query does not benefit fromthe index. For this purpose, we have introduced in [?] anapproach that allows to switch execution plan after the indexis populated.
While this approach still treats indexes as atomic objectswhich can be constructed and used only as a whole, the workon adaptive indexing described in [?] breaks this down toincrementally build and maintain indexes. Adaptive indexingis based on the idea of database cracking [?] combined withadaptive merging. As our index-building queries, databasecracking aims at making index creation and maintenance abyproduct of query processing. To achieve this, queries areinterpreted also as an advice to “crack” the physical databaseinto smaller pieces. These pieces are then assembled in acracker index to speedup subsequent queries. For adaptiveindexing, a new cracker index is initialized when a columnis queried by a predicate for the first time. Then, drivenby subsequent queries with predicates on the index column,the cracker index is refined by range partitioning - leadingto a form of incremental quicksort. The work described in
[?] combines this idea with adaptive merging where the firstquery produces already sorted runs which are later mergedby following queries. In this way, indexes become self-tunedand self-optimized data structures which could help to lowerthe burden for index maintenance and eliminate the need ofexplicit tuning.
However, as we will see in the following sections, thisaddresses only one aspect of the problem space of physicaldatabase design.
IV. MATERIALIZATION
OLAP queries typically access large chunks of data andperform complex joins and aggregations. Because of thatit may become feasible to materialize calculations on theraw fact data in order to speed up (later) query execution.Different names have been given to this idea: materializedviews, aggregation tables, materialized query tables or indexedviews. The idea behind it is simple: Invest some (extra) work,that commonly needs to be done for many queries and benefitfrom that during query execution. The additional cost of theinvestment is then amortized over the execution of multiplequeries. That means in this case it is important to have querieswith a at least partially shared workload, which is typical forOLAP scenarios.
Similar to the index selection problem, a view selectionproblem (VSP) can be defined. Let the view candidates beV1, . . . , Vn instead of index candidates I1, . . . , In and theVSP can be defined analogous. Again, the profit for theselected views is to be maximized while still holding the spaceconstraint.
Research on view materialization has evolved from staticto dynamic approaches, where the lifetime of a materializedview has shrunk during this evolvement. In the static casea typically cost-based decision based on workload analysisis made on which views to materialize and in the dynamiccase a starting set of materialized views is chosen and ismaintained and evolved over time based on the analysis of thecurrent load. The latest approaches have become more fine-grained, where instead of full views only potentially interestingintermediate results of query operators (that typically arealready materialized anyway) are kept in a buffer to acceleratequeries in a nearer future.
Research on static approaches led to some form of rec-ommendation tools found in the various commercial products[?], [?], [?]. The original recommendation tools worked on arepresentative workload and the process was for all of themsimilar: generate candidates from the representative workload,prune the candidate set, rate the candidates based on a costmodel and recommend the most valuable candidates.
For example [?], where syntactically relevant candidates ofviews are generated, based on analyzing the SQL queries.From these candidates an interesting subset is chosen as toomany candidates are generated otherwise. This choice is basedon finding interesting table subsets in the candidates, i.e.candidates that cover a tables subset and thus can potentiallyreduce costs when this subset needs to be accessed. After this
region
nation
city
street
day month year
(4, 1) (4, 2) (4, 3)
(3, 1)
(2, 1)
(1, 1)
(3, 2)
(2, 2)
(1, 2)
(3, 3)
(2, 3)
(1, 3)
Fig. 2. Slice of an example lattice with customer and time dimension. Solidlines are direct relationships, dotted lines represent indirect relationships.
pruning step a detailed optimizer-based cost analysis over therepresentative workload is performed for the remaining candi-dates, where potential gain of each view across a representativeset of queries is calculated. A problem of materialized viewsaside from the materializing costs itself is space consumption.For this reason [?] in addition tries to merge candidate viewswith the goal of reducing space consumption but keepingquery performance at about the same level. In the end thetop k views are chosen as recommendations and the DBA hasto choose which ones to deploy. With the variety of databaseobjects, and restrictions on space these advisors typically takemany different objects into account and recommend a setdatabase objects of different kinds.
As the static approaches could not deal with changing work-loads, more dynamic approaches became relevant, leading todesign alerters or design recommenders that are always on.In addition to an initial representative workload, the actualworkload is monitored and the recommendations are adaptedaccording to this. Some solutions treat the workload as a set[?], [?] or others argue to exploit sequence information [?]in order to generate recommendations with more precision.However, in order to exploit sequence information, the querysequence needs to be known in advance to a certain extent.Otherwise no reasoning about when and how to change aview configuration during the sequence would be possible. Inboth cases the general process is similar: find an initial setupbased on an initial workload, monitor the current workload andre-evaluate the current design, suggest design changes if thecurrent workload requires it. Commonly these optimizationsare done in parallel in case of indexing. However, with theadvantage of having views for the current workload comes thedisadvantage of paying for creation parallel to the workload.
All previously explained approaches still lead to recommen-dations and required offline invocations of either the designtool or at least a reaction to the alerter’s output. To over-come these limitations, approaches to continuously monitorworkloads and adapt the design on-the-fly were developed.[?] however focuses only on index creation. In [?] we have
presented strategies based on the analysis of MDX queriesand are applied in the Mondrian [?] system. Here, based ondimension hierarchies, an aggregation lattice for a cube iscreated, where the lattice edges represent dimensions withdimension levels and each crossing point (node) in the latticerepresents a possible aggregation table. Figure 2 shows anexample slice of such a lattice for two dimensions, customerwith a hierarchy from region to street and date with a hierarchyyear to day. The solid line arrows denote which node can bederived from which node, i.e. (4,1) can be calculated from(3,1). For node (3,2) we show dotted line arrows, that denoteindirect relationships, i.e. indirect ways to derive the node(3,2). Based on workload monitoring nodes are added to thelattice, if a corresponding query has been issued or if a node isa common base node for two others, i.e. the two other nodes’aggregation tables can be derived from the common base node.Workload cost is monitored and estimated for materialized andnon-materialized nodes of the lattice and based on heuristicsconfiguration changes are invoked.
But even in the online case someone has to pay forthe materialization of an aggregation table, either the queryresponsible for triggering the materialization or subsequentqueries as the table is materialized in the background. Inaddition, maintenance costs for materialized views need to betaken into account as they can explode with the number andsize of the views.
A last approach in this category that is able to ignore ma-terialization and maintenance costs is recycling intermediateresults. One may argue that this is not strictly database design,but we argue that it is worth viewing intermediate results asdatabase design objects. The idea behind it is simple: keepinteresting intermediate results in a buffer, which can be mem-ory or disk, and reuse these for subsequent similar queries.In [?] an architecture for an operator-at-a-time column storeis presented and in [?] the idea is taken further to pipelinedquery execution. Both approaches rely on keeping intermediateresults that are materialized anyway during query execution,in the operator-at-a-time model this happens by default. In thepipelined approach recycling is based on matching subtreesof the query DAG with corresponding intermediates in therecycler graph. The challenge of course is to select the subtreesfor which an intermediate should be kept. Also, taking therecycler cache to a hierarchical structure so it matches thememory structure is not considered. And thus, these techniquescan only partially be viewed as part of database design.
V. MULTIDIMENSIONAL CLUSTERING
In addition to traditional indexing and materialization strate-gies, multidimensional indexing or clustering techniques havebeen developed, e.g. [?], [?], [?]. These schemes index dataaccording to multiple keys and actually cluster tuples withcommon characteristics on disk. By indexing according tomultiple keys, beneficial access is achieved across a broaderrange of queries, when compared to a single clustered index,especially when multiple indexing keys are involved in exe-cuting a query. However, if only a single index key is relevant,
a specialized clustered index on this key is likely to performbetter.
An automated design approach for Multi-Dimensional Clus-tering in DB2 (short MDC) is described in [?]. Here, potentialdimensions, or clustering keys, are identified by the optimizeranalyzing the SQL queries. Next, a “what-if” analysis isperformed in the optimizer based on simulating the queryfor the highest possible benefit per dimension according tothe predicates of the SQL query. As dimensions can be usedat different granularities, the benefit of each dimension ateach granularity level is calculated based on a logarithmicfunction model. Next, a set of candidate clustering keys isgenerated based on the dimensions with their various degreesof coarseness. The keys in this set are ranked and in a greedyapproach the highest ranked key that satisfies the storageconstraints is picked. However, this analysis is done tableby table, though considering interdependent dimensions, andleads to a set of clustering keys per table from which a multi-dimensional clustering, again per table, is derived in the end.
We have taken the idea further when designing a schemafor our approach of co-clustered tables [?]. Rather thanconsidering a multidimensional clustering table by table, wegenerate a multidimensional clustering for a whole databaseschema. Of importance here is, that we take the foreignkey references in the original schema into account whencreating the co-clustered schema. This way we provide notonly an access path optimized database design but also a queryexecution optimized database design by explicitly consideringjoins to be important for query execution. In [?] we show thatquery processing (or, more precisely, operator execution foraggregation, grouping, sort and join) can greatly benefit fromdatabase design in such a multidimensional setup.
Figure 3 sketches the idea of co-clustering for a samplesetup with three different fact tables and three dimensions.Ideally all dimensions are used along all foreign key joinpaths in such a setup. This allows for efficient selectionpushdown but also for efficient processing of joins, sortsand aggregations, for details see [?]. For example, the timedimension is used to co-cluster tables A and B. The decision onusing this dimension in both tables is based on the foreign keyrelationship of A and B (FK A B). As a result, time selectionscan be pushed down to both tables and in case of a join theco-clustered layout from the time dimension contributes to thesandwiched execution of the join of A and B. Sandwichedexecution here is similar to a partitioned execution of thejoin and matching partitions stem from the co-clustering. Also,aggregations/groupings based on a time attribute or an attributefunctionally determining a time attribute can be executed in apartitioned fashion, for more details we refer to [?].
A database design is then created in three steps. First dimen-sions need to be identified. Here, we currently do not rely onquery analysis for finding dimensions but rather just take DBAhints, formulated as CREATE INDEX statements. Second, thesedimensions are propagated through the original schema graphaccording to the defined foreign key relationships as illustratedin Figure 3. And third, each table is created autonomously
product
1nails
valuescrews
bolts4
23
id
pins
FK_A_D2
time
2000199919981997value
4321id
table B
253
72
7
195
899
...
192
A_id
...39
671
220
28337
399
...
...
340
C_id
281121
y
a
z
a
y
y
b
bc
z
...
...
d
...
cols
z
other
d
...
a
z
x
FK_A_D1
FK_B_C
FK_C_D1
FK_C_D3
FK_B_A
table Aid
252253
2
999
3
250...
1
251
...
g_id1
21
3
1
2
2
...
...
4
t_id
l
z
...
m
...
m3
ok
4
l
2 m
p
...
2
1
1 p
l
1 q
q
...
...
...
p
oother cols
k4table C
2
1
400
2
f
p_id
1
s
...
g
2
1 t
...
21h
...
...
3
s
...
id
f
tg
f
r
138
136
rg_id
...
...
4
433
2
f
s
...
2
1
137
g
...
r
135
cols1
...
other
3
4
geography
2Asia
Africa1value
3America
4 Europe
id
product
1nails
valuescrews
bolts4
23
id
pins
FK_A_D2
time
2000199919981997value
4321id
table B
253
72
7
195
899
...
192
A_id
...39
671
220
28337
399
...
...
340
C_id
281121
y
a
z
a
y
y
b
bc
z
...
...
d
...
cols
z
other
d
...
a
z
x
FK_A_D1
FK_B_C
FK_C_D1
FK_C_D3
FK_B_A
table Aid
252253
2
999
3
250...
1
251
...
g_id1
21
3
1
2
2
...
...
4
t_id
l
z
...
m
...
m3
ok
4
l
2 m
p
...
2
1
1 p
l
1 q
q
...
...
...
p
oother cols
k4table C
2
1
400
2
f
p_id
1
s
...
g
2
1 t
...
21h
...
...
3
s
...
id
f
tg
f
r
138
136
rg_id
...
...
4
433
2
f
s
...
2
1
137
g
...
r
135
cols1
...
other
3
4
geography
2Asia
Africa1value
3America
4 Europe
id
Fig. 3. Table setup with three fact tables and three dimensions. Left side: plain data without clustering. Right side: schematic visualization of a co-clusteredtable layout - same color means compatible clustering on that dimension.
based on the assigned dimensions from the second step, wherethe granularity of the clustering is auto-tuned according to thedata distribution and the disk access block size. In the thirdstep a round robin assignment of the dimension bits insuresthat all dimensions contribute to the clustering, which laterprovides the benefits for data access and query processing.
By integrating database design and query processing, orbetter support query processing by the database design, weachieve a design that we call workload agnostic. From ourperspective this means that it is able to serve a broad rangeof queries, not always to the optimum, but with a significantcontribution for very many different queries. In addition weview a co-clustered design as the primary physical design of adatabase that introduces only a marginal space overhead anddoes not introduce additional maintenance as extra indexes ormaterialized views would do.
VI. BENCHMARKING THE PHYSICAL DESIGN
In this paper we have looked at various autonomousdatabase design approaches, from traditional and adaptiveindexing over static and dynamic materialization strategies tomultidimensional clustering. Each of the discussed approacheshas its particular use case and this way its right to exist.However, comparing the different approaches is quite difficult.In [?] ideas on how to benchmark design recommenders areproposed and the authors of [?] have taken these ideas furtherdefining a benchmark for online database design tools. Incontrast to traditional database benchmarking where the finalsystem performance is of interest, a design benchmark needsto make a statement about effectiveness or robustness of arecommended or automatic design.
[?] argue that the benchmark needs to be aware of theperformance goals of recommenders, as these can range fromtotal execution time of a workload over an improvement ratebetween old and newly recommended design to executing acertain number/fraction of all queries within a certain time.The benchmark uses real world (NREF) and synthetic (TPC-H) data for each of which a large set of queries if defined ofwhich a sample set is used during the benchmark. The rec-
ommenders are then compared against two additional setups,one using primary key indexes and one using single columnindexes for all possibly relevant columns.
Benchmarking online index tuning algorithms is quite dif-ferent to benchmarking recommenders. The characteristic dif-ference described in [?] is that shifting workloads are requiredin order to trigger design changes by the online algorithms.As a metric here the total execution time and the optimizationobjective are used. The latter provides insight on how thesystem performs during query execution and online adaption.Other than [?] no further reasoning about performance goalsof the tuning tools are made.
To understand and assess more fine-granular tuning asin adaptive indexing, [?] proposed benchmarking guidelinessuited to these scenarios. An adaptive indexing approachtypically requires a startup phase where indexes are build pieceby piece and query execution is actually slowed down beforeit reaches a stable phase of good performance. The authorsactually split the time span in between in two phases, thenursing phase, that starts, when execution time drops below theexecution time without any index and the growing phase thatstarts when the first queries arrive that no longer contributeto the adaptive index, i.e. the relevant part of the index isalready present and these queries are executed with optimalperformance. So not only the stable performance in the end orthe overall execution time but also the duration of the differentphases are of interest. As adaptive indexing techniques aredesigned for shifting workloads in particular, the benchmarkshould account for this. Each shift in a workload will typicallyrequire re-tuning the system and imply an extra cycle of theabove phases.
VII. CONCLUSIONS AND OUTLOOK
Physical database design is one of the fundamental prob-lems of database design and development which is subjectof research since the seventies, e.g. [?]. Over the last twodecades significant effort has been made to relieve the burdenof physical design tuning from the DBA by developing au-tonomous techniques adjusting physical schema configurations
automatically or at least give recommendation for tuning steps.In this paper, we have discussed some of these basic designproblems as well as autonomous techniques to tackle them.
Though, numerous approaches and techniques have beenproposed and also found their way into commercial databasesystems, there are still some challenging research problems.First of all, physical design tuning aims at improving per-formance and scalability of a database – however, in thedaily business of database operation peek performance isnot the main goal. Instead, autonomous techniques shouldimprove the robustness of the database system, both in termsof predictability and good performance [?].
In [?] we already argued for adaptive index structures whichhave received attention over the past years, namely databasecracking and index merging techniques [?], [?]. In additionto that adaptive materialization strategies [?], [?] have beenproposed. However, to our knowledge, these techniques havenot yet found their way into the major database systems.Combined with a workload agnostic setup [?], to minimizethe penalty when a query does not hit the highly specializedadaptive index, systems should be one step closer to providingrobust and high performance.