Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.2Kb
Man Pages (Zip) - 305.4Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  INFORMATION_SCHEMA Tables  /  The INFORMATION_SCHEMA FILES Table

22.8 The INFORMATION_SCHEMA FILES Table

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

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

INFORMATION_SCHEMA NameSHOW NameRemarks
FILE_ID MySQL extension
FILE_NAME MySQL extension
FILE_TYPE MySQL extension
TABLESPACE_NAME MySQL extension
TABLE_CATALOG MySQL extension
TABLE_SCHEMA MySQL extension
TABLE_NAME MySQL extension
LOGFILE_GROUP_NAME MySQL extension
LOGFILE_GROUP_NUMBER MySQL extension
ENGINE MySQL extension
FULLTEXT_KEYS MySQL extension
DELETED_ROWS MySQL extension
UPDATE_COUNT MySQL extension
FREE_EXTENTS MySQL extension
TOTAL_EXTENTS MySQL extension
EXTENT_SIZE MySQL extension
INITIAL_SIZE MySQL extension
MAXIMUM_SIZE MySQL extension
AUTOEXTEND_SIZE MySQL extension
CREATION_TIME MySQL extension
LAST_UPDATE_TIME MySQL extension
LAST_ACCESS_TIME MySQL extension
RECOVER_TIME MySQL extension
TRANSACTION_COUNTER MySQL extension
VERSION MySQL extension
ROW_FORMAT MySQL extension
TABLE_ROWS MySQL extension
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH MySQL extension
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
CREATE_TIME MySQL extension
UPDATE_TIME MySQL extension
CHECK_TIME MySQL extension
CHECKSUM MySQL extension
STATUS MySQL extension
EXTRA MySQL extension

InnoDB Notes

The following notes apply to InnoDB data files. INFORMATION_SCHEMA.FILES fields that are not described below are not applicable to InnoDB and report a NULL value.

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

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

  • Undo tablespace data is reported by INFORMATION_SCHEMA.FILES when separate undo tablespaces are configured using the innodb_undo_tablespaces configuration option.

  • FILE_ID is the tablespace ID, also referred to as the space_id or fil_space_t::id.

  • FILE_NAME is the name of the data file. File-per-table and general tablespaces have a .ibd file name extension. Undo tablespaces are prefixed by undo. The system tablespace is prefixed by ibdata. Temporary table tablespaces are prefixed by ibtmp. The file name includes the file path, which may be relative to the MySQL data directory (datadir).

  • FILE_TYPE is 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 table tablespaces. UNDO LOG is the file type for undo log tablespaces, which hold undo records. By default, undo records are stored in the system tablespace. Separate undo log tablespaces can be added using the innodb_undo_tablespaces option.

  • TABLESPACE_NAME is 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.

  • ENGINE is the storage engine. For InnoDB files, the value is always InnoDB.

  • FREE_EXTENTS is the number of fully free extents in the current data file.

  • TOTAL_EXTENTS is the number of full extents used in the current data file. Any partial extent at the end of the file is not counted.

  • EXTENT_SIZE is 1048576 (1MB) for files with a 4k, 8k, or 16k page size. Extent size is 2097152 bytes (2MB) for files with a 32k page size, and 4194304 (4MB) for files with a 64k page size. INFORMATION_SCHEMA.FILES does not report InnoDB page size. Page size is defined by the innodb_page_size option. Extent size information can also be retrieved from INNODB_SYS_TABLESPACES where FILES.FILE_ID = INNODB_SYS_TABLESPACES.SPACE_ID.

  • INITIAL_SIZE is the initial size of the file, in bytes.

  • MAXIMUM_SIZE is the maximum number of bytes allowed 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 temporary table 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.

  • AUTOEXTEND_SIZE is the auto-extend size defined by innodb_data_file_path for the system tablespace, or defined by innodb_temp_data_file_path for temporary table tablespaces.

  • DATA_FREE is 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.

  • STATUS is NORMAL by default. InnoDB file-per-table tablespaces may report IMPORTING, which indicates that the tablespace is not yet available.

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

    mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, TOTAL_EXTENTS, 
    EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE 
    FROM INFORMATION_SCHEMA.FILES \G

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 19.4.10, “ndb_desc — Describe NDB Tables”.

  • FILE_ID column values are auto-generated.

  • FILE_NAME is 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 is one of the values UNDOFILE, DATAFILE, or TABLESPACE.

  • TABLESPACE_NAME is the name of the tablespace with which the file is associated.

  • Currently, the value of the TABLESPACE_CATALOG column is always NULL.

  • TABLE_NAME is the name of the Disk Data table with which the file is associated, if any.

  • The LOGFILE_GROUP_NAME column gives the name of the log file group to which the log file or data file belongs.

  • For an UNDO log file, the LOGFILE_GROUP_NUMBER contains the auto-generated ID number of the log file group to which the log file belongs.

  • For a MySQL Cluster Disk Data log file or data file, the value of the ENGINE column is always NDB or NDBCLUSTER.

  • For a MySQL Cluster Disk Data log file or data file, the value of the FULLTEXT_KEYS column is always empty.

  • The FREE EXTENTS column displays the number of extents which have not yet been used by the file. The TOTAL EXTENTS column show the total number of extents allocated to the file.

    The difference between these two columns is the number of extents currently in use by the file:

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

    You can approximate the amount of disk space in use by the file by multiplying this difference by the value of the EXTENT_SIZE column, which gives the size of an extent for the file in bytes:

    SELECT (TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE AS bytes_used
        FROM INFORMATION_SCHEMA.FILES
        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:

    SELECT FREE_EXTENTS * EXTENT_SIZE AS bytes_free
        FROM INFORMATION_SCHEMA.FILES
        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. See Section 14.1.19, “CREATE TABLESPACE Syntax”, for more information.

  • The INITIAL_SIZE column shows the size in bytes of the file. 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.

    For MySQL Cluster Disk Data files, the value of the MAXIMUM_SIZE column is always the same as INITIAL_SIZE, and the AUTOEXTEND_SIZE column is always empty.

  • The CREATION_TIME column shows the date and time when the file was created. The LAST_UPDATE_TIME column displays the date and time when the file was last modified. The LAST_ACCESSED column provides the date and time when the file was last accessed by the server.

    Currently, the values of these columns 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.

  • For MySQL Cluster Disk Data files, the value of the RECOVER_TIME and TRANSACTION_COUNTER columns is always 0.

  • For MySQL Cluster Disk Data files, the following columns are always NULL:

    • VERSION

    • ROW_FORMAT

    • TABLE_ROWS

    • AVG_ROW_LENGTH

    • DATA_LENGTH

    • MAX_DATA_LENGTH

    • INDEX_LENGTH

    • DATA_FREE

    • CREATE_TIME

    • UPDATE_TIME

    • CHECK_TIME

    • CHECKSUM

  • For MySQL Cluster Disk Data files, the value of the STATUS column is always NORMAL.

  • For MySQL Cluster Disk Data files, the EXTRA column shows which data node the file belongs to, as each data node has its own copy of the file. Suppose that you use this statement on a MySQL Cluster with four data nodes:

    CREATE LOGFILE GROUP mygroup
        ADD UNDOFILE 'new_undo.dat'
        INITIAL_SIZE 2G
        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:

    mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA
        ->     FROM INFORMATION_SCHEMA.FILES
        ->     WHERE FILE_NAME = 'new_undo.dat';
    +--------------------+-------------+----------------+
    | LOGFILE_GROUP_NAME | FILE_TYPE   | EXTRA          |
    +--------------------+-------------+----------------+
    | mygroup            | UNDO FILE   | CLUSTER_NODE=3 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=4 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=5 |
    | mygroup            | UNDO FILE   | CLUSTER_NODE=6 |
    +--------------------+-------------+----------------+
    4 rows in set (0.01 sec)
    
  • The FILES table is a nonstandard table.

  • 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. Currently, 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 MySQL Cluster, and you create one using the following statement:

    mysql> CREATE LOGFILE GROUP lg1
        ->   ADD UNDOFILE 'undofile.dat'
        ->   INITIAL_SIZE = 16M
        ->   UNDO_BUFFER_SIZE = 1M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (3.81 sec)
    

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

    mysql> SELECT DISTINCT
        ->   FILE_NAME AS File,
        ->   FREE_EXTENTS AS Free,
        ->   TOTAL_EXTENTS AS Total,
        ->   EXTENT_SIZE AS Size,
        ->   INITIAL_SIZE AS Initial
        ->   FROM INFORMATION_SCHEMA.FILES;
    +--------------+---------+---------+------+----------+
    | File         | Free    | Total   | Size | Initial  |
    +--------------+---------+---------+------+----------+
    | undofile.dat |    NULL | 4194304 |    4 | 16777216 |
    | NULL         | 4184068 |    NULL |    4 |     NULL |
    +--------------+---------+---------+------+----------+
    2 rows in set (0.01 sec)
    

    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:

    mysql> ALTER LOGFILE GROUP lg1
        ->   ADD UNDOFILE 'undofile02.dat'
        ->   INITIAL_SIZE = 4M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (1.02 sec)
    
    mysql> SELECT DISTINCT
        ->   FILE_NAME AS File,
        ->   FREE_EXTENTS AS Free,
        ->   TOTAL_EXTENTS AS Total,
        ->   EXTENT_SIZE AS Size,
        ->   INITIAL_SIZE AS Initial
        ->   FROM INFORMATION_SCHEMA.FILES;
    +----------------+---------+---------+------+----------+
    | File           | Free    | Total   | Size | Initial  |
    +----------------+---------+---------+------+----------+
    | undofile.dat   |    NULL | 4194304 |    4 | 16777216 |
    | undofile02.dat |    NULL | 1048576 |    4 |  4194304 |
    | NULL           | 5223944 |    NULL |    4 |     NULL |
    +----------------+---------+---------+------+----------+
    3 rows in set (0.01 sec)
    

    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:

    mysql> SELECT
        ->   FREE_EXTENTS AS 'Free Extents',
        ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
        ->   FROM INFORMATION_SCHEMA.FILES
        ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
        ->   AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5223944 |   20895776 |
    +--------------+------------+
    1 row in set (0.02 sec)
    

    If you create a MySQL 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:

    mysql> CREATE TABLESPACE ts1
        ->   ADD DATAFILE 'data1.dat'
        ->   USE LOGFILE GROUP lg1
        ->   INITIAL_SIZE 512M
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (8.71 sec)
    
    mysql> CREATE TABLE dd (
        ->   c1 INT NOT NULL PRIMARY KEY,
        ->   c2 INT,
        ->   c3 DATE
        ->   )
        ->   TABLESPACE ts1 STORAGE DISK
        ->   ENGINE = NDB;
    Query OK, 0 rows affected (2.11 sec)
    
    mysql> INSERT INTO dd VALUES
        ->   (NULL, 1234567890, '2007-02-02'),
        ->   (NULL, 1126789005, '2007-02-03'),
        ->   (NULL, 1357924680, '2007-02-04'),
        ->   (NULL, 1642097531, '2007-02-05');
    Query OK, 4 rows affected (0.01 sec)
    
    mysql> SELECT
        ->   FREE_EXTENTS AS 'Free Extents',
        ->   FREE_EXTENTS * EXTENT_SIZE AS 'Free Bytes'
        ->   FROM INFORMATION_SCHEMA.FILES
        ->   WHERE LOGFILE_GROUP_NAME = 'lg1'
        ->   AND FILE_NAME IS NULL;
    +--------------+------------+
    | Free Extents | Free Bytes |
    +--------------+------------+
    |      5207565 |   20830260 |
    +--------------+------------+
    1 row in set (0.01 sec)
    
  • An additional row is present in the FILES table for any MySQL 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. Currently, the value of the ENGINE column is always NDBCLUSTER.

  • There are no SHOW statements associated with the FILES table.

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


User Comments
Sign Up Login You must be logged in to post a comment.