Metadatabases and data dictionaries

The metadatabase of a PolarDB-Xdatabase is an INFORMATION_SCHEMA database that is compatible with the metadatabase of MySQL. To query data in the metadatabase of a PolarDB-X database, you can execute SQL statements over a Java Database Connectivity (JDBC) connection.

Item in INFORMATION_SCHEMA Compatible with MySQL
SCHEMATA Yes
TABLES Yes
COLUMNS Yes
STATISTICS Yes
COLUMN_STATISTICS Histograms are generated based on data that is processed by PolarDB-Xand is in a specific format supported by PolarDB-X.
ENGINES Yes
KEYWORDS Yes
COLLATIONS Yes
TABLE_CONSTRAINTS Yes
PROCESSLIST Yes
SESSION_VARIABLES Yes
GLOBAL_VARIABLES Yes
INNODB_LOCKS Yes
INNODB_TRX Yes
INNODB_BUFFER_PAGE Yes
INNODB_BUFFER_POOL_STATS Yes
INNODB_BUFFER_PAGE_LRU Yes
INNODB_LOCK_WAITS Yes
USER_PRIVILEGES Yes
PARTITIONS Only partitioned tables in the current PolarDB-Xdatabase have partitions.
WORKLOAD The hybrid transaction/analytical processing (HTAP) workloads on the current PolarDB-Xdatabase.
GLOBAL_INDEXES Global secondary indexes (GSIs) in the current PolarDB-Xdatabase.
METADATA_LOCK Information about metadata locks (MDLs) in the current PolarDB-Xdatabase.
TABLE_GROUP Information about table groups in the current PolarDB-Xdatabase.
TABLE_DETAIL Information about partitions in partitioned tables in the current PolarDB-Xdatabase.
LOCALITY_INFO Information about the locality of a database or table in the current PolarDB-Xdatabase.
PHYSICAL_PROCESSLIST Information about the SQL statements that are pushed down from compute nodes to data nodes of the current PolarDB-Xdatabase for execution.
PLAN_CACHE Information about caches of execution plans in the current PolarDB-Xdatabase.
STATISTIC_TASK Information about statistics tasks in the current PolarDB-Xdatabase.
CCL_RULE Information about concurrency control (CCL) rules in the current PolarDB-Xdatabase.
CCL_TRIGGER Information about CCL triggers in the current PolarDB-Xdatabase.
STATEMENTS_SUMMARY Summary of the current SQL statements that are executed on the current PolarDB database.
STATEMENTS_SUMMARY_HISTORY Summary of historical SQL statements that were executed on the current PolarDB database.
SEQUENCES Information about sequences in the current PolarDB-X database.
SCHEMA_PRIVILEGES No
TABLE_PRIVILEGES No
INNODB_TEMP_TABLE_INFO No
INNODB_SYS_INDEXES No
INNODB_SYS_FIELDS No
INNODB_CMP_PER_INDEX_RESET No
INNODB_FT_DEFAULT_STOPWORD No
INNODB_FT_INDEX_TABLE No
INNODB_FT_INDEX_CACHE No
INNODB_SYS_TABLESPACES No
INNODB_METRICS No
INNODB_SYS_FOREIGN_COLS No
INNODB_CMPMEM No
INNODB_SYS_COLUMNS No
INNODB_SYS_FOREIGN No
INNODB_SYS_TABLESTATS No
INNODB_FT_CONFIG No
INNODB_SYS_VIRTUAL No
INNODB_CMP No
INNODB_FT_BEING_DELETED No
INNODB_CMP_PER_INDEX No
INNODB_CMPMEM_RESET No
INNODB_CMP_RESET No
INNODB_FT_DELETED No
INNODB_SYS_TABLES No
INNODB_SYS_DATAFILES No
PROFILING No
REFERENTIAL_CONSTRAINTS No
SESSION_STATUS No
TABLESPACES No
EVENTS No
TRIGGERS No
ROUTINES No
COLUMN_PRIVILEGES No
FILES No
KEY_COLUMN_USAGE No
OPTIMIZER_TRACE No
PARAMETERS No
CHARACTER_SETS No
COLLATION_CHARACTER_SET_APPLICABILITY No
PLUGINS No

SCHEMATA

The SCHEMATA table stores information about the current database. The SCHEMATA table contains the following columns:

  • CATALOG_NAME: the name of the catalog to which the database belongs. Valid value: def.
  • SCHEMA_NAME: the name of the database.
  • DEFAULT_CHARACTER_SET_NAME: the name of the default character set of the database.
  • DEFAULT_COLLATION_NAME: the name of the default collation of the database.
  • SQL_PATH: The value is NULL.

TABLES

The TABLES table stores information about database tables. The TABLES table contains the following columns:

  • TABLE_CATALOG: the name of the catalog to which the table belongs. Valid value: def.
  • TABLE_SCHEMA: the name of the database to which the table belongs.
  • TABLE_NAME: the name of the table.
  • ENGINE: the storage engine of the database.
  • VERSION: the version of the database.
  • ROW_FORMAT: the row format.
  • TABLE_ROWS: the estimated number of rows in the table.
  • AVG_ROW_LENGTH: the average length of values in rows.
  • DATA_LENGTH: the estimated size of the base table.
  • MAX_DATA_LENGTH: the maximum size of the table. Default value: NULL.
  • INDEX_LENGTH: the estimated size of the index table.
  • DATA_FREE: the idle storage space of the table.
  • AUTO_INCREMENT: the next AUTO_INCREMENT value.
  • CREATE_TIME: the point in time when the table was created.
  • UPDATE_TIME: the most recent point in time when the table was updated.
  • CHECK_TIME: the point in time when the table was checked. Valid value: NULL.
  • TABLE_COLLATION: the default collation of the table.
  • CHECKSUM: the CHECKSUM value of the table.
  • CREATE_OPTIONS: the options in the CREATE TABLE statement that is executed to create the table.
  • TABLE_COMMENT: the comments that are specified for the table.

COLUMNS

The COLUMNS table stores information about columns in the database. The COLUMNS table contains the following columns:

  • TABLE_CATALOG: the name of the catalog of the table to which the column belongs. Valid value: def.
  • TABLE_SCHEMA: the name of the database to which the column belongs.
  • TABLE_NAME: the name of the table to which the column belongs.
  • COLUMN_NAME: the name of the column.
  • ORDINAL_POSITION: the position of the column in the table.
  • COLUMN_DEFAULT: the default value in the column.
  • IS_NULLABLE: specifies whether NULL can be specified as a value in the column.
  • DATA_TYPE: the data type of the column. The value does not contain the precision of values in the column.
  • CHARACTER_MAXIMUM_LENGTH: the maximum length of a value in the column. Unit: characters.
  • CHARACTER_OCTET_LENGTH: the maximum length of a value in the column. Unit: bytes.
  • NUMERIC_PRECISION: the precision of numeric values in the column.
  • NUMERIC_SCALE: the scale of numeric values in the column.
  • DATETIME_PRECISION: the precision of datetime values in the column.
  • CHARACTER_SET_NAME: the name of the character set of the column.
  • COLLATION_NAME: the name of the collation of the column.
  • COLUMN_TYPE: the column type. The value includes the precision of values in the column.
  • COLUMN_KEY: the index on the column.
  • EXTRA: other information about the column.
  • PRIVILEGES: the permissions on the column.
  • COLUMN_COMMENT: the comments that are specified in the column definition.
  • GENERATION_EXPRESSION: the expression that is used to generate the column.

STATISTICS

The STATISTICS table stores information about indexes. The STATISTICS table contains the following columns:

  • TABLE_CATALOG: the name of the catalog of the base table to which the index belongs. Valid value: def.
  • TABLE_SCHEMA: the name of the schema of the base table to which the index belongs.
  • TABLE_NAME: the name of the base table to which the index belongs.
  • NON_UNIQUE: specifies whether the index is unique.
  • INDEX_SCHEMA: the name of the schema of the index table to which the index belongs.
  • INDEX_NAME: the name of the index.
  • SEQ_IN_INDEX: the sequence number of the index that is used to index the column.
  • COLUMN_NAME: the name of the column.
  • COLLATION: information about the column collation.
  • CARDINALITY: the cardinality of the column.
  • SUB_PART: the prefix of the index.
  • PACKED: information about the compressed index.
  • NULLABLE: specifies whether the column can contain NULL values.
  • INDEX_TYPE: the type of the index.
  • COMMENT: the comments of the index. The comments are not specified based on columns.
  • INDEX_COMMENT: the comments of the index.

STATEMENTS_SUMMARY and STATEMENTS_SUMMARY_HISTORY

The STATEMENTS_SUMMARY table stores summary information about SQL statements that are executed in the current statistical period. Summary information about SQL statements is collected based on SQL templates.

The STATEMENTS_SUMMARY_HISTORY table stores summary information about SQL statements that were executed in the historical statistical periods. Summary information about SQL statements is collected based on SQL templates.

By default, a statistical period is 30 minutes. The system can collect information about up to 1,000 SQL statements within each statistical period. The STATEMENTS_SUMMARY_HISTORY table contains information about SQL statements that are collected in the previous 24 statistical periods.

The STATEMENTS_SUMMARY table and STATEMENTS_SUMMARY_HISTORY table contains the following columns:

  • BEGIN_TIME: the start time of the statistical period.
  • SCHEMA: the name of the database.
  • SQL_TYPE: the type of the SQL statement.
  • TEMPLATE_ID: the ID of the SQL template.
  • PLAN_HASH: the hash value of the execution plan.
  • SQL_TEMPLATE: the SQL template.
  • COUNT: the number of executions.
  • ERROR_COUNT: the number of execution errors.
  • SUM_RESPONSE_TIME_MS: the total response time. Unit: ms.
  • AVG_RESPONSE_TIME_MS: the average response time. Unit: ms.
  • MAX_RESPONSE_TIME_MS: the maximum response time that is allowed. Unit: ms.
  • SUM_AFFECTED_ROWS: the total number of rows that are returned or updated. Unit: rows.
  • AVG_AFFECTED_ROWS: the average number of rows that are returned or updated. Unit: rows.
  • MAX_AFFECTED_ROWS: the maximum number of rows that can be returned or updated. Unit: rows.
  • SUM_TRANSACTION_TIME_MS: the total transaction time. Unit: ms.

Note

Transaction time indicates the transaction duration until the execution of the statement is complete.

  • AVG_TRANSACTION_TIME_MS: the average transaction time. Unit: ms.
  • MAX_TRANSACTION_TIME_MS: the maximum transaction time that is allowed. Unit: ms.
  • SUM_BUILD_PLAN_CPU_TIME_MS: the total CPU time that is required to build execution plans. Unit: ms.
  • AVG_BUILD_PLAN_CPU_TIME_MS: the average CPU time that is required to build an execution plan. Unit: ms.
  • MAX_BUILD_PLAN_CPU_TIME_MS: the maximum CPU time that can be consumed to build an execution plan. Unit: ms.
  • SUM_EXEC_PLAN_CPU_TIME_MS: the total CPU time that is required to run execution plans. Unit: ms.
  • AVG_EXEC_PLAN_CPU_TIME_MS: the average CPU time that is required to run an execution plan. Unit: ms.
  • MAX_EXEC_PLAN_CPU_TIME_MS: the maximum CPU time that can be consumed to run an execution plan. Unit: ms.
  • SUM_PHYSICAL_TIME_MS: the total period of time that is required to push down SQL statements from compute nodes to data nodes for execution. Unit: ms.
  • AVG_PHYSICAL_TIME_MS: the average period of time that is required to push down an SQL statement from compute nodes to data nodes for execution. Unit: ms.
  • MAX_PHYSICAL_TIME_MS: the maximum period of time that can be consumed to push down an SQL statement from compute nodes to data nodes for execution. Unit: ms.
  • SUM_PHYSICAL_EXEC_COUNT: the total number of physical SQL executions.
  • AVG_PHYSICAL_EXEC_COUNT: the average number of physical SQL executions.
  • MAX_PHYSICAL_EXEC_COUNT: the maximum number of physical SQL executions.
  • SUM_PHYSICAL_FETCH_ROWS: the total number of rows that are retrieved by physical SQL queries.
  • AVG_PHYSICAL_FETCH_ROWS: the average number of rows that are retrieved by a physical SQL query.
  • MAX_PHYSICAL_FETCH_ROWS: the maximum number of rows that can be retrieved by a physical SQL query.
  • FIRST_SEEN: the point in time when the SQL template was collected for the first time.
  • LAST_SEEN: the point in time when the SQL template was collected the last time.
  • SQL_SAMPLE: the sample SQL statement.
  • PREV_TEMPLATE_ID: the ID of the template of the previous SQL statement.
  • PREV_SAMPLE_SQL: the previous sample SQL statement.
  • SAMPLE_TRACE_ID: the trace ID of the sample SQL statement.
  • WORKLOAD_TYPE: the type of workload. Valid values: TP and AP. TP indicates transactional processing and AP indicates analytical processing.

  • EXECUTE_MODE: the execution mode. Valid values:

    • NONE
  • CURSOR
  • TP_LOCAL
  • AP_LOCAL
  • MPP

For more information about INFORMATION_SCHEMA tables, visit the MySQL official website.

results matching ""

    No results matching ""