Topic
  • 2 replies
  • Latest Post - ‏2013-10-03T15:40:06Z by dgowda01
dgowda01
dgowda01
32 Posts

Pinned topic DB2 Storage Optimization - Impact on performance

‏2013-10-01T17:53:36Z |

Hello folks,

I am trying to tune our ITIM for performance. Going through the document, there is a mention of the row-level compression using DB2 Storage Optimization feature, as a means to improve performance by reducing I/O. During normal operation, we normally have quite a bit of spare CPU on our DB2 server, so we can take more in CPU usage as a trade-off. 

If any of you have enabled row-level compression, I would like a feedback from anyone with respect to performance in conjunction with ITIM (space is not a problem ATM for us).

Any other ideas WRT to DB2 performance tuning, not noted in the guide, are also welcome.

  • goonitsupport
    goonitsupport
    101 Posts

    Re: DB2 Storage Optimization - Impact on performance

    ‏2013-10-03T14:55:58Z  

    I am not sure whether you have a performance problem at the moment perhaps you can share what you are trying to achieve? Do you have an io problem/bottleneck? Is this for the ITIM LDAP DB or the ITIMDB. I have only performed row compression on the LDAP database and so my answer relates more to this and the command idsdbmaint -r works out the tables that will benefit from compression.

    Before performing anything other than basic recommended tuning (e.g.  idsdbmaint -i for index reorg) you need to ensure that there are benchmarked performance statistics somehow so you can compare before and after effects and that you know how to back out any changes.

    Personally I wouldn't set this unless I know performance is a problem and it would normally be one of the later things I try. Anecdotally I do believe this did improve performance at 1 site I was at but the idsdbmaint -r command also reorgs the table after setting compression on which also may have been a benefit. I have never seen this command cause any problems, compression is only set on the tables where it will be of benefit (not sure about setting it manually though).

    I have only ever seen documents saying it can be run rather than it should be run;-) Although can't see it doing any harm my old mentor would say "if it ain't broke, don't fix it" and if it is broke get a DB2 db expert for advice.

    Hope someone can add something more definitive here

    Just found this too but it still looks like a suck it and see approach http://www.ibm.com/developerworks/data/library/long/dm-0610chang/

     

  • dgowda01
    dgowda01
    32 Posts

    Re: DB2 Storage Optimization - Impact on performance

    ‏2013-10-03T15:40:06Z  

    I am not sure whether you have a performance problem at the moment perhaps you can share what you are trying to achieve? Do you have an io problem/bottleneck? Is this for the ITIM LDAP DB or the ITIMDB. I have only performed row compression on the LDAP database and so my answer relates more to this and the command idsdbmaint -r works out the tables that will benefit from compression.

    Before performing anything other than basic recommended tuning (e.g.  idsdbmaint -i for index reorg) you need to ensure that there are benchmarked performance statistics somehow so you can compare before and after effects and that you know how to back out any changes.

    Personally I wouldn't set this unless I know performance is a problem and it would normally be one of the later things I try. Anecdotally I do believe this did improve performance at 1 site I was at but the idsdbmaint -r command also reorgs the table after setting compression on which also may have been a benefit. I have never seen this command cause any problems, compression is only set on the tables where it will be of benefit (not sure about setting it manually though).

    I have only ever seen documents saying it can be run rather than it should be run;-) Although can't see it doing any harm my old mentor would say "if it ain't broke, don't fix it" and if it is broke get a DB2 db expert for advice.

    Hope someone can add something more definitive here

    Just found this too but it still looks like a suck it and see approach http://www.ibm.com/developerworks/data/library/long/dm-0610chang/

     

    Thanks Goonitsupport for your feedback and thoughts.

    I am only evaluating our current setup and making recommendations for my architect and management. I am not convinced by the 'performance improvement' promise of the row-level compression and DB2 Strorage Optimization feature and was looking for feedback from the fellow admins who have experience with this feature. We have scheduled scripts for re-org and other maintenance tasks, which are done periodically. So if the major benefit of this extra feature is achievable through such ordinarily available maintenance tasks, I'd rather not bring up the point of purchasing a new feature.