Sign inTry Free

Examples

On this page

Finding Expensive Queries

For example, to find the five most expensive queries, run:

SELECT EXECUTION_TIME/COMMITS, QUERY_TEXT from information_schema.PLANCACHE
ORDER BY EXECUTION_TIME/COMMITS DESC LIMIT 5;

Finding Specific Queries

In SingleStore, the PROCESSLIST view also has a PLAN_ID column that can be used to look up the query currently running on a given connection in PLANCACHE. For example:

SELECT QUERY_TEXT FROM information_schema.PROCESSLIST
as pl JOIN information_schema.PLANCACHE
as pc ON pl.PLAN_ID = pc.PLAN_ID;

This is the most reliable way to find the query executing on a given connection. The PROCESSLIST.INFO column is not always set by SingleStore.

Note

If you are dealing with sensitive data and want to limit access to query information, but not the full PROCESSLIST view, this can be controlled via the variable, show_query_parameters. By default, this variable is set to ON, so query parameters will be visible in the output of SELECT from information_schema.processlist. If set to OFF, parameters will be hidden. This can be changed only at startup, not during runtime; it must be set in the memsql.cnf file, followed by a system restart.

Finding the Number of Queries Running on All Databases

You can use the processlist to get connections on the database level.

SELECT * FROM INFORMATION_SCHEMA.MV_PROCESSLIST shows all the queries running on the cluster. If you filter or group by the field db, you can get the number of connections by database.

For example:

SELECT db, COUNT(*) FROM information_schema.mv_processlist WHERE db IS NOT NULL GROUP BY 1;
+--------------------+----------+
| db                 | COUNT(*) |
+--------------------+----------+
| information_schema |        2 |
| metrics            |        2 |
+--------------------+----------+

Last modified: September 17, 2024

Was this article helpful?

On this page

Was this article helpful?