13.1.18.9 Setting NDB_TABLE Options
In MySQL NDB Cluster 7.5.2 and later, the table comment in a CREATE TABLE
or ALTER TABLE
statement can also be used to specify an NDB_TABLE
option, which consists of one or more name-value pairs, separated by commas if need be, following the string NDB_TABLE=
. Complete syntax for names and values syntax is shown here:
COMMENT="NDB_TABLE=ndb_table_option[,ndb_table_option[,...]]"
ndb_table_option: {
NOLOGGING={1 | 0}
| READ_BACKUP={1 | 0}
| PARTITION_BALANCE={FOR_RP_BY_NODE | FOR_RA_BY_NODE | FOR_RP_BY_LDM
| FOR_RA_BY_LDM | FOR_RA_BY_LDM_X_2
| FOR_RA_BY_LDM_X_3 | FOR_RA_BY_LDM_X_4}
| FULLY_REPLICATED={1 | 0}
}
Spaces are not permitted within the quoted string. The string is case-insensitive.
The four NDB
table options that can be set as part of a comment in this way are described in more detail in the next few paragraphs.
NOLOGGING
: Using 1 corresponds to having ndb_table_no_logging
enabled, but has no actual effect. Provided as a placeholder, mostly for completeness of ALTER TABLE
statements.
READ_BACKUP
: Setting this option to 1 has the same effect as though ndb_read_backup
were enabled; enables reading from any replica. Doing so greatly improves the performance of reads from the table at a relatively small cost to write performance.
Starting with MySQL NDB Cluster 7.5.3, you can set READ_BACKUP
for an existing table online (Bug #80858, Bug #23001617), using an ALTER TABLE
statement similar to one of those shown here:
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";
Prior to MySQL NDB Cluster 7.5.4, setting READ_BACKUP
to 1 also caused FRAGMENT_COUNT_TYPE
to be set to ONE_PER_LDM_PER_NODE_GROUP
.
For more information about the ALGORITHM
option for ALTER TABLE
, see Section 21.5.11, “Online Operations with ALTER TABLE in NDB Cluster”.
PARTITION_BALANCE
: Provides additional control over assignment and placement of partitions. The following four schemes are supported:
FOR_RP_BY_NODE
: One partition per node.Only one LDM on each node stores a primary partition. Each partition is stored in the same LDM (same ID) on all nodes.
FOR_RA_BY_NODE
: One partition per node group.Each node stores a single partition, which can be either a primary replica or a backup replica. Each partition is stored in the same LDM on all nodes.
FOR_RP_BY_LDM
: One partition for each LDM on each node; the default.This is the same behavior as prior to MySQL NDB Cluster 7.5.2, except for a slightly different mapping of partitions to LDMs, starting with LDM 0 and placing one partition per node group, then moving on to the next LDM.
In MySQL NDB Cluster 7.5.4 and later, this is the setting used if
READ_BACKUP
is set to 1. (Bug #82634, Bug #24482114)FOR_RA_BY_LDM
: One partition per LDM in each node group.These partitions can be primary or backup partitions.
Prior to MySQL NDB Cluster 7.5.4, this was the setting used if
READ_BACKUP
was set to 1.FOR_RA_BY_LDM_X_2
: Two partitions per LDM in each node group.These partitions can be primary or backup partitions.
This setting was added in NDB 7.5.4.
FOR_RA_BY_LDM_X_3
: Three partitions per LDM in each node group.These partitions can be primary or backup partitions.
This setting was added in NDB 7.5.4.
FOR_RA_BY_LDM_X_4
: Four partitions per LDM in each node group.These partitions can be primary or backup partitions.
This setting was added in NDB 7.5.4.
Beginning with NDB 7.5.4, PARTITION_BALANCE
is the preferred interface for setting the number of partitions per table. Using MAX_ROWS
to force the number of partitions is deprecated as of NDB 7.5.4, continues to be supported in NDB 7.6 for backward compatibility, but is subject to removal in a future release of MySQL NDB Cluster. (Bug #81759, Bug #23544301)
Prior to MySQL NDB Cluster 7.5.4, PARTITION_BALANCE
was named FRAGMENT_COUNT_TYPE
, and accepted as its value one of (in the same order as that of the listing just shown) ONE_PER_NODE
, ONE_PER_NODE_GROUP
, ONE_PER_LDM_PER_NODE
, or ONE_PER_LDM_PER_NODE_GROUP
. (Bug #81761, Bug #23547525)
FULLY_REPLICATED
controls whether the table is fully replicated, that is, whether each data node has a complete copy of the table. To enable full replication of the table, use FULLY_REPLICATED=1
.
This setting can also be controlled using the ndb_fully_replicated
system variable. Setting it to ON
enables the option by default for all new NDB
tables; the default is OFF
, which maintains the previous behavior (as in MySQL NDB Cluster 7.5.1 and earlier, before support for fully replicated tables was introduced). The ndb_data_node_neighbour
system variable is also used for fully replicated tables, to ensure that when a fully replicated table is accessed, we access the data node which is local to this MySQL Server.
An example of a CREATE TABLE
statement using such a comment when creating an NDB
table is shown here:
mysql> CREATE TABLE t1 (
> c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> c2 VARCHAR(100),
> c3 VARCHAR(100) )
> ENGINE=NDB
>
COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
The comment is displayed as part of the ouput of SHOW CREATE TABLE
. The text of the comment is also available from querying the MySQL Information Schema TABLES
table, as in this example:
mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
*************************** 1. row ***************************
TABLE_NAME: t1
TABLE_SCHEMA: test
TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE
1 row in set (0.01 sec)
This comment syntax is also supported with ALTER TABLE
statements for NDB
tables, as shown here:
mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
Beginning with NDB 7.6.15, the TABLE_COMMENT
column displays the comment that is required to re-create the table as it is following the ALTER TABLE
statement, like this:
mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
-> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
*************************** 1. row ***************************
TABLE_NAME: t1
TABLE_SCHEMA: test
TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE
1 row in set (0.01 sec)
mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
+------------+--------------+--------------------------------------------------+
| TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT |
+------------+--------------+--------------------------------------------------+
| t1 | c | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE |
| t1 | d | |
+------------+--------------+--------------------------------------------------+
2 rows in set (0.01 sec)
Keep in mind that a table comment used with ALTER TABLE
replaces any existing comment which the table might have.
mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
+------------+--------------+--------------------------------------------------+
| TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT |
+------------+--------------+--------------------------------------------------+
| t1 | c | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE |
| t1 | d | |
+------------+--------------+--------------------------------------------------+
2 rows in set (0.01 sec)
Prior to NDB 7.6.15, the table comment used with ALTER TABLE
replaced any existing comment which the table might have had. This meant that (for example) the READ_BACKUP
value was not carried over to the new comment set by the ALTER TABLE
statement, and that any unspecified values reverted to their defaults. (BUG#30428829) There was thus no longer any way using SQL to retrieve the value previously set for the comment. To keep comment values from reverting to their defaults, it was necessry to preserve any such values from the existing comment string and include them in the comment passed to ALTER TABLE
.
You can also see the value of the PARTITION_BALANCE
option in the output of ndb_desc. ndb_desc also shows whether the READ_BACKUP
and FULLY_REPLICATED
options are set for the table. See the description of this program for more information.