Author: jstephens
Date: 2007-05-18 16:49:47 +0200 (Fri, 18 May 2007)
New Revision: 6530
Log:
Remaining 5.0 Limitations sections.
Modified:
trunk/refman-5.0/mysql-cluster-limitations-working.xml
Modified: trunk/refman-5.0/mysql-cluster-limitations-working.xml
===================================================================
--- trunk/refman-5.0/mysql-cluster-limitations-working.xml 2007-05-18 14:41:27 UTC (rev 6529)
+++ trunk/refman-5.0/mysql-cluster-limitations-working.xml 2007-05-18 14:49:47 UTC (rev 6530)
Changed blocks: 3, Lines Added: 667, Lines Deleted: 712; 53204 bytes
@@ -7,757 +7,678 @@
]>
<section id="mysql-cluster-limitations">
- <title>Known Limitations of MySQL Cluster</title>
-
+ <title>Known Limitations of MySQL Cluster</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>limitations of MySQL Cluster</primary>
+ </indexterm>
+
+ <para>
+ In the sections that follow, we discuss known limitations in MySQL
+ ¤t-series; Cluster and &mccge-series; releases as compared
+ with the features available when using the <literal>MyISAM</literal>
+ and <literal>InnoDB</literal> storage engines. Currently, there are
+ no plans to address these in coming releases of MySQL
+ ¤t-series; or &mccge-series;; however, we will attempt to
+ supply fixes for these issues in subsequent release series. If you
+ check the <quote>Cluster</quote> category in the MySQL bugs database
+ at <ulink url="http://bugs.mysql.com"/>, you can find known bugs
+ which (if marked <quote>¤t-series;</quote>) we intend to
+ correct in upcoming releases of MySQL ¤t-series;. Some of
+ these issues may be addressed in &mccge-series; and the fixes
+ included in a future mainline MySQL Cluster version.
+ </para>
+
+ <para>
+ This information 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
+ <xref linkend="bug-reports"/>. If we do not plan to fix the problem
+ in MySQL ¤t-series;, we will add it to the list.
+ </para>
+
+ <para>
+ See <xref linkend="mysql-cluster-limitations-resolved"/> for a list
+ of issues in MySQL Cluster in MySQL &previous-series; that have been
+ resolved in the current version.
+ </para>
+
+
+
+ <section id="mysql-cluster-limitations-syntax">
+
+ <title>Non-Compliance In SQL Syntax</title>
+
<indexterm>
<primary>MySQL Cluster limitations</primary>
+ <secondary>syntax</secondary>
</indexterm>
-
- <indexterm>
- <primary>limitations of MySQL Cluster</primary>
-<!-- <see>MySQL Cluster limitations</see> -->
- </indexterm>
-
+
<para>
- In this section, we provide a list of known limitations in MySQL
- Cluster releases in the 5.0.x series compared to
- features available when using the <literal>MyISAM</literal> and
- <literal>InnoDB</literal> storage engines. Currently, there are no
- plans to address these in coming releases of MySQL
- 5.0; however, we will attempt to supply fixes for
- these issues in subsequent release series. If you check the
- <quote>Cluster</quote> category in the MySQL bugs database at
- <ulink url="http://bugs.mysql.com"/>, you can find known bugs
- which (if marked <quote>5.0</quote>) we intend to
- correct in upcoming releases of MySQL 5.0.
- </para>
-
- <para>
- 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 <xref linkend="bug-reports"/>. If we do not plan to fix
- the problem in MySQL 5.0, we will add it to the list.
- </para>
-
- <para>
- (<emphasis role="bold">Note</emphasis>: See the end of this
- section for a list of issues in MySQL 4.1 Cluster
- that have been resolved in the current version.)
- </para>
-
- <itemizedlist>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>syntax</secondary>
- </indexterm>
-
- <para>
- <emphasis role="bold">Noncompliance in syntax</emphasis>
- (resulting in errors when running existing applications):
- </para>
-
- <itemizedlist>
-
- <listitem>
+ Some SQL statements relating to certain MySQL features produce
+ errors when used with <literal>NDB</literal> tables, as described
+ in the following list:
+
+ <itemizedlist>
+
+ <listitem>
+ <formalpara>
+
+ <title>Temporary tables</title>
+
<para>
- Temporary tables are not supported.
+ Temporary tables are not supported. Trying either to
+ create a temporary table that uses the
+ <literal>NDB</literal> storage engine or to alter an
+ existing temporary table to use <literal>NDB</literal>
+ fails with the error <errortext>Table storage engine
+ 'ndbcluster' does not support the create option
+ 'TEMPORARY'</errortext>.
</para>
- </listitem>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Indexes and keys in <literal>NDB</literal> tables</title>
+
<para>
- You cannot create indexes on <literal>NDB</literal> table
- columns that use any of the <literal>TEXT</literal> or
- <literal>BLOB</literal> data types.
+ Keys and indexes on MySQL Cluster tables are subject to
+ the following limitations:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You cannot create indexes on <literal>NDB</literal>
+ table columns that use any of the
+ <literal>TEXT</literal> or <literal>BLOB</literal>
+ data types.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>NDB</literal> storage engine does not
+ support <literal>FULLTEXT</literal> indexes, which
+ are possible for <literal>MyISAM</literal> tables
+ only.
+ </para>
+
+ <para>
+ However, you can create indexes on
+ <literal>VARCHAR</literal> columns of
+ <literal>NDB</literal> tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A <literal>BIT</literal> column cannot be a primary
+ key, unique key, or index, nor can it be part of a
+ composite primary key, unique key, or index.
+ </para>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Duplicate keys</title>
+
+ <para>
+ A duplicate key error returns the error message
+ <errortext>ERROR 23000: Can't write; duplicate key
+ in table
+ '<replaceable>tbl_name</replaceable>'</errortext>.
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title><literal>AUTO_INCREMENT</literal> columns</title>
+
+ <para>
+ Like other MySQL storage engines, the
+ <literal>NDB</literal> storage engine can handle a
+ maximum of one <literal>AUTO_INCREMENT</literal>
+ column per table. However, in the case of a
+ Cluster table with no explicit primary key, an
+ <literal>AUTO_INCREMENT</literal> column is
+ automatically defined and used as a
+ <quote>hidden</quote> primary key. For this
+ reason, you cannot define a table that has an
+ explicit <literal>AUTO_INCREMENT</literal> column
+ unless that column is also declared using the
+ <literal>PRIMARY KEY</literal> option. Attempting
+ to create a table with an
+ <literal>AUTO_INCREMENT</literal> column that is
+ not the table's primary key, and using the
+ <literal>NDB</literal> storage engine, fails with
+ an error.
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
</para>
-
- <para>
- The <literal>NDB</literal> storage engine also does not
- support <literal>FULLTEXT</literal> indexes (these are
- supported by <literal>MyISAM</literal> only).
- </para>
-
- <para>
- However, you can index <literal>VARCHAR</literal> columns
- of <literal>NDB</literal> tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A <literal>BIT</literal> column cannot be a primary key,
- unique key, or index, nor can it be part of a composite
- primary key, unique key, or index.
- </para>
- </listitem>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>MySQL Cluster and geometry data types</title>
+
<indexterm>
<primary>MySQL Cluster limitations</primary>
- <secondary>geometry datatypes</secondary>
+ <secondary>geometry data types</secondary>
</indexterm>
-
+
<para>
Geometry datatypes (<literal>WKT</literal> and
- <literal>WKB</literal>) are not supported by the
- <literal>NDB</literal> storage engine prior to MySQL
- 5.0.16. (Note that spatial indexes are still not supported
- for Cluster tables in MySQL 5.0.16 and newer.)
+ <literal>WKB</literal>) are supported in
+ <literal>NDB</literal> tables in MySQL ¤t-series;.
+ However, spatial indexes are not supported.
</para>
- </listitem>
-
- <listitem>
- <para>
- In MySQL 5.0.19 and earlier, <literal>INSERT
- IGNORE</literal>, <literal>UPDATE IGNORE</literal>, and
- <literal>REPLACE</literal> are supported only for primary
- keys, but not for unique keys. One possible workaround is
- to remove the constraint by dropping the unique index,
- perform any inserts, and then add the unique index again.
- </para>
-
- <para>
- This limitation is removed for <literal>INSERT
- IGNORE</literal> and <literal>REPLACE</literal> in MySQL
- 5.0.20. (Bug #17431)
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>causing errors</secondary>
- </indexterm>
-
- <para>
- <emphasis role="bold">Non-compliance in limits or
- behavior</emphasis> (may result in errors when running
- existing applications):
- </para>
-
- <itemizedlist>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+
+
+ <listitem>
+ <formalpara>
+
+ <title>Row-based replication</title>
+
<indexterm>
- <primary>memory use</primary>
- <secondary>in MySQL Cluster</secondary>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>replication</secondary>
</indexterm>
-
- <indexterm>
- <primary>MySQL Cluster</primary>
- <secondary>memory usage and recovery</secondary>
- </indexterm>
-
- <indexterm>
- <primary><literal>DELETE</literal></primary>
- <secondary>and MySQL Cluster</secondary>
- </indexterm>
-
- <indexterm>
- <primary><literal>DROP TABLE</literal></primary>
- <secondary>and MySQL Cluster</secondary>
- </indexterm>
-
- <indexterm>
- <primary><literal>TRUNCATE</literal></primary>
- <secondary>and MySQL Cluster</secondary>
- </indexterm>
-
+
<para>
- <emphasis role="bold">Memory Usage</emphasis>:
+ When using row-based replication with MySQL Cluster,
+ binary logging cannot be disabled. That is, the
+ <literal>NDB</literal> storage engine ignores the value of
+ <literal>SQL_LOG_BIN</literal>. (Bug #16680)
</para>
-
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title><literal>auto_increment_increment</literal> and
+ <literal>auto_increment_offset</literal></title>
+
<para>
+ The <literal>auto_increment_increment</literal> and
+ <literal>auto_increment_offset</literal> server system
+ variables are not supported for Cluster replication.
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </section>
+
+ <section id="mysql-cluster-limitations-limits">
+
+ <title>Limits and Differences from Standard MySQL Limits</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>and differences from standard MySQL limits</secondary>
+ </indexterm>
+
+ <para>
+ In this section, we list limits found in MySQL Cluster that either
+ differ from limits found in, or that are not found in, standard
+ MySQL.
+
+ <itemizedlist>
+
+ <listitem>
+ <indexterm>
+ <primary>memory use</primary>
+ <secondary>in MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>memory usage and recovery</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>DELETE</literal></primary>
+ <secondary>and MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>DROP TABLE</literal></primary>
+ <secondary>and MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>TRUNCATE</literal></primary>
+ <secondary>and MySQL Cluster</secondary>
+ </indexterm>
+
+ <formalpara>
+
+ <title>Memory usage and recovery</title>
+
+ <para>
Memory comsumed when data is inserted into an
<literal>NDB</literal> table is not automatically
recovered when deleted, as it is with other storage
engines. Instead, the following rules hold true:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A <literal>DELETE</literal> statement on an
+ <literal>NDB</literal> table makes the memory
+ formerly used by the deleted rows available for
+ re-use by inserts on the same table only. This
+ memory cannot be used by other
+ <literal>NDB</literal> tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A <literal>DROP TABLE</literal> or
+ <literal>TRUNCATE</literal> operation on an
+ <literal>NDB</literal> table frees the memory that
+ was used by this table for re-use by any
+ <literal>NDB</literal> table, either by the same
+ table or by another <literal>NDB</literal> table.
+ </para>
+
+ <note>
+ <para>
+ Recall that <literal>TRUNCATE</literal> drops and
+ re-creates the table. See
+ <xref linkend="truncate"/>.
+ </para>
+ </note>
+
+ <para>
+ Memory freed by <literal>DELETE</literal> operations
+ but still allocated to a specific table can also be
+ made available for general re-use by performing a
+ rolling restart of the cluster. See
+ <xref linkend="mysql-cluster-rolling-restart"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Limits imposed by the cluster's configuration</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>imposed by configuration</secondary>
+ </indexterm>
+
+ <para>
+ 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
+ <xref linkend="mysql-cluster-config-file"/>. Most
+ configuration parameters can be upgraded online.
+ These hard limits include:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Database memory size and index memory size
+ (<literal>DataMemory</literal> and
+ <literal>IndexMemory</literal>,
+ respectively).
+ </para>
+
+ <para>
+ <literal>DataMemory</literal> is allocated
+ as 32KB pages. As each
+ <literal>DataMemory</literal> page is used,
+ it is assigned to a specific table; once
+ allocated, this memory cannot be freed
+ except by dropping the table.
+ </para>
+
+ <para>
+ See
+ <xref linkend="mysql-cluster-ndbd-definition"/>,
+ for further information about
+ <literal>DataMemory</literal> and
+ <literal>IndexMemory</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The maximum number of operations that can be
+ performed per transaction is set using the
+ configuration parameters
+ <literal>MaxNoOfConcurrentOperations</literal>
+ and
+ <literal>MaxNoOfLocalOperations</literal>.
+ </para><note><para>
+ Bulk loading, <literal>TRUNCATE
+ TABLE</literal>, and <literal>ALTER
+ TABLE</literal> are handled as special cases
+ by running multiple transactions, and so are
+ not subject to this limitation.
+ </para></note>
+ </listitem>
+
+ <listitem>
+ <para>
+ Different limits related to tables and
+ indexes. For example, the maximum number of
+ ordered indexes per table is determined by
+ <literal>MaxNoOfOrderedIndexes</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+ <title>Memory usage</title>
+ <para>
+ All Cluster table rows are of fixed length. This means
+ (for example) that if a table has one or more
+ <literal>VARCHAR</literal> fields containing only
+ relatively small values, more memory and disk space is
+ required when using the <literal>NDB</literal> storage
+ engine than would be the case for the same table and data
+ using the <literal>MyISAM</literal> engine. (In other
+ words, in the case of a <literal>VARCHAR</literal> column,
+ the column requires the same amount of storage as a
+ <literal>CHAR</literal> column of the same size.)
+ </para></formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Node and data object maximums</title>
+
+ <para>
+ The following limits apply to numbers of cluster
+ nodes and metadata objects:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The maximum number of data nodes is 48.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The total maximum number of nodes in a MySQL
+ Cluster is 63. This number includes all SQL
+ nodes (MySQL Servers), API nodes
+ (applications accessing the cluster other
+ than MySQL servers), data nodes, and
+ management servers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The maximum number of metadata objects in
+ MySQL 5.0 Cluster 20320. This limit is
+ hard-coded.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
</para>
-
- <itemizedlist>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </section>
+ <section id="mysql-cluster-limitations-transactions">
+
+ <title>Limits Relating to Transaction Handling</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>transactions</secondary>
+ </indexterm>
+
+ <para>
+ A number of limitations exist in MySQL Cluster with regard to the
+ handling of transactions. These include the following:
+
+ <itemizedlist>
+
+ <listitem>
+ <formalpara>
+ <title>Transaction isolation level</title>
+ <para>
+ The <literal>NDBCLUSTER</literal> storage engine supports
+ only the <literal>READ COMMITTED</literal> transaction
+ isolation level.
+
+ <important>
<para>
- A <literal>DELETE</literal> statement on an
- <literal>NDB</literal> table makes the memory formerly
- used by the deleted rows available for re-use by
- inserts on the same table only. The memory cannot be
- used by other <literal>NDB</literal> tables.
+ If a <literal>SELECT</literal> from a Cluster table
+ includes a <literal>BLOB</literal> or
+ <literal>TEXT</literal> column, the <literal>READ
+ COMMITTED</literal> 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.
</para>
- </listitem>
-
- <listitem>
- <para>
- A <literal>DROP TABLE</literal> or
- <literal>TRUNCATE</literal> operation on an
- <literal>NDB</literal> table frees the memory that was
- used by this table for re-use by any
- <literal>NDB</literal> table — either by the
- same table or by another <literal>NDB</literal> table.
- </para>
-
- <para>
- (Recall that <literal>TRUNCATE</literal> drops and
- re-creates the table. See <xref linkend="truncate"/>.)
- </para>
-
- <para>
- Memory freed by <literal>DELETE</literal> operations
- but still allocated to a specific table can also be
- made available for general re-use by performing a
- rolling restart of the cluster. See
- <xref linkend="mysql-cluster-rolling-restart"/>.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
+ </important>
+ </para></formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+ <title>Rollbacks</title>
+
<para>
- <emphasis role="bold">Error Reporting</emphasis>:
+ There is no partial rollback of transactions. A duplicate
+ key or similar error rolls back the entire transaction.
</para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A duplicate key error returns the error message
- <errortext>ERROR 23000: Can't write; duplicate key in
- table
- '<replaceable>tbl_name</replaceable>'</errortext>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Like other MySQL storage engines, the
- <literal>NDB</literal> storage engine can handle a
- maximum of one <literal>AUTO_INCREMENT</literal>
- column per table. However, in the case of a Cluster
- table with no explicit primary key, an
- <literal>AUTO_INCREMENT</literal> column is
- automatically defined and used as a
- <quote>hidden</quote> primary key. For this reason,
- you cannot define a table that has an explicit
- <literal>AUTO_INCREMENT</literal> column unless that
- column is also declared using the <literal>PRIMARY
- KEY</literal> option.
- </para>
-
- <para>
- Attempting to create a table with an
- <literal>AUTO_INCREMENT</literal> column that is not
- the table's primary key, and using the
- <literal>NDB</literal> storage engine, fails with an
- error.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+ <title>Transactions and memory usage</title>
+
<indexterm>
<primary>MySQL Cluster limitations</primary>
- <secondary>transactions</secondary>
+ <secondary>memory usage and transaction handling</secondary>
</indexterm>
-
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>transaction handling</secondary>
+ </indexterm>
<para>
- <emphasis role="bold">Transaction Handling</emphasis>:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>NDB Cluster</literal> supports only the
- <literal>READ COMMITTED</literal> transaction
- isolation level.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is no partial rollback of transactions. A
- duplicate key or similar error results in a rollback
- of the entire transaction.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Important</emphasis>: If a
- <literal>SELECT</literal> from a Cluster table
- includes a <literal>BLOB</literal>,
- <literal>TEXT</literal>, or <literal>VARCHAR</literal>
- column, the <literal>READ COMMITTED</literal>
- 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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- As noted elsewhere in this chapter, MySQL Cluster does
- not handle large transactions well; it is better to
- perform a number of small transactions with a few
- operations each than to attempt a single large
- transaction containing a great many operations.
- </para>
-
- <para>
- Among other considerations, large transactions require
- very large amounts of memory. Because of this, the
- transactional behaviour of a number of MySQL
- statements is effected as described in the following
- list:
- </para>
-
- <itemizedlist>
-
- <listitem>
+ As noted elsewhere in this chapter, MySQL Cluster does not
+ handle large transactions well; it is better to perform a
+ number of small transactions with a few operations each than
+ to attempt a single large transaction containing a great
+ many operations. Among other considerations, large
+ transactions require very large amounts of memory. Because
+ of this, the transactional behaviour of a number of MySQL
+ statements is effected as described in the following list:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>TRUNCATE</literal> is not transactional when
+ used on <literal>NDB</literal> tables. If a
+ <literal>TRUNCATE</literal> fails to empty the table,
+ then it must be re-run until it is successful.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>DELETE FROM</literal> (even with no
+ <literal>WHERE</literal> clause)
+ <emphasis>is</emphasis> transactional. For tables
+ containing a great many rows, you may find that
+ performance is improved by using several
+ <literal>DELETE FROM ... LIMIT ...</literal>
+ statements to <quote>chunk</quote> the delete
+ operation. If your objective is to empty the table,
+ then you may wish to use <literal>TRUNCATE</literal>
+ instead.
+ </para>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title><literal>LOAD DATA</literal> statements</title>
+
<para>
- <literal>TRUNCATE</literal> is not transactional
- when used on <literal>NDB</literal> tables. If a
- <literal>TRUNCATE</literal> fails to empty the
- table, then it must be re-run until it is
- successful.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>DELETE FROM</literal> (even with no
- <literal>WHERE</literal> clause)
- <emphasis>is</emphasis> transactional. For tables
- containing a great many rows, you may find that
- performance is improved by using several
- <literal>DELETE FROM ... LIMIT ...</literal>
- statements to <quote>chunk</quote> the delete
- operation. If the objective is to empty the table,
- then you may wish to use
- <literal>TRUNCATE</literal> instead.
- </para>
- </listitem>
-
- <listitem>
- <para>
<literal>LOAD DATA INFILE</literal> is not
transactional when used on <literal>NDB</literal>
- tables. <emphasis>During such an operation, the
- <literal>NDB</literal> engine can and does commit
- at will.</emphasis>
- </para>
-
- <para>
+ tables.
+
+ <important>
+ <para>
+ When executing a <literal>LOAD DATA
+ INFILE</literal> statement, the
+ <literal>NDB</literal> engine can and does
+ commit at will.
+ </para>
+ </important>
+
<literal>LOAD DATA FROM MASTER</literal> is not
supported in MySQL Cluster.
</para>
- </listitem>
-
- <listitem>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title><literal>ALTER TABLE</literal> and transactions</title>
+
<para>
- When copying a table as part of an <literal>ALTER
- TABLE</literal>, the creation of the copy is
- non-transactional. (In any case, this operation is
- rolled back when the copy is deleted.)
+ When copying an <literal>NDB</literal> table as part
+ of an <literal>ALTER TABLE</literal>, the creation
+ of the copy is non-transactional. (In any case, this
+ operation is rolled back when the copy is deleted.)
</para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- <emphasis role="bold">Node Start, Stop, or
- Restart:</emphasis>: Starting, stopping, or restarting
- a node may give rise to temporary errors causing some
- transactions to fail. These include the following
- cases:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- When first starting a node, it is possible that
- you may see Error 1204 <errortext>Temporary
- failure, distribution changed</errortext> and
- similar temporary errors.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The stopping or failure of any data node can
- result in a number of different node failure
- errors. (However, there should be no aborted
- transactions when performing a planned shutdown of
- the cluster.)
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- In either of these cases, any errors that are
- generated must be handled within the application. This
- should be done by retrying the transaction.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>imposed by configuration</secondary>
- </indexterm>
-
- <para>
- 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
- <xref linkend="mysql-cluster-config-file"/>. Most
- configuration parameters can be upgraded online. These
- hard limits include:
+
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
</para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Database memory size and index memory size
- (<literal>DataMemory</literal> and
- <literal>IndexMemory</literal>, respectively).
- </para>
-
- <para>
- <literal>DataMemory</literal> is allocated as 32KB
- pages. As each <literal>DataMemory</literal> page is
- used, it is assigned to a specific table; once
- allocated, this memory cannot be freed except by
- dropping the table.
- </para>
-
- <para>
- See <xref linkend="mysql-cluster-ndbd-definition"/>,
- for further information about
- <literal>DataMemory</literal> and
- <literal>IndexMemory</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of operations that can be performed
- per transaction is set using the configuration
- parameters
- <literal>MaxNoOfConcurrentOperations</literal> and
- <literal>MaxNoOfLocalOperations</literal>. Note that
- bulk loading, <literal>TRUNCATE TABLE</literal>, and
- <literal>ALTER TABLE</literal> are handled as special
- cases by running multiple transactions, and so are not
- subject to this limitation.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Different limits related to tables and indexes. For
- example, the maximum number of ordered indexes per
- table is determined by
- <literal>MaxNoOfOrderedIndexes</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>database objects</secondary>
- </indexterm>
-
- <para>
- Database names, table names and attribute names cannot be
- as long in <literal>NDB</literal> 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 <literal>NDB Cluster</literal> table name is 122
- characters less the number of characters in the name of
- the database of which that table is a part.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- All Cluster table rows are of fixed length. This means
- (for example) that if a table has one or more
- <literal>VARCHAR</literal> fields containing only
- relatively small values, more memory and disk space is
- required when using the <literal>NDB</literal> storage
- engine than would be the case for the same table and data
- using the <literal>MyISAM</literal> engine. (In other
- words, in the case of a <literal>VARCHAR</literal> column,
- the column requires the same amount of storage as a
- <literal>CHAR</literal> column of the same size.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of tables in a Cluster database is
- limited to 1792.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of ordered indexes per cluster,
- including <literal>AUTO_INCREMENT</literal> columns and
- hidden primary keys, is 2048.
- </para>
-
- <para>
- This limitation was lifted in MySQL 5.0.23.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of attributes per table is limited to
- 128.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum permitted size of any one row is 8KB. Note
- that each <literal>BLOB</literal> or
- <literal>TEXT</literal> column contributes a maximum of
- 256 bytes towards this total.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of attributes per key is 32.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>unsupported features</secondary>
- </indexterm>
-
- <para>
- <emphasis role="bold">Unsupported features</emphasis> (do not
- cause errors, but are not supported or enforced):
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The foreign key construct is ignored, just as it is in
- <literal>MyISAM</literal> tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Savepoints and rollbacks to savepoints are ignored as in
- <literal>MyISAM</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>OPTIMIZE</literal> operations are not supported.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LOAD TABLE ... FROM MASTER</literal> is not
- supported.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>performance</secondary>
- </indexterm>
-
- <para>
- <emphasis role="bold">Performance and limitation-related
- issues</emphasis>:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- There are query performance issues due to sequential
- access to the <literal>NDB</literal> storage engine; it is
- also relatively more expensive to do many range scans than
- it is with either <literal>MyISAM</literal> or
- <literal>InnoDB</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The <literal>Records in range</literal> statistic is not
- supported. This may result in non-optimal query plans in
- some cases. If necessary, you can employ <literal>USE
- INDEX</literal> or <literal>FORCE INDEX</literal> to alter
- the execution plan. See <xref linkend="index-hints"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Unique hash indexes created with <literal>USING
- HASH</literal> cannot be used for accessing a table if
- <literal>NULL</literal> is given as part of the key.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>SQL_LOG_BIN</literal> has no effect on data
- operations; however, it is supported for schema
- operations.
- </para>
-
- <para>
- MySQL Cluster cannot produce a binlog for tables having
- <literal>BLOB</literal> columns but no primary key.
- </para>
-
- <para>
- Only the following schema operations are logged in a
- cluster binlog which is <emphasis>not</emphasis> on the
- <command>mysqld</command> executing the statement:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>CREATE TABLE</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ALTER TABLE</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>DROP TABLE</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CREATE DATABASE</literal> / <literal>CREATE
- SCHEMA</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>DROP DATABASE</literal> / <literal>DROP
- SCHEMA</literal>
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <indexterm>
- <primary>MySQL Cluster limitations</primary>
- <secondary>unsupported features</secondary>
- </indexterm>
-
- <para>
- <emphasis role="bold">Missing features</emphasis>:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The only supported isolation level is <literal>READ
- COMMITTED</literal>. (InnoDB supports <literal>READ
- COMMITTED</literal>, <literal>READ UNCOMMITTED</literal>,
- <literal>REPEATABLE READ</literal>, and
- <literal>SERIALIZABLE</literal>.) See
- <xref linkend="mysql-cluster-backup-troubleshooting"/>,
- for information on how this can affect backup and restore
- of Cluster databases.
- </para>
- </listitem>
-
- <listitem>
- <para>
- No durable commits on disk. Commits are replicated, but
- there is no guarantee that logs are flushed to disk on
- commit.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
-
- </itemizedlist>
-
- <section id="mysql-cluster-limitations-syntax">
- <title></title>
+ </formalpara>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+
</section>
- <section id="mysql-cluster-limitations-limits">
- <title></title>
- </section>
-
- <section id="mysql-cluster-limitations-transactions">
- <title></title>
- </section>
-
<section id="mysql-cluster-limitations-error-handling">
- <title></title>
+
+ <title>Error Handling</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>error handling and reporting</secondary>
+ </indexterm>
+
+ <para>
+ Starting, stopping, or restarting a node may give rise to
+ temporary errors causing some transactions to fail. These include
+ the following cases:
+
+ <itemizedlist>
+
+ <listitem>
+ <formalpara>
+ <title>Temporary errors</title>
+ <para>
+ When first starting a node, it is possible that you may see
+ Error 1204 <errortext>Temporary failure, distribution
+ changed</errortext> and similar temporary errors.
+ </para></formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+ <title>Errors due to node failure</title>
+ <para>
+ The stopping or failure of any data node can result in a
+ number of different node failure errors. (However, there
+ should be no aborted transactions when performing a planned
+ shutdown of the cluster.)
+ </para></formalpara>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ <para>
+ In either of these cases, any errors that are generated must be
+ handled within the application. This should be done by retrying
+ the transaction.
+ </para>
+
+ <para>
+ See also <xref linkend="mysql-cluster-limitations-limits"/>.
+ </para>
</section>
<section id="mysql-cluster-limitations-database-objects">
@@ -794,10 +715,13 @@
<listitem>
<formalpara>
<title>Number of tables</title>
- <para>
- The maximum number of <literal>NDB</literal> tables is
- limited to 20320.
- </para></formalpara>
+
+
+ <para>
+ The maximum number of tables in a Cluster database in
+ MySQL 5.0 is limited to 1792.
+ </para>
+ </formalpara>
</listitem>
<listitem><formalpara>
@@ -1401,8 +1325,39 @@
</para></formalpara>
</listitem>
+
+
+ <listitem>
+ <formalpara>
+
+ <title><literal>IGNORE</literal> and <literal>REPLACE</literal> functionality</title>
+
+ <indexterm>
+ <primary>MySQL Cluster limitations</primary>
+ <secondary>INSERT IGNORE, UPDATE IGNORE, and REPLACE statements</secondary>
+ </indexterm>
+
+ <para>
+ In MySQL 5.0.19 and earlier, <literal>INSERT
+ IGNORE</literal>, <literal>UPDATE IGNORE</literal>, and
+ <literal>REPLACE</literal> were supported only for primary
+ keys, but not for unique keys. It was possible to work
+ around this issue by removing the constraint, then
+ dropping the unique index, performing any inserts, and
+ then adding the unique index again.
+ </para>
+
+ </formalpara>
+
+ <para>
+ This limitation was removed for <literal>INSERT
+ IGNORE</literal> and <literal>REPLACE</literal> in MySQL
+ 5.0.20. (See Bug #17431.)
+ </para>
+ </listitem>
+
</itemizedlist>
</section>
- </section>
+</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r6530 - trunk/refman-5.0 | jon | 18 May |
