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.

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.