3. Are any queries waiting on something?
Use the following steps to determine whether your queries are being blocked or waiting for another reason.
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
If you are using the database client, query MV_PROCESSLIST.
SELECT * FROM information_schema.MV_PROCESSLIST WHERE state = 'queued';
Are there queries lingering or in an unkillable state?
Check the
PROCESSLIST
or the Active Processes page by using the above.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.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.
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.
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 theStat
andValue
columns.SELECT * FROM information_schema.WORKLOAD_MANAGEMENT_STATUS;
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
andBLOCKING_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 theID
ininformation_schema.PROCESSLIST
, withKILL QUERY <id>
.
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.