Outdated Version

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.

Info

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.