Skip to main content

SingleStoreDB v8.0

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 Processes page inStudio, or the PROCESSLIST.

    • On the active processes page in Studio (see an image of this below), check to see whether there are many queued queries by sorting on the status column.

    • The default workload_management_max_queue_depth before queries fail is 100. For more information about this variable, see the List of Engine Variables. You can also check the current setting by running SHOW VARIABLES

      16083b57960a3f.png

    • 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 Processes 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_STATUSand 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. sql 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. sql 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.

Next Steps

  • My queries are queued and waiting on resources: move onto Step 4.

  • There are no queued, blocked, or hung queries: move onto Step 4.

In this section: