Topic
  • 2 replies
  • Latest Post - ‏2014-07-23T05:28:46Z by RajaSubramaniyan
RajaSubramaniyan
RajaSubramaniyan
2 Posts

Pinned topic SMP Implementation for DB2 on i (for SAP BW application)

‏2014-07-22T11:20:33Z |

Hi,

For performance improvement for certain queries, I would like to implement / turn on, SMP feature for one of the LPAR. The application is SAP BW system. (OLTP).

Experts, please advise me, the step by step procedure to implement SMP feature for SAP based IBM i LPAR. Thanks in advance.

  • rschmerb
    rschmerb
    1 Post

    Re: SMP Implementation for DB2 on i (for SAP BW application)

    ‏2014-07-22T15:47:16Z  

    Hi Raja,

    If you are looking to improve complex query performance for SAP BW, there are several options - SMP being only one of them.  I'll go through the steps here just for awareness - for you and anyone else that may be reading this.

    My first suggestion is a move to IBM i 7.2.  I really expect SAP BW customers to notice a difference.  My experiences with SAP BW in the lab has shown significantly cut in response time for complex BW queries as compared to 7.1.  In addition to response time improvements, CPU utilization dropped significantly, leaving room at the upper end for more throughput.   This is all without SMP, but because it frees up CPU, the 7.2 improvements would also be very helpful in an SMP environment so I'd recommend this regardless of what you do with SMP.

    Next, SAP provides a base EVI strategy that is usually good enough, but for optimal performance on some custom queries you may need to create a few more EVIs.  Use the SAP DBCockpit to identify the longest running queries and look to the IX Advisor in the Navigator, paying close attention to the EVI type.  I'd create an single column EVI over any field that was frequently advised and did not have one. Make sure to do this through the SAP data dictionary so the EVI would persist across SAP release upgrades.

    Using SMP also assumes you do not have an IO bottleneck.  If you are paging with just one CPU, multiple CPUs making requests for data in parallel may compound that issue.   SSD or Flash storage can make a huge difference in reducing any IO bottlenecks.

    If you have confirmed that IO is not a major bottleneck now and you have CPU available, then I would agree that SMP could provide further benefits.  

    There are 2 ways to turn it on.  The first would be a system wide change using the QQRYDEGREE system value.   *NONE is the default (although *IO parallelism is always on).  We do not recommend *MAX in multi-user SAP environments as one query may be allowed to use all CPU resources, leaving nothing for other SAP work processes.   *OPTIMIZE would leave it up to the optimizer to be kind to others, but still may be more aggressive than you might like if there are more than a few users competing for cpu resources at a time.  The second (and preferred) way to enable SMP parallelism would be with an alternate INI file that SAP would use only for the BW queries.  There you would use the PARALLEL_DEGREE parameter with a value of *OPTIMIZE <n>.  The <n> parameter will cap the maximum # of threads that the optimizer may use per query.  Set <n> keeping in mind how many CPU cores you have allocated to this partition and knowing that on Power7 there are 4 threads per core (Power 8 has 8 threads per core), because as all CPU threads become fully utilized, short running queries (like moving between bw screens) could be impacted.  A conservative approach would start with *OPTIMIZE 2 to see how it behaves, then move to 4 or more.  Optimally,  even a value of 2 could cut your longest running query times up to 50%, or 4 could cut them down to a quarter of the time so it doesn't take a large number here to make a big impact if SMP is going to help at all.   SAP Note 501572 provides direction on how to implement the alternate ini for BW.

     

    Updated on 2014-07-22T15:48:32Z at 2014-07-22T15:48:32Z by rschmerb
  • RajaSubramaniyan
    RajaSubramaniyan
    2 Posts

    Re: SMP Implementation for DB2 on i (for SAP BW application)

    ‏2014-07-23T05:28:46Z  
    • rschmerb
    • ‏2014-07-22T15:47:16Z

    Hi Raja,

    If you are looking to improve complex query performance for SAP BW, there are several options - SMP being only one of them.  I'll go through the steps here just for awareness - for you and anyone else that may be reading this.

    My first suggestion is a move to IBM i 7.2.  I really expect SAP BW customers to notice a difference.  My experiences with SAP BW in the lab has shown significantly cut in response time for complex BW queries as compared to 7.1.  In addition to response time improvements, CPU utilization dropped significantly, leaving room at the upper end for more throughput.   This is all without SMP, but because it frees up CPU, the 7.2 improvements would also be very helpful in an SMP environment so I'd recommend this regardless of what you do with SMP.

    Next, SAP provides a base EVI strategy that is usually good enough, but for optimal performance on some custom queries you may need to create a few more EVIs.  Use the SAP DBCockpit to identify the longest running queries and look to the IX Advisor in the Navigator, paying close attention to the EVI type.  I'd create an single column EVI over any field that was frequently advised and did not have one. Make sure to do this through the SAP data dictionary so the EVI would persist across SAP release upgrades.

    Using SMP also assumes you do not have an IO bottleneck.  If you are paging with just one CPU, multiple CPUs making requests for data in parallel may compound that issue.   SSD or Flash storage can make a huge difference in reducing any IO bottlenecks.

    If you have confirmed that IO is not a major bottleneck now and you have CPU available, then I would agree that SMP could provide further benefits.  

    There are 2 ways to turn it on.  The first would be a system wide change using the QQRYDEGREE system value.   *NONE is the default (although *IO parallelism is always on).  We do not recommend *MAX in multi-user SAP environments as one query may be allowed to use all CPU resources, leaving nothing for other SAP work processes.   *OPTIMIZE would leave it up to the optimizer to be kind to others, but still may be more aggressive than you might like if there are more than a few users competing for cpu resources at a time.  The second (and preferred) way to enable SMP parallelism would be with an alternate INI file that SAP would use only for the BW queries.  There you would use the PARALLEL_DEGREE parameter with a value of *OPTIMIZE <n>.  The <n> parameter will cap the maximum # of threads that the optimizer may use per query.  Set <n> keeping in mind how many CPU cores you have allocated to this partition and knowing that on Power7 there are 4 threads per core (Power 8 has 8 threads per core), because as all CPU threads become fully utilized, short running queries (like moving between bw screens) could be impacted.  A conservative approach would start with *OPTIMIZE 2 to see how it behaves, then move to 4 or more.  Optimally,  even a value of 2 could cut your longest running query times up to 50%, or 4 could cut them down to a quarter of the time so it doesn't take a large number here to make a big impact if SMP is going to help at all.   SAP Note 501572 provides direction on how to implement the alternate ini for BW.

     

    Thanks  Ron Schmerbauch for detailed explanation.

    Updated on 2014-07-23T05:29:01Z at 2014-07-23T05:29:01Z by RajaSubramaniyan