28.3.15 The INFORMATION_SCHEMA FILES Table (original) (raw)

28.3.15 The INFORMATION_SCHEMA FILES Table

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

The FILES 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 to InnoDB, seeInnoDB Notes, later in this section; for additional information specific to NDB Cluster, seeNDB Notes.

The FILES table has these columns:

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 the FILES 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

InnoDB Notes

The following notes apply to InnoDB data files.

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

NDB Notes

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 do not release disk space.
The extent size can be set in a CREATE TABLESPACE statement. For more information, seeSection 15.1.21, “CREATE TABLESPACE Statement”.