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 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.