MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The FILES
table provides information
about the files in which MySQL NDB
Disk Data tables are stored.
The FILES
table has these columns:
FILE_ID
A file identifier. FILE_ID
column values
are auto-generated.
FILE_NAME
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
One of the values UNDO LOG
,
DATAFILE
, or TABLESPACE
.
TABLESPACE_NAME
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
The name of the Disk Data table with which the file is associated, if any.
LOGFILE_GROUP_NAME
The name of the log file group to which the log file or data file belongs.
LOGFILE_GROUP_NUMBER
For an UNDO
log file, the auto-generated ID
number of the log file group to which the log file belongs.
ENGINE
For an NDB Cluster Disk Data log file or data file, this value
always NDB
or
NDBCLUSTER
.
FULLTEXT_KEYS
For an NDB Cluster Disk Data log file or data file, this value is always empty.
DELETED_ROWS
This value is always NULL
.
UPDATE_COUNT
This value is always NULL
.
FREE_EXTENTS
The number of extents which have not yet been used by the file.
TOTAL_EXTENTS
The total number of extents allocated to the file.
EXTENT_SIZE
The size of an extent for the file in bytes.
INITIAL_SIZE
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 NDB Cluster Disk Data files, this value is always the same
as the INITIAL_SIZE
value.
AUTOEXTEND_SIZE
For NDB Cluster Disk Data files, this value is always empty.
CREATION_TIME
The date and time when the file was created.
LAST_UPDATE_TIME
The date and time when the file was last modified.
LAST_ACCESS_TIME
The date and time when the file was last accessed by the server.
RECOVER_TIME
For NDB Cluster Disk Data files, this value is always
0
.
TRANSACTION_COUNTER
For NDB Cluster Disk Data files, this value is always
0
.
VERSION
For NDB Cluster Disk Data files, this value is always
NULL
.
ROW_FORMAT
For NDB Cluster Disk Data files, this value is always
NULL
.
TABLE_ROWS
For NDB Cluster Disk Data files, this value is always
NULL
.
AVG_ROW_LENGTH
For NDB Cluster Disk Data files, this value is always
NULL
.
DATA_LENGTH
For NDB Cluster Disk Data files, this value is always
NULL
.
MAX_DATA_LENGTH
For NDB Cluster Disk Data files, this value is always
NULL
.
INDEX_LENGTH
For NDB Cluster Disk Data files, this value is always
NULL
.
DATA_FREE
For NDB Cluster Disk Data files, this value is always
NULL
.
CREATE_TIME
For NDB Cluster Disk Data files, this value is always
NULL
.
UPDATE_TIME
For NDB Cluster Disk Data files, this value is always
NULL
.
CHECK_TIME
For NDB Cluster Disk Data files, this value is always
NULL
.
CHECKSUM
For NDB Cluster Disk Data files, this value is always
NULL
.
STATUS
For NDB Cluster Disk Data files, this value is always
NORMAL
.
EXTRA
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:
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 LOG | CLUSTER_NODE=5;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=6;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=7;UNDO_BUFFER_SIZE=8388608 | | mygroup | UNDO LOG | CLUSTER_NODE=8;UNDO_BUFFER_SIZE=8388608 | +--------------------+-----------+-----------------------------------------+
The FILES
table is a
nonstandard INFORMATION_SCHEMA
table.
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, 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 18.5.10, “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:
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 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. For more information, see
Section 13.1.18, “CREATE TABLESPACE Statement”.
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
LOG
, 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:
mysql>CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE = 16M
UNDO_BUFFER_SIZE = 1M
ENGINE = NDB;
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 | +--------------+---------+---------+------+----------+
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;
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 | +----------------+---------+---------+------+----------+
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 | +--------------+------------+
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:
mysql>CREATE TABLESPACE ts1
ADD DATAFILE 'data1.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 512M
ENGINE = NDB;
mysql>CREATE TABLE dd (
c1 INT NOT NULL PRIMARY KEY,
c2 INT,
c3 DATE
)
TABLESPACE ts1 STORAGE DISK
ENGINE = NDB;
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');
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 | +--------------+------------+
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 18.6.11, “NDB Cluster Disk Data Tables”.