an introduction to system sizing for data warehousing workloads

7/31/2019 An Introduction to System Sizing for Data Warehousing Workloads

1/38

Redbooks Paper

Copyright IBM Corp. 2004. All rights reserved. ibm.com/redbooks 1

An Introduction to System Sizing for

Data Warehousing Workloads

Sizing the hardware for a data warehouse

Todays successful enterprises have a significant interest in BusinessIntelligence (BI) and data warehousing (DW) because they rely on these tools togain a better understanding of their business and to establish a competitiveposition within their marketplaces. When reviewing analyst reports and marketresearch papers related to data warehousing and Business Intelligenceapplications, we can easily see the following common theme: Data volumes are

growing at unprecedented rates.

To accommodate huge volumes of data, we must learn to build large datawarehouses that can function successfully and provide an ever-increasing returnon investment. The need to effectively process large volumes of data requiresabilities beyond just storing the data in a warehouse.

This paper introduces the reader to the various aspects of sizing a system fordata warehousing. To bring awareness to the critical issues that are frequentlyignored in sizing projects, a sizing example is provided that estimates thehardware requirements of a recently published data warehousing benchmark.

Tony Petrossian

Ann Matzou

Kwai Wong
http://www.redbooks.ibm.com/http://www.redbooks.ibm.com/http://www.redbooks.ibm.com/http://www.redbooks.ibm.com/


2/38

2 An Introduction to System Sizing for Data Warehousing Workloads

The common misconception about sizing

One of the key elements contributing to the success of a data warehouse projectis the hardware that stores, processes, and facilitates the movement of the data.Obviously, a large warehouse requires a large storage capacity, but the

challenges of building a successful data warehouse are not limited to amassing ahuge storage complex.

Unfortunately, many system sizing exercises put too much emphasis on thecapacity and function of the storage without considering the overall IOsubsystem and the balance of system resources needed to make efficient use ofthe storage investment. The ability to attach a large storage complex to a systemdoes not suggest that the system is appropriately equipped to process the largevolumes of data within a reasonable window of time.

Understanding the sizing problems for a data warehouse

Sizing a system for a new data warehouse project without any experimental datacan be a daunting task. Unlike more traditional OLTP workloads, only a smallportion of common performance information can be used to size different datawarehouse systems and applications.

The majority of OLTP workloads tend to have well understood units of work per

transaction. As a result, resource requirements can be scaled using transactionrates and number of users. In contrast, a unit of work in a data warehouseapplication is variable and mostly unrelated to the data size. This variabilitymakes it difficult to compare resource utilizations of different DW applications forestimating system requirements.

Many existing DW installations have created a science of capacity planning andmeasuring the resource util ization of their workloads. Unfortunately, most of thisinformation is unavailable or inapplicable to a new installation. The ad hoc nature

of a DW workload makes it difficult to compare different systems.

Estimating CPU requirements for data processing in a data warehouse is acomplex task. CPU requirements to process 100 MB of data can vary dependingon the complexity of the queries. In order to build a knowledge base from whichto estimate the processing requirements for a specific warehouse workload, beprepared to experiment, use benchmarks, seek expert opinions, and even guess.

Understanding sizing problems can help to build flexible configurations for future

refinements.


3/38

An Introduction to System Sizing for Data Warehousing Workloads 3

Accuracy goal in sizing estimates

An overly complex sizing methodology that requires massive amounts ofestimated input will most likely produce a false sense of accuracy withoutnecessarily producing a better system sizing estimate.

The goal should be to produce a sizing estimate for a flexible systemconfiguration with room for minor adjustments in the future. The system shouldhave a good balance of resources that scale proportionally.

It is important to remember that the outcome of any sizing methodology is anestimate, and although the accuracy can be improved, it will never reach onehundred percent. It is critical to recognize the point of diminishing returns whengoing through a sizing process. Hopefully, there exists a point between knowing

thesize of the data and understanding the resourcerequirements of all possiblequeries in order to achieve a reasonable size estimate.

Each sizing effort should include an accuracy goal and a margin of error basedon the level of existing knowledge of the application. Like any other businessdecision, this task requires risk calculation and contingency planning.

An alternative to a sizing estimate is to run the custom benchmark using specificapplication and data. When feasible, these efforts are usually very expensive. In

most cases, an application is built after the hardware infrastructure is installed,so benchmarking the application before buying hardware is not possible.

Optimally balanced system

Regardless of the methodology used to establish sizing estimates for datawarehouse workloads, the outcome should always be a system with balancedresources that can be used efficiently. A well balanced configuration should becapable of maximizing one or more of the most expensive system resources at

any time. Quite often, poorly configured systems leave expensive processingpower idle due to an inadequate I/O subsystem.

Data warehousing workloads present additional challenges that are not seen intraditional OLTP systems. The volume of data moved between storage and CPUfor any given OLTP transaction is very small. The aggregate data movement fora large OLTP system is minuscule when compared with data warehousingsystems.

The balance between system CPU power, storage capacity, and the I/Osubsystem is critical when building data warehousing systems. The I/Osubsystem connects the storage to the CPU and accommodates the movementof data between the two components.


4/38


Figure 1 Data movement in systems

Due to the high volume of data moving through data warehousing systems,special consideration should be given to the I/O subsystem for this type ofworkload.

Overview of the sizing process

To effectively size a system for optimal performance, architecting a solutionrequires the following steps:

1. Understand system performance capabilities and limits.2. Understand the workload and resource usage characteristics.3. Establish business requirements and system expectations.4. Size the system for optimal use of its resources.

With some analytical work a reasonable configuration that meets the businessrequirements can be estimated. As the quality of data increases, sizing estimatesbecome more accurate.

Sizing methodologyThis section introduces the sizing methodology using a sample sizing effort. Theworkload used is a benchmark from the Transaction Processing PerformanceCouncil (TPC) for data warehousing. The TPC Benchmark H (TPC-H) is a wellrecognized data warehousing benchmark and its detailed description is publiclyavailable. TPC-H Benchmark overview on page 30contains more informationabout the TPC and the TPC-H benchmark.

The following diagram illustrates the methodology used.

SystemStorage

IO

Subsystem

CPU

&Memory

IO BUS

Storage Interconnect

NetworksNetwork Interconnect

SystemStorage

IO

Subsystem

CPU

&Memory

IO BUS

Storage Interconnect

NetworksNetwork Interconnect


5/38


Figure 2 System sizing methodology

This example characterizes the behavior of a data warehouse workload and setsspecific performance goals for achieving the business objective. Performancedata sheets on the IBM Eserver pSeriesTM 655 (p655) and IBMTotalStorageTM FAStT900 Storage Server were used to establish a systemsizing estimate to meet the goals.

Although not part of this example, the data was used to run and publish a TPC-Hbenchmark that validated our work. More details on this benchmark result canbe found on the TPC Web site:

http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/1

The following sections describe the steps required to collect data and size thesystem.

Choosing a system

Selecting a vendor, a product line, and a system for a new project is a

complicated process beyond the scope of this paper. It should be noted that

1 TPC Transaction Processing Performance Council:http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/

Sizing Process

Assumptions

Facts

Requirements

Workload

Characteristics

Business

Requirements

Product

Data

Sizing

Estimate

Data

Collection

Sizing

Knowledge

Base

Sizing Process

Assumptions

Facts

Requirements

Workload

Characteristics

Business

Requirements

Product

Data

Sizing

Estimate

Data

Collection

Sizing

Knowledge

Base
http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=103120801/


6/38


most selection processes are influenced by organizational preferences, historicalpurchasing patterns, and other non-technical issues.

Regardless of the reasoning, the selection team is responsible for ensuring that aselected system is capable of meeting the technical requirements of the

workload and providing the return on investment sought by the business.

For this project the following products were selected:

Clustered configuration of IBM eServer pSeries 655 systems IBM FAStT900 Storage Server IBM DB2 UDB

The choice of products was influenced by the project requirements, as well asthe desire to highlight these products.

Understanding system capabilities

In this section we discuss system capabilities.

The eServer pSeries 655 System

The pSeries 655 Central Electronics Complex (CEC) (7039-651) is a 4U tall,24-inch half drawer, rack-mounted device. It houses the system processors,memory, system support processor, I/O drawer connection capability, andassociated components.

The p655 server includes the latest IBM POWER4+ chip technology in abuilding-block approach to the requirements of high-performance, clusteredtechnical and commercial computing. With the speed advantages provided bythe powerful 1.7GHz POWER4+ processor and its associated system

architecture, a fast system bus, extremely high memory bandwidth, and robustinput/output (I/O) subsystems, the pSeries 655 (p655) provides a versatilesolution to the most demanding client requirements.

The following diagram shows the p655 system configuration.


7/38


Figure 3 p655 CEC

The following sections describe the major components of the p655. For generaldescription and configuration information about the p655, refer to the followingIBM Web site:

http://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.html 2

Processing powerThe p655 system is powered by a single multi-chip processor module. AMulti-Chip Module (MCM) has either four or eight 1.7 GHz, POWER4+ processorcores. Each processor core contains 32 KB of data cache and 64 KB ofinstruction cache. Each processor chip has a 1.5 MB L2 cache on board thatoperates at chip frequency. On the 8-way MCM, the two cores on eachprocessor chip share that chips L2 cache, while on the 4-way MCM each core

has a dedicated L2 cache. A 32 MB L3 cache is located between each processorchip and main memory and operates at one-third of the chip frequency. For moredetailed information on the p655 configuration, refer to the following white paper:

http://www-1.ibm.com/servers/eserver/pseries/hardware/whitepapers/p655_hpc.pdf 3

2 pSeries 655 description:

http://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.html3 IBM eServer pSeries 655Ultra-dense Cluser Server for High Performance Computing, Business

Intelligence and Data Warehousing Applications by Harry M. Mathis, John D. McCalpin, JacobThomas

http://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.html

MCM

MemorySlot

MemorySlot

MemorySlot

MemorySlot

GX to

RIO-2

Bridge

RIO-2

RIO-2 to

PCI-X

Bridge

RIO-2

GX

Internal PCI Devicesand PCI Slots

PCI Buses

External I/ODrawer

MCM

MemorySlot

MemorySlot

MemorySlot

MemorySlot

GX to

RIO-2

Bridge

RIO-2

RIO-2 to

PCI-X

Bridge

RIO-2

GX

Internal PCI Devicesand PCI Slots

PCI Buses

External I/ODrawer
http://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/whitepapers/p655_hpc.pdfhttp://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/midrange/p655_desc.htmlhttp://www-1.ibm.com/servers/eserver/pseries/hardware/whitepapers/p655_hpc.pdf


8/38


Memory configurationThe p655 System has four memory slots that allow from 4 GB to 64 GB ofmemory to be installed. Memory cards are available in 4 GB, 8 GB and 16 GBsizes. The following table shows possible memory configurations.

Table 1 System memory configuration options

I/O subsystemThe p655 has two RIO-2 (Remote I/O) buses. The first RIO-2 bus supports theservice processor, two Ethernet ports, an integrated SCSI adapter, and threehot-plug/blind-swap PCI-X slots on the system board (see Figure 3 on page 7).The second RIO-2 bus can be connected to the 7040-61D I/O drawer foradditional I/O adapter slots and performance. The p655 supports a maximum ofone I/O drawer with two RIO-2 ports. The I/O drawer contains two PCI I/Oplanars. Each planar has three PCI Host Buses (PHBs). The first PHB has four64-bit (133MHz) PCI slots, the second and third ones have three 64-bit(133MHz) PCI slots. Figure 4 on page 9 shows the detailed configuration of theI/O drawer connected to the RIO-2 bus.

Total memory Slot 1 Slot 2 Slot 3 Slot 4

4 GB 4 GB

8 GB 4 GB 4 GB

16 GB 4 GB 4 GB 4 GB 4 GB

16 GB 8 GB 8 GB

32 GB 8 GB 8 GB 8 GB 8 GB

32 GB 16 GB 16 GB

64 GB 16 GB 16 GB 16 GB 16 GB


9/38


Figure 4 I/O drawer configuration

FAStT900 Storage Server

The FAStT900 Storage Server is a member of the IBM FAStT family of diskstorage products. The FAStT900 is an enterprise-class storage server designedto provide performance and flexibility for data-intensive computing environments.

The FAStT900 Storage Server has four host-side FC interfaces that provide anextremely high I/O bandwidth and four drive-side interfaces that accommodate avery large storage capacity. It offers up to 32 TB of Fibre Channel disk capacityusing 18.2, 36.4, 73.4, and 146.8 GB drives with EXP700 disk drive enclosures.Dual controllers with mirrored cache in the FAStT900 provide for the RAID

functions necessary to protect data from disk failures. A FAStT900 can beconnected through SAN switches or attached directly to the host.

The FAStT900 Storage Servers sustain an enormous I/O rate with a mixture ofread and write operations. When performing sequential I/O operations, a

RIO-2 to

PCI-X Bridge

PHB1

PCI-PCI

Bridge

PHB3

PCI-PCI

Bridge

PHB2

PCI-PCI

Bridge

RIO-2 to

PCI-X Bridge

PHB1

PCI-PCI

Bridge

PHB3

PCI-PCI

Bridge

PHB2

PCI-PCI

Bridge

RIO-2 HUB

Passive/Failover

ActiveActive

RIO-2

64 bit PHB600MB/s

Sustained

Sustained

1050MB/s Duplex

Sustained

2100MB/s Duplex

7040-61D IO Drawer

RIO-2 RIO-2

RIO-2 to

PCI-X Bridge

PHB1

PCI-PCI

Bridge

PHB3

PCI-PCI

Bridge

PHB2

PCI-PCI

Bridge

RIO-2 to

PCI-X Bridge

PHB1

PCI-PCI

Bridge

PHB3

PCI-PCI

Bridge

PHB2

PCI-PCI

Bridge

RIO-2 HUB

Passive/Failover

ActiveActive

RIO-2

64 bit PHB600MB/s

Sustained

Sustained

1050MB/s Duplex

Sustained

2100MB/s Duplex

7040-61D IO Drawer

RIO-2 RIO-2


10/38


FAStT900 can saturate the four 2Gb FC host interfaces and deliver more than720 MB per second of I/O to the system.

For more information about the FAStT900 features and performance refer to thefollowing IBM Web site:

http://www.storage.ibm.com/disk/fastt/fast900/index.html4

Understanding the workload

It should be mentioned that the authors have experience with the TPC-Hworkload based on previous projects. To characterize a workload for sizing asystem, it is useful to have experience in data warehousing, with a good

understanding of the specific database products and the targeted businessenvironment.

There are two major workload-related areas of concern in sizing a system fordata warehousing projects:

The storage of the data warehouse The processing of the data

Both storage and processing requirements have an impact on all system

components. Each will be considered separately.

Storage space requirements of the workload

Estimating the disk space to store data is the simpler aspect of system sizing. somany sizing efforts put most of the emphasis on this task. For this example, theassumption was to have 1,000 GB of raw data. Most DW projects can easilycalculate the raw data size based on information provided by the data sources.For example, when the data is extracted from an existing transactional system,

its size is either known or easy to estimate.

The various components requiring storage space are:

Table data storage Index data storage Database log space Temporary space required by the database Staging space required to store raw data

4 FAStT900 Storage ServerScalable, high-performance storage for on demand computingenvironments

http://www.storage.ibm.com/disk/fastt/fast900/index.html
http://www.storage.ibm.com/disk/fastt/fast900/index.htmlhttp://www.storage.ibm.com/disk/fastt/fast900/index.htmlhttp://www.storage.ibm.com/disk/fastt/fast900/index.htmlhttp://www.storage.ibm.com/disk/fastt/fast900/index.html


11/38


Once the raw data size is established, it is necessary to estimate the databasespace requirement for storing the data in tables using the internal format of thedatabase. For this, the schema, page size, and row density per page arerequired. A database administrator and a data architect must be involved in thisprocess. Most database vendors provide accurate estimates once the schema

and data size are known. For this example, the information provided in the DB2Administration Guide: Planning, SC09-4822,5 document was used. This manualhas a specific section that can help estimate table, index, log, and temporaryspace requirements for the schema.

Table data storageFor each table in the database, the following information was used to calculatethe space requirements for the base table that holds the warehouse data:

Raw data size Data row size Number of rows Page size Rows per page

Including page overhead Free slots per page

Free space for future page additions

Considering the above items, the space requirement for storing all the basetables was estimated to be 1,350 GB. Most database vendors provide ampledocumentation to help calculate the database table space requirement for anygiven schema. The database product documentation should be consulted forinformation on estimating table space requirements.

Index data storageFor each index, the following information was used to calculate the spacerequirements for the indices in the schema:

Index size per row of the table Page size Index page overhead Rows per page

Including the page overhead Free slots per page

Free space for future page additions

5 DB2 Administration Guide: Planning, SC09-4822


12/38


Considering the above information, the space requirement for storing all theindices was estimated to be 258 GB. Once again, the database productdocumentation should be consulted for information on estimating index spacerequirements.

Database log spaceMost data warehouses have infrequent update activity in comparison with OLTPworkloads. The data warehouse maintenance strategy will generally dictate thedatabase log requirements. Some data warehouses are loaded periodically fromoperational data and are never modified between loads; these configurationshave insignificant logging requirements. Other strategies involve regularmaintenance of data that requires inserts and deletes from the database. In thisconfiguration, regular updates to the data had to be accommodated, but the

volume of data being changed was only 0.1 percent of the total warehouse,which adds up to 1 GB (see TPC-H overview on page 30 for details) per updatecycle.

The following was taken into consideration:

Data warehouse update and maintenance strategy Frequency of updates Volume of changing data per cycle Data recovery requirements

Update cycles between log backups.

Transactional characteristics of the workload

The log space requirements were estimated to be insignificant in size relative tothe data size of the warehouse. 36 GB of space was allocated to the databaselogs to satisfy logging needs for at least twenty update cycles between logbackups. Consult DB2 Administration Guide: Planning, SC09-4822,6for moredetails on sizing log requirements.

Temporary database spaceWhen databases are executing queries that process large join, sort, oraggregation operations, they require memory to hold intermediate or partiallyprocessed data. As the database reaches the limits of its allotted memory, it usesdisk space to temporarily store the partially processed data and free up memoryfor further processing. Most data warehouse systems process more data thancan be held in memory and therefore they need temporary database storagespace. For example, a query that attempts to sort 300 GB of data on a system

with 16 GB of memory will require significant temporary storage.

6 DB2 Administration Guide: Planning, SC09-4822


13/38


Estimating the temporary storage space requirements for a DW workload isdifficult because several external factors such as system memory size andconcurrency levels impact the need for space. It usually takes an experienceddata warehousing architect with help from database vendors to estimatetemporary space needs. Underestimating the temporary space requirements of a

workload can prevent the proper execution of large queries or limit concurrencylevels of query execution.

The following information was considered when estimating our temporarystorage needs:

Percentage of data used in large sort and join queries Number of concurrent queries that can be running at any one time

Number of concurrent query segments per query

Previous experience with the workload memory usage Expert guesses and rules of thumb available from product vendors Comparative estimates provided by the database vendor Future growth of data and increase of concurrency levels

Based on the above information, 140 GB of temporary space was estimated forthe worst-case query, and since seven query streams could run concurrently,about 1,000 GB of temporary space was needed.

Staging spaceMost data warehouse projects require some space for staging raw data forloading or maintaining the warehouse. Depending on the operationalprocedures, the space requirement can vary drastically.

The following information was considered when estimating the staging spacerequirement:

Data warehouse loading procedures

Location and storage needs for the raw data Data warehouse maintenance procedures

Location and storage needs for the maintenance data

Future growth of data

Based on the operational needs to store update data and some load data it wasestimated that 1,500 GB of space was sufficient for the project.

Minimum storage space requirementThe storage space estimate is the minimum of space requirements. There areseveral factors that impact the overall storage configuration, for instance:

RAID requirements to protect the data


14/38


Number of disks required to meet the performance requirements Number of disks needed to balance the I/O performance.

For example, suppose that the storage requirement can be satisfied with 11disk drives, but if the system has two disk controllers it might be better to use

six disks per controller to evenly distribute the I/O on both controllers.Adjustments to the number of disks for performance reasons may result inhaving more space than the minimum required, but as always one mustbalance the performance needs and cost based on the project priorities.

The following table shows the overall storage requirements for theconfiguration.

Table 2 Minimum storage space requirements

Data processing characteristics of the workload

The TPC-H workload consists of a number of queries executed on the data.Although an infinite number of queries can be formulated in an ad hocenvironment, most of these queries can be put into a few general categories. Forexample, queries with a significant number of arithmetic operations per row ofdata are CPU bound, while other queries that require simple filtering operations

on large volumes of data become IO bound. It is important to understand theresource needs of the different categories of queries and to size the system toaccommodate as many categories as possible.

The three major system resources that are stressed by a data warehousingworkload are:

CPU resources Memory resources I/O resources

Network Disk

Sequential I/O scans Random I/O scans

1,000 GB TPC-H warehouse space requirement

Data 1,350 GB

Index 258 GB

Database log 36 GB

Database temporary storage 1,000 GB

Staging space 1,500 GB

Total storage space 4,144 GB


15/38


A well balanced data warehouse system maximizes one or more of theresources in the system. Unlike OLTP workloads, a single unit of a DW work(query) can be parallelized to maximize the utilization of the system and returnresults in a minimum amount of time. When additional concurrent queries areadded, the databases start to distribute system resources among the active

queries. The time to complete a query in a DW workload will vary from oneexecution to another depending on the mix of queries running at the same time.

Only an expert in data warehousing workloads, with help from the databasevendor and a data architect, can analyze a schema and anticipate the resourceneeds of potential queries. Without the ability to experiment and run test queries,even experts can have a hard time gauging the CPU needs of a query.

The process of categorizing the various queries starts by careful examination of

the database schema, data characteristics, and queries. The goal is to determinethe following:

1. Estimate the size of the data accessed by each group of queries.2. Categorize the queries based on the dominant resource needs.3. Select some larger queries to represent these categories.4. Use these queries as a guide for system sizing.

Estimate the size of each query

Based on the predicates used in a query and knowledge of the data, theminimum amount of data each query would need to produce the query result canbe anticipated. For example, the following query should access the entireLINEITEM table (see An Introduction to System Sizing for Data WarehousingWorkloads on page 1 for details):

selectsum(l_extendedprice*l_discount) as revenuefromlineitem

For this workload, the various queries were organized in three groups:

Small: Less than 15 percent of data is needed to produce results. Medium: Between 15 and 50 percent of data. Large: More than 50 percent of the data.

In estimating the data set size for queries, it was assumed that the database canbe optimized using indices and other schemes to minimize data access to whatis necessary to calculate and produce the query results. Different databases and

schema definitions may behave differently for the same queries. For example, amissing index may force the database to execute a full table scan and result insignificantly more access to data.


16/38


Since it was intended to use the largest queries for this characterization work,there was less concern about the ability of the database to optimize data access.The following chart shows the approximate minimum data size required tocomplete the six largest queries in the workload.

Figure 5 Query size estimates

The details of the size categorization for all the queries are gathered in Table 3on page 17.

Categorizing queriesTPC-H queries are categorized with respect to the most dominant resource theyuse. To do this, the intensity of the processing being applied to the data being

read for the query is estimated. For example, the following segment of SQL codeshows a query with a significant number of calculations for every row of data tobe processed:

selectl_returnflag,l_linestatus,sum(l_quantity) as sum_qty,sum(l_extendedprice) as sum_base_price,sum(l_extendedprice*(1-l_discount)) as sum_disc_price,

sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,avg(l_quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_discfrom

0

500

1,000

1,500

2,000

2,500

3,000

21 9 17 19 18 1

Query Number

Data

Size(GB)


17/38


lineitemgroup byl_returnflag,l_linestatus

In contrast, the following query has modest processing requirements for eachrow being scanned. This query will run as fast as the data can be read by adatabase and therefore it is I/O bound.

selectsum(l_extendedprice*l_discount) as revenuefromlineitem

A few of the diverse queries were compared and contrasted with each other and

based on the experience of the team with DB2 query processing. They werecategorized in the following table.

Table 3 Query categorization

Query

Data set

Size

Memory

Require-

ment

CPU

Require-

ment

I/O requirement Network

RequirementSequential Random

21 Large High High Low Low Low

9 Large High High Low Medium High

17 Large Low Low High None Low

19 Large Low Low High None Low

1 Large Low Medium Medium None Low

18 Large High High Low Medium Low

7 Medium High High Medium Medium High

5 Medium High High Low None High

13 Medium Medium High Low Low Low

11 Small Medium Low High None Low

6 Small Low Low High None Low

2 Small Low Medium Medium None Low

22 Small Low High Low Low Medium

16 Small High High Low Low Medium

14 Small Low High Low Low High


18/38


Once the characteristics of the various queries are established, it can be seenthat most of the queries have high CPU or sequential IO requirements. To

balance the system, sufficient IO performance is needed to keep CPU resourcesmaximally utilized.

As can be seen in the above table, the top six largest queries can easilyrepresent the entire query set with respect to resource needs. All the majorsystem resource categories can be maximized by one or more of the six largestqueries.

Selecting a representative set of queries

For this characterization work, the assumption was that if the system isconfigured to meet the resource needs of the six largest queries it can alsoprovide for the smaller queries with similar characteristics.

The following chart was built using data from the analysis of the six largestqueries. The chart showed the relationship between CPU and I/O requirementsof each query. Each bubble on the chart represents a query. The size of thebubble is proportional to the volume of data required to obtain the query result.The location of the bubble on the chart shows I/O versus CPU requirements for

the query. When a query is CPU intensive, its I/O requirements are lower than aquery that requires little CPU power to process the same amount of data.

15 Small Medium High Low Low High


19/38


Figure 6 Relative resource requirements

Figure 6 can be used to make system sizing decisions based on the relativeinformation. For example, if a system is configured with just enough I/Obandwidth to maximize the CPU utilization during query 1, then all queries to theright of query 1 will be I/O bound. It can also be concluded that configuring thesystem with more I/O than is necessary for query 17 will not provide any benefit.

Since it is not possible to configure the system optimally for every possible query,this type of categorization can be used to optimize for the largest class of queries

within a given budget.

The reference queriesIn this section we discuss the reference queries.

The I/O sizing query

Based on the information in Estimate the size of each query on page 15 andFigure 5 on page 16, query 17 reads 80 percent of the data to produce a result.Assuming this query is I/O bound, the time to complete this query is equal to thetime it takes to read the data. A system with 1 GB per second of I/O bandwidthwould require 800 seconds to read the 800 GB of data to complete this query.Based on the business requirement, a reasonable response time for this class ofqueries can be set and the I/O system can be configured accordingly.

Relative Resource Needs of Queries

1

21

1719

18

9

0.0

0.2

0.4

0.6

0.8

1.0

0.0 0.2 0.4 0.6 0.8 1.0

Relative IO Throughput Requirements

RelativeC

PUResourceRequirements


20/38


To complete a given query in a fixed period of time, the system needs enoughI/O bandwidth to read the required data in that time and enough CPU power tokeep up with the processing of the data.

The point representing query 17 on the far right edge of the chart in Figure 6 on

page 19 indicates that this query has an insatiable requirement to read datawhen compared to the other queries in the workload.

The CPU sizing query

Based on the assessments in Categorizing queries on page 16 and theinformation provided in Figure 6 on page 19, it was determined that query 18 isthe most CPU intensive query. Using the information in Figure 6 on page 19, ifthe I/O requirements of query 17 can be met, there will be no shortage of I/Obandwidth for query 18. Query 18 will be limited by the CPU during its

executions.

The point representing query 18 on the top left edge of the chart in Figure 6 onpage 19 indicates that this query has massive requirements for data processingpower relative to all other queries in the workload.

Operational characteristics of the workloadThe TPC-H benchmark has two execution models, the Power Test and theThroughput Test. These two modes of operation have very different systemresource requirements, so the needs of the two must be balanced. Thismulti-mode operational requirement is also common in many DW installations,where, depending on time of day or class of users, system requirements can bevery different. When sizing a system all modes of operation must be consideredand prioritized between conflicting requirements.

The Power Test (single-user batch jobs)For this test the goal was to optimize the single-user query processing (Power

Test) because of the benchmark requirements. The decision to optimize forsingle stream query processing makes sense depending on the business needsfor the system. If users are scheduled to have dedicated system time with theexpectation of the fastest possible response time to the queries they submit, thenthe system has to be optimized for single stream processing. On the other hand,if the system is mostly used by multiple users who submit queries and are notsensitive to response time detail, then single stream performance is less critical.

The Throughput Test (multi-user operation)Considering that the number of queries that access large amounts of data areCPU intensive and have low I/O rate requirements (large bubbles near the topleft of the chart in Figure 6 on page 19), it can be assumed that running multiplequeries at the same time would result in a CPU bound system. If a single stream


21/38


of queries takes one CPU hour to complete, then seven streams of similarqueries could potentially take about seven hours of CPU time. This rule of thumbprovides reasonable estimates. However, sometimes simultaneously runningquery streams can benefit from sharing data caches, while at other times thereduced availability of memory results in resource conflicts. To be safe a margin

of error should be anticipated.

If a system is mostly I/O bound during the execution of single stream queries,then more than one stream of queries is required to fully utilize the systemresources. To get the best return on investment, the system should beconfigured to fully utilize CPU resources as much as possible.

Business requirementsObviously, the primary purpose of the data warehouse infrastructure is to solvebusiness problems; so it is critical to collect the business requirements applicableto the sizing effort and translate them to system requirements. For example, abusiness unit may require that a warehouse be reloaded from transactional dataonce per week and the task must be completed in a six-hour window of time.This requirement must be translated to the various read, write, and process ratesfor the data to ensure the system has the appropriate capacity.

The following table shows the list of business requirements and expectationsaddressed in this sizing example. This list is by no means conclusive, but it doescapture the most critical elements for this purpose.

Table 4 Business requirements

Requirement Expectation Comment

Raw data size 1000 GB of rawtext data

The raw data size is only the base number forcalculating storage space requirements.

Annual growth rate of data Less than 2% Normal growth rate must be accommodated withoutmajor architectural changes in the system.

Tolerance for performancedecline due to growth rate

Less than 2% Slower response time for any operation can betolerated if the percentage of degradation is less thanor equal to that of the growth in data.

Service life expectancy 3 years The system is expected to operate for at least threeyears without major changes.


22/38


Raw data load rates 145 MB/Sec 145 MB per second load rate is derived from the needto load the 1000 GB data in less than two hours. At thisrate, a DBA will have enough time to rebuild thewarehouse from scratch and prepare it for queryprocess in less than four hours.

Scan query response timebased on total data

Less than 200seconds

This requirement is critical because a significantnumber of ad hoc queries in the workload performscan and filter operations on all or part of the data. Inaddition, extract operations from the warehouse arebound by the scan rate of the system. The responsetime for several large and small queries with simplecalculation will be impacted by the scan rate. Query 17is our guide for this criterion.

Reporting andcomputational queryresponse time based ontotal data.

Less than 900seconds

Our workload has several queries that frequently run togenerate reports. These queries require severalcomplicated computations and sort operation on all orparts of the data. The intent of this requirement is toensure the worst case report will complete in what isconsidered reasonable time based on the businessrequirement. We also intend to run multiplesimultaneous queries and we like to ensure the workcan be completed in a reasonable time. We will usequery 18 as our guide for sizing the system to meet this

requirement.

Query concurrency rate 7 query streams The workload requires that at least 7 query streamscan operate concurrently at any one time. In addition,query response times must not degrade by more thana factor of 7 to 8, when compared to a single streamexecution.

Performance versus costoptimization priority

Performance In this exercise, reaching the specific performancetargets had the highest priority. Although the overall

hardware cost had a budget ceiling, theprice/performance was secondary to overallperformance. Our goal was to achieve theperformance target with optimal configuration andprice within the budget guidelines.

Data protections RAID level 5 Our workload requires RAID protection to prevent asingle disk failure from disabling the system. RAIDlevel 5 is most appropriate for us because it allowsprotection with a reasonable overhead. A 5+P RAID

level 5 configuration requires 5 disks for data and onedisk for parity; this adds a 17% disk overhead forRAID-5.


23/38


The above table maps the business requirements to a set of checklist items forthe system. When sizing the system, this table should be used as a boundaryguide to ensure the business needs are met.

Sizing the system for effective utilization

In data warehouse workloads, it is difficult to always maximize the utilization ofCPU, I/O, or both resources. In many situations, factors such as limited memoryor network resources can result in idle CPU or I/O subsystems. In addition, alldatabases occasionally have execution paths that fail to maximize systemutilization. The goal is to configure a system that meets the workload

requirements and runs as close to the system utilization limit as possible.Saturating processing power and maximizing the I/O subsystem during most ofthe operational hours of the system will provide the best return on investment.

Sizing the storage

After collecting all the relevant data from workload characterization, businessrequirements, and system data sheets, the information can be compiled into a

single set of guidelines for sizing the storage configuration. RAID level 5 configuration using 5+P settings

145 MB/sec load rate

5,000 MB/sec scan rate based on query 17 I/O profile and 200 second limit(see Table 4 on page 21) on scan query response time.

Approximately 700 MB/sec I/O rate per FAStT900 (see FAStT900 StorageServer on page 9)

4 Fibre Channel interfaces per FAStT900 14 disk capacity per FAStT700 Expansion unit

Disk size of our choice 36 GB

Minimum storage space needs 4,144 GB

Minimum number of disks is 122 (4144 / 34 = 122)

Number of FAStT900 needed to meet IO 5,000 MB/sec I/O rate is 8 (5000 /700 = 8)

Number of FAStT900 needed to meet load rate of 145 MB/sec is 1

Number of EXP700 needed to fully util ize all FAStT900 disk side interfaces is16 (2 per T900)


24/38


Since 122 disks do not evenly distribute amongst 16 EXP700, the number ofdisks should be rounded up from 7.6 per EXP700 to 8. Additionally to configurefor 5+P RAID level 5 configuration, the number of data disks in each EXP700should be divisible by 5 so once again the number of disks in each EXP700 isrounded up to 10. For every 5 data disks, a parity disk should be added so the

total number of disks per EXP700 is 12. The following diagram shows the logicalconfiguration of a FAStT900 with the enclosures and disks attached.

Figure 7 FAStT900 disk configuration

Twelve disks per EXP700, 2 EXP700 per FAStT900, and 8 FAStT900 brings the

total number of disks to 192. Obviously 192 disks is significantly more than theinitial requirement of 122 disks, but this configuration provides a balanced,evenly distributed load with RAID level 5 protection that meets the performancerequirement.

Random read and write requirements for this workload were relatively low whencompared to the rest of the I/O needs. Considering the number of disks andFAStT900 servers, the random IO requirements of the workload is easily met.

Although a single FAStT900 can easily accommodate the space needs for theworkload, it cannot possibly meet the performance requirements. Space shouldnever be the only determining factor for storage configuration.

FAStT900

EXP700 Enclosure 0

1 2 3 4 5 6 7 8 9 10

11

12

E E

A

B

ESM A

SFP

SFP

ESM B

SFP

SFP

EXP700 Enclosure 1

1 2 3 4 5 6 7 8 91

0

1

1

1

2E E

A

B

ESM A

SFP

SFP

ESM B

SFP

SFP

FC HBA

FC HBA

FC HBA

CTRLA

CTRLB

FC HBA

Host

PCI-X

5+P RAID 5 Arrays


25/38


Based on the storage sizing the system has to be able to accommodate 8storage servers, each with 4 Fibre Channel interfaces. A total of 32 FibreChannel host bus adapters are required to match the performance of the storagesubsystem.

Each p655 can easily provide for 1400 MB/sec of I/O (see I/O subsystem onpage 8). From this it can be determined that at least four p655 nodes arerequired to satisfy the 5000 MB/sec I/O needs of the workload. Depending on theCPU and memory needs for the workloads, the storage can be connected to 8 or16 nodes to satisfy the I/O bandwidth requirements.

Considering the flexibility of the FAStT900, any size system can be connectedthat can evenly distribute the access to 32 HBAs amongst all processors toprovide for 175 MB/sec of bandwidth for each HBA. This system can be a single

node with any number of CPUs or a multi-node cluster of systems with anaggregate I/O bandwidth of 5000MB/sec.

The following table sums up the storage requirements.

Table 5 Storage configuration

Sizing for CPU and memory

Sizing for CPU and memory requires extensive experience and significantknowledge of the workload. For this project, the experience of past testing withspecific queries and the knowledge of the relative performance of the oldersystems compared to the targeted systems was beneficial.

In addition to past experiences, the sizing team needs to run experiments andmake educated guesses. To estimate processor requirements for CPU intensivequeries, a small one processor system can be set up with a fraction of the data tomeasure query performance. The test system can have any processors so longas their performance can be related to the targeted system. For example, as longas it is known that the test system processor is ten times slower than the targetedsystem processor, reasonable estimates can be made. The test system can be

Storage configuration

FAStT900 Storage Server 8

EXP700 Enclosures 16

36GB disk drives 192

2 Gb Fibre Channel Host bus adapters 32


26/38


used to measure the time it takes to process a fixed amount of data by the worstcase query.

If the test system completes a 1 GB query in 50 seconds then it is known that aprocessor that is ten times faster can complete the work in 5 seconds. To

process 1000 GB in 500 seconds, 10 of the new processors are needed. Thistype of estimate can only be applied to simple compute intensive queries that areknown to scale and that have stable query plans regardless of their size.Complex multi-table join queries are not good candidates for simple CPU testingbecause most databases try to apply query optimizations that may behavedifferently based on data size.

The memory usage of the test system needs to be limited to a relative size. Itwould be unreasonable to allow the 1 GB test query to consume 1 GB of memory

unless is was intended to configure the target system with 1000 GB of memory.To simplify the estimates it is preferable to establish a fixed relation betweenmemory size and data size during testing. For this testing, it was established thatat least 100 MB of database memory was needed for each 1 GB of data.Throughout the testing, a similar ratio was maintained. This ratio can bedrastically different from one workload to another.

Workloads that concurrently run many large queries with multi-table join and sortoperations require more memory than workloads that run mostly scan/filter

queries and aggregate data. Alternatively, some sizing experts use the ratio ofmemory per CPU as a guide. A common rule of thumb for data warehouseworkloads used to be 1 GB of memory per CPU, but as memory prices havegone down and processor speeds have gone up this ratio has increased to 4GB.Too little memory will result in increased use of database temporary storage,which will require more I/O operations and possibly more idle processing power.Too much memory will fail to provide performance improvements onceprocessing power is saturated.

Adding memory to a production system is much simpler than adding I/O orprocessing capacity, so this area of the configuration can further be improved solong as some flexibility is built into the plan.

For this project, query 18 was selected as a guide for sizing the systemprocessing needs. Query 1 was known to be a much simpler query to run testswith and that it scales for all data sizes. Query 1 was used for small scale testingand measurement and the relative resource usage graph (Figure 6 on page 19)was used to estimate the processing needs for query 18.

Based on the business requirements, it was known that the worst case queryrunning on 1000 GB of data needed to complete in 900 seconds. Assumingquery 18 was the worst case and knowing that it used about 80 percent of thedata, it was estimated that it would take 720 seconds to complete a query similar


27/38


to 18. Using the relative resource requirement chart (Figure 6 on page 19), it wasestimated that query 1 used about 2.5 times less processing resources toprocess the same size data as query 18 in a fixed period of time, and from this itwas determined that enough CPU processing power was needed to completequery 1 in approximately 300 seconds.

Using experience with older systems and a subset of the data, it was calculatedthat the 1.7 GHz processor available for the p655 system could process query 1at the approximate rate of 180 MB per second. For query 1 to process 800 GB ofdata in 300 seconds a processing rate of 2,730 MB per second was needed. At arate of 180 MB per processor, at least 16 processors were needed to meet theperformance target.

It was estimated that 16 1.7 GHz p655 processors could complete seven

concurrently executing queries similar to query 18 in less than 90 minutes. Thesetiming estimates were well within the business requirements.

Based on these estimates it was assumed a configuration with 16 processorsand approximately 128 GB of memory would satisfy the requirements.

Sizing for the network

Based on the workload characteristics, the data management schemes used by

DB2 and previous experience, it was estimated that a single Gigabit Ethernetinterface per node would be sufficient for the configuration. Considering that onlya few queries in the workload required significant data movement between thenodes, the risk of under configuring the network was low. Having said that, theteam was prepared to add a second interface if necessary. Relative to the totalsystem cost, adding an additional network interface would have beeninsignificant. The network switch and I/O subsystem were configured toaccommodate additional network interfaces if needed.

Different databases have different network bandwidth requirements even whenrunning the same workload. When sizing the network requirements for aclustered data warehouse installation the database vendor should be consultedand their recommendation should be followed with care.

The overall system

Based on the I/O, CPU, and memory requirements, a 4-node cluster of p655systems was needed to meet the workload requirements. This configuration

provided a balanced performance for the workload that maximized the overallsystem utilization. Ability to provide more than 1400 MB per second of I/Obandwidth was a critical feature of the IBM eServer p655 system that made it anattractive option. The ability to provide a huge I/O bandwidth to feed the powerful


28/38


processor and maximize utilization helps achieve the return on the initialinvestment needed to be successful. The following is a general diagram of thesystem configuration.

Figure 8 Overall system configuration

The four node cluster was connected with a gigabit Ethernet switch for allinter-node communications. The overall configuration provided about 5,500 MBper second of read bandwidth from disk to system memory. Each node wasdirectly connected to one-fourth of the total storage configuration. Thisconfiguration was well balanced and flexible and could be easily extended for

larger DB2 installations.

The project was completed by building the configuration based on the sizingestimate and executing the TPC-H benchmark. The sizing effort was a successsince the minimum requirements for the workload were met. Some of the I/Obound queries performed better than expected due to conservative performanceestimates for various components, but the results were within 510 percent ofexpectations. The CPU bound queries performed within 10 percent ofexpectation with about equal number on the plus and minus side. The

benchmark was submitted to the TPC and published in December, 2003. Forfurther details, see the following TPC Web site:

http://www.tpc.org/tpch/results/tpch_result_detail.asp?id=1031208017

Gigabit Switch

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700

IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700

IBM

^

p655

Gigabit Switch

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700

IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700IBM

^

p655

FAStT900

FAStT900

EXP700

EXP700

EXP700

EXP700

IBM

^

p655


29/38


Conclusion

System sizing for a data warehouse is a complicated task that requires someexpertise to accurately estimate the configuration that can meet the needs of abusiness. The quality of the sizing estimate depends on the accuracy of the data

put into the process. With some analysis and workload characterization, it ispossible to drastically improve a sizing estimate. In the absence of the workloadcharacterization data, the expectation of an accurate system sizing estimateshould be set appropriately.

To safeguard a project, build flexibility into plans by configuring systems that arewell balanced in resources and are extensible. For clustered configurations,ensure that the basic building block system is well balanced and meets both I/Oand CPU requirements for its subset of the workload. The reliability, availability,

and serviceability of a cluster are only as good as that of the building blocks.

7 TPC-H Result HighlightsIBM eServer p655 with DB2 UDB

http://www.tpc.org/tpch/results/tpch_result_detail.asp?id-103120801
http://www.tpc.org/tpch/results/tpch_result_detail.asp?id-103120801http://www.tpc.org/tpch/results/tpch_result_detail.asp?id-103120801


30/38


TPC-H Benchmark overview

This section is the TPC-H benchmark overview.

PC Council

The Transaction Processing Performance Council (TPC) was founded inAugust 1988 by eight leading hardware and software companies as a non-profitorganization with the objective to produce common benchmarks to measuredatabase system performance. More than 20 companies are currently membersof the council. There are four active benchmarks (TPC-C, TPC-H, TPC-R andTPC-W) for which results can be published. IBM was the very first company topublish a TPC-H result at the 10,000 GB scale on December 5, 2000.

Prior to publication, results must be reviewed and approved by designatedauditors and a full disclosure report documenting compliance is submitted to theTPC. Published benchmarks as well as the benchmark specifications areaccessible on the TPC Web site:

http://www.tpc.org

TPC-H overviewThe TPC-H benchmark models a decision support system by executing ad-hocqueries and concurrent updates against a standard database under controlledconditions. The purpose of the benchmark is to provide relevant, objectiveperformance data to industry users according to the specifications and allimplementations of the benchmark, in addition to adhering to the specifications,must be relevant to real-world (that is, customer) implementations. TPC-Hrepresents information analysis of an industry that must manage, sell, or

distribute a product worldwide. The 22 queries answer questions in areas suchas pricing and promotions, supply and demand management, profit and revenuemanagement, customer satisfaction, market share, shipping management. Therefresh functions are not meant to represent concurrent online transactionprocessing (OLTP); they are meant to reflect the need to periodically update thedatabase.

The TPC-H database size is determined by the scale factor (SF). A scale factorof 1 represents a database with 10,000 suppliers and corresponds approximately

to 1 GB of raw data. Only a subset of scale factors are permitted for publication:1, 10, 30, 100, 300, 1000, 3000 and 10000. The database is populated with aTPC-supplied data generation program, dbgen, which creates the synthetic dataset. The set of rows to be inser ted or deleted by each execution of the update
http://www.tpc.org/http://www.tpc.org/


31/38


functions is also generated by using dbgen. The database consists of eighttables.

Table 6 Eight tables

The chart below gives a more detailed view of the relationships between thetables and the number of rows per table when the scale factor is 10,000, that isfor a 10,000 GB (10 TB) database.

Table name Cardinality

REGION 5

NATION 25

SUPPLIER SF*10 K

CUSTOMER SF*150 K

PART SF*200 K

PARTSUPP SF*800 KORDER SF*1500 K

LINEITEM SF*6000 K (approximate)


32/38


Figure 9 TPC-H schema

TPC-H enforces the ad-hoc model by severely restricting the implementation ofauxiliary data structures such as indices and materialized query tables(sometimes known as automatic summary tables or materialized views). It alsorestricts how horizontal partitioning (by row) may be implemented. Thepartitioning column is constrained to primary keys, foreign keys, and datecolumns. If range partitioning is used, the ranges must be divided equallybetween the minimum and maximum value.

By imposing these restrictions, the TPC-H benchmark maintains the serverplatform as part of the performance equation and represents an ad-hocenvironment. The TPC-R benchmark that does not restrict auxiliary structuresmodels a reporting environment.

The table below summarizes the differences between the TPC-H and TPC-Rbenchmarks.

PARTKEYNAME

MFGR

BRAND

TYPE

SIZE

CONTAINER

RETAILPRICE

COMMENT

PART (P_)

2,000M

REGIONKEY

NAME

COMMENT

REGION (R_)

5

CUSTKEY

NAME

ADDRESS

NATIONKEY

PHONE

ACCTBAL

MKTSEGMENT

COMMENT

CUSTOMER (C_)

1,500M

NATIONKEYNAME

REGIONKEY

COMMENT

NATION (N_)

25

ORDERKEY

CUSTKEY

ORDERSTATUS

TOTALPRICE

ORDERDATE

ORDERPRIORITY

CLERK

SHIPPRIORITY

COMMENT

ORDER (O_)

15,000M

ORDERKEY

PARTKEY

SUPPKEY

LINENUMBER

QUANTITY

EXTENDEDPRICE

DISCOUNT

TAX

RETURNFLAG

LINESTATUS

SHIPDATE

COMMITDATE

RECEIPTDATE

SHIPINSTRUCT

SHIPMODE

COMMENT

LINEITEM (L_)

60,000M

PARTKEY

SUPPKEY

AVAILQTY

SUPPLYCOST

COMMENT

PARTSUPP (PS_)

8,000M

SUPPKEY

NAME

ADDRESS

NATIONKEY

PHONE

ACCTBAL

COMMENT

SUPPLIER (S_)

100M

PARTKEYNAME

MFGR

BRAND

TYPE

SIZE

CONTAINER

RETAILPRICE

COMMENT

PART (P_)

2,000M

REGIONKEY

NAME

COMMENT

REGION (R_)

5

CUSTKEY

NAME

ADDRESS

NATIONKEY

PHONE

ACCTBAL

MKTSEGMENT

COMMENT

CUSTOMER (C_)

1,500M

NATIONKEYNAME

REGIONKEY

COMMENT

NATION (N_)

25

ORDERKEY

CUSTKEY

ORDERSTATUS

TOTALPRICE

ORDERDATE

ORDERPRIORITY

CLERK

SHIPPRIORITY

COMMENT

ORDER (O_)

15,000M

ORDERKEY

PARTKEY

SUPPKEY

LINENUMBER

QUANTITY

EXTENDEDPRICE

DISCOUNT

TAX

RETURNFLAG

LINESTATUS

SHIPDATE

COMMITDATE

RECEIPTDATE

SHIPINSTRUCT

SHIPMODE

COMMENT

LINEITEM (L_)

60,000M

PARTKEY

SUPPKEY

AVAILQTY

SUPPLYCOST

COMMENT

PARTSUPP (PS_)

8,000M

SUPPKEY

NAME

ADDRESS

NATIONKEY

PHONE

ACCTBAL

COMMENT

SUPPLIER (S_)

100M

T bl 7 Diff b t TPC H d TPC R b h k


33/38


Table 7 Differences between TPC-H and TPC-R benchmarks

The TPC-H benchmark exercises the following areas:

Twenty-two queries with the following characteristics:

Left outer join

Very complex queries with nested sub queries

Aggregate with "HAVING" clause

Queries with multiple "OR" predicates

Query combining "EXISTS" and "NOT EXISTS"

Query with multiple "SUBSTRING" operators

Large scans with multi-table joins

Aggregate operations with large number of distinct values

Large number aggregations and sorts.

Queries relying on index access as well as table access

Long running queries as well as short running queries exercising allaspects of query processing

Database refresh functions to perform inserts and deletes on the database

The benchmark specifications require that the implementation chosen for thebenchmark satisfy Atomicity, Consistency, Isolation and Durability (ACID)properties. Specific tests are designed to show:

That the system either performs individual operations on the data or assurethat no partially completed operations leave any effects on the data (A).

That execution of transactions take the database from one consistent state toanother (C).

TPC-H (ad-hoc) TPC-R (reporting)

Auxiliary data structures Restrictions on indicesNo aggregates

Extensive indices and aggregates OK

Simulated environment Ad-hoc queryHeavy stress on system

Pre planned, frequently asked queriesLots of tuning by DBA

Side effects Average response time severalminutesUpdate function times similar toquery times

Sub-second response times for severalqueriesLoad time much longerUpdate function times much longerthan query times

That concurrent database transactions are handled correctly (I)


34/38


That concurrent database transactions are handled correctly (I).

That committed transactions and database consistency are preserved afterrecovery from hardware failures such as loss of power, communications,memory, data and log disks, etc. (D).

The concurrent updates insert and delete from the two large tables, LINEITEMand ORDER. Each of the refresh functions represents 0.1 percent of the initialpopulation of these two tables. Each pair of refresh functions alters 0.2 percent ofthese two tables. A single update pair must be run for the power test and a setof update pairs for each query stream is run in the multi-user throughput test. Theexact implementation of the refresh functions is left to the vendor. There arecertain rules that need to be followed for the implementation of these refreshfunctions. The TPC-H specification states that each refresh function (RF1 orRF2) can be decomposed into any number of database transactions as long as

the following conditions are met:

All ACID properties are satisfied.

Each atomic transaction includes a sufficient number of updates to maintainthe logical database consistency. For example, when adding or deleting anew order, the LINEITEM and ORDER tables are both updates within thesame transaction.

An output message is sent when the last transaction of the update functionhas completed successfully.

TPC-H Metrics

The benchmark specification provides details on how to report results, whichconsist of two performance metrics and one price/performance metric:

Composite Metric (QphH@Size) = . Thismetric is the primary performance metric, which is composed of the twopieces:

Power (QppH@Size) =

Where Q1, Q2,... RF1, RF2 are timing intervals in seconds of queries andupdates. The geometric mean of the queries and updates is used here togive equal weighting to all the queries even though some may be muchlonger running than others. The power metric is derived from a power run

sizeQthHsizeQppH@*@

24 2*1*22*...*2*1

*3600

RFRFQQQ

SF

(single stream) in which all queries and update functions are run in a


35/38


(single stream) in which all queries and update functions are run in aspecified sequence.

Throughput (QthH@Size) =

Where each stream is defined as a set of the 22 queries and 2 updates inthe predefined order and total elapsed time includes the timing interval forthe completion of all query streams and the parallel update stream. Thethroughput metric must be derived from a throughput run (multi-stream).

Price/Performance (Price-per-QphH@size) =

Where $ is the total hardware, software and three-year maintenance costs forthe system under test.

The size of the database (or scale factor) is explicitly stated in the metric names.The TPC believes that comparisons of TPC-H results measured against differentdatabase sizes are misleading and discourages such comparisons.

In addition to these TPC metrics, the number of streams is reported, which givesan indication of the amount of concurrency during the throughput run. Each scalefactor has a required minimum number of streams that must be run for thethroughput run, defined in the specifications. The database load time (defined asthe total elapsed time to create the tables, load data, create indices, define andvalidate constraints, gather database statistics and configure the system undertest) is also reported. Two consecutive runs must be executed. The metrics forthe run with the lower QphH are reported.

Benchmark evolution

The following table shows the evolution of the TPC Decision Supportbenchmarks.

Table 8 Evolution of TPC Decision Support benchmarks

edTimeTotalElaps

SFreamsNumberOfSt *3600*24*

SizeQphH@

$

Benchmark Version Date released Date obsolete

TPC-H 2.* November 2002 Current

TPC-H 1.* Feb. 1999 November 2002

TPC-D 1.* & 2.* May 1995 April 1999

Although the TPC-H benchmark evolved from TPC-D they are vastly different


36/38


Although the TPC H benchmark evolved from TPC D, they are vastly differentand cannot be compared in any way. The TPC-H Version 2 became effective inNovember of 2002, and although the basic performance aspects of V1 and V2are identical, the pricing methodology was changed. The price/performancemetric of TPC-H V2 is based on 3-year cost, while V1 was based on 5-years.

Performance evolution

Since the inception of the TPC Decision Support benchmark, there have beensome general trends in the industry standard benchmark results, which reflectthe growth pattern of the business intelligence market.

Although the incompatibilities between the four different versions of the TPC

Decision Support benchmarks make it impossible to chart a continuous trend linefrom 1995 to 2003 the following points are undisputed:

Price/performance has improved steadily Scale factor size had increased, that is, database sizes have increased Processing power has increased Memory and disk requirements have increased

The TPC-H benchmark measures the servers I/O, CPU, and memorycapabilities via various database operations such as full table scans, sorting,

joins and aggregation. It also measures how well a DBMS performs these basicdatabase operations, and rewards those with efficient code paths and advancedquery optimizers and parallel technology.

Notices


37/38

Copyright IBM Corp. 2004. All rights reserved. 37

Notices

This information was developed for products and services offered in the U.S.A.

IBM may not offer the products, services, or features discussed in this document in other countries. Consultyour local IBM representative for information on the products and services currently available in your area.Any reference to an IBM product, program, or service is not intended to state or imply that only that IBMproduct, program, or service may be used. Any functionally equivalent product, program, or service thatdoes not infringe any IBM intellectual property right may be used instead. However, it is the user'sresponsibility to evaluate and verify the operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter described in this document.The furnishing of this document does not give you any license to these patents. You can send licenseinquiries, in writing, to:

IBM Director of Licensing, IBM Corporation, North Castle Drive Armonk, NY 10504-1785 U.S.A.

The following paragraph does not apply to the United Kingdom or any other country where suchprovisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATIONPROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS ORIMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimerof express or implied warranties in certain transactions, therefore, this statement may not apply to you.

This information could include technical inaccuracies or typographical errors. Changes are periodically madeto the information herein; these changes will be incorporated in new editions of the publication. IBM maymake improvements and/or changes in the product(s) and/or the program(s) described in this publication at

any time without notice.

Any references in this information to non-IBM Web sites are provided for convenience only and do not in anymanner serve as an endorsement of those Web sites. The materials at those Web sites are not part of thematerials for this IBM product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it believes appropriate withoutincurring any obligation to you.

Any performance data contained herein was determined in a controlled environment. Therefore, the resultsobtained in other operating environments may vary significantly. Some measurements may have been madeon development-level systems and there is no guarantee that these measurements will be the same on

generally available systems. Furthermore, some measurement may have been estimated throughextrapolation. Actual results may vary. Users of this document should verify the applicable data for theirspecific environment.

Information concerning non-IBM products was obtained from the suppliers of those products, their publishedannouncements or other publicly available sources. IBM has not tested those products and cannot confirmthe accuracy of performance, compatibility or any other claims related to non-IBM products. Questions onthe capabilities of non-IBM products should be addressed to the suppliers of those products.

This information contains examples of data and reports used in daily business operations. To illustrate themas completely as possible, the examples include the names of individuals, companies, brands, and products.All of these names are fictitious and any similarity to the names and addresses used by an actual business

enterprise is entirely coincidental.

COPYRIGHT LICENSE:This information contains sample application programs in source language, which illustrates programmingtechniques on various operating platforms. You may copy, modify, and distribute these sample programs in

any form without payment to IBM, for the purposes of developing, using, marketing or distributing applicationf i h li i i i f f h i l f f hi h h


38/38


programs conforming to the application programming interface for the operating platform for which thesample programs are written. These examples have not been thoroughly tested under all conditions. IBM,therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. You may copy,modify, and distribute these sample programs in any form without payment to IBM for the purposes ofdeveloping, using, marketing, or distributing application programs conforming to IBM's applicationprogramming interfaces.

This document created or updated on July 23, 2004.

Send us your comments in one of the following ways: Use the online Contact us review redbook form found at:

ibm.com/redbooks Send your comments in an email to:

[email protected] Mail your comments to:

IBM Corporation, International Technical Support OrganizationDept. JN9B, Building 905, Internal Mail Drop 9053D005, 11501 Burnet RoadAustin, Texas 78758-3493 U.S.A

Trademarks

The following terms are trademarks of the International Business Machines Corporation in the United States,other countries, or both:

DB2EserverIBM

ibm.comPOWER4pSeries

RedbooksRedbooks (logo)

Other company, product, and service names may be trademarks or service marks of others.

TPC Benchmark, TPC-D, TPC-H, TPC-R, QppD, QppH, QppR, QthD, QthH, QthR and QphD, QphH,QphR are trademarks of the Transaction Processing Performance Council.

The following terms are trademarks or registered trademarks of IBM in the United States and/or othercountries: IBM, AIX, DB2, DB2 Universal Database, Power PC Architecture, POWER, IBM Eserver,pSeries.

Performance results described in this paper were obtained under controlled conditions and may not beachievable under different conditions. All information is provided AS IS and no warranties orguarantees are expressed or implied by IBM. Actual system performance may vary and is dependentupon many factors including system hardware configuration and software design and configuration.

All TPC-H results referenced are as of March 30, 2004.
http://www.redbooks.ibm.com/http://www.ibm.com/redbooks/http://www.ibm.com/redbooks/http://www.ibm.com/redbooks/http://www.redbooks.ibm.com/http://www.ibm.com/redbooks/http://www.redbooks.ibm.com/contacts.htmlhttp://www.redbooks.ibm.com/contacts.htmlhttp://www.redbooks.ibm.com/contacts.htmlhttp://www.redbooks.ibm.com/contacts.htmlhttp://www.ibm.com/redbooks/http://www.ibm.com/redbooks/http://www.redbooks.ibm.com/

an introduction to system sizing for data warehousing workloads

Documents