The FILES
table provides information
about the files in which MySQL NDB
Disk Data tables are stored.
This table provides information about Disk Data
files only; you cannot use it for
determining disk space allocation or availability for individual
NDB
tables. However, beginning with MySQL
Cluster NDB 6.3.27 and MySQL Cluster NDB 7.0.8, 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 of
data on disk for that table, using ndb_desc.
For more information, see
Section 17.4.9, “ndb_desc — Describe NDB Tables”.
INFORMATION_SCHEMA
Name |
SHOW
Name |
Remarks |
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 |
Notes:
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
or DATAFILE
.
Beginning with MySQL Cluster NDB 6.2.19, MySQL Cluster NDB
6.3.32, MySQL Cluster NDB 7.0.13, and MySQL Cluster NDB 7.1.2,
this column can also have the value
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';
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 12.1.18, “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. It was added in MySQL 5.1.6.
Beginning with MySQL 5.1.14, 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 afterwards, 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)
Beginning with MySQL Cluster NDB 6.2.19, MySQL Cluster NDB
6.3.32, MySQL Cluster NDB 7.0.13, and MySQL Cluster NDB 7.1.2,
an additional row is present in the
FILES
table for any 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
.
For additional information, and examples of creating and dropping MySQL Cluster Disk Data objects, see Section 17.5.10, “MySQL Cluster Disk Data Tables”.
User Comments
Add your own comment.