DB2 increase bufferpool size and compressed tables not equal better performance. Why?

Question

Hi,

I’m working on tuning and increasing the performance of my IBM DB2 version 9.7 database. I’ve been searching around the net for the last couple of days and learned that if I created my tables in COMPRESS mode and created one more bufferpool and set both of them to access 1024mb, then the performance in my queries should increase because of the less I/Os to the disks.

However, when I run my time analysis, the performance Decrease. I added the new additions to my regular database with the indexes I’ve used all the time. Each time I search google I come up with the statement that: Increased bufferpool size and several bufferpools AND a table compression SHOULD prove to get better performance.

I’m very puzzled about the total unexpected result. Are there some tuning mechanisms I’ve forgot or does anyone have a explanation for this odd behavior?

Sincerely

Mestika

Answer 1

The first rule of performance tuning is, "it depends." Compression or increasing bufferpool size don't necessarily mean better performance.

Compression in DB2 adds overhead -- DB2 has to compress and uncompress rows, which requires extra CPU over an uncompressed workload. Compression MAY improve performance IF you have a disk-bound workload.

A larger bufferpool will only help performance if your bufferpool was undersized to begin with.