You are viewing an older version of this section. View current production version.
Information Schema
MemSQL 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 views PLANCACHE, LEAVES, AGGREGATORS, DISTRIBUTED_PARTITIONS, and MV_CLUSTER_STATUS.
For 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 MemSQL, 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 MemSQL.
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 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, followed by a system restart.
