You are viewing an older version of this section. View current production version.
SingleStore DB supports the same
information_schema views as MySQL. In addition, the information available in SHOW PLANCACHE, SHOW LEAVES , SHOW AGGREGATORS, SHOW PARTITIONS, and SHOW CLUSTER STATUS is exposed in the
information_schema tables with row-level security, a user can only view rows from tables for which the user has access privileges. This row-level security only applies to some
information_schema tables, for example, the
DISTRIBUTED_PARTITIONS table. A user with any of the following access privileges can view the rows in the table:
SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | REFERENCES | INDEX | ALTER | CREATE VIEW | SHOW VIEW | TRIGGER | ALTER VIEW | DROP VIEW | CREATE PIPELINE | START PIPELINE | ALTER PIPELINE | SHOW PIPELINE | DROP PIPELINE | EXECUTE | CREATE PROCESS | ALTER PROCESS | CREATE TEMPORARY TABLES | LOCK TABLES | EVENT | REPLICATION | RELOAD | BACKUP | CREATE DATABASE | DROP DATABASE
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
In SingleStore DB, 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 DB.
If you are dealing with sensitive data and want to limit access to query information, 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
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, followed by a system restart.