3. Are any queries waiting on something?

Use the following steps to determine whether your queries are being blocked or waiting for another reason.

1. Check the Active Queries page in Studio, or the PROCESSLIST.

   • On the active queries page in Studio, check whether there are many queued queries by sorting on the status column.

   • The default workload_management_max_queue_depth before queries fail is 100. Refer to the List of Engine Variables for more information on this variable. You can also check the current setting by running SHOW VARIABLES

     

   • If you are using the database client, query MV_PROCESSLIST. SELECT * FROM information_schema.MV_PROCESSLIST WHERE state = 'queued';

2. Are there queries lingering or in an unkillable state?

   • Check the PROCESSLIST or the Active Queries page by using the above.

     1. Sort by time. If there is a long runtime (depending on a normal run time for your queries, which could be minutes, hours, or days), it is likely the query is hung.

     2. Identify those queries and run KILL QUERY on them. This can be done in Studio by checking the boxes shown above, or from the client.

     3. You can also run KILLALL QUERIES to remove a large number of queries at once. Note: This will kill all queries, whether hanging or running.

     4. If you see queries with both a long runtime and a state of KILLED on a given node, there may be queries stuck in an unkillable state. Restart the affected node.

   • Are queries queued by the workload manager? Check this by querying MV_WORKLOAD_MANAGEMENT_STATUS and looking at Queued Queries (from global queue) & Queued Queries (from local queue) within the Stat and Value columns. SELECT * FROM information_schema.WORKLOAD_MANAGEMENT_STATUS;

3. Are queries blocked?

   • Check this by querying information_schema.MV_BLOCKED_QUERIES. SELECT * FROM information_schema.MV_BLOCKED_QUERIES;

   • Which query is blocked? Check the QUERY_TEXT column.

   • What is blocking it and where is it running? Check the BLOCKING_NODE_ID and BLOCKING_QUERY_TEXT columns.

   • Check the blocking reason by looking at the BLOCKING_TYPE

   • Kill the blocking query by using the ID column, which is the same as the ID in information_schema.PROCESSLIST, with KILL QUERY <id>.

4. If your queries are failing:

   • Is the queue depth too large? Query MV_PROCESSLIST to check this. SELECT COUNT(*) FROM information_schema.MV_PROCESSLIST WHERE state = 'queued';

   • Are these queries getting killed by the resource governor? Check resource the status of resource pools by querying RESOURCE_POOLS. SELECT * FROM information_schema.RESOURCE_POOLS;

   • Examine the failure errors from failed queries.