Within the DWH domain, a new architecture known as column store is emerging especially to improve the performance of data analyses/aggregations. Column stores o er good results in performance benchmarks like TPC-H. In recent years, row stores dominate the data warehousing domain. We consider application elds for both architectures. We want to gure out and examine di erent application elds for row and column stores. To encourage our assumption, we perform a case study based on the TPC-H benchmark. To the best of our knowledge, there is no advisor for selection of storage architecture for a given application. We present an idea to overcome the workload analysis problems across di erent architectures in the DWH domain.
Database management systems (DBMSs) are pervasive in
current applications. With database tuning, practitioners
aim at optimal performance especially for large-scale
systems like a data warehouse (DWH). The administration
and optimization of DBMSs is costly. In recent years,
roworiented DBMSs (row stores) dominate the DWH domain
but a new approach known as column-oriented DBMSs
(column stores) arises [
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. To copy otherwise, to republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee.
GvD Workshop’10, 25.-28.05.2010, Bad Helmstedt, Germany
Copyright 2010 ACM Copyright is held by the author/owner(s). ...$10.00.
tions [
In this paper, we analyze the di erences between column and row stores to show the increased challenge of system design within the DWH domain. We illustrate new challenges for workload analyses and introduce a solution. Afterwards, our study illustrates that there are application elds for both architectures within the DWH domain. 2.
This section gives an overview to the di erences in storage method and functionality between column and row stores. Due to the di erences, we show the increased complexity of system design and the di culty of performance estimation across row and column stores.
First, the storage architecture di ers in the type of
partitioning a (relational) table. Row stores horizontally
partition a table, i.e., all attribute values of one tuple are
sequentially stored. In contrast, column stores sequentially
store the values of an attribute (column). Hence, column
stores have to reconstruct the tuples during the query
execution if more than one column is a ected. In the DWH
domain, we mostly process aggregations over one column.
Column stores reduce the overhead for aggregations, e.g.,
I/O costs, because the a ected column is directly accessed.
However, the vertical partitioning in column stores also
implies worse performance on update processing. That means,
caused by the column-oriented architecture, the tuples have
to be reconstructed and partitioned subsequently during
update processing. In contrast, row stores perform better on
tuples (operations) and updates that are also important
operations within workloads even in the DWH domain. Several
approaches [
Second, column and row stores also di er in their
functionality. Row stores utilize indexes and materialized views
to improve the performance. Therefore, DBMS vendors and
researchers develop a number of self-tuning techniques [
In conclusion, we state that the complexity of database
design (and also tuning) has been increased by the appearance
of column stores within the DWH domain. In recent years,
we estimate the performance of row stores for a given
workload and tune these systems concerning the given workload.
We are able to compare several systems (row stores) because
their core functionality only di ers very slightly. Today, we
have to estimate the performance of DBMSs across two
architectures, i.e., we have to choose the most suitable
architecture for a given workload. One can argue that column
stores are more suitable for DWH applications in general
because column stores perform better on essential operations
for the DWH domain, e.g., aggregations. We argue there are
application elds for column and row stores because none of
the two architectures is suitable for every workload.
Current approaches [
New applications demand for performant aggregations in column stores as well as performant tuple operations in row stores or at least row store functionality. Hence, we need new approaches for workload analyses because we have to compare di erent DBMSs (row vs. column store) that di er signi cantly in their storage, functionality, and query processing techniques.
In recent years, we compare di erent tuning and optimization techniques of DBMSs to gure out the most suitable DBMSs for a given workload. Row stores have only minor di erences in functionality and they often only di er in their implementation. Therefore, workload analyses based on entire queries are suitable because the queries are processed in the same way and the comparison of query execution times is su ciently.
To compare (systems with) di erent architectures, we have to estimate the performance of a given workload for both architectures. Due to the di erent performance of both architectures on certain operations, it seems to be obvious to analyze the operations itself. Consequently, current workload analysis approaches and estimation tools have to be adapted because these approaches process on entire queries and their structure and do not o er the opportunity to analyze operations of a query. We argue that this step is necessary because certain operations, e.g., tuple reconstructions in column stores, are not appraisable from a given query and its structure. Furthermore, a certain operation can directly in uence the overall performance of a query, e.g., tuple operations. Due to heuristics, column stores will be chosen for workloads that contain an amount of aggregations. This assumption is often applicable but we cannot generalize it. If the workload contains a huge number of tuple operations besides the amount of aggregations then column stores perform poorly because they have reconstruct a lot of tuples. Tuple reconstructions are not necessary for row stores, thus we have to analyze the operations of a query to estimate the overall performance of a query correctly. With help of workload analyses based on operations, we can obtain weighted comparable estimations according to di erent operations even if certain operations only exist for one of the both architectures, e.g., tuple reconstruction for column stores. 4.
In this section, we present our case study according to the TPC-H benchmark on a column store and a row store. We describe our study environment and introduce our assumptions for this study. Afterwards, we discuss the results of the benchmark runs. 4.1
Our test environment is an Ubuntu 9.10 64bit system running on Samsung X65 with a 2.2GHz dual core processor, 2GB RAM, and 2GB swap partition. We decide to use Infobright ICE2 3.2.2 and MySQL3 5.1.37 for our study.
Thereby, ICE represents the column stores and MySQL represents the row stores. Our decision is based on two main reasons. First, both DBMSs are freely available, and second both DBMSs are relatively similar. Both systems use the common MySQL kernel/management services except that they utilize di erent storage architectures. Of course, Infobright adds functionality to the underlying MySQL, e.g., another storage manager, but there are no other DBMSs that utilize di erent storage architectures and are as similar as these two DBMSs. To the best of our knowledge, there are no DBMSs more suitable to compare impacts on column and row store even if ICE is already a DBMS for DWH applications and MySQL is implemented for transactional processing (OLTP). We adjust both DBMSs con gurations to the MySQL standard con guration to guarantee the comparability of the results. For both systems, no additional indexes or views are created except indexes and views that are caused by the DDL (primary key) or by the workload itself.
To exclude impacts from a poor chosen workload, we use the standardized TPC-H (2.8.0) benchmark with scale factor 1 (1GB). This benchmark is representative for the DWH 2http://www.infobright.org 3http://www.mysql.org domain. The data (1GB) does not t completely into RAM for MySQL standard con guration, e.g., 16MB key bu er size is much less than 1GB. We run two series of tests with the TPC-H benchmark to show that there are still application elds for row stores in the DWH domain, thus column stores do not outperform row stores at each query.
We run one series of tests with the standard TPC-H benchmark to obtain reference values. The second series of tests run with an adjusted TPC-H benchmark. We adjusted the TPC-H benchmark in the following way. We want to show that a storage architecture decision can be easily shifted by changing workloads. We modify the TPC-H benchmark, i.e., we change the number of returned attributes for each query. Hence, each query returns the result without projection (in the SELECT-statement). Listing 1 shows an exemplary adjusted TPC-H query. We have to add a GROUP BY-statements to the queries Q6, Q14, Q17, and Q19 to create valid SQL statements because more than one attribute has to be processed now. We decide to group all four queries by the same attribute (l shipdate), i.e., each query is extended by GROUP BY l shipdate. We also apply these changes to the series of tests with standard TPC-H benchmark to guarantee the comparability.
Finally, we state that we exclude three queries from our test series. First, Q13 is not executable on MySQL-syntax. Second, we remove Q18 from our test series because MySQL is not able to nish the query. We abort the execution after over 21 hours. In contrast, the execution time on ICE is only 8 seconds. Third, Q21 has an extreme execution time of 6 hours on ICE that indicates optimizer problems for this query. MySQL executes Q21 in only 2 minutes and 48 seconds.
Our results of the two test series are shown in Table 1 and will be discussed in the following section. 4.2
Our study shows di erent impacts on the query execution time of the queries. We determine three di erent impacts within our study.
First, we cannot gure out an impact of our adjustments on the query execution of MySQL. There are only some queries that show a negligible impact, e.g., the query execution times of Q2 and Q5 are only increased by 3 seconds with respect to the overall query execution time above 1 minute. We expect this behavior because row stores do not have drawbacks while processing entire tuples. We assume, the nal projection within a query plan has no impact on query execution time for row stores because unnecessary attribute will only be cropped from the result sets. We assume, this fact does not refer to projections on intermediate result sets during the optimization process.
Second, ICE shows an obvious impact on several queries, e.g., Q3 and Q19. The increased costs for these queries show the in uence of the size of processed tuples to the performance of column stores. We assume, these costs are caused by the necessary tuple reconstruction during the query execution. Hence, analyses on larger tuples within result sets can corrupt the performance of column stores. We state that large tuple sizes within a result set cannot disregarded for storage architecture decision, especially not for analyzing and reporting tools that process on huge data sets.
Third, some queries do not show an impact according to our adjustments of the TPC-H benchmark e.g., Q7 or Q15. Query # TPC-H Q1 TPC-H Q2 TPC-H Q3 TPC-H Q4 TPC-H Q5 TPC-H Q6 TPC-H Q7 TPC-H Q8 TPC-H Q9 TPC-H Q10 TPC-H Q11 TPC-H Q12 TPC-H Q14 TPC-H Q15 TPC-H Q16 TPC-H Q17 TPC-H Q19 TPC-H Q20 TPC-H Q22 We assume that the costs for tuple reconstructions for these queries do not have a major share of total costs. The result sets and the interim results are comparatively small. Hence, there are other operations within these queries that cause the major part of the costs. Consequently, we cannot gure out general decision rules. We have to analyze the in uence of single operations to the total costs of a query.
Queries Q4, Q17, Q19, and Q22 have to be separately considered. The long query execution times for ICE indicate the same issue with respect to the query structure. We assume that the ICE optimizer or the ICE query processor has an issue while processing nested queries. However, our results also show that there is only a negligible impact for both systems by our adjustments for these queries.
Our assumption is con rmed that column stores cannot outperform row stores at each query, i.e., there are application elds for row and column stores in the DWH domain. 5.
Several open- and closed-source column stores have been
released [
Regarding the solutions for architectural problems, there
are several approaches [
We need to analyze all workloads to gure out the applications elds for row and column stores. Therefore, we can utilize, adapt and extend existing approaches such as Turby
ll [
Listing 1: Adjusted TPC-H Query Q16 sider analyses on changing workload. We could adopt these approaches to develop an alerter in architectural manner to advice the redesign of a system.
The current research re ects new approaches to solve the
update problems of OLAP applications, e.g., dimension
updates [
We illustrated the major di erences in storage form and functionality between column and row stores. Therefrom, we showed the di culty to compare the performances of the two architectures or systems with di erent architectures. Consequently, we discussed the increased complexity of the physical design process for DWH applications, e.g., distinction between two architectures. We mentioned several approaches that show the necessity of column stores as well as row stores or at least row store functionality within the DWH domain. Afterwards, we analyzed the impact of certain operations on both architectures and gured out that current workload analysis approaches are not su cient. The current analysis approaches cannot analyze workloads for both architectures su ciently because we proposed that workload analysis should analyze workloads based on operations instead of entire queries. We argued that this approach can satisfy the necessity to evaluate performance of systems across di erent architectures.
Our study with ICE and MySQL shows that column stores cannot outperform row stores for every workload even ICE is implemented for DWH applications and vice versa MySQL is not. Hence, we con rm our assumption that there are application elds for column and row stores within the DWH domain. Thus, a framework for the selection of optimal architecture is necessary. Our study shows the necessity to adopt current approaches for more signi cant performance estimations. Finally, we discuss the impact of certain operations on the overall performance of a query.