tablespace_nameADD DATAFILE '
file_name' USE LOGFILE GROUP
extent_size] [INITIAL_SIZE [=]
initial_size] [AUTOEXTEND_SIZE [=]
autoextend_size] [MAX_SIZE [=]
max_size] [NODEGROUP [=]
nodegroup_id] [WAIT] [COMMENT [=]
comment_text] ENGINE [=]
This statement is used to create a tablespace, which can contain one or more data files, providing storage space for tables. One data file is created and added to the tablespace using this statement. Additional data files may be added to the tablespace by using the
ALTER TABLESPACEstatement (see Section 12.1.8, “
ALTER TABLESPACESyntax”). For rules covering the naming of tablespaces, see Section 8.2, “Schema Object Names”.
All MySQL Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name.
Prior to MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3, path and file names for data files could not be longer than 128 characters. (Bug#31770)
A log file group of one or more
UNDOlog files must be assigned to the tablespace to be created with the
USE LOGFILE GROUPclause.
logfile_groupmust be an existing log file group created with
CREATE LOGFILE GROUP(see Section 12.1.14, “
CREATE LOGFILE GROUPSyntax”). Multiple tablespaces may use the same log file group for
EXTENT_SIZEsets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and theoretical maximum is 2G, although the practical maximum size depends on a number of factors. In most cases, changing the extent size does not have any measurable effect on performance, and the default value is recommended for all but the most unusual situations.
An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G—that is, 32K extents × 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose
INITIAL_SIZEis 256 MB and whose
EXTENT_SIZEis 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.
You can see how many extents remain free in a given data file by querying the
INFORMATION_SCHEMA.FILEStable, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 20.21, “The
INITIAL_SIZEparameter sets the data file's total size in bytes. Once the file has been created, its size cannot be changed; however, you can add more data files to the tablespace using
ALTER TABLESPACE ... ADD DATAFILE. See Section 12.1.8, “
INITIAL_SIZEis optional; its default value is
On 32-bit systems, the maximum supported value for
INITIAL_SIZE(either or both), you may optionally follow the number with a one-letter abbreviation for an order of magnitude, similar to those used in
my.cnf. Generally, this is one of the letters
M(for megabytes) or
UNDO_BUFFER_SIZEare subject to rounding as follows:
UNDO_BUFFER_SIZEare each rounded up to the nearest whole multiple of 32K.
INITIAL_SIZEis rounded down to the nearest whole multiple of 32K.
For data files, INITIAL_SIZE is subject to further rounding; the result just obtained is rounded up to the nearest whole multiple of
EXTENT_SIZE(after any rounding).
The rounding just described has always (since Disk Data tablespaces were introduced in MySQL 5.1.6) been performed implicitly, but 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 rounding is done explicitly, and a warning is issued by the MySQL Server when any such rounding is performed. The rounded values are also used by the NDB kernel for calculating
INFORMATION_SCHEMA.FILEScolumn values and other purposes. However, to avoid an unexpected result, we suggest that you always use whole multiples of 32K in specifying these options.
COMMENTare parsed but ignored, and so currently have no effect. These options are intended for future expansion.
ENGINEparameter determines the storage engine which uses this tablespace, with
engine_namebeing the name of the storage engine. In MySQL 5.1,
engine_namemust be one of the values
CREATE TABLESPACEis used with
ENGINE = NDB, a tablespace and associated data file are created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the
INFORMATION_SCHEMA.FILEStable. For example:
SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA->
WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';+--------------------+-------------+----------------+ | LOGFILE_GROUP_NAME | FILE_NAME | EXTRA | +--------------------+-------------+----------------+ | lg_3 | newdata.dat | CLUSTER_NODE=3 | | lg_3 | newdata.dat | CLUSTER_NODE=4 | +--------------------+-------------+----------------+ 2 rows in set (0.01 sec)
CREATE TABLESPACEwas added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.10, “MySQL Cluster Disk Data Tables”.