Author: js221926
Date: 2011-02-21 12:18:35 +0100 (Mon, 21 Feb 2011)
New Revision: 25118
Log:
WL#5738 (Where To Use Cluster, Cluster Compared With InnoDB)
Modified:
trunk/refman-5.1/mysql-cluster-overview.xml
Modified: trunk/refman-5.1/mysql-cluster-overview.xml
===================================================================
--- trunk/refman-5.1/mysql-cluster-overview.xml 2011-02-18 02:23:47 UTC (rev 25117)
+++ trunk/refman-5.1/mysql-cluster-overview.xml 2011-02-21 11:18:35 UTC (rev 25118)
Changed blocks: 1, Lines Added: 852, Lines Deleted: 0; 27406 bytes
@@ -4756,6 +4756,858 @@
</section>
+ <section id="mysql-cluster-compared">
+
+ <title>MySQL Server using <literal>InnoDB</literal> Compared with MySQL Cluster</title>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to standalone MySQL Server</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to InnoDB</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>compared to MySQL Cluster</secondary>
+ </indexterm>
+
+ <remark role="todo">
+ [js] Add one or more sections discussing differences between
+ Cluster and Replication, especially with regard to
+ failover/recovery and scaleout/scaleup strategies. Possibly add
+ new wrapper for it all.
+ </remark>
+
+ <para>
+ MySQL Server offers a number of choices in storage engines. Since
+ both <literal role="se">NDBCLUSTER</literal> and
+ <literal role="se">InnoDB</literal> can serve as transactional
+ MySQL storage engines, users of MySQL Server sometimes become
+ interested in MySQL Cluster. They see
+ <literal role="se">NDB</literal> as a possible alternative or
+ upgrade to the default <literal role="se">InnoDB</literal> storage
+ engine in MySQL 5.5. While <literal role="se">NDB</literal> and
+ <literal role="se">InnoDB</literal> share common characteristics,
+ there are differences in architecture and implementation, so that
+ some existing MySQL Server applications and usage scenarios can be
+ a good fit for MySQL Cluster, but not all of them.
+ </para>
+
+ <para>
+ In this section, we discuss and compare some characteristics of
+ the <literal role="se">NDB</literal> storage engine used by MySQL
+ Cluster with <literal role="se">InnoDB</literal> used in MySQL 5.1
+ and MySQL 5.5. The next few sections provide a technical
+ comparison. In many instances, decisions about when and where to
+ use MySQL Cluster must be made on a case-by-case basis, taking all
+ factors into consideration. While it is beyind the scope of this
+ documentation to provide specifics for every conceivable usage
+ scenario, we also attempt to offer some very general guidance on
+ the relative suitability of some common types of applications for
+ <literal role="se">NDB</literal> as opposed to
+ <literal role="se">InnoDB</literal> backends.
+ </para>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>availability</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>availability</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engines</primary>
+ <secondary>availability</secondary>
+ </indexterm>
+
+ <para>
+ While it is possible to use <literal role="se">InnoDB</literal>
+ tables with MySQL Cluster, such tables are not clustered. It is
+ also not always possible to use the latest MySQL Cluster and
+ <literal role="se">InnoDB</literal> features and enhancements
+ together due to availability issues. Currently, the latest MySQL
+ Cluster NDB 7.1 releases use a <command>mysqld</command> based on
+ MySQL 5.1; while the most recent
+ <literal role="se">InnoDB</literal> enhancements are available
+ only in MySQL Server 5.5, where MySQL Cluster is not currently
+ supported. It is also not possible to use programs or libraries
+ from a MySQL Cluster NDB 6.x or MySQL Cluster NDB 7.x distribution
+ with MySQL Server 5.1 or MySQL Server 5.5, or the reverse.
+ </para>
+
+ <para>
+ While it is also true that some types of common business
+ applications can be run either on MySQL Cluster or on MySQL Server
+ (most likely using the <literal role="se">InnoDB</literal> storage
+ engine), there are some important architectural and implementation
+ differences. <xref linkend="mysql-cluster-ndb-innodb-engines"/>,
+ provides a summary of the these differences. Due to the
+ differences, some usage scenarios are clearly more suitable for
+ one engine or the other; see
+ <xref linkend="mysql-cluster-ndb-innodb-workloads"/>. This in turn
+ has an impact on the types of applications that better suited for
+ use with <literal role="se">NDB</literal> or
+ <literal role="se">InnoDB</literal>. See
+ <xref linkend="mysql-cluster-ndb-innodb-usage"/>, for a comparison
+ of the relative suitability of each for use in common types of
+ database applications.
+ </para>
+
+ <para>
+ See <xref linkend="storage-engines"/>, for additional information
+ about MySQL storage engines.
+ </para>
+
+ <section id="mysql-cluster-ndb-innodb-engines">
+
+ <title>Differences Between the <literal role="se">NDB</literal> and
+ <literal role="se">InnoDB</literal> Storage Engines</title>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to standalone MySQL Server</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to InnoDB</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>compared to MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engines</primary>
+ <secondary>differences between NDB and InnoDB</secondary>
+ </indexterm>
+
+ <para>
+ The MySQL Cluster <literal role="se">NDB</literal> storage
+ engine is implemented using a distributed, shared-nothing
+ architecture, which causes it to behave differently from
+ <literal role="se">InnoDB</literal> in a number of ways. For
+ those unaccustomed to working with
+ <literal role="se">NDB</literal>, unexpected behaviors can arise
+ due to its distributed nature with regard to transactions,
+ foreign keys, joins, and other characteristics. These are shown
+ in the following table:
+ </para>
+
+ <informaltable>
+ <tgroup cols="3">
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <thead>
+ <row>
+ <entry><para>
+ Feature
+ </para></entry>
+ <entry><para>
+ <literal role="se">InnoDB</literal>
+ </para></entry>
+ <entry><para>
+ MySQL Cluster (<literal role="se">NDB</literal>)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><para>
+ <emphasis>MySQL Server Availability</emphasis>
+ </para></entry>
+ <entry><para>
+ 5.5 (in latest MySQL 5.5 GA release)
+ </para></entry>
+ <entry><para>
+ 5.1 (SQL nodes currently based on MySQL 5.1.51 GA)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis><literal>InnoDB</literal> Availability
+ </emphasis>
+ </para></entry>
+ <entry><para>
+ <literal role="se">InnoDB</literal> 1.1 plugin
+ </para></entry>
+ <entry><para>
+ <literal role="se">InnoDB</literal> 1.0 storage engine
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Storage Limits</emphasis>
+ </para></entry>
+ <entry><para>
+ 64TB
+ </para></entry>
+ <entry><para>
+ 2TB
+ </para>
+
+
+
+ <para>
+ (Practical upper limit for database size)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Foreign Keys</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ No
+ </para>
+
+
+
+ <para>
+ (Foreign keys ignored, as with
+ <literal role="se">MyISAM</literal>)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Transactions</emphasis>
+ </para></entry>
+ <entry><para>
+ All standard types
+ </para></entry>
+ <entry><para>
+ Only <literal role="isolevel">READ COMMITTED</literal>
+ supported
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>MVCC Non-Blocking Reads</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (Read concurrency without locks)
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Multi-Table Join Performance</emphasis>
+ </para></entry>
+ <entry><para>
+ Good
+ </para></entry>
+ <entry><para>
+ Poor
+ </para>
+
+
+
+ <para>
+ (Push-down joins can improve MySQL Cluster
+ performance, but <literal role="se">InnoDB</literal>
+ provides higher throughput)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Data Compression</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ No
+ </para>
+
+
+
+ <para>
+ (MySQL Cluster checkpoint and backup files can be
+ compressed)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Large Row Support (> 8K)</emphasis>
+ </para></entry>
+ <entry><para>
+ Supported for
+ <literal role="type">VARBINARY</literal>,
+ <literal role="typr">VARCHAR</literal>,
+ <literal role="type">BLOB</literal>, and
+ <literal role="type">TEXT</literal> columns
+ </para></entry>
+ <entry><para>
+ Supported for <literal role="type">BLOB</literal> and
+ <literal role="type">TEXT</literal> columns only
+ </para>
+
+
+
+ <para>
+ (Using these types to store very large amounts of data
+ can lower MySQL Cluster performance)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Virtualization</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (Can be deployed on both physical and virtual
+ infrastructure)
+ </para></entry>
+ <entry><para>
+ No
+ </para>
+
+
+
+ <para>
+ (Not supported in virtual environments)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Minimum Number of Physical Hosts for
+ Redundancy </emphasis>
+ </para></entry>
+ <entry><para>
+ <emphasis>2</emphasis>: 1 active, 1 passive
+ </para></entry>
+ <entry><para>
+ <emphasis>3</emphasis>: 2 data nodes, 1 management
+ node
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Availability (HA)</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes (99.999%)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Node Failure Recovery and
+ Failover</emphasis>
+ </para></entry>
+ <entry><para>
+ Requires additional software
+ </para></entry>
+ <entry><para>
+ Automatic
+ </para>
+
+
+
+ <para>
+ (Key element in MySQL Cluster architecture)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Time for Node Failure Recovery</emphasis>
+ </para></entry>
+ <entry><para>
+ 30 seconds to several hours
+ </para></entry>
+ <entry><para>
+ < 1 second
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Real-Time Performance</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes (Low latency)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>In-Memory Storage</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (Some data can optionally be stored on disk; both
+ in-memory and disk data storage are durable)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Direct (non-SQL) API Access to Storage
+ Engine </emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (Faster access possible when bypassing SQL interface
+ to data)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Scalability</emphasis>
+ </para></entry>
+ <entry><para>
+ Poor
+ </para>
+
+
+
+ <para>
+ (Requires application-level partitioning)
+ </para></entry>
+ <entry><para>
+ Good
+ </para>
+
+
+
+ <para>
+ (Data already partitioned by MySQL Cluster; schema and
+ query optimization required to maximize scaling of
+ writes)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Concurrent and Parallel Writes</emphasis>
+ </para></entry>
+ <entry><para>
+ Not supported
+ </para></entry>
+ <entry><para>
+ Up to 48 writers, optimized for concurrent writes
+ </para>
+
+
+
+ <para>
+ (MySQL Cluster supports up to 48 data nodes for
+ processing, with 24 nodes for storage, due to
+ requirements for two-fold redundancy)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Conflict Detection and Resolution (Multiple
+ Replication Masters)</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (True active-active geographic replication, in which
+ each application node can read or write to its own
+ local database)
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Hash Indexes</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes
+ </para>
+
+
+
+ <para>
+ (Can provide fast access to key/value data)
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ </section>
+
+ <section id="mysql-cluster-ndb-innodb-workloads">
+
+ <title><literal role="se">NDB</literal> and <literal role="se">InnoDB</literal>
+ Workloads</title>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to standalone MySQL Server</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to InnoDB</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>compared to MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>applications supported</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>applications supported</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engines</primary>
+ <secondary>applications supported</secondary>
+ </indexterm>
+
+ <para>
+ MySQL Cluster has a range of unique attributes that make it
+ ideal to serve applications requiring high availability, fast
+ failover, high throughput, and low latency. Due to its real-time
+ nature, distributed architecture, and multi-node implementation,
+ MySQL Cluster also has specific constraints that may keep some
+ workloads from performing well. A number of major differences in
+ behavior between the <literal role="se">NDB</literal> and
+ <literal role="se">InnoDB</literal> storage engines with regard
+ to some common types of database-driven application workloads
+ are shown in the following table:
+ </para>
+
+ <informaltable>
+ <tgroup cols="3">
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <thead>
+ <row>
+ <entry><para>
+ Workload
+ </para></entry>
+ <entry><para>
+ <literal role="se">InnoDB</literal>
+ </para></entry>
+ <entry><para>
+ MySQL Cluster (<literal role="se">NDB</literal>)
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><para>
+ <emphasis>In-Network Telecoms Applications (HLR, HSS,
+ SDP)</emphasis>
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Packaged Applications</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Access should be mostly by primary key
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Custom Applications</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>OLTP Applications</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>DSS Applications (data marts,
+ analytics)</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ No
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Content Management</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Limited support
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>Web Session Management</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>E-Commerce Applications</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ <row>
+ <entry><para>
+ <emphasis>User Profile Management, AAA
+ Protocol</emphasis>
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ <entry><para>
+ Yes
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ </section>
+
+ <section id="mysql-cluster-ndb-innodb-usage">
+
+ <title><literal role="se">NDB</literal> and <literal role="se">InnoDB</literal>
+ Feature Usage Summary</title>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to standalone MySQL Server</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>compared to InnoDB</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>compared to MySQL Cluster</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engines</primary>
+ <secondary>usage scenarios</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>and application feature requirements</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>InnoDB</primary>
+ <secondary>and application feature requirements</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engines</primary>
+ <secondary>and application feature requirements</secondary>
+ </indexterm>
+
+ <para>
+ When comparing application feature requirements to the
+ capabilities of <literal role="se">InnoDB</literal> with
+ <literal role="se">NDB</literal>, some are clearly more
+ compatible with one storage engine than the other. For example,
+ since <literal role="se">NDB</literal> does not support foreign
+ keys, an application that requires them and cannot be
+ re-engineered to remove this requirement is likely not to be a
+ good match for MySQL Cluster.
+ </para>
+
+ <para>
+ The following table shows required supported features for
+ applications according to which of these two storage engines
+ each of them is usually better suited:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <thead>
+ <row>
+ <entry><para>
+ Application requirements better supported with
+ <literal role="se">InnoDB</literal>
+ </para></entry>
+ <entry><para>
+ Application requirements better supported with
+ <literal role="se">NDB</literal>
+ </para></entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><itemizedlist>
+
+ <listitem>
+ <para>
+ Foreign keys
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Large or complex joins
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Very large datastores or transactions
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Transactions other than
+ <literal role="isolevel">READ COMMITTED</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Preference for SQL interface to data
+ </para>
+ </listitem>
+
+ </itemizedlist></entry>
+ <entry><itemizedlist>
+
+ <listitem>
+ <para>
+ Real-time concurrency
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ High availability, high-speed failover
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Fast low-level APIs (see
+ <xref linkend="mysql-cluster-api-overview"/>)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Primary-key lookups, key/value data
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Limited use of <literal role="type">BLOB</literal>
+ columns
+ </para>
+ </listitem>
+
+ </itemizedlist></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ </section>
+
+ </section>
+
<section id="mysql-cluster-limitations">
<title>Known Limitations of MySQL Cluster</title>
| Thread |
|---|
| • svn commit - mysqldoc@oter02: r25118 - trunk/refman-5.1 | jon.stephens | 21 Feb |
