WL#7943: InnoDB: Implement Information_Schema.Files
With the completion of WL#6205 (InnoDB: Implement CREATE TABLESPACE), InnoDB should provide data for INFORMATION_SCHEMA.FILES for all the datafiles that are in its internal cache. This table contains fields to describe a file including some statistical detail.
Since there is a plan to redirect INFORMATION_SCHEMA.FILES to the Global DD tables described in WL#6379, This worklog will report a limited amount of data to INFORMATION_SCHEMA.FILES. This can be expanded later.
See also BUG#76182 - THE NEW CREATED TABLESPACE IS NOT REPORTED IN THE INFORMATION_SCHEMA
FR1: INFORMATION_SCHEMA.FILES can display data for each datafile in each system, general, or file-per-table tablespace. FR2: The data provided is correct.
INFORMATION_SCHEMA.FILES is intended to describe data files on disk. Its design has been influenced by NDB which, before this worklog, was the only engine that provided data for this table. In this worklog, InnoDB will provide data for those fields that make sense from the InnoDB perspective. Even though it may be possible to provide more detail, the metadata provided will be limited. InnoDB will report from its internal in-memory cache of open files. This is different from INFORMATION_SCHEMA.INNODB_SYS_DATAFILES which reports from the dictionary table SYS_DATAFILES. The following are the differences in content: 1) Since the system temporary tablespace (ibtmp1, ...) is not recorded in SYS_DATAFILES but is known to the InnoDB fil_system cache, it will show up in a report from FILES, but not INNODB_SYS_DATAFILES. 2) INNODB_SYS_DATAFILES does not show the UNDO log files if innodb_undo_tablespaces > 1. But they will be in FILES. NOTE: REDO log files are stored in the fil_system cache, but they will not be reported to FILES since their format is very different from tablespace datafiles. The fields in INFORMATION_SCHEMA.FILES are defined in sql/sql_show.h. Some of them seem to apply to tables, not datafiles for tablespaces. This worklog will not provide table level info, which could be provided when only a single table exists in the tablespace. The following list describes the columns currently defined in INFORMATION_SCHEMA.FILES, and how InnoDB will implement them in this worklog. https://dev.mysql.com/doc/refman/5.7/en/files-table.html FILE_ID SYS_TABLESPACES.ID: (also refered to as space_id, or fil_space_t::id) FILE_NAME The SYS_DATAFILES.NAME: file name (*.ibd, undo*, ibdata*, ib_logfile*) This name will include the path, which may be a relative path from the datadir. FILE_TYPE Just these three file types are used in this worklog. "TABLESPACE": This will include any system, general, or file-per-table tablespace that holds tables, indexes and other forms of user data. "TEMPORARY": This is a special kind of tablespace that will be emptied on server startup. User data in these tablespaces are lost when the system is shut down or killed. These tablespaces are also optimized since any table in them can only be accessed by a single client. "UNDO LOG": This kind of file holds UNDO records. By default, UNDO records are stored in the system tablespace, but these files can be added for greater concurrency. (NDB is using "UNDO LOG", "TABLESPACE", "DATAFILE".) TABLESPACE_NAME SQL name for the tablespace. For general tablespaces in WL#6205, use the name from SYS_TABLESPACES.NAME. For other tablespace files, this name will start with 'innodb_', such as 'innodb_system', 'innodb_undo' or 'innodb_file_per_table'. File-per-table tablespace files will look like 'innodb_file_per_table_##' where ## is the tablespace ID. This is different from SYS_TABLESPACES.NAME where the TABLE_NAME is used. TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME Leave empty. These are table-specific. LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER Leave empty. InnoDB has common log tablespaces covering all tablespaces. These fields do not seem to make sense for InnoDB. ENGINE 'InnoDB' FULLTEXT_KEYS, DELETED_ROWS, UPDATE_COUNT Leave empty. These are table-specific. FREE_EXTENTS The number of fully free extents in the current datafile. TOTAL_EXTENTS The total number of full extents used in the current datafile. Any partial extent at the end of the file is not counted. EXTENT_SIZE This should be 1048576 (1MB) for files with page size 4k, 8k and 16k, 2097152 bytes (2MB) for 32k page size and 4194304 (4MB) for 64k page size. Note that there is no column in INFORMATION_SCHEMA.FILES to report the page size. It can be retrieved from INNODB_SYS_TABLESPACES where FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID. INITIAL_SIZE The number of bytes that this file was initially created with. MAXIMUM_SIZE The maximum number of bytes allowed in the file. This is NULL for all datafiles except predefined system tablespaces, set by innodb_data_file_path and innodb_temp_data_file_path. A NULL means that the stafile is not explicitly limited in size. AUTOEXTEND_SIZE This will be the number defined in innodb_data_file_path and innodb_temp_data_file_path if it is defined. Else this number will be the extent size. CREATION_TIME Leave empty. This is not recorded by InnoDB. LAST_UPDATE_TIME, LAST_ACCESS_TIME Leave empty. These seem table-specific. RECOVER_TIME, TRANSACTION_COUNTER Leave empty. InnoDB has a global transaction counter; it is not file-specific. There is no internal field to keep track of last recovered LSN or time. VERSION Leave empty. InnoDB datafiles use a flag that indicated capabilities, not strictly file version. The has historically been a file version name (Antelope and Barracuda), but these are being deprecated and do not apply to general tablespaces. ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH Leave empty. These are table or index options, not tablespace attributes. DATA_FREE Report the total amount of free space in bytes for the whole tablespace which may include more than one datafile for predefined system tablespaces. CREATE_TIME, UPDATE_TIME, CHECK_TIME These are table-specific options. InnoDB does not maintain these at the tablespace level. CHECKSUM InnoDB does not keep file-level checksums, only page-level checksums. Leave empty; we want the access to be quick. STATUS This seems to be 'NORMAL' by default. InnoDB file-per-table tablespaces might also have the following status: 'IMPORTING' - The tablespace is not yet available. EXTRA Leave empty. We do not elect to report anything here.
Interface ========= InnoDB can mimic sql/ha_ndbcluster.cc to implement handlerton::fill_is_table: innobase_hton->fill_is_table = innobase_fill_i_s_table; The routine innobase_fill_i_s_table() will call i_s_files_table_fill() if the I_S.FILES table is specified. The routine i_s_files_table_fill() will collect file information from the file system cache (fil_system_t). So only tablespaces whose files are known to InnoDB will get reported. This will exclude any tablespaces whose files are missing or discarded. fil_node_next() =============== A routine called fil_node_next(fil_node_t* node) will be used to traverse all fil_node_t objects within each fil_space_t object in the fil_system->space_list. The first call to this function to retrieve the very first fil_node_t sends NULL for the node. After that, it is called with the last one processed. Since the fil_node_t has a pointer to its fil_space_t, it can either get the next node on the current space or the first node on the next space. Each call to fil_node_next() will get and hold the fil_system->mutex until it returns. While that mutex is not held, the current space and node cannot be deleted since fil_node_next() increments space->n_pending_ops and decrements it when it returns a node from the next space. i_s_files_table_fill ==================== For each file, data from a file_node_t and a file_space_t is reported to the I_S system much like the other reports in i_s.cc. The memory in fil_node_t and fil_space_t are accessed with no mutex protection since fil_node_next() has incremented n_pending_ops. No other mutex is needed since size_in_header, free_len, and free_limit have recently been put into the fil_space_t object. With this data, the available space can be calculated with the inner/lower portion of fsp_get_available_space_in_free_extents(). However, it is necessary to store two new fields in struct fil_node_t. ulint init_size; /*!< initial size of the file in database pages, defaults to FIL_IBD_FILE_INITIAL_SIZE. */ ulint max_size; /*!< maximum size of the file in database pages, This is zero if the maximum size is not set. */ These two new fields must be recorded when the fil_node_t is created. init_size is set to the 'size' sent to fil_node_create() and max_size is 0 by default but can be some maximum size for predefined ttablespaces that use multiple files. Only the last one can grow. The others are fixed at this max size. UNDO Log Files ============== These files will also be included in the report from InnoDB. REDO Log Files ============== These files will NOT be included in the report from InnoDB. System Temporary File ===================== This file, typically called ibtmp1, will be included.
Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.