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.

Was this Article Helpful?

Thanks For Your Feedback!