CREATE TABLESPACEtablespace_name
ADD DATAFILE 'file_name
' USE LOGFILE GROUPlogfile_group
[EXTENT_SIZE [=]extent_size
] [INITIAL_SIZE [=]initial_size
] [AUTOEXTEND_SIZE [=]autoextend_size
] [MAX_SIZE [=]max_size
] [NODEGROUP [=]nodegroup_id
] [WAIT] [COMMENT [=]comment_text
] ENGINE [=]engine_name
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 TABLESPACE
statement (see
Section 12.1.8, “ALTER TABLESPACE
Syntax”). 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 UNDO
log files
must be assigned to the tablespace to be created with the
USE LOGFILE GROUP
clause.
logfile_group
must be an existing log
file group created with CREATE LOGFILE GROUP
(see Section 12.1.14, “CREATE LOGFILE GROUP
Syntax”). Multiple tablespaces
may use the same log file group for UNDO
logging.
The EXTENT_SIZE
sets 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_SIZE
is 256 MB
and whose EXTENT_SIZE
is 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.FILES
table, and so derive an estimate for how much space remains free
in the file. For further discussion and examples, see
Section 20.21, “The INFORMATION_SCHEMA FILES
Table”.
The INITIAL_SIZE
parameter 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, “ALTER TABLESPACE
Syntax”.
INITIAL_SIZE
is optional; its default value is
128M
.
On 32-bit systems, the maximum supported value for
INITIAL_SIZE
is 4G
.
(Bug#29186)
When setting EXTENT_SIZE
or
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 G
(for
gigabytes).
INITIAL_SIZE
, EXTENT_SIZE
,
and UNDO_BUFFER_SIZE
are subject to rounding as
follows:
EXTENT_SIZE
and
UNDO_BUFFER_SIZE
are each rounded up to the
nearest whole multiple of 32K.
INITIAL_SIZE
is 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.FILES
column
values and other purposes. However, to avoid an unexpected result,
we suggest that you always use whole multiples of 32K in
specifying these options.
AUTOEXTEND_SIZE
, MAX_SIZE
,
NODEGROUP
, WAIT
, and
COMMENT
are parsed but ignored, and so
currently have no effect. These options are intended for future
expansion.
The ENGINE
parameter determines the storage
engine which uses this tablespace, with
engine_name
being the name of the
storage engine. In MySQL 5.1,
engine_name
must be one of the values
NDB
or
NDBCLUSTER
.
When CREATE TABLESPACE
is 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.FILES
table. For example:
mysql>SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA
->FROM INFORMATION_SCHEMA.FILES
->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)
(See Section 20.21, “The INFORMATION_SCHEMA FILES
Table”.)
CREATE TABLESPACE
was 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”.
User Comments
Add your own comment.