WL#9814: Implement INFORMATION_SCHEMA system views for FILES/PARTITIONS
Affects: Server-8.0
—
Status: Complete
This WL should implement new system view definition for following I_S tables, reading metadata from data dictionary tables and avoid creation of temporary table for these I_S table as we do today for I_S.TABLES/COLUMNS/etc: INFORMATION_SCHEMA.PARTITIONS INFORMATION_SCHEMA.FILES INFORMATION_SCHEMA.TABLESPACES:- This I_S tables does not provide any information for now.
Abbreviation: I_S - INFORMATION_SCHEMA. DD - Data dictionary. SE - Storage engine. Functional Requirements: FR1: Implement I_S.FILES system view over DD tables. FR2: Implement I_S.PARTITIONS system view over DD tables. FR3: Implement handlerton API for InnoDB to retrieve tablespace metadata required by I_S.FILES. FR4: Implement UDF GET_TABLESPACE_SE_METADATA() to be used by FR1, which invokes handlerton API designed in FR4. FR5: Add new columns to representing utf8 string version of PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION. FR6: Implement UDF INTERNAL_GET_PARTITION_NODEGROUP() to be used by FR2. To read nodegroup id from DD tables options. FR7: Change to partition code to introduce mysql.partitions.parent_partition_id, instead of 'level' column. Non-Functional Requirements: NFR1: User interface to I_S.FILES/PARTITIONS should not change. NFR2: Related old I_S code should be removed.
WL#6049: Meta-data locking for FOREIGN KEY tables
WL#7743: New data dictionary: changes to DDL-related parts of SE API
WL#7743: New data dictionary: changes to DDL-related parts of SE API
Introduction:
As discussed above, this WL aims to implement I_S tables as a
view over data dictionary tables. The following text describes
the mapping of DD columns to I_S columns. There are I_S columns
that do not map to DD columns, but the get their values from SE.
A) I_S.FILES system view design (FR1):
1) Columns that map to DD tables:
- FILE_NAME maps to mysql.tablespaces.file_name.
- TABLESPACE_NAME maps to mysql.tablespaces.name.
- TABLE_CATALOG maps to mysql.catalog.name.
- TABLE_SCHEMA maps to mysql.schemata.name.
- TABLE_NAME maps to mysql.tabes.name.
- ENGINE maps to mysql.tablespaces.engine.
2) Columns that requests SE for their value (FR4):
typedef bool (*get_tablespace_se_metadata_t)(
const char *se_private_data,
ha_tablespace_se_metadata *tsm /* OUT PARAM */);
/*
The following members will be fill by respective SE as
mentioned below.
*/
class ha_tablespace_se_metadata
{
ulonglong m_file_id; // InnoDB & NDB
char *m_file_type; // InnoDB & NDB
char *m_logfile_group_name; // NDB
ulonglong m_logfile_group_number; // NDB
ulonglong m_version; // NDB
char *m_row_format; // NDB
ulonglong m_FREE_EXTENTS; // InnoDB & NDB
ulonglong m_TOTAL_EXTENTS; // InnoDB & NDB
ulonglong m_EXTENT_SIZE; // InnoDB & NDB
ulonglong m_INITIAL_SIZE; // InnoDB & NDB
ulonglong m_MAXIMUM_SIZE; // InnoDB & NDB
ulonglong m_AUTOEXTEND_SIZE; // InnoDB
ulonglong m_DATA_FREE; // InnoDB
char *m_status; // InnoDB & NDB
char *m_extra; // NDB
};
3) (FR5) Implement UDF GET_TABLESPACE_SE_METADATA() which invokes
get_tablespace_se_metadata_t() handlerton API. This UDF would
be used in system view definition for I_S.FILES.
Q1) Can some of these information be stored in DD ?
may not be as part of this WL, but in future.
Q2) With above proposal of handlerton API, one drawback is that
SE would calculate and fill all the applicable metadata into
ha_tablespace_se_metadata, irrespective of user requesting just
few or single metadata value. E.g., User can request
I_S.FILES.DATA_FREE, but all SE would work to return all
metadata. This might affect performance. Should we really
bother about it now ?
AFAIU, the performance of I_S.FILES is not critical and hence the
above design is fine. If there is a need to improve performance
of such queries in future, we should consider introducing more
handlerton API's one per I_S column.
3) Unused columns in I_S.FILES by all SE:
FULLTEXT_KEYS
DELETED_ROWS
UPDATE_COUNT
CREATION_TIME
LAST_UPDATE_TIME
LAST_ACCESS_TIME
RECOVER_TIME
TRANSACTION_COUNTER
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
CREATE_TIME
UPDATE_TIME
CHECK_TIME
CHECKSUM
B) I_S.PARTITIONS system view design:
1) Columns that map to DD tables:
- TABLE_CATALOG maps to mysql.catalogs.name.
- TABLE_SCHEMA maps to mysql.schemata.name.
- TABLE_NAME maps to mysql.tables.name.
- PARTITION_NAME maps to mysql.partitions.name.
- SUBPARTITION_NAME maps to mysql.partitions.name.
- PARTITION_ORDINAL_POSITION maps to mysql.partitions.ordinal_position.
- SUBPARTITION_ORDINAL_POSITION maps to mysql.partitions.ordinal_position.
- PARTITION_METHOD maps to mysql.partitions.partition_type.
- SUBPARTITION_METHOD maps to mysql.partitions.subpartition_type
- PARTITION_EXPRESSION maps to mysql.tables.partition_expression
- SUBPARTITION_EXPRESSION mysql.tables.subpartition_expression
(FR6) Add new columns to representing utf8 string version of
partition_expression and subpartition_expression. Following new
columns are added.
mysql.tables.partition_expression_utf8 VARCHAR(2048)
mysql.tables.subpartition_expression_utf8 VARCHAR(2048)
- PARTITION_COMMENT maps to mysql.partitions.comment.
- TABLESPACE_NAME maps to mysql.tablespaces.name.
- (FR6) PARTITION_DESCRIPTION maps to more than one rows in DD tables.
It is difficult to calculate this value in the system view definition
by using SELECT grammer. Hence the value is now stored in new column
mysql.table_partitions.description_utf8 field.
- (FR7) NODEGROUP maps to a value stored for the key 'nodegroup_id' in
mysql.partitions.options. The following UDF is user to retrieve
the value.
INTERNAL_GET_PARTITION_NODEGROUP(mysql.partitions.options) AS NODEGROUP
2) Columns that map to respective columns in I_S.TABLES.
- TABLE_ROWS
- AVG_ROW_LENGTH
- DATA_LENGTH
- MAX_DATA_LENGTH
- INDEX_LENGTH
- DATA_FREE
- CREATE_TIME
- UPDATE_TIME
- CHECK_TIME
- CHECKSUM
Q1) Value of these columns in I_S.TABLES work based on server
setting information_schema_stats=latest/cached. I presume the
same behavior should be applicable to I_S.PARTITIONS too ?
3) Changes to DD schema (FR8) :
In order to develop a system view to map each subpartition to
its parent partition, we need following task to be completed.
Without this implementation of B) is not possible.
C) Performance:
C.1)
Performance of I_S.FILES is expected to be better than 5.7.
C.2)
Performance of I_S.PARTITIONS with current implementation would
be slower than 5.7 if the query uses on of column listed in B.2).
This is because we end-up opening tables for each row. We should
ideally develop SE API to fetch required statistics per partition.
This needs effort from InnoDB and some server code changes too.
This work will be handled as part of WL#11076.
However, if the I_S.PARTITIONS query does not use columns listed
in B.2), then the execution of I_S.PARTITIONS is expected to be
faster (at-least 10times) than 5.7.
D) Upgrade:
5.7 to 8.0 Upgrade might not be affected. Database created before
this WL, may not work with server that has this WL implemented.
IMO, as we do not support DD to DD upgrade as of now, no action
is required.
E) Documentation:
Sections that might apply for documentation are,
FR1, FR2 and D). And also mention about C.2) which
expects I_S.PARTITIONS to be slower in certain cases
and it is going to be improved.
[DRAFT content - To be updated]
A) System view definition for FILES
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.FILES AS
SELECT
'UDF" as FILE_ID,
tsf.file_name AS FILE_NAME,
'UDF' AS FILE_TYPE, /* cluster/innodb */
ts.name AS TABLESPACE_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tab.name AS TABLE_NAME,
'UDF' AS LOGFILE_GROUP_NAME, /* cluster */
'UDF' AS LOGFILE_GROUP_NUMBER, /* cluster */
ts.engine AS ENGINE,
'' AS FULLTEXT_KEYS,
'' AS DELETED_ROWS,
'' AS UPDATE_COUNT,
'UDF' AS FREE_EXTENTS, /* cluster/innodb */
'UDF' AS TOTAL_EXTENTS, /* cluster/innodb */
'UDF' AS EXTENT_SIZE, /* cluster/innodb */
'UDF' AS INITIAL_SIZE, /* cluster/innodb */
'UDF' AS MAXIMUM_SIZE, /* cluster/innodb */
'UDF' AS AUTOEXTEND_SIZE,
'' AS CREATION_TIME,
'' AS LAST_UPDATE_TIME,
'' AS LAST_ACCESS_TIME,
'' AS RECOVER_TIME,
'' AS TRANSACTION_COUNTER,
'UDF' AS VERSION, /* cluster */
'UDF' AS ROW_FORMAT, /* cluster */
'' AS TABLE_ROWS,
'' AS AVG_ROW_LENGTH,
'' AS DATA_LENGTH,
'' AS MAX_DATA_LENGTH,
'' AS INDEX_LENGTH,
'UDF' AS DATA_FREE, /* innodb */
'' AS CREATE_TIME,
'' AS UPDATE_TIME,
'' AS CHECK_TIME,
'' AS CHECKSUM,
'UDF' AS STATUS, /* innodb/cluster always NORMAL */
'' AS EXTRA /* no-innodb/cluster*/
FROM mysql.tablespaces ts
JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
LEFT JOIN mysql.tables tab ON tab.tablespace_id=ts.id
JOIN mysql.schemata sch ON tab.schema_id=sch.id
JOIN mysql.catalogs cat ON sch.catalog_id=cat.id");
B) System view definition of PARTITIONS.
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `information_schema`.`PARTITIONS`
AS SELECT
`cat`.`name` AS `TABLE_CATALOG`,
`sch`.`name` AS `TABLE_SCHEMA`,,
`tbl`.`name` AS `TABLE_NAME`,
`part`.`name` AS `PARTITION_NAME`,
`sub_part`.`name` AS `SUBPARTITION_NAME`,
`part`.`number` + 1 AS `PARTITION_ORDINAL_POSITION`,
`sub_part`.`number` + 1 AS `SUBPARTITION_ORDINAL_POSITION`,
(case `tbl`.`partition_type`
when 'HASH' then 'HASH'
when 'RANGE' then 'RANGE'
when 'LIST' then 'LIST'
when 'AUTO' then 'AUTO'
when 'KEY_51' then 'KEY'
when 'KEY_55' then 'KEY'
when 'LINEAR_KEY_51' then 'LINEAR KEY'
when 'LINEAR_KEY_55' then 'LINEAR KEY'
when 'LINEAR_HASH' then 'LINEAR HASH'
when 'RANGE_COLUMNS' then 'RANGE COLUMNS'
when 'LIST_COLUMNS' then 'LIST COLUMNS' else NULL end) AS
`PARTITION_METHOD`,
(case `tbl`.`subpartition_type`
when 'HASH' then 'HASH'
when 'RANGE' then 'RANGE'
when 'LIST' then 'LIST'
when 'AUTO' then 'AUTO'
when 'KEY_51' then 'KEY'
when 'KEY_55' then 'KEY'
when 'LINEAR_KEY_51' then 'LINEAR KEY'
when 'LINEAR_KEY_55' then 'LINEAR KEY'
when 'LINEAR_HASH' then 'LINEAR HASH'
when 'RANGE_COLUMNS' then 'RANGE COLUMNS'
when 'LIST_COLUMNS' then 'LIST COLUMNS' else NULL end) AS
`SUBPARTITION_METHOD`,
`tbl`.`partition_expression_utf8` AS `PARTITION_EXPRESSION`,
`tbl`.`subpartition_expression_utf8` AS `SUBPARTITION_EXPRESSION`,
`part`.`description_utf8` AS `PARTITION_DESCRIPTION`,
internal_table_rows(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `TABLE_ROWS`,
internal_avg_row_length(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `AVG_ROW_LENGTH`,
internal_data_length(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `DATA_LENGTH`,
internal_max_data_length(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `MAX_DATA_LENGTH`,
internal_index_length(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `INDEX_LENGTH`,
internal_data_free(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `DATA_FREE`,
`tbl`.`created` AS `CREATE_TIME`,
internal_update_time(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `UPDATE_TIME`,
internal_check_time(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `CHECK_TIME`,
internal_checksum(`sch`.`name`,`tbl`.`name`,
if((ifnull(`tbl`.`partition_type`,'') = ''),`tbl`.`engine`,''),
`tbl`.`se_private_id`,
if(isnull(`sub_part`.`name`),if(isnull(`part`.`name`),
`tbl`.`se_private_data`, `part_ts`.`se_private_data`),
`sub_part_ts`.`se_private_data`),
ifnull(`sub_part`.`name`,`part`.`name`)) AS `CHECKSUM`,
if(isnull(`sub_part`.`name`),
ifnull(`part`.`comment`,''),
ifnull(`sub_part`.`comment`,'')) AS `PARTITION_COMMENT`,
if(isnull(`part`.`name`),'',
internal_get_partition_nodegroup(if(isnull(`sub_part`.`name`),`part`.`options`,`sub_part`.`options`)))
AS `NODEGROUP`,
ifnull(`sub_part_ts`.`name`,`part_ts`.`name`) AS `TABLESPACE_NAME`
FROM mysql.tables tbl");
JOIN mysql.schemata sch ON sch.id=tbl.schema_id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
LEFT JOIN mysql.table_partitions sub_part ON
sub_part.parent_partition_id=part.id
LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
LEFT JOIN mysql.tablespaces sub_part_ts ON sub_part.tablespace_id IS NOT
NULL
AND sub_part_ts.id=sub_part.tablespace_id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
AND part.parent_partition_id IS NULL;
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.