Data Warehouse Performance Tuning

This IBM e-book is a must-read if you’re using DB2 UDB 8.1. IBM Redbooks | Up and Running with DB2 UDB ESE: Partitioning for Performance in an e-Business Intelligence World

It discusses:

• Guidelines on building the large database and determining the number of partitions
• Bulk load using the new multipartition load
• Performance enhancements using MultiDimensional Clustering and Materialized Query Tables.
• Availability through the new online utilities
• Self Managing And Resource Tuning features
• Migration scenarios

Some hints about hardware configuration for BizGres/Greenplum: CPUs and the cheap disk revolution

Open source data warehousing platform using an approach that seems very close to Netezza’s: Greenplum – Products – DeepGreen Clustering Edition

The arrival of open source business intelligence? BizGres

This post is where I’ll be collecting my thoughts about a performance tuning methodology for hardware.

The purpose of the methodology is to improve query response in the most time and cost efficient manner. The steps that need to be followed are:

1. Identify the bottleneck: using OS and RDBMS monitoring tools, figure out if query response is limited by I/O, memory, or CPU speed.
2. If I/O is the problem, check if there is any way to tune the OS to improve performance. If not, check if installing faster hard-drives or more controllers could help.
3. If memory is the bottleneck, first figure out if adding additional memory would improve performance. Then check if tuning OS or RDBMS memory settings would help. I’m working under the assumption that adding RAM is cheap, whereas tuning parameters takes time/money because of all the testing and specialized knowledge required.
4. If CPU speed is the bottleneck, check if adding more CPUs is an option. How well does your hardware/OS/RDBMS scale with more CPUs? Can you upgrade to faster CPUs?

Not directly related to data warehousing, but a nice performance comparison of MySQL performance on multiple operating systems: Comparing MySQL performance

Check also the methodology used to get the results: Using MySQL to benchmark OS performance

Reading list: Amazon.com: Books: High Performance Oracle Data Warehousing: All You Need to Master Professional Database Development Using Oracle

Reading list: SQL Server Performance Guide for Data Warehousing

Reading list: Data Warehouse Design Considerations for SQL Server 2000

Reading list: Teradata.com -> Solutions -> Data Warehousing -> Teradata Warehouse Hardware