Workload Representation across Different Storage Architectures for Relational DBMS Andreas Lübcke Veit Köppen Gunter Saake School of Computer Science School of Computer Science School of Computer Science University of Magdeburg University of Magdeburg University of Magdeburg Magdeburg, Germany Magdeburg, Germany Magdeburg, Germany andreas.luebcke@ovgu.de veit.koeppen@ovgu.de gunter.saake@ovgu.de ABSTRACT 2. STATISTICS REPRESENTATION Database systems differ from small-scale stripped database pro- To select the optimal storage architecture, we have to analyze a grams for embedded devices with minimal footprint to large-scale given workload; thus, we need to decompose this workload. We OLAP applications for server devices. For relational database man- have to map single operations of a workload (at least of one query) agement systems, two storage architectures have been introduced: and their optimizer statistics to evaluable patterns. Therefore, we the row-oriented and the column-oriented architecture. To select present our pattern framework which stores all necessary statistics the optimal architecture for a certain application, we need workload for subsequent performance analyses. In [18], we illustrate the information and statistics. In this paper, we present a workload rep- procedure of our decision process regarding the storage architecture resentation approach that enables us to represent workloads across selection. Below, we outline the design of our pattern framework. different DBMSs and architectures. Our approach also supports fine granular workload analyses based on database operations. 2.1 Pattern Types To analyze the influence of single operations, we propose three patterns for operations in workload queries. The three operation 1. INTRODUCTION patterns are tuple operations, aggregations and groupings, and join operations. We define a number of sub-patterns for each of New requirements for database applications [23, 26, 27] came up those three to characterize particular operations more precisely within in recent years. Therefore, database management system (DBMS) the patterns. This way, we support analyses based on the three pat- vendors and researchers developed new technologies, e.g., column- terns and additionally fine granular analyses based on sub-patterns, oriented DBMSs (column stores) [1, 22, 30]. New approaches are i.e., we can determine where the majority of costs emerge within a developed to satisfy the new requirements for database applica- workload (at least one query). tions, thus the number of candidates in the decision process has also First, the tuple operation pattern covers all operations that pro- increased. Moreover, new application fields imply a more complex cess or modify tuples, e.g., selection, sort operations. We propose decision process to find the suitable DBMS for a certain use case. this pattern for performance analyses because row stores process We need statistics to come to a suitable design decision. These directly on tuples in contrast to column stores that costly recon- statistics have to be represented system-independent for sound and struct tuples. We identify the following sub-patterns: comparable decision. That implies the independence of workload representation from different storage architectures. In this paper, Sort/order operation: Sort/order operation creates sequences of we introduce a new approach of workload statistics aggregation tuples and affects all attributes of a tuple. We consider dupli- and maintenance across different DBMSs and architectures. We cate elimination as a sort operation because an internal sort showed in [16] that query-based workload analyses, as described is necessary to find duplicates. in [7], are not suitable to select the optimal storage architecture. To Data access and tuple reconstruction: Row stores always access overcome drawbacks of query-based workload analyses, we define tuples and column stores must reconstruct tuples to access workload patterns based on database operations. We introduce a more than one column. workload decomposition algorithm that enables us to analyze query Projection: Projection returns a subset of tuple attribute values parts. Workload patterns represent the decomposed workloads to and causes (normally) no additional costs for query execu- compare the performance of database operations for column and tion. row stores. These workload patterns contain all statistics needed Filtering: Filtering selects tuples from tables or intermediate re- for cost estimations. We simulate the statistic gathering process sults based on a selection predicate, e.g., selection in WHERE- with a exemplary workload. clause and HAVING-clause. Second, we cover all column processing operations in the aggre- gation and grouping pattern, e.g., COUNT and MIN/MAX. We propose this pattern as counterpart to the tuple operation pattern. The operations of this pattern work only on single columns except for grouping operations which can also process several columns, e.g., GROUP BY CUBE. Due to column-wise partitioned data and single column processing, column stores perform well on aggrega- 23rd GI-Workshop on Foundations of Databases (Grundlagen von Daten- tions (cf. [16]). We identify the following sub-patterns: banken), 31.05.2011 - 03.06.2011, Obergurgl, Austria. Min/Max operation: The min/max operation provides the mini- Copyright is held by the author/owner(s). mum/maximum value of a single attribute (column). 79 Sum operation: This operation provides the sum of all values in sustain the comparability of operations beyond the architectures be- one column. cause row stores are not affected by tuple reconstructions. Count operation: The count operation provides the number of at- We assume further workload decomposition is not meaningful tribute values in a column and COUNT(*) provides only the because administrative costs would affect the performance of exist- number of key values, thus it processes a single column. ing systems as well as the comparability of performance issues be- Average operation: The average operation computes all values of tween the architectures according to certain workload parts. These a single column as well as the sum operation, but it can have impacts would disadvantageously affect the usability of our pattern different characteristics, e.g., mean (avg) or median. framework. Group by operation: This operation merges equal values accord- ing to a certain column and results in a subset of tuples. Grouping across a number of columns is also possible. 3. QUERY DECOMPOSITION Cube operations: The cube operation computes all feasible com- In this section, we introduce the query decomposition approach. bination of groupings for selected dimensions. This gener- First, we illustrate the (re-) used DBMS functionality and how we ation requires the power set of aggregating columns, i.e., n gather necessary statistics from existing systems. Second, we in- attributes are computed by 2n GROUP BY clauses. troduce the mapping of decomposed query parts to our established Standard deviation: The standard deviation (or variance) is a sta- workload patterns and show a decomposition result by example. tistical measure for the variability of a data set and is com- Our approach is applicable to each relational DBMS. Nevertheless, puted by a two pass algorithm which means two cycles. we decide to use a closed source system for the following consider- Third, the join pattern matches all join operations of a workload. ations because the richness of detail of optimizer/query plan output Join operations are costly tasks for DBMSs. This pattern shows dif- is higher and easier to understand. More detailed information will ferences of join techniques between column and row stores, e.g., result in more accurate recommendation. join processing on compressed columns or on bitmaps. Within this pattern, we evaluate the different processing techniques against 3.1 Query Plans each other. Consequently, we define the following sub-patterns: A workload decomposition based on database operations is nec- essary to select the optimal storage architecture (cf. [16]). There- Vector based: The column oriented architecture naturally supports fore, we use query plans [4] which exist in each relational DBMS. vector based join techniques while row stores have to main- On the one hand, we reuse database functionality and avoid new tain and create structures, e.g., bitmap (join) indexes [15]. calculation costs for optimization. On the other hand, we make Non-vector based: This pattern matches "classic" join techniques use of system optimizer estimations that are necessary for physical (from row stores1 ) to differentiate the performance between database design [10]. vector and non-vector based join, thus we can estimate ef- Based on query plans, we gather statistics directly from DBMS fects on the join behavior by architecture. and use the optimizer cost estimations. The example in Listing 1 shows an SQL query and we transform this to a query plan in Ta- We only propose these two sub-patterns because the join concepts, ble 1 [19]. Table 1 already offers some statistics such as number e.g., merge or nested loop join, exist for both architectures. Hence, of rows, accessed bytes by the operation, or costs. Nevertheless, we assume that there is no necessity to map each join concept into Table 1 shows only an excerpt of gathered statistics. All possible its own sub-pattern. Figure 1 shows all introduced patterns and values for query plan statistics can be found in [20] Chapter 12.10. their relation to each other based on our exemplary workload. Hence, we are able to determine the performance of operations on 2.2 Dependencies between Patterns a certain architecture (in our example a row store) by statistics such as CPU costs and/or I/O costs. In addition to performance evalua- Database operations are not always independent from each other. tion by several estimated costs, we can gather further statistics from We can identify dependencies between the following patterns: join, query plans which may influence performance of an operation on a filtering, sort/order, group/cube, and data access pattern. certain architecture, e.g., cardinality. For column stores, the oper- Join operations innately imply tuple selections (filtering pattern). ation cardinality can indirectly affect performance if the operation However, the tuple selection itself is part of the join operation by processes several columns, thus column stores have to process a definition, thus we assume that an additional decomposition of join number of tuple reconstructions, e.g., high cardinality means many operations is not necessary. Moreover, new techniques would have reconstructions. Thus, we use meta-data to estimate influences of to be implemented to further decompose join operations and gather data itself on the performance, e.g., we can compute the selectivity the necessary statistics. Hence, the administrative cost for tun- of attributes. ing will be noticeably increased. To a side-effect, the comparison of join techniques belonging to different architectures will be no longer possible because of system-specific decomposition. 3.2 From Query Plans to Workload Patterns We state that two different types of sort/order operation can oc- We have to map the gathered statistics from DBMS to our work- cur, i.e., implicit and explicit sort. The explicit sort is caused by load patterns. We use a second example [21] (Listing 2 and Table 2) workload or user, thus we consider this operation in the sort/order to simulate a minimum workload instead of a single query. In the pattern. In contrast, we do not consider the implicit sort operation following, we illustrate the mapping approach by using the exam- in the sort/order pattern because this sort operation is caused by the ples in Listing 1 and 2. In our name convention, we define a unique optimizer, e.g., for sort-merge join. Therefore, we assign all costs number2 that identifies queries of the workload within our mapping of grouping to the GROUP BY (or CUBE) pattern including the algorithm, i.e., 1.X represents query 1 (Listing 1) and equally 2.X sort costs to sustain comparability. represents query 2 (Listing 2). Furthermore, we reuse the opera- Third, tuple reconstruction is part of several operations for col- tion IDs from query plans (Table 1 and 2) in the second hierarchy umn stores. We add these costs to the tuple operation pattern. We 2 In the following considerations, we start with 1 which represents 1 Some column stores also support these join techniques. the first query. 80 1 SELECT * ID Operation Name Rows Bytes Cost (%CPU) ... 2 FROM employees e JOIN departments d 0 SELECT STATEMENT 106 9328 7 (29) ... 3 ON e.department_id=d.department_id 1 SORT ORDER BY 106 9328 7 (29) ... 4 ORDER BY last_name; *2 HASH JOIN 106 9328 6 (17) .... 3 TABLE ACCESS FULL DEPARTMENTS 27 540 2 (0) ... 4 TABLE ACCESS FULL EMPLOYEES 107 7276 3 (0) ... Listing 1: Example SQL query (14- 1) [19] Table 1: Textual query plan of SQL example (14-1) [19] level (for X), e.g., 1.4 is the operation with ID 4 of query 1 (cf. can be also mapped to our workload patterns. Representatively, we Table 1). In the following, we refer the CPU cost of Table 1 and 2. illustrate the mapping of C-Store/Vertica query plan operations in- The first query (Listing 1) is decomposed into four patterns. First, troduced in [25] and map them to our workload patterns as follows: we see the data access operation of the department (ID 3) and the employees (ID 4) tables in the corresponding query Decompress: Decompress is mapped to the data access pattern. plan in Table 1. The total cost for the data access operations is 5. This operation decompresses data for subsequent operations Second, the join operation (ID 2) is executed with a hash join in the query plan that cannot process on compressed data algorithm. The hash join cost is only 1 because in Table 1 costs (cf. [1]). Select: Select is equivalent to the selection of relational algebra are iteratively sum up and the costs of its children (5) and its own with the exception that the result is represented as bitstring. cost (1) are summed up to 6 for ID 2. Third, the sort opera- Hence, we map it to the filtering pattern. tion (ID 1) implements the ORDER BY statement with cost of Mask: Mask process on bitstrings and returns only those values 1. The total costs of all processed operations are 7 now. Fourth, whose associated bits in the bitstring are 1. Consequently, the select statement (ID 0) represents the projection and causes we map mask to the filtering pattern. no additional cost (remain 7). Following our name convention, the Project: Projection is equivalent to the projection of relational al- identifiers from 1.0 to 1.4 represent the operations of our first gebra, thus this operation is mapped to the projection pattern. query (Listing 1) in Figure 1. We also decompose the second example (Listing 2) into four op- Sort: This operation sorts the columns of a C-Store projection ac- eration types (cf. Table 2). First, IDs 3, 7, and 8 represent cording to a (set of) sort column(s). This technique is equiv- the data access operations and cause total costs of 14. Second, alent to sort operations on projected tuples, i.e., we can map the optimizer estimates both hash joins (ID 2 and 6) with no this operation to the sort/order pattern. (additional) costs because their costs are only composed by the Aggregation Operators: These operations compute aggregations summed costs of their children (ID 3, 4 and ID 7, 8). Third, and groupings like in SQL [1], thus we directly map these the GROUP BY statement in Listing 2 is implemented by hash- operations to the corresponding sub-pattern in the aggrega- based grouping operations (ID 1 and ID 5). The cost of each tion & grouping pattern. HASH GROUP BY is 1 and the total costs of this operation type Concat: Concat combines C-Store projections sorted in the same are 2. Fourth, the projection (ID 0) and the sum operation rep- order into a new projection. We regard this operation as tuple resented by select statement causes again no additional costs. If reconstruction and map it to the corresponding pattern. the sum operation causes costs then it will be represented by a sep- Permute: This operation permutes the order of columns in C-Store arate operation (ID). Following our name convention, the identi- projections according to the given order by a join index. It fiers from 2.0 to 2.8 represent the operations of the second query prevents additional replication overhead that would emerge (Listing 2) in Figure 1. The view (ID 2.4) is not represented in through creation of join indexes and C-Store projections in our workload pattern because its costs are already mapped by its several orders. This operation is used for joins, thus we map child operations (ID 2.5-2.8). its cost to the join pattern. In our examples, we summarize single operations of similar types Join: We map this operation to the join pattern and distinguish two (five for example query two). In the following, we list the five op- join types. First, if tuples are already reconstructed then we eration types and assign them to our workload patterns and their process them as row stores, i.e., we map this join type to sub-patterns that we introduced in Section 2. The join operations the non-vector based join pattern. Second, the join operation of our example queries ID 1.2, 2.2, and 2.6 are assigned to only processes columns that are needed to evaluate the join the non-vector based join pattern. We assign the operations with predicate. The join result is a set of pairs of positions in the ID 1.3, 1.4, 2.3, 2.7, and 2.8 to the data access sub- input columns [1]. This join type can process on compressed pattern of the tuple operation pattern. We also assign the projec- data as well as it can use vector based join techniques, thus, tions (ID 1.0 and 2.0) and the sort operation (ID 1.1) to we map this join type to the vector based join pattern. the tuple operation pattern. Finally, we assign the group by op- Bitstring Operations: These operations (AND, OR, NOT) pro- erations (ID 2.1 and 2.5) to the group by sub-pattern within the cess bitstrings and compute a new bitstring with respect to aggregation and grouping pattern. We present the result in Figure 1 the corresponding logical operator. These operations im- whereby we only show ID and cost of each operation for reasons plement the concatenation of different selection predicates. of readability. We state that the we do not need to directly extract Therefore, we map these operations to the filtering pattern. statistics from existing systems. Our pattern framework is system Finally, we state that our approach can be used for each rela- independent, thus we are also able to use already extracted (or ag- tional DBMS. Each relational DBMS is referable to the relational gregated) data as well as estimated values. data model, so these DBMSs are based on the relational algebra in some manner too. Thus, we can reduce or map those opera- 3.3 Operations in Column Stores tions to our workload patterns; in worst case, we have to add an We state that we do not need a separate decomposition algorithm architecture-specific operation for hybrid DBMSs to our pattern, for column stores, i.e., the query plan operations of column stores e.g., tuple reconstruction for column stores. For a future (relational) 81 ID Operation Name Rows Bytes Cost (%CPU) ... 0 SELECT STATEMENT 144 4608 16 (32) ... 1 SELECT c.cust_last_name, SUM(revenue) 1 HASH GROUP BY 144 4608 16 (32) ... 2 FROM customers c, v_orders o *2 HASH JOIN OUTER 663 21216 15 (27) ... 3 WHERE c.credit_limit > 2000 *3 TABLE ACCESS FULL CUSTOMERS 195 2925 6 (17) ... 4 AND o.customer_id(+) = c.customer_id 4 VIEW V_ORDERS 665 11305 ... 5 GROUP BY c.cust_last_name; 5 HASH GROUP BY 665 15960 9 (34) ... *6 HASH JOIN 665 15960 8 (25) ... Listing 2: Example SQL query (11- *7 TABLE ACCESS FULL ORDERS 105 840 4 (25) ... 8 TABLE ACCESS FULL ORDER_ITEMS 665 10640 4 (25) ... 9) [21] Table 2: Textual query plan of SQL example (11-9) [21] Workload Aggregation & Join Tuple Operation Grouping Non-vector based Count Min / Max Projection ID Cost Filtering Vector based ID Cost 1.2 1 (Having, Selection) 1.0 0 2.2 0 Sum Cube 2.0 0 2.6 0 Tuple Reconstruction / Data Access St. Dev. Avg ID Cost Sort / Order 1.3 2 ID Cost 1.4 3 1.1 1 Group by 2.3 6 2.7 4 ID Cost Median 2.8 4 2.1 1 2.5 1 Figure 1: Workload patterns with cost of operations for the row store example workload hybrid storage architecture, such an operation could be necessary measurements of ICE are on the data pack (65k) level. to map the cost for conversions between row- and column-oriented In Figure 2, we present our workload patterns with I/O costs of structures and vice versa. the corresponding TPC-H queries. As we mentioned before, the projection operation causes no additional costs. Hence, the I/O 4. DEMONSTRATING EXAMPLE costs in Table 3 and Figure 2 represent the size of final results. The stored information can be analyzed and aggregated in decision We decide to simulate the workload with the standardized TPC- models with any necessary granularity. In our example, we only H benchmark (2.8.0) to show the usability of our approach. We use sum up all values of the data access pattern for each query to calcu- the DBMSs Oracle 11gR2 Enterprise Edition and Infobright ICE late the I/O costs per query in Kbytes. For these three queries, all 3.3.1 for our experiments3 . We run all 22 TPC-H queries and ex- results and intermediate results are smaller than the available main tract the optimizer statistics from the DBMSs. For reasons of clar- memory, thus no data has to be reread subsequently. Oracle reads ity and comprehensibility, we only map three representative TPC-H 1452.133 Kbytes for query Q2 and takes 8.14 seconds. ICE needs queries namely Q2, Q6, and Q14 to the workload patterns. 41 seconds and access 2340 Kbytes. We suppose, the DBMS with The query structure, syntax, and execution time are not sufficient minimal I/O cost performs best. Our assumption is confirmed for to estimate the query behavior on different storage architectures. query Q14. Oracle accesses 7020.894 Kbytes and computes the We introduced an approach based on database operations that pro- query in 22.55 seconds whereas ICE computes it in 3 seconds and vides analyses to find long running operations (bottlenecks). More- reads 6240 Kbytes. Nevertheless, we cannot prove our assumption over, we want to figure out reasons for the behavior, thus we have for query Q6. Oracle (3118 Kbytes) accesses less data than ICE to use additional metrics. We select the I/O cost to compare the (5980) Kbytes but ICE (2 seconds) computes this query ten times DBMSs and summarize the optimizer output in Table 3. Following faster than Oracle (22.64 seconds). Hence, we cannot figure out a our previous name convention, we define the query IDs according definite correlation for our sample workload. to their TPC-H query number, i.e., we map the queries with the We state that only I/O cost is not sufficient to estimate the be- IDs 2, 6, and 14. The operations are identified by their query havior of database operations. However, I/O cost is one important plan number (IDs in Table 3), thus the root operation of TPC-H metric to describe performance behavior on different storage archi- query Q2 has the ID 2.0 in Figure 2. All values in Table 3 are tectures because one of the crucial achievements of column stores is given in Kbytes. The given values are input costs of each opera- the reduction of data size (i.e., I/O cost) by aggressive compression. tion except the table access costs because no information on input The I/O cost also gives an insight into necessary main memory for costs to table access operations are available. Note, the granular- database operations or if operations have to access the secondary ity of Oracle’s costs measurements is on the byte level whereas the memory. Hence, we can estimate that database operations are com- 3 We also wanted to evaluate our approach with the DBMSs solu- pletely computed in main memory or data have to be reread/read tions from Vertica and Sybase because both DBMSs use cost-based stepwise4 . optimizer and we would be able to receive more expressive results. We requested the permission to use the systems for our evaluation 4 We remind of the performance gap (circa 105 ) between main but until now the decision is pending. memory and HDDs. 82 Oracle ICE Operation Q2 (8.14sec) Q6 (22.64sec) Q14 (22.55sec) Q2 (41sec) Q6 (2sec) Q14 (3sec) Data Access ID7:0.8;ID12:0.029;ID13:11.2; ID2:3118 ID3:1620.894; ID4:65;ID5:65;ID6:845;ID7:65ID8:260; ID2:5980 ID3:5980; ID15:0.104;ID16:1440 ID4:5400 ID10:65;ID11:65;ID12:65;ID13:845 ID4:260 Non-vector based join ID6:202.760;ID8:1440;ID9:88.016; ID2:7020.894 ID3:1300;ID9:1040 ID2:6240 ID10:17;ID11:11.229 Sort ID3:33.18;ID5:45.346 ID2:65 Count ID1:31.284 ID1:65 Sum ID1:3118 ID1:3610.173 ID1:5980 ID1:65 Projection ID0:19.800 ID0:0.020 ID0:0.049 ID0:65 ID0:65 ID0:65 Table 3: Accessed Kbytes by query operations of TPC-H query Q2, Q6, and Q14. Workload Aggregation & Join Tuple Operation Grouping Count Non-vector based Projection ICE Oracle ICE Oracle Filtering ICE Oracle Min / Max Vector based ID KBytes ID Kbytes ID KBytes ID KBytes (Having, Selection) ID Kbytes ID KBytes 2.1 65 2.1 31.284 2.3 1300 2.6 202.760 2.0 65 2.0 19.800 2.9 1040 2.8 1440.000 6.0 65 6.0 0.020 14.2 6240 2.9 88.016 Tupel Reconstruction / Data Access 14.0 65 14.0 0.049 Sum 2.10 17.000 ICE Oracle ICE Oracle 2.11 11.229 ID KBytes ID KBytes ID KBytes ID KBytes Cube 14.2 7020.894 2.4 65 2.7 0.800 Sort / Order 6.1 5980 6.1 3118.000 2.5 65 2.12 0.029 ICE Oracle 14.1 65 14.1 3610.173 2.6 845 2.13 11.200 ID Cost ID Cost 2.7 65 2.15 0.104 2.2 65 2.3 33.180 2.8 260 2.16 1440.000 2.5 45.346 St. Dev. Avg 2.10 65 6.2 3118.000 2.11 65 14.3 1620.894 2.12 65 14.4 5400.000 Group by Median 2.13 845 6.2 5980 14.3 5980 14.4 260 Figure 2: Workload graph with mapped I/O costs of TPC-H query Q2, Q6, and Q14. 5. RELATED WORK these approaches operate on single systems instead of comparing Several column stores have been proposed [1, 14, 30] for OLAP two or more systems. In contrast to the mentioned approaches, our applications. But all systems are pure column stores and do not approach do not consider tune configurations, indexes, etc. support any row store functionality. Thus, a storage architecture Another approach for OLAP applications is Ingres/Vectorwise decision between row and column store is necessary. Abadi et which applies the Vectorwise (formerly MonetDB/X100) architec- al. [2] compare row and column store with respect to performance ture into the Ingres product family [12]. In cooperation with Vec- on the star schema benchmark. They simulate column store archi- torwise, Ingres developes a new storage manager ColumnBM for tecture by indexing every single column or vertical partitioning of the new Ingres/Vectorwise. However, the integration of the new the schema. They show that using column store architecture in a architecture into the existing environment remains unclear [12]. row store is possible but the performance is poor. In this paper, we do not compare end to end performance of DBMSs or architectures. 6. CONCLUSION We support sound and comparable analyses based on database op- erations across different DBMSs with our approach. We do not In recent years, column stores have shown good results for DWH discuss approaches like DSM [8], hybrid NSM/DSM schemes [9], applications and often outperformed established row stores. How- or PAX [3] because the differences to state-of-the-art column stores ever, new requirements arise in the DWH domain that cannot be have been already discussed, e.g., Harizopoulus et al. [11]. satisfied only by column stores. The new requirements demand There are systems available which attempt to fill the gap between also for row store functionality, e.g., real-time DWHs need suffi- a column and a row store. C-Store [1] uses two different storage cient update processing. Thereby, the complexity of design pro- areas to overcome the update problems of column stores. A related cess increases because we have to choose the optimal architecture approach brings together a column store approach and the typical for given applications. We showed with an experiment that work- row store domain of OLTP data [24]. However, we do not develop load analyses based on query structure and syntax are not sufficient hybrid solutions that attempt to fill this gap for now. to select the optimal storage architecture. Consequently, we sug- There exist a number of design advisors which are related to our gested a new approach based on database operations. We intro- work, e.g., IBM DB2 Configuration Advisor [13]. The IBM Con- duced workload patterns which contain all workload information figuration Advisor recommends pre-configurations for databases. beyond the architectures, e.g., statistics and operation cost. We Zilio et al. [28, 29] introduce an approach that gathers statistics like also presented a workload decomposition approach based on exist- our approach directly from DBMSs. The statistics are used to ad- ing database functionality that maps operations of a given workload vise index and materialized view configurations. Similarly, Chaud- to our workload patterns. We illustrated the methodology of our de- huri et al. [5, 6] present two approaches which illustrate the whole composition approach using an example workload. Subsequently, tuning process using constraints such as space threshold. However, we state that a separate decomposition algorithm for column stores is not needed. We stated that our presented approach is transparent 83 to any workload and any storage architecture based on the rela- Database: Compressing years of performance expertise into tional data model. In the evaluation, we proved the usability of seconds of execution. In BTW ’03, pages 620–629, 2003. our approach. Additionally, we demonstrate the comparability of [14] T. Legler, W. Lehner, and A. Ross. Data mining with the different systems using different architectures even if the systems SAP NetWeaver BI Accelerator. In VLDB ’06, pages provide different information with respect to their query execution. 1059–1068. VLDB Endowment, 2006. The decision process can be periodically repeated, thus the storage [15] A. Lübcke. Cost-effective usage of bitmap-indexes in architecture selection is not static. Moreover, our approach can be DS-Systems. In 20th Workshop "Grundlagen von used for optimizer (decisions) in hybrid relational DBMS that has Datenbanken", pages 96–100. School of Information to select the storage method for parts of data. Technology, International University in Germany, 2008. In future work, we will investigate two strategies to implement [16] A. Lübcke. Challenges in workload analyses for column and our workload patterns in a prototype. First, we utilize a new DBS row stores. In 22nd Workshop "Grundlagen von to export periodically statistics and operation costs which we map Datenbanken", volume 581. CEUR-WS.org, 2010. to our workload patterns. This way, we will not affect performance [17] A. Lübcke, I. Geist, and R. Bubke. Dynamic construction of analyzed systems by prediction computation. Second, we adapt and administration of the workload graph for materialized existing approaches [5, 17] to automatically gather statistics, e.g., views selection. Int. Journal of Information Studies, mapping statistics and workload patterns directly into a graph struc- 1(3):172–181, 2009. ture (query graph model). Additionally, aggregated or estimated [18] A. Lübcke, V. Köppen, and G. Saake. A decision model to values from other sources can be stored. We will perform detailed select the optimal storage architecture for relational studies on OLAP, OTLP, and mixed workloads to gather expressive databases. RCIS, France, MAY 2011. IEEE. to appear. values for predictions. [19] Oracle Corp. Oracle Database Concepts 11g Release (11.2). 14 Memory Architecture (Part Number E10713-05), March 7. REFERENCES 2010. [1] D. J. Abadi. Query execution in column-oriented database [20] Oracle Corp. Oracle Performance Tuning Guide 11g Release systems. PhD thesis, Cambridge, MA, USA, 2008. Adviser: (11.2). 12 Using EXPLAIN PLAN (Part Number Madden, Samuel. E10821-05), March 2010. [2] D. J. Abadi, S. R. Madden, and N. Hachem. Column-stores [21] Oracle Corp. Oracle Performance Tuning Guide 11g Release vs. row-stores: How different are they really? In SIGMOD (11.2). 11 The Query Optimizer (Part Number E10821-05), ’08, pages 967–980, New York, NY, USA, 2008. ACM. March 2010. [3] A. Ailamaki, D. J. DeWitt, M. D. Hill, and M. Skounakis. [22] H. Plattner. A common database approach for OLTP and Weaving relations for cache performance. In VLDB ’01, OLAP using an in-memory column database. In SIGMOD pages 169–180, San Francisco, CA, USA, 2001. Morgan ’09, pages 1–2, New York, NY, USA, 2009. ACM. Kaufmann Publishers Inc. [23] R. J. Santos and J. Bernardino. Real-time data warehouse [4] M. M. Astrahan, M. W. Blasgen, D. D. Chamberlin, K. P. loading methodology. In IDEAS ’08, pages 49–58, New Eswaran, J. Gray, P. P. Griffiths, W. F. K. III, R. A. Lorie, York, NY, USA, 2008. ACM. P. R. McJones, J. W. Mehl, G. R. Putzolu, I. L. Traiger, B. W. [24] J. Schaffner, A. Bog, J. Krüger, and A. Zeier. A hybrid Wade, and V. Watson. System R: Relational approach to row-column OLTP database architecture for operational database management. ACM TODS, 1(2):97–137, 1976. reporting. In BIRTE ’08, 2008. [5] N. Bruno and S. Chaudhuri. To tune or not to tune? A [25] M. Stonebraker, D. J. Abadi, A. Batkin, X. Chen, lightweight physical design alerter. In VLDB ’06, pages M. Cherniack, M. Ferreira, E. Lau, A. Lin, S. Madden, E. J. 499–510. VLDB Endowment, 2006. O’Neil, P. E. O’Neil, A. Rasin, N. Tran, and S. B. Zdonik. [6] N. Bruno and S. Chaudhuri. An online approach to physical C-Store: A column-oriented DBMS. In VLDB ’05, pages design tuning. In ICDE ’07, pages 826–835, 2007. 553–564. VLDB Endowment, 2005. [7] S. Chaudhuri and V. Narasayya. Autoadmin “what-if” index [26] A. A. Vaisman, A. O. Mendelzon, W. Ruaro, and S. G. analysis utility. In SIGMOD ’98, pages 367–378, New York, Cymerman. Supporting dimension updates in an OLAP NY, USA, 1998. ACM. server. Information Systems, 29(2):165–185, 2004. [8] G. P. Copeland and S. N. Khoshafian. A decomposition [27] Y. Zhu, L. An, and S. Liu. Data updating and query in storage model. In SIGMOD ’85, pages 268–279, New York, real-time data warehouse system. In CSSE ’08, pages NY, USA, 1985. ACM. 1295–1297, Washington, DC, USA, 2008. IEEE Computer [9] D. W. Cornell and P. S. Yu. An effective approach to vertical Society. partitioning for physical design of relational databases. IEEE [28] D. C. Zilio, J. Rao, S. Lightstone, G. M. Lohman, A. J. Trans. Softw. Eng., 16(2):248–258, 1990. Storm, C. Garcia-Arellano, and S. Fadden. DB2 Design [10] S. J. Finkelstein, M. Schkolnick, and P. Tiberio. Physical Advisor: Integrated automatic physical database design. In database design for relational databases. ACM TODS, VLDB ’04, pages 1087–1097. VLDB Endowment, 2004. 13(1):91–128, 1988. [29] D. C. Zilio, C. Zuzarte, S. Lightstone, W. Ma, G. M. [11] S. Harizopoulos, V. Liang, D. J. Abadi, and S. Madden. Lohman, R. Cochrane, H. Pirahesh, L. S. Colby, J. Gryz, Performance tradeoffs in read-optimized databases. In VLDB E. Alton, D. Liang, and G. Valentin. Recommending ’06, pages 487–498. VLDB Endowment, 2006. materialized views and indexes with IBM DB2 Design [12] Ingres/Vectorwise. Ingres/VectorWise sneak preview on the Advisor. In ICAC ’04, pages 180–188, 2004. Intel Xeon processor 5500 series-based platform. White [30] M. Zukowski, P. A. Boncz, N. Nes, and S. Heman. Paper, September 2009. MonetDB/X100 - a DBMS in the CPU cache. IEEE Data [13] E. Kwan, S. Lightstone, K. B. Schiefer, A. J. Storm, and Eng. Bulletin, 28(2):17–22, June 2005. L. Wu. Automatic database configuration for DB2 Universal 84