Rechercher dans le manuel MySQL

25.11 The INFORMATION_SCHEMA FILES Table

Contents

The FILES table provides information about the files in which MySQL tablespace data is stored.

The FILES table provides information about InnoDB data files. In NDB Cluster, this table also provides information about the files in which NDB Cluster Disk Data tables are stored. For additional information specific to InnoDB, see InnoDB Notes, later in this section; for additional information specific to NDB Cluster, see NDB Notes.

The FILES table has these columns:

  • FILE_ID

    For InnoDB: The tablespace ID, also referred to as the space_id or fil_space_t::id.

    For NDB: A file identifier. FILE_ID column values are auto-generated.

  • FILE_NAME

    For InnoDB: The name of the data file. File-per-table and general tablespaces have an .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. The global temporary tablespace is prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (the value of the datadir system variable).

    For NDB: The name of an UNDO log file created by CREATE LOGFILE GROUP or ALTER LOGFILE GROUP, or of a data file created by CREATE TABLESPACE or ALTER TABLESPACE.

  • FILE_TYPE

    For InnoDB: The tablespace file type. There are three possible file types for InnoDB files. TABLESPACE is the file type for any system, general, or file-per-table tablespace file that holds tables, indexes, or other forms of user data. TEMPORARY is the file type for temporary tablespaces. UNDO LOG is the file type for undo tablespaces, which hold undo records.

    For NDB: One of the values UNDO LOG, DATAFILE, or TABLESPACE.

  • TABLESPACE_NAME

    For InnoDB: The SQL name for the tablespace. A general tablespace name is the SYS_TABLESPACES.NAME value. For other tablespace files, names start with innodb_, such as innodb_system, innodb_undo, and innodb_file_per_table. The file-per-table tablespace name format is innodb_file_per_table_##, where ## is the tablespace ID.

    For NDB: The name of the tablespace with which the file is associated.

  • TABLE_CATALOG

    This value is always empty.

  • TABLE_SCHEMA

    This value is always NULL.

  • TABLE_NAME

    For InnoDB: This value is always NULL.

    For NDB: The name of the Disk Data table with which the file is associated, if any.

  • LOGFILE_GROUP_NAME

    For InnoDB: This value is always NULL.

    For NDB: The name of the log file group to which the log file or data file belongs.

  • LOGFILE_GROUP_NUMBER

    For InnoDB: This value is always NULL.

    For NDB: For an UNDO log file, the auto-generated ID number of the log file group to which the log file belongs.

  • ENGINE

    For InnoDB: This value is always InnoDB.

    For NDB: For an NDB Cluster Disk Data log file or data file, this value is always NDB or NDBCLUSTER.

  • FULLTEXT_KEYS

    For InnoDB: This value is always NULL.

    For NDB: For an NDB Cluster Disk Data log file or data file, this value is always empty.

  • DELETED_ROWS

    For InnoDB: This value is always NULL.

  • UPDATE_COUNT

    For InnoDB: This value is always NULL.

  • FREE_EXTENTS

    For InnoDB: The number of fully free extents in the current data file.

    For NDB: The number of extents which have not yet been used by the file.

  • TOTAL_EXTENTS

    For InnoDB: The number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

    For NDB: The total number of extents allocated to the file.

  • EXTENT_SIZE

    For InnoDB: Extent size is 1048576 (1MB) for files with a 4KB, 8KB, or 16KB page size. Extent size is 2097152 bytes (2MB) for files with a 32KB page size, and 4194304 (4MB) for files with a 64KB page size. FILES does not report InnoDB page size. Page size is defined by the innodb_page_size system variable. Extent size information can also be retrieved from the INNODB_TABLESPACES table where FILES.FILE_ID = INNODB_TABLESPACES.SPACE.

    For NDB: The size of an extent for the file in bytes.

  • INITIAL_SIZE

    For InnoDB: The initial size of the file in bytes.

    For NDB: The size of the file in bytes. This is the same value that was used in the INITIAL_SIZE clause of the CREATE LOGFILE GROUP, ALTER LOGFILE GROUP, CREATE TABLESPACE, or ALTER TABLESPACE statement used to create the file.

  • MAXIMUM_SIZE

    For InnoDB: The maximum number of bytes permitted in the file. The value is NULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined by innodb_data_file_path. Maximum global temporary tablespace file size is defined by innodb_temp_data_file_path. A NULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.

    For NDB: For NDB Cluster Disk Data files, this value is always the same as the INITIAL_SIZE value.

  • AUTOEXTEND_SIZE

    For InnoDB: AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or by innodb_temp_data_file_path for the global temporary tablespace.

    For NDB: For NDB Cluster Disk Data files, this value is always empty.

  • CREATION_TIME

    For InnoDB: This value is always NULL.

    For NDB: The date and time when the file was created.

  • LAST_UPDATE_TIME

    For InnoDB: This value is always NULL.

    For NDB: The date and time when the file was last modified.

  • LAST_ACCESS_TIME

    For InnoDB: This value is always NULL.

    For NDB: The date and time when the file was last accessed by the server.

  • RECOVER_TIME

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always 0.

  • TRANSACTION_COUNTER

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always 0.

  • VERSION

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • ROW_FORMAT

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • TABLE_ROWS

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • AVG_ROW_LENGTH

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • DATA_LENGTH

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • MAX_DATA_LENGTH

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • INDEX_LENGTH

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • DATA_FREE

    For InnoDB: The total amount of free space (in bytes) for the entire tablespace. Predefined system tablespaces, which include the system tablespace and temporary table tablespaces, may have one or more data files.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • CREATE_TIME

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • UPDATE_TIME

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • CHECK_TIME

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • CHECKSUM

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, this value is always NULL.

  • STATUS

    For InnoDB: This value is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

    For NDB: For NDB Cluster Disk Data files, this value is always NORMAL.

  • EXTRA

    For InnoDB: This value is always NULL.

    For NDB: For NDB Cluster Disk Data files, the EXTRA column shows which data node the file belongs to (each data node having its own copy), as well as the size of its undo buffer. Suppose that you use this statement on an NDB Cluster with four data nodes:

    1. CREATE LOGFILE GROUP mygroup
    2.     ADD UNDOFILE 'new_undo.dat'
    3.     INITIAL_SIZE 2G
    4.     ENGINE NDB;

    After running the CREATE LOGFILE GROUP statement successfully, you should see a result similar to the one shown here for this query against the FILES table:

    1. mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
    2.          FROM INFORMATION_SCHEMA.FILES
    3.          WHERE FILE_NAME = 'new_undo.dat';
    4.  
    5. +--------------------+-----------+-----------------------------------------+
    6. | LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA                                   |
    7. +--------------------+-----------+-----------------------------------------+
    8. | mygroup            | UNDO LOG  | CLUSTER_NODE=5;UNDO_BUFFER_SIZE=8388608 |
    9. | mygroup            | UNDO LOG  | CLUSTER_NODE=6;UNDO_BUFFER_SIZE=8388608 |
    10. | mygroup            | UNDO LOG  | CLUSTER_NODE=7;UNDO_BUFFER_SIZE=8388608 |
    11. | mygroup            | UNDO LOG  | CLUSTER_NODE=8;UNDO_BUFFER_SIZE=8388608 |
    12. +--------------------+-----------+-----------------------------------------+

    This information was not included in NDB Cluster 8.0 prior to NDB 8.0.15. (Bug #92796, Bug #28800252)

Notes

  • The FILES table is a nonstandard INFORMATION_SCHEMA table.

Contents Haut

InnoDB Notes

The following notes apply to InnoDB data files.

  • Data reported by FILES is reported from the InnoDB in-memory cache for open files. By comparison, INNODB_DATAFILES reports data from the InnoDB SYS_DATAFILES internal data dictionary table.

  • The data reported by FILES includes global temporary tablespace data. This data is not available in the InnoDB SYS_DATAFILES internal data dictionary table, and is therefore not reported by INNODB_DATAFILES.

  • Undo tablespace data is reported by FILES when separate undo tablespaces are present, which they are by default in MySQL 8.0

  • The following query returns all data pertinent to InnoDB tablespaces.

    1. SELECT
    2.   FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
    3.   TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,
    4.   AUTOEXTEND_SIZE, DATA_FREE, STATUS
    5. FROM INFORMATION_SCHEMA.FILES WHERE ENGINE='InnoDB'\G

Contents Haut

NDB Notes

  • The FILES table provides information about Disk Data files only; you cannot use it for determining disk space allocation or availability for individual NDB tables. However, it is possible to see how much space is allocated for each NDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—using ndb_desc. For more information, see Section 22.4.9, “ndb_desc — Describe NDB Tables”.

  • The CREATION_TIME, LAST_UPDATE_TIME, and LAST_ACCESSED values are as reported by the operating system, and are not supplied by the NDB storage engine. Where no value is provided by the operating system, these columns display 0000-00-00 00:00:00.

  • The difference between the TOTAL EXTENTS and FREE_EXTENTS columns is the number of extents currently in use by the file:

    1. SELECT TOTAL_EXTENTS - FREE_EXTENTS AS extents_used
    2.     FROM INFORMATION_SCHEMA.FILES
    3.     WHERE FILE_NAME = 'myfile.dat';

    To approximate the amount of disk space in use by the file, multiply that difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

    1. SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
    2.     FROM INFORMATION_SCHEMA.FILES
    3.     WHERE FILE_NAME = 'myfile.dat';

    Similarly, you can estimate the amount of space that remains available in a given file by multiplying FREE_EXTENTS by EXTENT_SIZE:

    1. SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
    2.     FROM INFORMATION_SCHEMA.FILES
    3.     WHERE FILE_NAME = 'myfile.dat';
    Important

    The byte values produced by the preceding queries are approximations only, and their precision is inversely proportional to the value of EXTENT_SIZE. That is, the larger EXTENT_SIZE becomes, the less accurate the approximations are.

    It is also important to remember that once an extent is used, it cannot be freed again without dropping the data file of which it is a part. This means that deletes from a Disk Data table do not release disk space.

    The extent size can be set in a CREATE TABLESPACE statement. For more information, see Section 13.1.21, “CREATE TABLESPACE Syntax”.

  • An additional row is present in the FILES table following the creation of a logfile group. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always UNDO FILE, and that of the STATUS column is always NORMAL. The value of the ENGINE column is always NDBCLUSTER.

    The FREE_EXTENTS column in this row shows the total number of free extents available to all undo files belonging to a given log file group whose name and number are shown in the LOGFILE_GROUP_NAME and LOGFILE_GROUP_NUMBER columns, respectively.

    Suppose there are no existing log file groups on your NDB Cluster, and you create one using the following statement:

    1. mysql> CREATE LOGFILE GROUP lg1
    2.          ADD UNDOFILE 'undofile.dat'
    3.          INITIAL_SIZE = 16M
    4.          UNDO_BUFFER_SIZE = 1M
    5.          ENGINE = NDB;

    You can now see this NULL row when you query the FILES table:

    1. mysql> SELECT DISTINCT
    2.          FILE_NAME AS File,
    3.          FREE_EXTENTS AS Free,
    4.          TOTAL_EXTENTS AS Total,
    5.          EXTENT_SIZE AS Size,
    6.          INITIAL_SIZE AS Initial
    7.          FROM INFORMATION_SCHEMA.FILES;
    8. +--------------+---------+---------+------+----------+
    9. | File         | Free    | Total   | Size | Initial  |
    10. +--------------+---------+---------+------+----------+
    11. | undofile.dat |    NULL | 4194304 |    4 | 16777216 |
    12. | NULL         | 4184068 |    NULL |    4 |     NULL |
    13. +--------------+---------+---------+------+----------+

    The total number of free extents available for undo logging is always somewhat less than the sum of the TOTAL_EXTENTS column values for all undo files in the log file group due to overhead required for maintaining the undo files. This can be seen by adding a second undo file to the log file group, then repeating the previous query against the FILES table:

    1. mysql> ALTER LOGFILE GROUP lg1
    2.          ADD UNDOFILE 'undofile02.dat'
    3.          INITIAL_SIZE = 4M
    4.          ENGINE = NDB;
    5.  
    6. mysql> SELECT DISTINCT
    7.          FILE_NAME AS File,
    8.          FREE_EXTENTS AS Free,
    9.          TOTAL_EXTENTS AS Total,
    10.          EXTENT_SIZE AS Size,
    11.          INITIAL_SIZE AS Initial
    12.          FROM INFORMATION_SCHEMA.FILES;
    13. +----------------+---------+---------+------+----------+
    14. | File           | Free    | Total   | Size | Initial  |
    15. +----------------+---------+---------+------+----------+
    16. | undofile.dat   |    NULL | 4194304 |    4 | 16777216 |
    17. | undofile02.dat |    NULL | 1048576 |    4 |  4194304 |
    18. | NULL           | 5223944 |    NULL |    4 |     NULL |
    19. +----------------+---------+---------+------+----------+

    The amount of free space in bytes which is available for undo logging by Disk Data tables using this log file group can be approximated by multiplying the number of free extents by the initial size:

    1. mysql> SELECT
    2.          FREE_EXTENTS AS 'Free Extents',
    3.          FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
    4.          FROM INFORMATION_SCHEMA.FILES
    5.          WHERE LOGFILE_GROUP_NAME = 'lg1'
    6.          AND FILE_NAME IS NULL;
    7. +--------------+------------+
    8. | Free Extents | Free Bytes |
    9. +--------------+------------+
    10. |      5223944 |   20895776 |
    11. +--------------+------------+

    If you create an NDB Cluster Disk Data table and then insert some rows into it, you can see approximately how much space remains for undo logging afterward, for example:

    1. mysql> CREATE TABLESPACE ts1
    2.          ADD DATAFILE 'data1.dat'
    3.          USE LOGFILE GROUP lg1
    4.          INITIAL_SIZE 512M
    5.          ENGINE = NDB;
    6.  
    7. mysql> CREATE TABLE dd (
    8.          c1 INT NOT NULL PRIMARY KEY,
    9.          c2 INT,
    10.          c3 DATE
    11.          )
    12.          TABLESPACE ts1 STORAGE DISK
    13.          ENGINE = NDB;
    14.  
    15. mysql> INSERT INTO dd VALUES
    16.          (NULL, 1234567890, '2007-02-02'),
    17.          (NULL, 1126789005, '2007-02-03'),
    18.          (NULL, 1357924680, '2007-02-04'),
    19.          (NULL, 1642097531, '2007-02-05');
    20.  
    21. mysql> SELECT
    22.          FREE_EXTENTS AS 'Free Extents',
    23.          FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
    24.          FROM INFORMATION_SCHEMA.FILES
    25.          WHERE LOGFILE_GROUP_NAME = 'lg1'
    26.          AND FILE_NAME IS NULL;
    27. +--------------+------------+
    28. | Free Extents | Free Bytes |
    29. +--------------+------------+
    30. |      5207565 |   20830260 |
    31. +--------------+------------+

  • An additional row is present in the FILES table for any NDB Cluster tablespace, whether or not any data files are associated with the tablespace. This row has NULL for the value of the FILE_NAME column. For this row, the value of the FILE_ID column is always 0, that of the FILE_TYPE column is always TABLESPACE, and that of the STATUS column is always NORMAL. The value of the ENGINE column is always NDBCLUSTER.

  • For additional information, and examples of creating and dropping NDB Cluster Disk Data objects, see Section 22.5.13, “NDB Cluster Disk Data Tables”.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-files-table.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut