MySQL AB :: MySQL 5.1 Reference Manual :: 15.11.1 Disk Data Objects

15.11.1. Disk Data Objects

This section discusses Disk Data objects — which include tables, log file groups, and tablespaces — as well as how to create and drop them.

Assuming that you have already set up a MySQL Cluster with all nodes (including management and SQL nodes) running MySQL 5.1.6 or newer, the basic steps for creating a Cluster table on disk are as follows:

Create a log file group, and assign one or more undo log files to it (an undo log file is also referred as an undofile).

Note

In MySQL 5.1, undo log files are necessary only for Disk Data tables. They are no longer used for tables that are stored in memory.
Create a tablespace, and assign the log file group to it, as well as one or more data files.
Create a Disk Data table that uses this tablespace for data storage.

Each of these tasks can be accomplished using SQL statements, as shown in the following example.

We create a log file group named lg_1 using CREATE LOGFILE GROUP. This log file group is to be made up of two undo log files, which we name undo_1.dat and undo_2.dat, whose initial sizes are 16 MB and 12 MB, respectively. (The default initial size for an undo log file is 128 MB.) Optionally, you can also specify a size for the log file group's UNDO buffer, or allow it to assume the default value of 8 MB. In this example, we set the UNDO buffer's size at 2 MB. A log file group must be created with an undo log file; so we add undo_1.dat to lg_1 in this CREATE LOGFILE GROUP statement:
```
CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.dat'
    INITIAL_SIZE 16M
    UNDO_BUFFER_SIZE 2M
    ENGINE NDB;
```
To add undo_2.dat to the log file group, use the following ALTER LOGFILE GROUP statement:
```
ALTER LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_2.dat'
    INITIAL_SIZE 12M
    ENGINE NDB;
```
Some items of note:
- The .dat file extension used here is not required. We use it merely to make the log and data files easily recognisable.
- Every CREATE LOGFILE GROUP and ALTER LOGFILE GROUP statement must include an ENGINE clause. In MySQL 5.1, the permitted values for this clause are NDB and NDBCLUSTER.
  
  In MySQL 5.1.8 and later, there can exist only one log file group at any given time.
- When you add an undo log file to a log file group using ADD UNDOFILE 'filename', a file with the name filename is created in the ndb_nodeid_fs directory within the DataDirectory of each data node in the cluster, where nodeid is the node ID of the data node.
- UNDO_BUFFER_SIZE is limited by the amount of system memory available.
- For more information about the CREATE LOGFILE GROUP statement, see Section 13.1.9, “CREATE LOGFILE GROUP Syntax”. For more information about ALTER LOGFILE GROUP, see Section 13.1.3, “ALTER LOGFILE GROUP Syntax”.
Now we can create a tablespace, which contains files to be used by MySQL Cluster Disk Data tables for storing their data. A tablespace is also associated with a particular log file group. When creating a new tablespace, you must specify the log file group which it is to use for undo logging; you must also specify a data file. You can add more data files to the tablespace after it the tablespace is created; it is also possible to drop data files from a tablespace (an example of dropping data files is provided later in this section).

Assume that we wish to create a tablespace named ts_1 which uses lg_1 as its log file group. This tablespace is to contain two data files named data_1.dat and data_2.dat, whose initial sizes are 32 MB and 48 MB, respectively. (The default value for INITIAL_SIZE is 128 MB.) We can do this using two SQL statements, as shown here:
```
CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    INITIAL_SIZE 32M
    ENGINE NDB;

ALTER TABLESPACE ts_1
    ADD DATAFILE 'data_2.dat'
    INITIAL_SIZE 48M
    ENGINE NDB;
```
The CREATE TABLESPACEstatement creates a tablespace ts_1 with the data file data_1.dat, and associates ts_1 with log file group lg_1. The ALTER TABLESPACE adds the second data file (data_2.dat).

Some items of note:
- As is the case with the filenames used here for undo log files, there is no special significance for the .dat file extension; it is used merely for easy recognition.
- All CREATE TABLESPACE and ALTER TABLESPACE statements must contain an ENGINE clause; only tables using the same storage engine as the tablespace can be created in the tablespace. In MySQL 5.1, the only permitted values for this clause are NDB and NDBCLUSTER.
  
  For more information about the CREATE TABLESPACE and ALTER TABLESPACE statements, see Section 13.1.10, “CREATE TABLESPACE Syntax”, and Section 13.1.4, “ALTER TABLESPACE Syntax”.
Now it is possible to create a table whose non-indexed columns are stored on disk in the tablespace ts_1:
```
CREATE TABLE dt_1 (
    member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    joined DATE NOT NULL,
    INDEX(last_name, first_name)
    )
    TABLESPACE ts_1 STORAGE DISK
    ENGINE NDB;
```
The TABLESPACE ... STORAGE DISK clause tells the NDB Cluster storage engine to use tablespace ts_1 for disk data storage.

Once table ts_1 has been created as shown, you can perform INSERT, SELECT, UPDATE, and DELETE statements on it just as you would with any other MySQL table.

For table dt_1 as it has been defined here, only the dob and joined columns are stored on disk. This is because there are indexes on the id, last_name, and first_name columns, and so data belonging to these columns is stored in RAM. In MySQL 5.1, only non-indexed columns can be held on disk; indexes and indexed column data continue to be stored in memory. This trade-off between the use of indexes and conservation of RAM is something you must keep in mind as you design Disk Data tables.

Performance note. The performance of a cluster using Disk Data storage is greatly improved if Disk Data files are kept on a separate physical disk from the data node filesystem. This must be done for each data node in the cluster to derive any noticeable benefit.

You may use absolute and relative filesystem paths with ADD UNDOFILE and ADD DATAFILE. Relative paths are calculated relative to the data node's data directory.

A log file group, a tablespace, and any Disk Data tables using these must be created in a particular order. The same is true for dropping any of these objects:

A log file group cannot be dropped, so long as any tablespaces are using it.
A tablespace cannot be dropped as long as it contains any data files.
You cannot drop any data files from a tablespace as long as there remain any tables which are using the tablespace.
Beginning with MySQL 5.1.12, it is no longer possible to drop files created in association with a different tablespace than the one with which the files were created. (Bug#20053)

For example, to drop all the objects created so far in this section, you would use the following statements:

mysql> DROP TABLE dt_1;

mysql> ALTER TABLESPACE ts_1
    -> DROP DATAFILE 'data_2.dat'
    -> ENGINE NDB;

mysql> ALTER TABLESPACE ts_1
    -> DROP DATAFILE 'data_1.dat'
    -> ENGINE NDB;

mysql> DROP TABLESPACE ts_1
    -> ENGINE NDB;

mysql> DROP LOGFILE GROUP lg_1
    -> ENGINE NDB;

These statements must be performed in the order shown, except that the two ALTER TABLESPACE ... DROP DATAFILE statements may be executed in either order.

You can obtain information about data files used by Disk Data tables by querying the FILES table in the INFORMATION_SCHEMA database. An extra “NULL row” was added to this table in MySQL 5.1.14 for providing additional information about undo log files. For more information and examples of use, see Section 22.21, “The INFORMATION_SCHEMA FILES Table”.

15.11.1. Disk Data Objects

Note

User Comments