Movatterモバイル変換

MySQL 9.4 Reference Manual

28.3.15 The INFORMATION_SCHEMA FILES Table

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

TheFILES table provides information aboutInnoDB 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 toInnoDB, seeInnoDB Notes, later in this section; for additional information specific to NDB Cluster, seeNDB Notes.

TheFILES table has these columns:

FILE_ID
ForInnoDB: The tablespace ID, also referred to as thespace_id orfil_space_t::id.
ForNDB: A file identifier.FILE_ID column values are auto-generated.
FILE_NAME
ForInnoDB: The name of the data file. File-per-table and general tablespaces have an.ibd file name extension. Undo tablespaces are prefixed byundo. The system tablespace is prefixed byibdata. The global temporary tablespace is prefixed byibtmp. The file name includes the file path, which may be relative to the MySQL data directory (the value of thedatadir system variable).
ForNDB: The name of an undo log file created byCREATE LOGFILE GROUP orALTER LOGFILE GROUP, or of a data file created byCREATE TABLESPACE orALTER TABLESPACE. In NDB 9.4, the file name is shown with a relative path; for an undo log file, this path is relative to the directoryDataDir/ndb_NodeId_fs/LG; for a data file, it is relative to the directoryDataDir/ndb_NodeId_fs/TS. This means, for example, that the name of a data file created withALTER TABLESPACE ts ADD DATAFILE 'data_2.dat' INITIAL SIZE 256M is shown as./data_2.dat.
FILE_TYPE
ForInnoDB: The tablespace file type. There are three possible file types forInnoDB 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.
ForNDB: One of the valuesUNDO LOG orDATAFILE.
TABLESPACE_NAME
The name of the tablespace with which the file is associated.
ForInnoDB: General tablespace names are as specified when created. File-per-table tablespace names are shown in the following format:schema_name/table_name. TheInnoDB system tablespace name isinnodb_system. The global temporary tablespace name isinnodb_temporary. Default undo tablespace names areinnodb_undo_001 andinnodb_undo_002. User-created undo tablespace names are as specified when created.
TABLE_CATALOG
This value is always empty.
TABLE_SCHEMA
This is alwaysNULL.
TABLE_NAME
This is alwaysNULL.
LOGFILE_GROUP_NAME
ForInnoDB: This is alwaysNULL.
ForNDB: The name of the log file group to which the log file or data file belongs.
LOGFILE_GROUP_NUMBER
ForInnoDB: This is alwaysNULL.
ForNDB: For a Disk Data undo log file, the auto-generated ID number of the log file group to which the log file belongs. This is the same as the value shown for theid column in thendbinfo.dict_obj_info table and thelog_id column in thendbinfo.logspaces andndbinfo.logspaces tables for this undo log file.
ENGINE
ForInnoDB: This value is alwaysInnoDB.
ForNDB: This value is alwaysndbcluster.
FULLTEXT_KEYS
This is alwaysNULL.
DELETED_ROWS
This is alwaysNULL.
UPDATE_COUNT
This is alwaysNULL.
FREE_EXTENTS
ForInnoDB: The number of fully free extents in the current data file.
ForNDB: The number of extents which have not yet been used by the file.
TOTAL_EXTENTS
ForInnoDB: The number of full extents used in the current data file. Any partial extent at the end of the file is not counted.
ForNDB: The total number of extents allocated to the file.
EXTENT_SIZE
ForInnoDB: 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 reportInnoDB page size. Page size is defined by theinnodb_page_size system variable. Extent size information can also be retrieved from theINNODB_TABLESPACES table whereFILES.FILE_ID = INNODB_TABLESPACES.SPACE.
ForNDB: The size of an extent for the file in bytes.
INITIAL_SIZE
ForInnoDB: The initial size of the file in bytes.
ForNDB: The size of the file in bytes. This is the same value that was used in theINITIAL_SIZE clause of theCREATE LOGFILE GROUP,ALTER LOGFILE GROUP,CREATE TABLESPACE, orALTER TABLESPACE statement used to create the file.
MAXIMUM_SIZE
ForInnoDB: The maximum number of bytes permitted in the file. The value isNULL for all data files except for predefined system tablespace data files. Maximum system tablespace file size is defined byinnodb_data_file_path. Maximum global temporary tablespace file size is defined byinnodb_temp_data_file_path. ANULL value for a predefined system tablespace data file indicates that a file size limit was not defined explicitly.
ForNDB: This value is always the same as theINITIAL_SIZE value.
AUTOEXTEND_SIZE
The auto-extend size of the tablespace. ForNDB,AUTOEXTEND_SIZE is alwaysNULL.
CREATION_TIME
This is alwaysNULL.
LAST_UPDATE_TIME
This is alwaysNULL.
LAST_ACCESS_TIME
This is alwaysNULL.
RECOVER_TIME
This is alwaysNULL.
TRANSACTION_COUNTER
This is alwaysNULL.
VERSION
ForInnoDB: This is alwaysNULL.
ForNDB: The version number of the file.
ROW_FORMAT
ForInnoDB: This is alwaysNULL.
ForNDB: One ofFIXED orDYNAMIC.
TABLE_ROWS
This is alwaysNULL.
AVG_ROW_LENGTH
This is alwaysNULL.
DATA_LENGTH
This is alwaysNULL.
MAX_DATA_LENGTH
This is alwaysNULL.
INDEX_LENGTH
This is alwaysNULL.
DATA_FREE
ForInnoDB: 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.
ForNDB: This is alwaysNULL.
CREATE_TIME
This is alwaysNULL.
UPDATE_TIME
This is alwaysNULL.
CHECK_TIME
This is alwaysNULL.
CHECKSUM
This is alwaysNULL.
STATUS
ForInnoDB: This value isNORMAL by default.InnoDB file-per-table tablespaces may reportIMPORTING, which indicates that the tablespace is not yet available.
ForNDB: For NDB Cluster Disk Data files, this value is alwaysNORMAL.

EXTRA

ForInnoDB: This is alwaysNULL.

ForNDB: For undo log files, this column shows the undo log buffer size; for data files, it is alwaysNULL. A more detailed explanation is provided in the next few paragraphs.

NDB stores a copy of each data file and each undo log file on each data node in the cluster. TheFILES table contains one row for each such file. Suppose that you run the following two statements on an NDB Cluster with four data nodes:

CREATE LOGFILE GROUP mygroup    ADD UNDOFILE 'new_undo.dat'    INITIAL_SIZE 2G    ENGINE NDBCLUSTER;CREATE TABLESPACE myts    ADD DATAFILE 'data_1.dat'    USE LOGFILE GROUP mygroup    INITIAL_SIZE 256M    ENGINE NDBCLUSTER;

After running these two statements successfully, you should see a result similar to the one shown here for this query against theFILES table:

mysql> SELECT LOGFILE_GROUP_NAME, FILE_TYPE, EXTRA    ->     FROM INFORMATION_SCHEMA.FILES    ->     WHERE ENGINE = 'ndbcluster';+--------------------+-----------+--------------------------+| LOGFILE_GROUP_NAME | FILE_TYPE | EXTRA                    |+--------------------+-----------+--------------------------+| mygroup            | UNDO LOG  | UNDO_BUFFER_SIZE=8388608 || mygroup            | DATAFILE  | NULL                     |+--------------------+-----------+--------------------------+

Notes

FILES is a nonstandardINFORMATION_SCHEMA table.
You must have thePROCESS privilege to query this table.

InnoDB Notes

The following notes apply toInnoDB data files.

Information reported byFILES is obtained from theInnoDB in-memory cache for open files, whereasINNODB_DATAFILES gets its data from theInnoDBSYS_DATAFILES internal data dictionary table.
The information provided byFILES includes global temporary tablespace information which is not available in theInnoDBSYS_DATAFILES internal data dictionary table, and is therefore not included inINNODB_DATAFILES.
Undo tablespace information is shown inFILES when separate undo tablespaces are present, as they are by default in MySQL 9.4.

The following query returns allFILES table information relating toInnoDB tablespaces.

SELECT  FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,  TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE,  AUTOEXTEND_SIZE, DATA_FREE, STATUSFROM INFORMATION_SCHEMA.FILESWHERE ENGINE='InnoDB'\G

NDB Notes

TheFILES table provides information about Disk Datafiles only; you cannot use it for determining disk space allocation or availability for individualNDB tables. However, it is possible to see how much space is allocated for eachNDB table having data stored on disk—as well as how much remains available for storage of data on disk for that table—usingndb_desc.
Much of the information contained in theFILES table can also be found in thendbinfofiles table.
TheCREATION_TIME,LAST_UPDATE_TIME, andLAST_ACCESSED values are as reported by the operating system, and are not supplied by theNDB storage engine. Where no value is provided by the operating system, these columns displayNULL.
The difference between theTOTAL EXTENTS andFREE_EXTENTS 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';
```
To approximate the amount of disk space in use by the file, multiply that difference by the value of theEXTENT_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 multiplyingFREE_EXTENTS byEXTENT_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 ofEXTENT_SIZE. That is, the largerEXTENT_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 donot release disk space.
The extent size can be set in aCREATE TABLESPACE statement. For more information, seeSection 15.1.25, “CREATE TABLESPACE Statement”.
You can obtain information about Disk Data tablespaces using thendb_desc utility. For more information, seeSection 25.6.11.1, “NDB Cluster Disk Data Objects”, as well as the description ofndb_desc.
For additional information, and examples of creating, dropping, and obtaining information about NDB Cluster Disk Data objects, seeSection 25.6.11, “NDB Cluster Disk Data Tables”.

PREV HOME UP NEXT