List:Commits« Previous MessageNext Message »
From:jon.stephens Date:February 21 2011 11:18am
Subject:svn commit - mysqldoc@oter02: r25118 - trunk/refman-5.1
View as plain text  
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 (&gt; 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>
+                  &lt; 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.1jon.stephens21 Feb