Information Schema
Introduction
Tables and views in the information schema surface metadata about clusters and all the components within them. SingleStore DB supports the same information_schema
views as MySQL, as well as a variety of additional views that are specific to SingleStore. The MySQL views are often useful, but it is important to note that some are included mostly for compatibility purposes, meaning that values for some columns may always be NULL
.
Accessing the Information Schema
A majority of tables in the information schema have row-level security. Information Schema tables are different from other types of base tables that can either be accessed in full or not depending on permissions. They are views into underlying data about a cluster and the objects within it. This means that users will be able to see rows in the tables for which they have access to the underlying data/objects.
To control user permissions for the information schema, you can grant read and/or write permissions on the objects for which a user needs access to the information schema data. Alternatively, it is possible to grant SHOW METADATA
on a particular information schema table, which allows access to information schema data without permissions for the related objects.
A few information schema tables do not have row-level security, and require an additional permission to access in full. In general, the exceptions are workload profiling tables, such as MV_QUERIES
, which require the PROCESS
permission, and pipelines tables, which require the INSERT
permission. For more information, see the Permissions Matrix.
Because information schema tables are simply views into the internal data structure which holds the information being surfaced in a given view, most information schema views do not have an impact on memory. For the same reason, the best way to secure this data is by managing user access.
To access the information_schema
, users can either SELECT
data from specific view(s) with a SQL query, or use one of the SHOW COMMANDS which will surface a subset of data from the information_schema
depending on the command. The same permissions apply to any information schema data whether it is being accessed via a query or a SHOW
command.
Examples
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, 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, followed by a system restart.