3298790 - SAP HANA running out of available JobWorker threads due to misconfigured workload management

Execution of SQL statements is hanging on the system even though CPU utilization is on rather low levels and the system is actually underused.

1.

According to the created rtedumps, a large number of threads are hanging due to "Barrier Waits" (see SAP note 1999998):

SqlExecutor at
 in syscall
 in Synchronization::BinarySemaphore::timedWait
 in Execution::JobBarrier::wait
 in Execution::JobObjectImpl::wait
 ...

2.

All sessions have a workload class with an aggregated thread limit applied (TSTL):

SqlExecutor, TID: 34, UTID: -1, CID: 328857, LCID: 328857, SEID: 844437897163907, parent: ---, SQLUserName: "USER1", AppUserName: "USER1", AppName: "SAP_HANARuntimeTools_HRA", ... Parent: 328857, NAME=WC_ANALYTIC_USER, P=5, TIMESec=0, PARENT=, TSTL=74, TSML=400

3.

There is one session executing an expensive analytic statement (for example MDS executed via the INA interface) that is consuming all the resources available and this workload class:

30695914905[thr=109476]: Request, TID: 107, UTID: -1, CID: 326575, LCID: 326575, SEID: 844433520281140, parent: ---, SQLUserName: "ANALYTICUSER1", AppUserName: "ANALYTICUSER1", AppName: "sap.bc.ina.service.v2", NAME=WC_ANALYTIC_USER, P=5, TIMESec=0, PARENT=, TSTL=74, TSML=400, 
MDS/InA Query:
{"Analytics":{"Capabilities":["SP9",...

4.

Workload management was configured in the following way:

 ;WORKLOAD_CLASS_NAME         ;PARENT_WORKLOAD_CLASS_NAME;PRIORITY;STATEMENT_MEMORY_LIMIT;STATEMENT_THREAD_LIMIT;TOTAL_STATEMENT_MEMORY_LIMIT;TOTAL_STATEMENT_THREAD_LIMIT;
5;WC_ANALYTIC                ;                          ;5       ;?                     ;8                     ;400                         ;?                           ;
6;WC_ANALYTIC_USER           ;                          ;5       ;?                     ;?                     ;400                         ;74                          ;      

  ;WORKLOAD_MAPPING_NAME   ;WORKLOAD_CLASS_NAME     ;USER_NAME      ;USERGROUP_NAME;APPLICATION_USER_NAME;APPLICATION_USER_NAME_WILDCARD;APPLICATION_NAME     ;
36;WLM_SAC_ANALYTICUSER1   ;WC_ANALYTIC_USER        ;?              ;?             ;ANALYTICUSER1        ;?                             ;?                    ;
16;WLM_SAC_USER1           ;WC_ANALYTIC_USER        ;?              ;?             ;USER1                ;?                             ;?                    ;
7 ;WLM_INA                 ;WC_ANALYTIC             ;?              ;?             ;?                    ;?                             ;sap.bc.ina.service.v2;     

So, we have a workload class with an aggregated thread limit of 74 (WC_ANALYTIC_USER) that is mapped to individual users and a second class (WC_ANALYTIC) that is mapped to all incoming INA requests.

KBA , HAN-DB-PERF , SAP HANA Database Performance , Problem