WL#7943: InnoDB: Implement Information_Schema.Files

Status: Complete   —   Priority: Medium

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.


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 

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.

   SYS_TABLESPACES.ID: (also refered to as space_id, or fil_space_t::id)

   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

   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.


   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
   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.

   Leave empty. These are table-specific.

   Leave empty. InnoDB has common log tablespaces covering all tablespaces.
   These fields do not seem to make sense for InnoDB.


   Leave empty. These are table-specific.

   The number of fully free extents in the current datafile.

   The total number of full extents used in the current datafile. Any partial
extent at the end of the file is not counted.

   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 =

   The number of bytes that this file was initially created with.

   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.

   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. 

   Leave empty. This is not recorded by InnoDB.

   Leave empty. These seem table-specific.

   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.

   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. 

   Leave empty. These are table or index options, not tablespace attributes.

   Report the total amount of free space in bytes for the whole tablespace which
may include more than one datafile for predefined system tablespaces.

   These are table-specific options.  InnoDB does not maintain these at the
tablespace level.

   InnoDB does not keep file-level checksums, only page-level checksums.
   Leave empty; we want the access to be quick.

   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.

   Leave empty. We do not elect to report anything here. 
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.

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.

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.