mysql集群的最佳实践 Best Practices For MySQL Cluster (文档 ID 1926680.1)

约定不等于承诺〃 2022-08-19 05:58 16阅读 0赞

本文来自oracle官方support

<table> 
 <tbody> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; padding:0px 6px 0px 0px; font-weight:bold">Best Practices For MySQL Cluster (文档 ID 1926680.1)</span></td> 
   <td align="right">&nbsp;</td> 
   <td><img src="https://support.oracle.com/epmos/adf/images/t.gif" alt="" width="20" height="10"></td> 
  </tr> 
 </tbody> 
</table>

--------------------

![t.gif][]

<table style="table-layout:fixed; overflow:hidden"> 
 <tbody> 
  <tr> 
   <td style="overflow:hidden; vertical-align:top"> 
    <div style="overflow:auto"> 
     <span style="font-size:12px"></span> 
     <p><strong>In this Document</strong><br> </p> 
     <table> 
      <tbody> 
       <tr> 
        <td></td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#PURPOSE" rel="nofollow">Purpose</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#SCOPE" rel="nofollow">Scope</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#BODYTEXT" rel="nofollow">Details</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section31" rel="nofollow">Architecture and Hardware</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section32" rel="nofollow">General Performance Considerations</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section33" rel="nofollow">Configuration of mysqld Nodes</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section34" rel="nofollow">Binary Logging</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section35" rel="nofollow">Indexes</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section36" rel="nofollow">BLOB/TEXT Columns</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section37" rel="nofollow">Disk Data</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section38" rel="nofollow">Access Methods</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section39" rel="nofollow">Monitoring</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#aref_section310" rel="nofollow">Backups</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <table> 
      <tbody> 
       <tr> 
        <td>&nbsp;</td> 
        <td><a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=160rvlpcqs_4&amp;_afrLoop=349639795075598#REF" rel="nofollow">References</a></td> 
       </tr> 
      </tbody> 
     </table> 
     <hr> 
     <p></p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> APPLIES TO:</h2> MySQL Cluster - Version 7.1 and later 
     <br> Information in this document applies to any platform. 
     <br> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>PURPOSE</h2> 
     <p>Provide an overview of the best practices for MySQL Cluster.</p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SCOPE</h2> 
     <p><span>Detailed general guidance for DBAs already familiar with MySQL Cluster seeking guidance for considerations how to use MySQL Cluster the best way.</span></p> 
     <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>DETAILS</h2> 
     <p>MySQL Cluster is a real-time transactional mostly in-memory storage engine. These three characteristics are the main components shaping how MySQL Cluster is best used.<br> <br> In general the many of the best practice advices that apply to other database systems such as InnoDB also apply to MySQL Cluster, but the real-time design of MySQL Cluster will in general magnifies the advices:</p> 
     <ul> 
      <li>keep transactions small - in Cluster all the transaction related objects are stored in memory,&nbsp;so large transactions directly reflects the amount of memory the data nodes require</li> 
      <li>do not leave transactions uncommitted</li> 
      <li>use indexes to reduce the cost of queries: 
       <ul> 
        <li>Primary Lookups are the cheapest - this is a strong point of MySQL Cluster.</li> 
        <li>Unique key lookups are in general cheaper than non-unique indexes.</li> 
        <li>Non-unique indexes lookups and scans are better than table scans.</li> 
       </ul> </li> 
      <li>the application should be prepared to handle temporary errors such as lock wait timeout</li> 
     </ul> 
     <p>&nbsp;</p> 
     <p>Additionally it applies to NDBCluster that all tables must have a Primary Key; if none are added explicitly a hidden Primary Key is added. The hidden Primary Key cannot be used in connection with BLOB/TEXT columns and will not be the same on the master and slave when replication is set up. As MySQL Cluster only supports ROW based replication, an explicit Primary Key or not-NULL unique index is required for optimal performance. So it is important always to explicitly define a Primary Key.<br> <br> It is also important to keep in mind that the MySQL Cluster data nodes allocate all memory up front. This is to ensure that delays do not occur if more memory is needed as that would violate the real-time promise. As transactions, operations, tables, etc. also requires memory, allocations must be specified those capacity settings up front as well. This means that capacity planning is important to avoid query failures.<br> <br> The recommendations in&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1678101.1" title="MySql Performance Tuning Best Practices" rel="nofollow">MySQL Performance Tuning Best Practices</a>&nbsp;also holds true for MySQL Cluster.<br> <br> In the following sections various aspects of MySQL Cluster will be considered in more details.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Architecture and Hardware</h3> 
     <p>The following are important considerations for setting up a MySQL Cluster:</p> 
     <ul> 
      <li>Make sure nodes using for arbitration are hosted on different physical hardware than the data nodes. Otherwise failure handling may not work as expected. For example if the arbitrator is on the same physical server as half the data nodes, and the server is shut down, it will cause a total cluster outage as the remaining data nodes cannot contact the arbitrator. In this connection it is important to note that only one node (management node in general) is arbitrator at a time and the node being the arbitrator will not change while handling a failure of one or more nodes.</li> 
      <li>Data nodes may be hosted on the same physical server, but you should have the following in mind: 
       <ul> 
        <li>Make sure the server has enough memory and CPU cores to avoid swapping and contention.</li> 
        <li>More so than for a single data node per host, consider using&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-threadconfig" title="MySQL Reference Manual - ThreadConfig" rel="nofollow">ThreadConfig</a>&nbsp;to lock the data node threads to CPU cores to avoid contention between the data nodes. This requires the use of ndbmtd.</li> 
       </ul> </li> 
      <li>If CPU locking is configured be aware of: 
       <ul> 
        <li>Non-ndbmtd processes also uses CPU, so these should be locked to other CPU cores or excluded from the ones used by ndbtmd.</li> 
        <li>If you have hyperthreading enabled, make sure the two threads locked to the same physical CPU can share the instruction cache in the L2 cache. This can for example be achieved by assigning two LDM threads. Alternatively if you cannot use all CPUs, ensure that the other virtual core is not used.</li> 
       </ul> </li> 
      <li>It is preferred to use physical hosts rather than virtual hardware. The reason for this is that it is easier to predict the performance and avoid interference between virtual machines on the same physical host. This is true for all database systems, but in particularly for MySQL Cluster as it aims to promise real-time performance.</li> 
      <li>While MySQL Cluster is primarily an in-memory database, unless you are using&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-diskless" title="MySQL Reference Manual - Diskless" rel="nofollow">diskless mode</a>, a lot of disk activity will still happen. This means you should ensure that you disk I/O bandwidth is high enough to be able to sustain the I/O generated by the data nodes. You should consider spreading the I/O activity across multiple disks to avoid saturation the disks. Activities causing I/O includes: 
       <ul> 
        <li>Local Checkpoints (LCP) to make the data persistent</li> 
        <li>the&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1469569.1" title="MySQL Cluster: How does the REDO Buffer and Log Work and What Parameters are Available for controlling it? RedoBuffer; TimeBetweenLocalCheckpoints; NoOfFragmentLogFiles; FragmentLogFileSize" rel="nofollow">REDO log</a>&nbsp;to store data changes made between LCPs</li> 
        <li>backups</li> 
        <li>Disk data</li> 
       </ul> </li> 
      <li>As MySQL Cluster is a distributed system the network can easily become a bottleneck. Make sure you have sufficient bandwidth and consider having&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-tcp-definition-direct.html" title="MySQL Reference Manual - MySQL Cluster TCP/IP Connections Using Direct Connections" rel="nofollow">direct links between data nodes</a>. A 10Gbit network can quickly become too slow for a high performance cluster. For the same reason it is recommended that the data nodes have their own dedicated network.</li> 
      <li>MySQL Cluster does not encrypt data send between nodes or require login to connect (other than if you connect through mysqld). So make sure that the cluster is appropriately isolated and behind a firewall.</li> 
     </ul> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>General Performance Considerations</h3> 
     <p>The following general performance considerations should be considered when using MySQL Cluster:</p> 
     <ul> 
      <li>MySQL Cluster works best with single table Primary Key lookups.</li> 
      <li>While MySQL Cluster 7.2 and later has much improved support for JOINs, due to the distributed nature of the data nodes JOINs are still relatively more expensive than for non-distributed systems.</li> 
      <li>Range scans have relatively poor performance compared to InnoDB and MyISAM.</li> 
      <li>NULLs are not supported for unique hash index lookups (see also below).</li> 
      <li>In MySQL Cluster 7.2 and later use&nbsp;<a href="https://dev.mysql.com/doc/en/analyze-table.html" title="MySQL Reference Manual - ANALYZE TABLE Syntax" rel="nofollow">ANALYZE TABLE</a>&nbsp;to calculate index statistics. Note these are not persistent.</li> 
      <li>The Records in range statistics are not as reliable as for e.g. InnoDB. Use&nbsp;<a href="https://dev.mysql.com/doc/refman/5.6/en/index-hints.html" title="MySQL Reference Manual - Index Hint Syntax" rel="nofollow">index hints</a>&nbsp;to change the execution plan if a non-optimal index has been chosen.</li> 
      <li>Both LCPs and backups support compression. This can be used to reduce I/O at the cost of increased CPU usage.</li> 
      <li>Consider denormalising the schema to reduce the number of JOINs.</li> 
      <li>mysqld nodes and the NDB API are partition aware, so by choosing a column for partitioning that allows partition pruning, you can reduce the number of data nodes used for a query and thus the amount of messaged required.</li> 
      <li>If you have tables that does not need to retain their data when the cluster is restarted, create the table with the&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-system-variables.html#sysvar_ndb_table_no_logging" title="MySQL Reference Manual - ndb_table_no_logging" rel="nofollow">ndb_table_no_logging</a>&nbsp;option enabled to avoid that LCPs will include it. For example:<br> 
       <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
        <pre>mysql&gt; SET SESSION ndb_table_no_logging = ON;
Query OK, 0 rows affected (0.58 sec)

mysql&gt; CREATE TABLE t_nologgin (
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; id int unsigned NOT NULL auto_increment PRIMARY KEY,
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; val varchar(10) NOT NULL DEFAULT ''
&nbsp;&nbsp;&nbsp; -&gt; ) ENGINE=NDBCluster;
Query OK, 0 rows affected (1.07 sec)

mysql&gt; SET SESSION ndb_table_no_logging = OFF;
Query OK, 0 rows affected (0.00 sec)</pre> 
       </div> </li> 
      <li>Use batching: for example instead of five&nbsp;<span style="font-family:'Courier New',Courier,monospace">INSERT</span>&nbsp;statements each inserting one row, use one&nbsp;<span style="font-family:'Courier New',Courier,monospace">INSERT</span>statement inserting all five rows. Though bear in mind that relatively small transactions are preferred.</li> 
      <li>Keep transactions small. As a rule of thumb try not to change more that 1000-10000 rows per transaction.</li> 
      <li>Consider enabling&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-system-variables.html#sysvar_transaction_allow_batching" title="MySQL Reference Manual - transaction_allow_batching" rel="nofollow">transaction_allow_batching</a>&nbsp;for transactions supporting it. For example:<br> 
       <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
        <pre>mysql&gt; SET SESSION ndb_table_no_logging = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET SESSION autocommit = OFF;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; SET SESSION transaction_allow_batching = ON;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO t1 (val) VALUES ('a');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql&gt; INSERT INTO t1 (val) VALUES ('b');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql&gt; INSERT INTO t1 (val) VALUES ('c');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql&gt; INSERT INTO t1 (val) VALUES ('d');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql&gt; UPDATE t1 SET val = 'a1' WHERE id = 1;
Query OK, 0 rows affected (0.04 sec)
Rows matched: 0&nbsp; Changed: 0&nbsp; Warnings: 0

mysql&gt; COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET SESSION autocommit = ON;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET SESSION transaction_allow_batching = OFF;
Query OK, 0 rows affected (0.00 sec)</pre> 
       </div> 
       <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)">
         Note: Be sure to read the documentation for transaction_allow_batching before using it. 
       </div> </li> 
      <li>Do not enable the Query Cache. It is very expensive to invalidate it across all the mysqld nodes. A write on either server will have to invalidate the caches on all other mysqld nodes.</li> 
      <li>If you need to generate large reports (OLAP) consider creating an InnoDB slave of the Cluster.</li> 
     </ul> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Configuration of mysqld Nodes</h3> 
     <p>If you are using mysqld nodes to execute queries, you can consider the following options for a general performance improvement:</p> 
     <ul> 
      <li><a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1362940.1" title="MySQL Cluster Option: ndb_autoincrement_prefetch_sz" rel="nofollow">ndb_autoincrement_prefetch_sz</a>&nbsp;- can be used to improve auto-increment performance. The higher, the less auto-increment overhead but also the more gaps and interleaving of auto-increment values.</li> 
      <li><a href="https://dev.mysql.com/doc/en/mysql-cluster-program-options-mysqld.html#option_mysqld_ndb-cluster-connection-pool" title="MySQL Reference Manual - ndb-cluster-connection-pool" rel="nofollow">ndb-cluster-connection-pool</a>&nbsp;- this specified the number of connections the mysqld node makes to the data nodes. The more connections, the better the mysqld node can support concurrent queries on NDBCluster tables. As a rule of thumb, set this to the number of CPU cores available for the mysqld node.<br> <br> 
       <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)">
         Note: each connection requires an mysqld/API node id. So you need to make sure the cluster configuration file has enough mysqld/API nodes configured for the&nbsp; 
        <span style="font-family:'Courier New',Courier,monospace">ndb-cluster-connection-pool</span>setting chosen. 
       </div> </li> 
     </ul> 
     <p>While it can seem like increasing the option&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-program-options-mysqld.html#option_mysqld_ndb-batch-size" title="MySQL Reference Manual - ndb-batch-size" rel="nofollow">ndb-batch-size</a>&nbsp;from its default value will improve performance, it is recommended to leave it at its default value, as large values can easily&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1904610.1" title="Setting ndb-batch-size Causes: Error 4012 'Request ndbd time-out, maybe due to high load or communication problems'; Error 218 'Out of LongMessageBuffer'; Error 1218 'Send Buffers overloaded in NDB kernel'" rel="nofollow">cause temporary overloads</a>&nbsp;on the data nodes causing queries to fail.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Binary Logging</h3> 
     <p>Because updates can happen in multiple API nodes concurrently, mysqld nodes that have the binary log enabled, will not only have to write the binary log for a single node. This increases the load on the binlogging mysqld nodes compared to non-Cluster MySQL instances.<br> <br> For this reason, it is recommended to dedicate mysqld nodes to handle the binary logging and not use those to execute queries. It is also recommended to ensure that the binary logs are not written to the same disk system as is used for other uses.<br> <br> That each binlogging mysqld node logs for all API nodes also means that restarting the mysqld node will cause&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1024297.1" title="Why does the Slave report LOST_EVENTS and what to do?" rel="nofollow">gaps in the binary log</a>&nbsp;if any other node makes any data changes while mysqld is offline. For that reason it is recommended to have two binlogging mysqld nodes, so there can be at least one online at all times. See also:&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-replication-two-channels.html" title="MySQL Reference Manual - Using Two Replication Channels for MySQL Cluster Replication" rel="nofollow">Using Two Replication Channels for MySQL Cluster Replication</a>.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Indexes</h3> 
     <p>When adding indexes to NDBCluster tables in addition to consider which indexes can aid your queries, you should also take into consideration that MySQL Cluster supports two types of indexes:</p> 
     <ul> 
      <li>Hash indexes - these are required for unique indexes (including the Primary Key) and are stored in IndexMemory. Hash indexes are used for equality comparisons (e.g.&nbsp;<span style="font-family:'Courier New',Courier,monospace">WHERE val = 1</span>) as an index lookup using a hash is cheap compared to finding a row using an ordered index.<br> <br> Note that NDBCluster implements unique indexes by storing the index values as the Primary Key in a separate table. This has the following consequences: 
       <ul> 
        <li><span style="font-family:'Courier New',Courier,monospace">NULL</span>&nbsp;values for unique indexes can't be stored in the unique index tables, so index access with&nbsp;<span style="font-family:'Courier New',Courier,monospace">NULL</span>&nbsp;results in a full ordered index or table scan.</li> 
        <li>Using a unique key for an index lookup involves using the index table as well. This makes a Primary Key lookup cheaper than the corresponding unique key lookup.</li> 
       </ul> </li> 
      <li>Ordered indexes - this is for non-unique indexes and is optional for unique indexes (added by default). Ordered indexes are particular useful for range conditions and inequality comparisons.</li> 
     </ul> 
     <p>That the indexes are stored in their own tables can also be seen from the following example:</p> 
     <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
      <pre>mysql&gt; CREATE TABLE t1 (
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; id int unsigned NOT NULL,
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; val int NOT NULL,
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; PRIMARY KEY (id),
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; UNIQUE KEY (val)
&nbsp;&nbsp;&nbsp; -&gt; ) ENGINE=NDBCluster;
Query OK, 0 rows affected (0.21 sec)

shell$ ndb_show_tables
id&nbsp;&nbsp;&nbsp; type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; state&nbsp;&nbsp;&nbsp; logging database&nbsp;&nbsp;&nbsp;&nbsp; schema&nbsp;&nbsp; name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;
...
12&nbsp;&nbsp;&nbsp; UserTable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Online&nbsp;&nbsp; Yes&nbsp;&nbsp;&nbsp;&nbsp; db1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; def&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; t1
13&nbsp;&nbsp;&nbsp; OrderedIndex&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Online&nbsp;&nbsp; No&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sys&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; def&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PRIMARY
14&nbsp;&nbsp;&nbsp; OrderedIndex&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Online&nbsp;&nbsp; No&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sys&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; def&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val
15&nbsp;&nbsp;&nbsp; UniqueHashIndex&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Online&nbsp;&nbsp; Yes&nbsp;&nbsp;&nbsp;&nbsp; sys&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; def&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val$unique</pre> 
     </div> 
     <p>Note how the&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-programs-ndb-show-tables.html" title="MySQL Reference Manual - ndb_show_tables — Display List of NDB Tables" rel="nofollow">ndb_show_tables</a>&nbsp;output has two table entries for the&nbsp;<em>val</em>&nbsp;index: one for the ordered index (which isn't actually a table), and one for the unique hash index (which is using a table). You can also see that the Primary Key does not have an additional table for the unique hash index. That's not needed as the unique has tables for secondary unique indexes are used to store the index values as the Primary Key.<br> <br> That the secondary unique indexes creates an additional table also means that when you configure<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnooftables" title="MySQL Reference Manual - MaxNoOfTables" rel="nofollow">MaxNoOfTables</a>, you should also take the number of unique indexes into account.<br> <br> The support for two index types is worth having in mind when adding unique indexes. If you only need the unique index for equality comparisons (including the constraint checks), you can save memory (remember all indexes are stored in-memory for NDBCluster tables) by only creating the hash index. You can do that with the&nbsp;<span style="font-family:'Courier New',Courier,monospace">USING HASH</span>&nbsp;clause when creating the index, for example:</p> 
     <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
      <pre>CREATE TABLE t1 (
&nbsp;&nbsp; id int unsigned NOT NULL,
&nbsp;&nbsp; val int NOT NULL,
&nbsp;&nbsp; PRIMARY KEY (id),
&nbsp;&nbsp; UNIQUE KEY (val) USING HASH
) ENGINE=NDBCluster;</pre> 
     </div> 
     <p>This will also help reduce the value needed for&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnooforderedindexes" title="MySQL Reference Manual - MaxNoOfOrderedIndexes" rel="nofollow">MaxNoOfOrderedIndexes</a>.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>BLOB/TEXT Columns</h3> 
     <p>MySQL Cluster implements BLOB/TEXT columns by storing the first 256 bytes (plus an 8 byte pointer) in the main table and the rest of the field in a complementary table with one complementary table per BLOB/TEXT column. These tables can for example be seen from the&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-programs-ndb-desc.html" title="MySQL Reference Manual - ndb_desc — Describe NDB Tables" rel="nofollow">ndb_desc</a>&nbsp;output:</p> 
     <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241)"> 
      <pre>mysql&gt; CREATE TABLE t2 (
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; id int unsigned NOT NULL,
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; val TEXT,
&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;&nbsp; PRIMARY KEY (id)
&nbsp;&nbsp;&nbsp; -&gt; ) ENGINE=NDBCluster;
Query OK, 0 rows affected (0.16 sec)

mysql&gt; INSERT INTO t2 VALUES (1, REPEAT('a', 4096));
Query OK, 1 row affected (0.06 sec)

shell$ ndb_desc --extra-partition-info --blob-info --database=db1 t2
-- t2 --
...
-- Attributes --
id Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
val Text(256,2000,0;latin1_swedish_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_28_1
-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex
-- Per partition info for t2 --
Partition&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Row count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Commit count&nbsp;&nbsp;&nbsp; Frag fixed memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Frag varsized memory&nbsp;&nbsp;&nbsp; Extent_space&nbsp;&nbsp;&nbsp; Free extent_space
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0

-- Per partition info for NDB$BLOB_28_1 --
Partition&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Row count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Commit count&nbsp;&nbsp;&nbsp; Frag fixed memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Frag varsized memory&nbsp;&nbsp;&nbsp; Extent_space&nbsp;&nbsp;&nbsp; Free extent_space
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32768&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0</pre> 
     </div> 
     <p>There are a few points to note about the above output:</p> 
     <ul> 
      <li>For the val column note "<span style="font-family:'Courier New',Courier,monospace">BT=NDB$BLOB_28_1</span>". This is the name of the complementary BLOB table.</li> 
      <li>From the "Per partition info for t2" it can be seen there is 1 row, but the&nbsp;<span style="font-family:'Courier New',Courier,monospace">NDB$BLOB_28_1</span>has two rows. This is because each row in the complementary BLOB table will hold at most 2000 bytes, i.e. in this case: 
       <ul> 
        <li>The first 256 bytes are stored in the main table.</li> 
        <li>Bytes 257 to 2256 are stored in the first row of the complementary BLOB table.</li> 
        <li>Bytes 2257 to 4096 are stored in the second row of the complementary BLOB table.</li> 
       </ul> </li> 
     </ul> 
     <p>The consequences of this are that the use of BLOB/TEXT columns both increases the number of tables needed and the number of rows to read. The additional tables involved in queries using BLOB/TEXT columns also causes MySQL Cluster to&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-limitations-transactions.html" title="MySQL Reference Manual - Limits Relating to Transaction Handling in MySQL Cluster" rel="nofollow">use additional locking</a>.</p> 
     <p>For these reasons it is important to limit the BLOB/TEXT usage as much as possible. For example do not use&nbsp;<span style="font-family:'Courier New',Courier,monospace">SELECT *</span>&nbsp;on a table with one or more BLOB/TEXT columns if you do not actually need the BLOB/TEXT data in the result.</p> 
     <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)">
       Note: It is in general recommended to avoid the use of SELECT *, but it is particularly the case for tables containing BLOB/TEXT columns. 
     </div> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Disk Data</h3> 
     <p>While MySQL Cluster originally was an entirely in-memory database, it has since MySQL Cluster 5.1.6 been possible to store non-indexed data on disk. This reduces the memory requirements, but if you use disk data have the following in mind:</p> 
     <ul> 
      <li>Only non-indexes columns can be stored on disk, so if most or all of the columns in a table are indexed, the benefit of disk data may be small or not existing at all.</li> 
      <li>Accessing disk data is slower and more complex than in-memory data, so there will be a performance impact. Configure&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#mysql-cluster-ndbd-definition-disk-data-parameters" title="MySQL Reference Manual - Disk Data Configuration Parameters" rel="nofollow">Disk Data Configuration Parameters</a>&nbsp;to reduce the impact of accessing the disks.</li> 
      <li>Data stored on disk is with the exception of BLOB/TEXT columns always fixed width. This will increase the amount of storage required, so make sure columns are not defined wider than necessary.</li> 
      <li>Each column uses 8 bytes in memory for a pointer to the disk data (BLOB/TEXT uses 256 bytes plus the pointer). This means that for some data types, storing the data on disk requires more memory than storing the data in-memory.</li> 
     </ul> 
     <p>The recommendation is to limit the use of disk data and make sure you test well before implementing disk data in a production cluster.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Access Methods</h3> 
     <p>MySQL Cluster supports a range of methods to access the data nodes. The methods have varying overhead, so for high performance clusters, you should take that into consideration. The access methods in order of increasing overhead are:</p> 
     <ul> 
      <li>The C NDB API</li> 
      <li>No SQL APIs such as Cluster/J</li> 
      <li>mysqld nodes</li> 
     </ul> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Monitoring</h3> 
     <p>It is strongly recommended to monitor both the mysqld/API nodes and the data nodes.</p> 
     <p><a href="https://www.mysql.com/products/enterprise/monitor.html" title="MySQL Enterprise Monitor" rel="nofollow">MySQL Enterprise Monitor 3.0</a>&nbsp;is one possibility and supports monitoring all of the node types. See&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1914923.1" title="How to Monitor a MySQL Cluster Data Node With MySQL Enterprise Monitor (MEM)?" rel="nofollow">How to Monitor a MySQL Cluster Data Node With MySQL Enterprise Monitor (MEM)?</a>&nbsp;for more information.</p> 
     <p>In addition to monitoring metrics such as disk I/O, query response time, etc. that a monitoring solution can provide, it is important to review the logs at a regular basis.</p> 
     <p>&nbsp;</p> 
     <a rel="nofollow"></a> 
     <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Backups</h3> 
     <p>As with all other database systems backups are recommended:</p> 
     <ul> 
      <li>Make sure to copy the backups out of the servers where the data nodes are stored.</li> 
      <li>To the extend possible make backups when the load is low.</li> 
      <li>Consider whether the CPU overhead of&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-compressedbackup" title="MySQL Reference Manual - CompressedBackup" rel="nofollow">compressing the backup</a>&nbsp;is worth the savings in disk I/O.</li> 
      <li>Combine with the binary logs to allow for&nbsp;<a href="https://dev.mysql.com/doc/en/mysql-cluster-replication-pitr.html" title="MySQL Reference Manual - Point-In-Time Recovery Using MySQL Cluster Replication" rel="nofollow">Point-in-Time recovery</a>.</li> 
      <li>Create separate backup of the schema using&nbsp;<a href="https://dev.mysql.com/doc/en/mysqldump.html" title="MySQL Reference Manual - mysqldump — A Database Backup Program" rel="nofollow">mysqldump</a>.</li> 
     </ul> 
     <p>See also:&nbsp;<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&amp;sourceId=1926680.1&amp;id=1023562.1" title="What is the Recommended Process for a Backup of MySQL Cluster?" rel="nofollow">What is the Recommended Process for a Backup of MySQL Cluster</a></p> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[t.gif]: https://support.oracle.com/epmos/adf/images/t.gif