Row compression is a new feature of IBM® DB2® 9 that yields storage capacity savings. This article examines the storage savings and performance impact of this feature. It describes the workload characteristics and environments in which row compression will thrive, as well as providing general guidelines that you can follow to efficiently use this feature.
In this article
- Experimental design
- Results: Compressed versus uncompressed
- Best practices for row compression through DB2 REORG
Every business model looks for the best way to minimize production costs in order to maximize profits. In the summer of 2006, IBM® released the latest version of its data server software technology, DB2® 9. One of the most prominent features introduced in this version is the ability to perform row compression. The goal of row compression is to reduce table storage. When using this feature, DB2 is able to shrink row sizes by building and utilizing a compression dictionary, which is used as an alphabet to compress the rows of data in the database tables. Since more data can reside on a data page, fewer data pages are allocated per table. The net effect is a reduced rate of storage consumption which may lead to fewer disks needed when allocating storage requirements. Since disk storage is one of the most expensive components on any data server, this feature can significantly reduce Total Cost of Ownership (TCO).
Since row compression can reduce TCO, what effect does it have on performance? Due to the nature of compression and expansion algorithms, row compression naturally consumes additional CPU cycles beyond the equivalent uncompressed database activity. In systems with a typical amount of idle CPU, the performance effect of row compression may be negligible and in some cases be more efficient depending upon the compression ratio and the amount of data being retrieved from disk. However, for workloads that consume significant amount of CPU, it may be necessary to increase CPU capacity in order to maintain desirable performance results.
In a 362 GB DSS database, overall database storage consumption was reduced by 40% when row compression was enabled. As a result, the compressed database was restructured to use 50% fewer data disks than the original uncompressed database used. Regardless, the compressed database was able to achieve throughput similar to the uncompressed database when running a single-stream workload, and even experience a 25% throughput improvement in a multi-stream scenario. For row compression to perform efficiently, free CPU resources are indispensable because they are necessary to account for the overhead of compressing and expanding rows. Row compression should provide a performance enhancement to a database system if complex select queries that require mostly sequential data access create the bulk of its workload. If these requirements are met, the benefits of decreased production costs and increased process efficiency can both be attained through the use of this feature.
|Article in PDF format||Row_Compression.pdf||297KB|
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.
- The article "Introducing DB2 9, Part 1: Data compression in DB2 9" (developerWorks, May 2006) gives a short introduction to the data compression concepts in DB2 9.
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Download a free trial version of DB2 Enterprise 9.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
- Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2, Lotus®, Rational®, Tivoli®, and WebSphere®.