Integrated Analytics System

Expand all | Collapse all

Concurrency Limit

  • 1.  Concurrency Limit

    Posted Tue February 12, 2019 04:30 PM

    May I know if there is any query concurrency limit in IIAS like in Netezza, if so, what is the limit?

    Chandhra Vadlamudi

    chandhra vadlamudi

  • 2.  RE: Concurrency Limit

    Posted Mon February 18, 2019 09:09 AM

    Hi -

    I received the following information from some team members:

    No specific limit - our adaptive workload manager does fit based admission - so concurrency achieved will depend on the resource demands of the jobs being run. With lightweight jobs it's possible to run 100's simultaneously. For heavier jobs we'll target 32 threads per core by default which typically means that if memory is not a limiting factor we'll try to execute approximately 32 queries at at time. HTH.

    Without Adaptive (Row organized IIAS) we were able to run 150+ concurrent of mix workload queries

    Check out the Adaptive WLM page in the manual:

    Thank you!

    Juliet Sigmann
    Community Marketing Manager

  • 3.  RE: Concurrency Limit

    Posted Sun February 24, 2019 06:48 PM
    I am interested in knowing this as well, so, can I continue asking for a followup question?

    Without considering the sort memory limitation and the locking factor, does the following calculation make any sense?

    # of concurrent sqls <= (total of agents) / effective_query_degree

    where total of agents = # of processor cores * wlm_agent_load_trgt

    If this formula does not a sense in some circumstances, my question is what value/how to set for the wlm_agent_load_trgt, 32; and which value/how to set for effective_query_degree?

    Daniel Wang

  • 4.  RE: Concurrency Limit

    Posted Tue February 26, 2019 10:16 PM
    The equation above is essentially correct, but there are two additional considerations which factor in which are - (1) the query degree can be configured and (2) that very short queries bypass admission control. 

    So the general behavior would be summarized by:

    # of concurrent sqls <= ((# processor cores * wlm_agent_load_trgt) / effective_query_degree) + # very short queries

    And in the case some sessions were doing something like SET CURRENT DEGREE '1' you would potentially get more concurrent queries. 

    As a final note - the reasoning behind the default wlm_agent_load_trgt value of 32 threads per core - the goal is to let in sufficient work to drive a healthy load on the CPUs but not let them get overloaded the point where we lose efficiency (basically maximize throughput as much as possible).


  • 5.  RE: Concurrency Limit

    Posted Wed February 27, 2019 10:07 AM
    Thank you David for your interesting in my question and providing with your answers!

    Yes, I missed including the short queries that bypass the adaptive workload manager. Also, the short query can be redefined/reconfigured as well, so, the # of concurrent sqls is really a unknown number:-)

    Now that you mentioned SET CURRENT DEGREE '1', when/in which situations  can we practically suggest users to do so?

    Daniel Wang

  • 6.  RE: Concurrency Limit

    Posted Wed February 27, 2019 12:19 PM
    In terms of the degree generally speaking you wouldn't tune it, but a hypothetical example might be that you have a high volume operational workload with relatively simple sqls where parallelizing actually adds more overhead than benefit - there you could knock down the parallelism to improve efficiency of execution.


  • 7.  RE: Concurrency Limit

    Posted Wed February 27, 2019 12:37 PM
    Thank you again David! I believe I got the concept.

    Daniel Wang