Syntax:
CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    USE LOGFILE GROUP logfile_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 [HELP ALTER TABLESPACE]). For rules covering the naming
of tablespaces, see
http://dev.mysql.com/doc/refman/5.5/en/identifiers.html.

*Note*:

All NDB 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 [HELP CREATE LOGFILE GROUP]). 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 x 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 http://dev.mysql.com/doc/refman/5.5/en/files-table.html.

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 [HELP ALTER TABLESPACE].

INITIAL_SIZE is optional; its default value is 134217728 (128 MB).

On 32-bit systems, the maximum supported value for INITIAL_SIZE is
4294967296 (4 GB). (Bug #29186)

When setting EXTENT_SIZE, 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). In MySQL NDB Cluster 7.2.14 and later, these
abbreviations are also supported when specifying INITIAL_SIZE as well.
(Bug #13116514, Bug #16104705, Bug #62858)

INITIAL_SIZE, EXTENT_SIZE, and UNDO_BUFFER_SIZE are subject to rounding
as follows:

o EXTENT_SIZE and UNDO_BUFFER_SIZE are each rounded up to the nearest
  whole multiple of 32K.

o 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 http://dev.mysql.com/doc/refman/5.5/en/files-table.html.)

CREATE TABLESPACE is useful only with Disk Data storage for NDB
Cluster. See
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-disk-data.html.

URL: http://dev.mysql.com/doc/refman/5.5/en/create-tablespace.html

