Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
3. Are any queries waiting on something?
On this page
Use the following steps to determine whether your queries are being blocked or waiting for another reason.
-
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_
before queries fail is 100.management_ max_ queue_ depth 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';
-
-
Are there queries lingering or in an unkillable state?
-
Check the
PROCESSLIST
or the Active Queries 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_ STATUS and 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. SELECT * FROM information_
schema. MV_ BLOCKED_ QUERIES; -
Which query is blocked? Check the
QUERY_
column.TEXT -
What is blocking it and where is it running? Check the
BLOCKING_
andNODE_ ID BLOCKING_
columns.QUERY_ TEXT -
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_
, withschema. PROCESSLIST KILL QUERY <id>
.
-
-
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.
-
Next Steps
Last modified: November 26, 2024