DB2 增加缓冲池大小和压缩表并不等于更好的性能。为什么？
我正在致力于调整和提高 IBM DB2 版本 9.7 数据库的性能。过去几天我一直在网上搜索，了解到如果我在 COMPRESS 模式下创建表并再创建一个缓冲池并将它们都设置为访问 1024mb，那么我的查询性能应该会提高，因为磁盘 I/O 较少。
但是，当我运行时间分析时，性能下降。我使用我一直使用的索引将新添加的内容添加到我的常规数据库中。每次我搜索谷歌时，我都会想到这样的说法：增加缓冲池大小和多个缓冲池以及表压缩应该证明可以获得更好的性能。
我对这个完全出乎意料的结果感到非常困惑。是否有一些我忘记的调整机制，或者有人对这种奇怪的行为有解释吗？
诚挚的
梅斯蒂卡
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
性能调优的第一条规则是“视情况而定”。压缩或增加缓冲池大小并不一定意味着更好的性能。
DB2 中的压缩增加了开销——DB2 必须压缩和解压缩行，这在未压缩的工作负载上需要额外的 CPU。如果您的工作负载受磁盘限制，压缩可能会提高性能。
仅当您的缓冲池一开始就不够大时，更大的缓冲池才会提高性能。
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.