Topic
  • 5 replies
  • Latest Post - ‏2012-12-18T23:29:54Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic v9.7 MAX_QUERYDEGREE formula

‏2012-12-06T04:09:18Z |
Hi All

I've asked a related question recently which led me to the following:

I have a v9.7 instance with typical OLTP queries running but also servicing larger more complex queries.

I would like to enable INTRA_PARALLEL and have MAX_QUERYDEGREE set to 2. We have an 8 CPU box.

In the old days there was a formula where MAXAGENTS = MAX_QUERYDEGREE * MAX_CONNECTIONS.

Since MAXAGENTS is now deprecated in v9.7, is there a similar formula for v9.7 to make sure you do not end up with high agent stealing activity?

Cheers
Updated on 2012-12-18T23:29:54Z at 2012-12-18T23:29:54Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: v9.7 MAX_QUERYDEGREE formula

    ‏2012-12-13T23:38:03Z  
    AndreS,
    Do you have the intra_parallel parm set to YES? I'm curious if controlling the max_querydegree is really benefiting your system :/
    A setting of -1 allows the optimizer to determine an appropriate value. If you don't have intra_parallel turned on, I believe max_querydegree is ignored anyway. There are other ways to control max_querydegree at a query level instead of setting a global limit.

    I had a conversation with one of the IBM engineers that help set up the iSAS appliances last year, and his recommendation is to set the intra_parallel parm to NO and max_querydegree to -1. The hardware we were setting up was a DPF environment with DB2 9.7, AIX 6.1 on 2 lpars with 6 DPF partitions, backed by a 320 drive SAN. The workload is both OLTP and OLAP and performs quite well.

    Have you seen improvements by coding hard values for this parm?

    thanks!

    Randy Wilson
  • bluey01
    bluey01
    50 Posts

    Re: v9.7 MAX_QUERYDEGREE formula

    ‏2012-12-14T02:39:59Z  
    AndreS,
    Do you have the intra_parallel parm set to YES? I'm curious if controlling the max_querydegree is really benefiting your system :/
    A setting of -1 allows the optimizer to determine an appropriate value. If you don't have intra_parallel turned on, I believe max_querydegree is ignored anyway. There are other ways to control max_querydegree at a query level instead of setting a global limit.

    I had a conversation with one of the IBM engineers that help set up the iSAS appliances last year, and his recommendation is to set the intra_parallel parm to NO and max_querydegree to -1. The hardware we were setting up was a DPF environment with DB2 9.7, AIX 6.1 on 2 lpars with 6 DPF partitions, backed by a 320 drive SAN. The workload is both OLTP and OLAP and performs quite well.

    Have you seen improvements by coding hard values for this parm?

    thanks!

    Randy Wilson
    Hi Randy
    When you say "There are other ways to control max_querydegree at a query level instead of setting a global limit." - what are some ways of doing this. Most of our DBs are ok, but there is one that is used for large queries and we want to get better performance from this DB.

    Even a pointer to an IBM reference or this would be great.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: v9.7 MAX_QUERYDEGREE formula

    ‏2012-12-14T09:31:13Z  
    AndreS,
    Do you have the intra_parallel parm set to YES? I'm curious if controlling the max_querydegree is really benefiting your system :/
    A setting of -1 allows the optimizer to determine an appropriate value. If you don't have intra_parallel turned on, I believe max_querydegree is ignored anyway. There are other ways to control max_querydegree at a query level instead of setting a global limit.

    I had a conversation with one of the IBM engineers that help set up the iSAS appliances last year, and his recommendation is to set the intra_parallel parm to NO and max_querydegree to -1. The hardware we were setting up was a DPF environment with DB2 9.7, AIX 6.1 on 2 lpars with 6 DPF partitions, backed by a 320 drive SAN. The workload is both OLTP and OLAP and performs quite well.

    Have you seen improvements by coding hard values for this parm?

    thanks!

    Randy Wilson
    Hello,

    I believe the recommandation for INTRA_PARALLEL NO is for DPF configuration.
    It would have been YES for a sigle node configuration with complex queries.

    Regards

    Yves-Antoine Emmanuelli
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: v9.7 MAX_QUERYDEGREE formula

    ‏2012-12-18T23:26:35Z  
    AndreS,
    Do you have the intra_parallel parm set to YES? I'm curious if controlling the max_querydegree is really benefiting your system :/
    A setting of -1 allows the optimizer to determine an appropriate value. If you don't have intra_parallel turned on, I believe max_querydegree is ignored anyway. There are other ways to control max_querydegree at a query level instead of setting a global limit.

    I had a conversation with one of the IBM engineers that help set up the iSAS appliances last year, and his recommendation is to set the intra_parallel parm to NO and max_querydegree to -1. The hardware we were setting up was a DPF environment with DB2 9.7, AIX 6.1 on 2 lpars with 6 DPF partitions, backed by a 320 drive SAN. The workload is both OLTP and OLAP and performs quite well.

    Have you seen improvements by coding hard values for this parm?

    thanks!

    Randy Wilson
    Hi Randy

    In my case I have a predominantly OTLP database but we do serve massive queries over 2000 chars in length joining 7+ tables. These kind of queries LOVE running with intra-parallelism on.

    I know this from experience when I was wokign at another place and a SUN T2 solution was sold to a customer by our sales team to upgrade their Sun v440 server. We did a like for like move of DB2 from the v440 to the T2 and performance dropped considerably. Daytime apllication processing was fine but after hours batch processing performed like a dog.

    What I've found was that DB2 was fielding complex queries returning multi row result sets in batch time and the threaded architecture of the sun T2 CPU was too light weight to service those single threaded. At the end we managed to get reasonable performance by enabling intra-parallelism with a high value for dft query degree.

    During my investigation why the T2 was so slow, I've discovered that high core speed CPUs with lower "hardware threads" such as Intel, still adds benefits to a DB server when running multi threaded queries as long as the dft degree is kept to a smaller value.

    Andre
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: v9.7 MAX_QUERYDEGREE formula

    ‏2012-12-18T23:29:54Z  
    Hello,

    I believe the recommandation for INTRA_PARALLEL NO is for DPF configuration.
    It would have been YES for a sigle node configuration with complex queries.

    Regards

    Yves-Antoine Emmanuelli
    Hi All

    So to answer my own question, it appears that in v9.7 at least, there is a clear link between "agents stolen" and INTRA_PARALLEL ON. I have tested this with other instances where I am running the same databases serving the same applications but where parallelism is turned off. Thos instances does not have an ever increasing "agents stolen" value.

    Agenst stolen increases even with connection pooling and concentrator enabled and with more than enough max connections specified.

    Andre