Anirudh Tamsekar made a post yesterday that laid out a few of the
limitations of MySQL Cluster that seem to cause him the most
pain. However his assessment of the situation is quite
misleading. A few of his statements are inaccurate but more than
half of the limitations he cites to are out right false. Since
comments on Anirudh's blog are being moderated, I give my
rebuttal here, and cite sources.
· Database names, table names, and attribute names cannot be
as long in NDB tables as with other table handlers. In NDB,
attribute names are truncated to 31 characters, and if they are
not unique after truncation, errors occur. Database names and
table names can total a maximum of 122 characters
False: "Identifiers. Formerly (in MySQL 5.0 and earlier),
database names, table names and attribute names could not be as
long for NDB tables as tables using other storage engines,
because attribute names were truncated internally. In MySQL 5.1
and later, names of MySQL Cluster databases, tables, and table
columns follow the same rules regarding length as they do for any
other storage engine."
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
· NDB does not support prefix indexes; only entire fields can
be indexed.
True
· A big limitation is that in MySQL 4.1 and 5.0, all cluster
table rows are of fixed length. This means, for example, that if
a table has one or more VARCHAR fields containing only relatively
small values, more memory and disk space will be required when
using the NDB storage engine than would be for the same table and
data using the MyISAM engine. This issue is on the “to-fix” list
for MySQL Cluster 5.1.
False: As of 5.1 (which has been GA for over a year and a half)
this limitation applies only to on-disk columns.
"* Variable-length column support. The NDBCLUSTER storage engine
now supports variable-length column types for in-memory
tables.
Previously, for example, any Cluster table having one or more
VARCHAR fields which contained only relatively small values, much
more memory and disk space were required when using the
NDBCLUSTER storage engine than would have been the case for the
same table and data using the MyISAM engine. In other words, in
the case of a VARCHAR column, such a column required the same
amount of storage as a CHAR column of the same size. In MySQL
5.1, this is no longer the case for in-memory tables, where
storage requirements for variable-length column types such as
VARCHAR and BINARY are comparable to those for these column types
when used in MyISAM tables (see Section 10.5, “Data Type Storage
Requirements”). "
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
· In NDB, the maximum number of metadata objects is limited to
20,000, including database tables, system tables, indexes, and
BLOBs (binary large objects). This is a hard-coded limit that you
cannot override with a configuration option.
True: However the actual number is 20320.
· The maximum permitted size of any one row in NDB is 8KB, not
including data stored in BLOB columns (which are actually stored
in a separate table internally).
True: However, with version 7.2 we have increased the variable
length part of a row to support up to 14KB, with the fixed
portion still limited to 8KB
There is actually a constant you can modify at compile time to
increase the max row length (as number of 4 byte words).
Current values are:
./storage/ndb/include/kernel/ndb_limits.h
#define MAX_TUPLE_SIZE_IN_WORDS 3500
./storage/ndb/include/ndbapi/ndbapi_limits.h
#define NDB_MAX_TUPLE_SIZE_IN_WORDS 3500
and
./src/ndbjtie/com/mysql/ndbjtie/ndbapi/NDBAPIConst.java
NDB_MAX_TUPLE_SIZE_IN_WORDS = 3500,
Changing these values is untested and unsupported so your mileage
may vary.
· The maximum number of attributes per key in NDB is
32.
True:
· Autodiscovery of databases is not supported in NDB for
multiple MySQL servers accessing the same cluster in MySQL
Cluster. (You have to add each database manually on each SQL
node.)
False: "Autodiscovery of databases is now supported for multiple
MySQL servers accessing the same MySQL Cluster. Formerly,
autodiscovery in MySQL Cluster 5.1 and MySQL Cluster NDB 6.x
releases required that a given mysqld was already running and
connected to the cluster at the time that the database was
created on a different mysqld—in other words, when a mysqld
process connected to the cluster after a database named db_name
was created, it was necessary to issue a CREATE DATABASE db_name
or CREATE SCHEMA db_name statement on the “new” MySQL server when
it first accesseed that MySQL Cluster. Beginning with MySQL
Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.18, such a CREATE
statement is no longer required. (Bug#39612)
This also means that online schema changes in NDB tables are now
possible. That is, the result of operations such as ALTER TABLE
and CREATE INDEX performed on one SQL node in the cluster are now
visible to the cluster's other SQL nodes without any additional
action being taken."
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
· MySQL replication does not work correctly in NDB if updates
are done on multiple MySQL servers; replication between clusters
is on the feature list for MySQL 5.1.
False: Again, 5.1 is GA over a year and a half. No need to treat
this is a forward looking statement.
"* Replication with MySQL Cluster. It is now possible to use
MySQL replication with Cluster databases. For details, see
Section 17.6, “MySQL Cluster Replication”.
Circular Replication. Circular replication is also supported with
MySQL Cluster, beginning with MySQL 5.1.18. See Section 17.6.10,
“MySQL Cluster Replication: Multi-Master and Circular
Replication”.
* auto_increment_increment and auto_increment_offset. The
auto_increment_increment and auto_increment_offset server system
variables are supported for Cluster replication beginning with
MySQL 5.1.20, MySQL Cluster NDB 6.2.5, and MySQL Cluster
6.3.2."
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
However, since the SQL node cannot know of the statements
executed on another SQL node only row based replication is
supported:
"Replication. Statement-based replication is not supported. Use
--binlog-format=ROW (or --binlog-format=MIXED) when setting up
cluster replication. See Section 17.6, “MySQL Cluster
Replication”, for more information. "
· ALTER TABLE is not fully locking in NDB when you’re running
multiple MySQL servers.
False: With the changes to support discovery of CREATE DATABASE
in MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.18 a global
schema lock was introduced which consistiently locks tables
across all SQL nodes in the cluster during DDL operations
· All storage and management nodes within a cluster in NDB
must have the same architecture. This restriction does not apply
to machines simply running SQL nodes or any other clients that
may be accessing the cluster.
Misleading: All nodes must be the same endian nature. However
machines running "mysql" clients have no such restriction. SQL
nodes themselves do have limitation of being the same endian type
as the data nodes. i.e. A Linux x86 machine cannot be a mysqld
(SQL node) front end to ndbd (DATA nodes) running Solaris
Sparc.
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-exclusive-to-cluster.html
· It is not possible to make online schema changes in NDB,
such as those accomplished using ALTER TABLE or CREATE INDEX.
(However, you can import or create a table that uses a different
storage engine and then convert it to NDB by using ALTER TABLE
tbl_name ENGINE=NDBCLUSTER;.) ALTER TABLE works on occasions, but
all it does is create a new table with the new structure and then
import the data. This generally causes an error as NDB hits a
limit somewhere. It is strongly recommended that you not use
ALTER TABLE to make online schema changes.
False: Again with the schema changes... See above.
· Adding or removing nodes online is not possible in NDB. (The
cluster must be restarted in such cases.)
False: "In MySQL Cluster NDB 7.0 (beginning with MySQL Cluster
NDB 6.4.0) and later MySQL Cluster release series, it is possible
to add new data nodes to a running MySQL Cluster by performing a
rolling restart, so that the cluster and the data stored in it
remain available to applications."
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
Removal of data nodes online is possible for nodes that were
added online. Nodes that were in the cluster at initial system
startup cannot be removed without a total shutdown and restore.
However online removal is not thouroghly tested so it is not
officially supported.
· The maximum number of storage nodes within an NDB cluster is
48.
True:
· The total maximum number of nodes in a cluster in MySQL
Cluster is 63. This number includes all MySQL servers (that is,
SQL nodes), storage nodes, and management servers.
False: "Starting with MySQL Cluster NDB 6.1.1, the total maximum
number of nodes in a MySQL Cluster is 255, including all SQL
nodes (MySQL Servers), API nodes (applications accessing the
cluster other than MySQL servers), data nodes, and management
servers. The total number of data nodes and management nodes
beginning with this version is 63, of which up to 48 can be data
nodes.
Note: The limitation that a data node cannot have a node ID
greater than 49 continues to apply."
-- http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-resolved.html
May
28
2010