Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

16.9. Known Limitations of MySQL Cluster

In this section, we provide a list of known limitations in MySQL Cluster releases in the 5.1.x series compared to features available when using the MyISAM and InnoDB storage engines. Currently, there are no plans to address these in coming releases of MySQL 5.1; however, we will attempt to supply fixes for these issues in subsequent release series. If you check the “Cluster” category in the MySQL bugs database at http://bugs.mysql.com, you can find known bugs which (if marked “5.1”) we intend to correct in upcoming releases of MySQL 5.1.

The list here is intended to be complete with respect to the conditions just set forth. You can report any discrepancies that you encounter to the MySQL bugs database using the instructions given in Section 1.8, “How to Report Bugs or Problems”. If we do not plan to fix the problem in MySQL 5.1, we will add it to the list.

  • Noncompliance in syntax (resulting in errors when running existing applications):

    • Text indexes are not supported.

    • A BIT column cannot be a primary key or part of a composite primary key.

    • Geometry datatypes (WKT and WKB) are supported in NDB tables in MySQL 5.1. However, spatial indexes are not supported.

    • It is not possible to drop partitions from NDB tables using ALTER TABLE ... DROP PARTITION. The other partitioning extensions to ALTER TABLEADD PARTITION, REORGANIZE PARTITION, and COALESCE PARTITION — are supported for Cluster tables, but use copying and so are not optimised. See Section 17.3.1, “Management of RANGE and LIST Partitions” and Section 13.1.2, “ALTER TABLE Syntax”.

      As of MySQL 5.1.6, all Cluster tables are by default partitioned by KEY using the table's primary key as the partitioning key. If no primary key is explicitly set for the table, the “hidden” primary key automatically created by the NDB storage engine is used instead. For additional discussion of these and related issues, see Section 17.2.4, “KEY Partitioning”.

  • Non-compliance in limits or behavior (may result in errors when running existing applications):

    • Error Reporting:

      • A duplicate key error returns the error message ERROR 23000: Can't write; duplicate key in table 'tbl_name'.

    • Transaction Handling:

      • NDB Cluster supports only the READ COMMITTED transaction isolation level.

      • There is no partial rollback of transactions. A duplicate key or similar error results in a rollback of the entire transaction.

      • Important: If a SELECT from a Cluster table includes a BLOB, TEXT, or VARCHAR column, the READ COMMITTED transaction isolation level is converted to a read with read lock. This is done to guarantee consistency, due to the fact that parts of the values stored in columns of these types are actually read from a separate table.

    • A number of hard limits exist which are configurable, but available main memory in the cluster sets limits. See the complete list of configuration parameters in Section 16.4.4, “Configuration File”. Most configuration parameters can be upgraded online. These hard limits include:

      • Database memory size and index memory size (DataMemory and IndexMemory, respectively).

      • The maximum number of transactions that can be performed is set using the configuration parameter MaxNoOfConcurrentOperations. Note that bulk loading, TRUNCATE TABLE, and ALTER TABLE are handled as special cases by running multiple transactions, and so are not subject to this limitation.

      • Different limits related to tables and indexes. For example, the maximum number of ordered indexes per table is determined by MaxNoOfOrderedIndexes.

    • Database names, table names and attribute names cannot be as long in NDB tables as with other table handlers. Attribute names are truncated to 31 characters, and if not unique after truncation give rise to errors. Database names and table names can total a maximum of 122 characters. (That is, the maximum length for an NDB Cluster table name is 122 characters less the number of characters in the name of the database of which that table is a part.)

    • 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 is required when using the NDB storage engine than would be the case for the same table and data using the MyISAM engine. (In other words, in the case of a VARCHAR column, the column requires the same amount of storage as a CHAR column of the same size.)

    • The maximum number of tables in a Cluster database is limited to 1792.

    • The maximum number of attributes per table is limited to 128.

    • The maximum permitted size of any one row is 8KB, not including data stored in BLOB columns.

    • The maximum number of attributes per key is 32.

  • Unsupported features (do not cause errors, but are not supported or enforced):

    • The foreign key construct is ignored, just as it is in MyISAM tables.

    • Savepoints and rollbacks to savepoints are ignored as in MyISAM.

  • Performance and limitation-related issues:

    • There are query performance issues due to sequential access to the NDB storage engine; it is also relatively more expensive to do many range scans than it is with either MyISAM or InnoDB.

    • The Records in range statistic is not supported, resulting in non-optimal query plans in some cases. Employ USE INDEX or FORCE INDEX as a workaround.

    • Unique hash indexes created with USING HASH cannot be used for accessing a table if NULL is given as part of the key.

    • MySQL Cluster does not support durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

  • Missing features:

    • The only supported isolation level is READ COMMITTED. (InnoDB supports READ COMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.) See Section 16.6.5.5, “Backup Troubleshooting”, for information on how this can effect backup and restore of Cluster databases.

    • No durable commits on disk. Commits are replicated, but there is no guarantee that logs are flushed to disk on commit.

  • Problems relating to multiple MySQL servers (not relating to MyISAM or InnoDB):

    • ALTER TABLE is not fully locking when running multiple MySQL servers (no distributed table lock).

    • MySQL replication will not work correctly if updates are done on multiple MySQL servers. However, if the database partitioning scheme is done at the application level and no transactions take place across these partitions, replication can be made to work.

    • Autodiscovery of databases is not supported for multiple MySQL servers accessing the same MySQL Cluster. However, autodiscovery of tables is supported in such cases. What this means is that after a database named db_name is created or imported using one MySQL server, you should issue a CREATE SCHEMA db_name statement on each additional MySQL server that accesses the same MySQL Cluster. Once this has been done for a given MySQL server, that server should be able to detect the database tables without error.

  • Issues exclusive to MySQL Cluster (not related to MyISAM or InnoDB):

    • All machines used in the cluster must have the same architecture. That is, all machines hosting nodes must be either big-endian or little-endian, and you cannot use a mixture of both. For example, you cannot have a management node running on a PowerPC which directs a data node that is running on an x86 machine. This restriction does not apply to machines simply running mysql or other clients that may be accessing the cluster's SQL nodes.

    • It is not possible to make online schema changes such as those accomplished using ALTER TABLE or CREATE INDEX, as the NDB Cluster does not support autodiscovery of such changes. (However, you can import or create a table that uses a different storage engine, and then convert it to NDB using ALTER TABLE tbl_name ENGINE=NDBCLUSTER. In such a case, you must issue a FLUSH TABLES statement to force the cluster to pick up the change.)

    • Online adding or dropping of nodes is not possible (the cluster must be restarted in such cases).

    • When using multiple management servers:

      • You must give nodes explicit IDs in connectstrings because automatic allocation of node IDs does not work across multiple management servers.

      • You must take extreme care to have the same configurations for all management servers. No special checks for this are performed by the cluster.

      • In order that management nodes be able to see one another, you must restart all data nodes after bringing up the cluster. (See Bug #13070 for a detailed explanation.)

    • Multiple network interfaces for data nodes are not supported. Use of these is liable to cause problems: In the event of a data node failure, an SQL node waits for confirmation that the data node went down but never receives it because another route to that data node remains open. This can effectively make the cluster inoperable.

    • The maximum number of data nodes is 48.

    • The total maximum number of nodes in a MySQL Cluster is 63. This number includes all MySQL Servers (SQL nodes), data nodes, and management servers.

    • The maximum number of metadata objects in MySQL 5.1 Cluster is 20320. This limit is hard-coded.


 
 
  Published under the terms of the GNU General Public License Design by Interspire