Spec-Zone .ru
спецификации, руководства, описания, API
|
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 13.1.8, "ALTER TABLESPACE
Syntax"). For rules covering the naming of tablespaces, see
Section 9.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.
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
13.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.8, "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
13.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 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. Currently, 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.8, "The INFORMATION_SCHEMA
FILES
Table".)
CREATE TABLESPACE
is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.12, "MySQL Cluster Disk Data Tables".