Queing of queries - How to makeout the reason

Help Query Tuning
1

Wanted to know is there any way (either Grafana Dashboard or Information Schema views) which would show us why the queries are getting queued. Is it due to lack of memory, or lack of cpu. We are working with support as well on this issue, morning CST hours we have huge workload and we get only 35-40 queries executing and rest around 100-150 sessions get queued up. We have multiple tickets going on. Any help would be greatly appreciated. We tried switching off workload management without much benefit.

If anyone of you can jump onto this ticket #18339 and suggest us, it would be of great help.

2

See the REASON_FOR_QUEUEING column information_schema.process_list. For example:

singlestore> select * from information_schema.processlist limit 1 \G 
*************************** 1. row ***************************
                  ID: 896
                USER: distributed
                HOST: 172.17.0.2:47285
                  DB: NULL
             COMMAND: Sleep
                TIME: 3
               STATE: 
                INFO: NULL
            RPC_INFO: {}
             PLAN_ID: NULL
   TRANSACTION_STATE: NULL
      ROW_LOCKS_HELD: 0
PARTITION_LOCKS_HELD: 0
               EPOCH: NULL
               LWPID: 3885
       RESOURCE_POOL: default_pool
        STMT_VERSION: 0
 REASON_FOR_QUEUEING: 
1 row in set (0.00 sec)

And information_schema.mv_processlist also includes the reason for queueing, plus some additional columns not in processlist.

1 Like
3

Thanks a lot Eric. Can we see if we do not have workload manager is not switched on as well?