Author: jstephens
Date: 2006-07-11 14:28:57 +0200 (Tue, 11 Jul 2006)
New Revision: 2698
Log:
New section in Cluster chapter: Calculating LCP Params.
Merge some unneeded diffs between versions.
Reformat.
Modified:
trunk/refman-4.1/mysql-cluster.xml
trunk/refman-5.0/mysql-cluster.xml
trunk/refman-5.1/mysql-cluster.xml
trunk/refman-common/titles.en.ent
Modified: trunk/refman-4.1/mysql-cluster.xml
===================================================================
--- trunk/refman-4.1/mysql-cluster.xml 2006-07-11 09:21:51 UTC (rev 2697)
+++ trunk/refman-4.1/mysql-cluster.xml 2006-07-11 12:28:57 UTC (rev 2698)
Changed blocks: 9, Lines Added: 278, Lines Deleted: 34; 13616 bytes
@@ -1784,8 +1784,8 @@
its tables have been created on one data node, you need to issue
the <literal>CREATE DATABASE world</literal> statement followed
by <literal>FLUSH TABLES</literal> on each SQL node in the
- cluster. This will cause the node to recognize the database and
- read its table definitions.
+ cluster. This causes the node to recognize the database and read
+ its table definitions.
</para>
<para>
@@ -2580,8 +2580,8 @@
<para>
Starting with MySQL 4.1.8, you may also use a separate
<literal>[mysql_cluster]</literal> section in the cluster
- <filename>my.cnf</filename> for settings to be read and used
- by all executables:
+ <filename>my.cnf</filename> file for settings to be read and
+ used by all executables:
</para>
<programlisting>
@@ -4270,17 +4270,11 @@
elsewhere in this chapter.
</para>
- <remark role="todo">
- [js] Get info from NDB-devs regarding these...
- </remark>
-
-<!--
<para>
- How these interact and proposals for how to configure them
- are provided in
- <xref linkend="MySQL Cluster Complex Configuration"/>.
+ How these parameters interact and proposals for how to
+ configure them are discussed in
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
</para>
--->
<para>
The default parameter value is 8, which means 8 sets of 4
@@ -5076,11 +5070,12 @@
<literal>IndexMemory</literal>.
</para>
- <remark>
- The interaction between these parameters and possible
- strategies for choosing them is shown in @ref{MySQL
- Cluster Complex Configuration}.
- </remark>
+ <para>
+ For more information about the interaction between these
+ parameters and possible strategies for choosing
+ appropriate values for them, see
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
+ </para>
<para>
The default value is 40 (3.2MB of data pages per second).
@@ -7334,6 +7329,265 @@
</section>-->
+ <section id="mysql-cluster-config-lcp-params">
+
+ <title>&title-mysql-cluster-config-lcp-params;</title>
+
+ <indexterm>
+ <primary><literal>DataMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>IndexMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartACC</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartTUP</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfFragmentLogFiles</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>local checkpoints (MySQL Cluster)</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>configuration</secondary>
+ </indexterm>
+
+ <remark role="note">
+ [js] Adapted by Jon Stephens from an item in Mikael Ronström's
+ blog, 2006-07-11.
+ </remark>
+
+ <para>
+ The parameters discussed in
+ <xref linkend="mysql-cluster-db-definition"/> that are used to
+ configure local checkpoints for a MySQL Cluster do not exist in
+ isolation, but rather are very much interdepedent on each other.
+ In this section, we illustrate how these parameters —
+ including <literal>DataMemory</literal>,
+ <literal>IndexMemory</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal>, and
+ <literal>NoOfFragmentLogFiles</literal> — relate to one
+ another in a working Cluster.
+ </para>
+
+ <para>
+ In this example, we assume that our application performs the
+ following numbers of types of operations per hour:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 selects
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 inserts
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 updates
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 deletes
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ We also make the following assumptions about the data used in
+ the application:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ We are working with a single table having 40 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each column can hold up to 32 bytes of data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A typical <literal>UPDATE</literal> run by the application
+ affects the values of 5 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ No <literal>NULL</literal> values are inserted by the
+ application.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ A good starting point is to determine the amount of time that
+ should elapse between local checkpoints (LCPs). It worth noting
+ that, in the event of a system restart, it takes 40-60 percent
+ of this interval to execute the REDO log — for example, if
+ the time between LCPs is 5 minutes (300 seconds), then it should
+ take 2 to 3 minutes (120 to 180 seconds) for the REDO log to be
+ read.
+ </para>
+
+ <para>
+ The maximum amount of data per node can be assumed to be the
+ size of the <literal>DataMemory</literal> parameter. In this
+ example, we assume that this is 2 GB. The
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> parameter
+ represents the amount of data to be checkpointed per unit time
+ — however, this parameter is actually expressed as the
+ number of 8K memory pages to be checkpointed per 100
+ milliseconds. 2 GB per 300 seconds is approximately 6.8 MB per
+ second, or 700 KB per 100 milliseconds, which works out to
+ roughly 85 pages per 100 milliseconds.
+ </para>
+
+ <para>
+ Similarly, we can calculate
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> in terms
+ of the time for local checkpoints and the amount of memory
+ required for indexes — that is, the
+ <literal>IndexMemory</literal>. Assuming that we allow 512 MB
+ for indexes, this works out to approximately 20 8-KB pages per
+ 100 milliseconds for this parameter.
+ </para>
+
+ <para>
+ Next, we need to determine the number of REDO logfiles required
+ — that is, fragment log files — the corresponding
+ parameter being <literal>NoOfFragmentLogFiles</literal>. We need
+ to make sure that there are sufficient REDO logfiles for keeping
+ records for at least 3 local checkpoints. In a production
+ setting, there are always uncertainties — for instance, we
+ cannot be sure that disks always operate at top speed or with
+ maximum throughput. For this reason, it is best to err on the
+ side of caution, so we double our requirement and calculate a
+ number of fragment logfiles which should be enough to keep
+ records covering 6 local checkpoints.
+ </para>
+
+ <para>
+ It is also important to remember that the disk also handles
+ writes to the REDO log and UNDO log, so if you find that the
+ amount of data being written to disk as detemined by the values
+ of <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> and
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> is
+ approaching the amount of disk bandwidth available, you may wish
+ to increase the time between local checkpoints.
+ </para>
+
+ <para>
+ Given 5 minutes (300 seconds) per local checkpoint, this means
+ that we need to support writing log records at maximum speed for
+ 6 * 300 = 1800 seconds. The size of a REDO log record is 72
+ bytes plus 4 bytes per updated column value plus the maximum
+ size of the updated column, and there is one REDO log record for
+ each table record updated in a transaction, on each node where
+ the data reside. Using the numbers of operations set out
+ previously in this section, we derive the following:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 select operations per hour yields 0 log records (and
+ thus 0 bytes), since <literal>SELECT</literal> statements
+ are not recorded in the REDO log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>DELETE</literal> statements per hour is
+ approximately 5 delete operations per second. (Since we wish
+ to be conservative in our estimate, we round up here and in
+ the following calculations.) No columns are updated by
+ deletes, so these statements consume only 5 operations * 72
+ bytes per operation = 360 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>UPDATE</literal> statements per hour is
+ roughly the same as 5 updates per second. Each update uses
+ 72 bytes, plus 4 bytes per column * 5 columns updated, plus
+ 32 bytes per column * 5 columns — this works out to 72
+ + 20 + 160 = 252 bytes per operation, and multiplying this
+ by 5 operation per second yields 1260 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>INSERT</literal> statements per hour is
+ equivalent to 5 insert operations per second. Each insert
+ requires REDO log space of 72 bytes, plus 4 bytes per record
+ * 40 columns, plus 32 bytes per column * 40 columns, which
+ is 72 + 160 + 1280 = 1512 bytes per operation. This times 5
+ operations per second yields 7560 bytes per second.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ So the total number of REDO log bytes being written per second
+ is approximately 0 + 360 + 1260 + 7560 = 9180 bytes. Mutiplied
+ by 1800 seconds, this yields 16524000 bytes required for REDO
+ logging, or approximately 15.75 MB. The unit used for
+ <literal>NoOfFragmentLogFiles</literal> represents a set of 4
+ 16-MB logfiles — that is, 64 MB. Thus, the minimum value
+ (3) for this parameter is sufficient for the scenario envisioned
+ in this example, since 3 times 64 = 192 MB, or about 12 times
+ what is required; the default value of 8 (or 512 MB) is more
+ than ample in this case.
+ </para>
+
+ <para>
+ A copy of each altered table record is kept in the UNDO log. In
+ the scenario discussed above, the UNDO log would not require any
+ more space than what is provided by the default seetings.
+ However, given the size of disks, it is sensible to allocate at
+ least 1 GB for it.
+ </para>
+
+ </section>
+
</section>
<section id="mysql-cluster-upgrade-downgrade">
@@ -8219,9 +8473,7 @@
<indexterm>
<primary>storage nodes</primary>
-<!-- <see>data nodes, <command>ndbd</command>
-
- </see> -->
+<!-- <see>data nodes, <command>ndbd</command></see> -->
</indexterm>
<para>
@@ -8476,16 +8728,12 @@
<indexterm>
<primary>management nodes (MySQL Cluster)</primary>
-<!-- <seealso><command>mgmd</command>
-
- </seealso> -->
+<!-- <seealso><command>mgmd</command></seealso> -->
</indexterm>
<indexterm>
<primary><command>ndb_mgmd</command></primary>
-<!-- <see><command>mgmd</command>
-
- </see> -->
+<!-- <see><command>mgmd</command></see> -->
</indexterm>
<para>
@@ -8614,16 +8862,12 @@
<indexterm>
<primary>management client (MySQL Cluster)</primary>
-<!-- <seealso><command>mgm</command>
-
- </seealso> -->
+<!-- <seealso><command>mgm</command></seealso> -->
</indexterm>
<indexterm>
<primary><command>ndb_mgm</command></primary>
-<!-- <see><command>mgm</command>
-
- </see> -->
+<!-- <see><command>mgm</command></see> -->
</indexterm>
<indexterm>
@@ -9584,7 +9828,7 @@
<para>
Commands for the event logs are given in
- <xref linkend="mysql-cluster-event-reports"/>. commands for
+ <xref linkend="mysql-cluster-event-reports"/>; commands for
creating backups and restoring from backup are provided in
<xref linkend="mysql-cluster-backup"/>.
</para>
Modified: trunk/refman-5.0/mysql-cluster.xml
===================================================================
--- trunk/refman-5.0/mysql-cluster.xml 2006-07-11 09:21:51 UTC (rev 2697)
+++ trunk/refman-5.0/mysql-cluster.xml 2006-07-11 12:28:57 UTC (rev 2698)
Changed blocks: 8, Lines Added: 277, Lines Deleted: 32; 13291 bytes
@@ -4265,17 +4265,11 @@
elsewhere in this chapter.
</para>
- <remark role="todo">
- [js] Get info from NDB-devs regarding these...
- </remark>
-
-<!--
<para>
- How these interact and proposals for how to configure them
- are provided in
- <xref linkend="MySQL Cluster Complex Configuration"/>.
+ How these parameters interact and proposals for how to
+ configure them are discussed in
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
</para>
--->
<para>
The default parameter value is 8, which means 8 sets of 4
@@ -5070,11 +5064,12 @@
<literal>IndexMemory</literal>.
</para>
- <remark>
- The interaction between these parameters and possible
- strategies for choosing them is shown in @ref{MySQL
- Cluster Complex Configuration}.
- </remark>
+ <para>
+ For more information about the interaction between these
+ parameters and possible strategies for choosing
+ appropriate values for them, see
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
+ </para>
<para>
The default value is 40 (3.2MB of data pages per second).
@@ -7353,6 +7348,265 @@
</section>-->
+ <section id="mysql-cluster-config-lcp-params">
+
+ <title>&title-mysql-cluster-config-lcp-params;</title>
+
+ <indexterm>
+ <primary><literal>DataMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>IndexMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartACC</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartTUP</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfFragmentLogFiles</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>local checkpoints (MySQL Cluster)</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>configuration</secondary>
+ </indexterm>
+
+ <remark role="note">
+ [js] Adapted by Jon Stephens from an item in Mikael Ronström's
+ blog, 2006-07-11.
+ </remark>
+
+ <para>
+ The parameters discussed in
+ <xref linkend="mysql-cluster-db-definition"/> that are used to
+ configure local checkpoints for a MySQL Cluster do not exist in
+ isolation, but rather are very much interdepedent on each other.
+ In this section, we illustrate how these parameters —
+ including <literal>DataMemory</literal>,
+ <literal>IndexMemory</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal>, and
+ <literal>NoOfFragmentLogFiles</literal> — relate to one
+ another in a working Cluster.
+ </para>
+
+ <para>
+ In this example, we assume that our application performs the
+ following numbers of types of operations per hour:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 selects
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 inserts
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 updates
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 deletes
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ We also make the following assumptions about the data used in
+ the application:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ We are working with a single table having 40 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each column can hold up to 32 bytes of data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A typical <literal>UPDATE</literal> run by the application
+ affects the values of 5 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ No <literal>NULL</literal> values are inserted by the
+ application.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ A good starting point is to determine the amount of time that
+ should elapse between local checkpoints (LCPs). It worth noting
+ that, in the event of a system restart, it takes 40-60 percent
+ of this interval to execute the REDO log — for example, if
+ the time between LCPs is 5 minutes (300 seconds), then it should
+ take 2 to 3 minutes (120 to 180 seconds) for the REDO log to be
+ read.
+ </para>
+
+ <para>
+ The maximum amount of data per node can be assumed to be the
+ size of the <literal>DataMemory</literal> parameter. In this
+ example, we assume that this is 2 GB. The
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> parameter
+ represents the amount of data to be checkpointed per unit time
+ — however, this parameter is actually expressed as the
+ number of 8K memory pages to be checkpointed per 100
+ milliseconds. 2 GB per 300 seconds is approximately 6.8 MB per
+ second, or 700 KB per 100 milliseconds, which works out to
+ roughly 85 pages per 100 milliseconds.
+ </para>
+
+ <para>
+ Similarly, we can calculate
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> in terms
+ of the time for local checkpoints and the amount of memory
+ required for indexes — that is, the
+ <literal>IndexMemory</literal>. Assuming that we allow 512 MB
+ for indexes, this works out to approximately 20 8-KB pages per
+ 100 milliseconds for this parameter.
+ </para>
+
+ <para>
+ Next, we need to determine the number of REDO logfiles required
+ — that is, fragment log files — the corresponding
+ parameter being <literal>NoOfFragmentLogFiles</literal>. We need
+ to make sure that there are sufficient REDO logfiles for keeping
+ records for at least 3 local checkpoints. In a production
+ setting, there are always uncertainties — for instance, we
+ cannot be sure that disks always operate at top speed or with
+ maximum throughput. For this reason, it is best to err on the
+ side of caution, so we double our requirement and calculate a
+ number of fragment logfiles which should be enough to keep
+ records covering 6 local checkpoints.
+ </para>
+
+ <para>
+ It is also important to remember that the disk also handles
+ writes to the REDO log and UNDO log, so if you find that the
+ amount of data being written to disk as detemined by the values
+ of <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> and
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> is
+ approaching the amount of disk bandwidth available, you may wish
+ to increase the time between local checkpoints.
+ </para>
+
+ <para>
+ Given 5 minutes (300 seconds) per local checkpoint, this means
+ that we need to support writing log records at maximum speed for
+ 6 * 300 = 1800 seconds. The size of a REDO log record is 72
+ bytes plus 4 bytes per updated column value plus the maximum
+ size of the updated column, and there is one REDO log record for
+ each table record updated in a transaction, on each node where
+ the data reside. Using the numbers of operations set out
+ previously in this section, we derive the following:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 select operations per hour yields 0 log records (and
+ thus 0 bytes), since <literal>SELECT</literal> statements
+ are not recorded in the REDO log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>DELETE</literal> statements per hour is
+ approximately 5 delete operations per second. (Since we wish
+ to be conservative in our estimate, we round up here and in
+ the following calculations.) No columns are updated by
+ deletes, so these statements consume only 5 operations * 72
+ bytes per operation = 360 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>UPDATE</literal> statements per hour is
+ roughly the same as 5 updates per second. Each update uses
+ 72 bytes, plus 4 bytes per column * 5 columns updated, plus
+ 32 bytes per column * 5 columns — this works out to 72
+ + 20 + 160 = 252 bytes per operation, and multiplying this
+ by 5 operation per second yields 1260 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>INSERT</literal> statements per hour is
+ equivalent to 5 insert operations per second. Each insert
+ requires REDO log space of 72 bytes, plus 4 bytes per record
+ * 40 columns, plus 32 bytes per column * 40 columns, which
+ is 72 + 160 + 1280 = 1512 bytes per operation. This times 5
+ operations per second yields 7560 bytes per second.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ So the total number of REDO log bytes being written per second
+ is approximately 0 + 360 + 1260 + 7560 = 9180 bytes. Mutiplied
+ by 1800 seconds, this yields 16524000 bytes required for REDO
+ logging, or approximately 15.75 MB. The unit used for
+ <literal>NoOfFragmentLogFiles</literal> represents a set of 4
+ 16-MB logfiles — that is, 64 MB. Thus, the minimum value
+ (3) for this parameter is sufficient for the scenario envisioned
+ in this example, since 3 times 64 = 192 MB, or about 12 times
+ what is required; the default value of 8 (or 512 MB) is more
+ than ample in this case.
+ </para>
+
+ <para>
+ A copy of each altered table record is kept in the UNDO log. In
+ the scenario discussed above, the UNDO log would not require any
+ more space than what is provided by the default seetings.
+ However, given the size of disks, it is sensible to allocate at
+ least 1 GB for it.
+ </para>
+
+ </section>
+
</section>
<section id="mysql-cluster-upgrade-downgrade">
@@ -8236,9 +8490,7 @@
<indexterm>
<primary>storage nodes</primary>
-<!-- <see>data nodes, <command>ndbd</command>
-
- </see> -->
+<!-- <see>data nodes, <command>ndbd</command></see> -->
</indexterm>
<para>
@@ -8475,16 +8727,12 @@
<indexterm>
<primary>management nodes (MySQL Cluster)</primary>
-<!-- <seealso><command>mgmd</command>
-
- </seealso> -->
+<!-- <seealso><command>mgmd</command></seealso> -->
</indexterm>
<indexterm>
<primary><command>ndb_mgmd</command></primary>
-<!-- <see><command>mgmd</command>
-
- </see> -->
+<!-- <see><command>mgmd</command></see> -->
</indexterm>
<para>
@@ -8606,16 +8854,12 @@
<indexterm>
<primary>management client (MySQL Cluster)</primary>
-<!-- <seealso><command>mgm</command>
-
- </seealso> -->
+<!-- <seealso><command>mgm</command></seealso> -->
</indexterm>
<indexterm>
<primary><command>ndb_mgm</command></primary>
-<!-- <see><command>mgm</command>
-
- </see> -->
+<!-- <see><command>mgm</command></see> -->
</indexterm>
<indexterm>
@@ -9120,8 +9364,8 @@
<listitem>
<para>
- <option>--config-file=<replaceable>file_name</replaceable></option>,
- <option>-f <replaceable>file_name</replaceable></option>,
+ <option>--config-file=<replaceable>filename</replaceable></option>,
+ <option>-f <replaceable>filename</replaceable></option>,
</para>
<para>
@@ -12670,7 +12914,8 @@
datatypes, nor does the <literal>NDB</literal> storage
engine support <literal>FULLTEXT</literal> indexes (these
are supported by <literal>MyISAM</literal> only). However,
- you can index <literal>VARCHAR</literal> columns of
+ you can index <literal>CHAR</literal> or
+ <literal>VARCHAR</literal> columns of
<literal>NDB</literal> tables.
</para>
</listitem>
Modified: trunk/refman-5.1/mysql-cluster.xml
===================================================================
--- trunk/refman-5.1/mysql-cluster.xml 2006-07-11 09:21:51 UTC (rev 2697)
+++ trunk/refman-5.1/mysql-cluster.xml 2006-07-11 12:28:57 UTC (rev 2698)
Changed blocks: 5, Lines Added: 273, Lines Deleted: 19; 11960 bytes
@@ -4261,17 +4261,11 @@
elsewhere in this chapter.
</para>
- <remark role="todo">
- [js] Get info from NDB-devs regarding these...
- </remark>
-
-<!--
<para>
- How these interact and proposals for how to configure them
- are provided in
- <xref linkend="MySQL Cluster Complex Configuration"/>.
+ How these parameters interact and proposals for how to
+ configure them are discussed in
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
</para>
--->
<para>
The default parameter value is 8, which means 8 sets of 4
@@ -5071,11 +5065,12 @@
<literal>IndexMemory</literal>.
</para>
- <remark>
- The interaction between these parameters and possible
- strategies for choosing them is shown in @ref{MySQL
- Cluster Complex Configuration}.
- </remark>
+ <para>
+ For more information about the interaction between these
+ parameters and possible strategies for choosing
+ appropriate values for them, see
+ <xref linkend="mysql-cluster-config-lcp-params"/>.
+ </para>
<para>
The default value is 40 (3.2MB of data pages per second).
@@ -7352,8 +7347,267 @@
</section>
- </section>-->
+ </section>-->
+ <section id="mysql-cluster-config-lcp-params">
+
+ <title>&title-mysql-cluster-config-lcp-params;</title>
+
+ <indexterm>
+ <primary><literal>DataMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>IndexMemory</literal></primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartACC</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfDiskPagesToDiskAfterRestartTUP</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>NoOfFragmentLogFiles</literal></primary>
+ <secondary>calculating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>local checkpoints (MySQL Cluster)</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MySQL Cluster</primary>
+ <secondary>configuration</secondary>
+ </indexterm>
+
+ <remark role="note">
+ [js] Adapted by Jon Stephens from an item in Mikael Ronström's
+ blog, 2006-07-11.
+ </remark>
+
+ <para>
+ The parameters discussed in
+ <xref linkend="mysql-cluster-db-definition"/> that are used to
+ configure local checkpoints for a MySQL Cluster do not exist in
+ isolation, but rather are very much interdepedent on each other.
+ In this section, we illustrate how these parameters —
+ including <literal>DataMemory</literal>,
+ <literal>IndexMemory</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal>,
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal>, and
+ <literal>NoOfFragmentLogFiles</literal> — relate to one
+ another in a working Cluster.
+ </para>
+
+ <para>
+ In this example, we assume that our application performs the
+ following numbers of types of operations per hour:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 selects
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 inserts
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 updates
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 deletes
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ We also make the following assumptions about the data used in
+ the application:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ We are working with a single table having 40 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each column can hold up to 32 bytes of data.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A typical <literal>UPDATE</literal> run by the application
+ affects the values of 5 columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ No <literal>NULL</literal> values are inserted by the
+ application.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ A good starting point is to determine the amount of time that
+ should elapse between local checkpoints (LCPs). It worth noting
+ that, in the event of a system restart, it takes 40-60 percent
+ of this interval to execute the REDO log — for example, if
+ the time between LCPs is 5 minutes (300 seconds), then it should
+ take 2 to 3 minutes (120 to 180 seconds) for the REDO log to be
+ read.
+ </para>
+
+ <para>
+ The maximum amount of data per node can be assumed to be the
+ size of the <literal>DataMemory</literal> parameter. In this
+ example, we assume that this is 2 GB. The
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> parameter
+ represents the amount of data to be checkpointed per unit time
+ — however, this parameter is actually expressed as the
+ number of 8K memory pages to be checkpointed per 100
+ milliseconds. 2 GB per 300 seconds is approximately 6.8 MB per
+ second, or 700 KB per 100 milliseconds, which works out to
+ roughly 85 pages per 100 milliseconds.
+ </para>
+
+ <para>
+ Similarly, we can calculate
+ <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> in terms
+ of the time for local checkpoints and the amount of memory
+ required for indexes — that is, the
+ <literal>IndexMemory</literal>. Assuming that we allow 512 MB
+ for indexes, this works out to approximately 20 8-KB pages per
+ 100 milliseconds for this parameter.
+ </para>
+
+ <para>
+ Next, we need to determine the number of REDO logfiles required
+ — that is, fragment log files — the corresponding
+ parameter being <literal>NoOfFragmentLogFiles</literal>. We need
+ to make sure that there are sufficient REDO logfiles for keeping
+ records for at least 3 local checkpoints. In a production
+ setting, there are always uncertainties — for instance, we
+ cannot be sure that disks always operate at top speed or with
+ maximum throughput. For this reason, it is best to err on the
+ side of caution, so we double our requirement and calculate a
+ number of fragment logfiles which should be enough to keep
+ records covering 6 local checkpoints.
+ </para>
+
+ <para>
+ It is also important to remember that the disk also handles
+ writes to the REDO log and UNDO log, so if you find that the
+ amount of data being written to disk as detemined by the values
+ of <literal>NoOfDiskPagesToDiskAfterRestartACC</literal> and
+ <literal>NoOfDiskPagesToDiskAfterRestartTUP</literal> is
+ approaching the amount of disk bandwidth available, you may wish
+ to increase the time between local checkpoints.
+ </para>
+
+ <para>
+ Given 5 minutes (300 seconds) per local checkpoint, this means
+ that we need to support writing log records at maximum speed for
+ 6 * 300 = 1800 seconds. The size of a REDO log record is 72
+ bytes plus 4 bytes per updated column value plus the maximum
+ size of the updated column, and there is one REDO log record for
+ each table record updated in a transaction, on each node where
+ the data reside. Using the numbers of operations set out
+ previously in this section, we derive the following:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 50000 select operations per hour yields 0 log records (and
+ thus 0 bytes), since <literal>SELECT</literal> statements
+ are not recorded in the REDO log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>DELETE</literal> statements per hour is
+ approximately 5 delete operations per second. (Since we wish
+ to be conservative in our estimate, we round up here and in
+ the following calculations.) No columns are updated by
+ deletes, so these statements consume only 5 operations * 72
+ bytes per operation = 360 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>UPDATE</literal> statements per hour is
+ roughly the same as 5 updates per second. Each update uses
+ 72 bytes, plus 4 bytes per column * 5 columns updated, plus
+ 32 bytes per column * 5 columns — this works out to 72
+ + 20 + 160 = 252 bytes per operation, and multiplying this
+ by 5 operation per second yields 1260 bytes per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 15000 <literal>INSERT</literal> statements per hour is
+ equivalent to 5 insert operations per second. Each insert
+ requires REDO log space of 72 bytes, plus 4 bytes per record
+ * 40 columns, plus 32 bytes per column * 40 columns, which
+ is 72 + 160 + 1280 = 1512 bytes per operation. This times 5
+ operations per second yields 7560 bytes per second.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ So the total number of REDO log bytes being written per second
+ is approximately 0 + 360 + 1260 + 7560 = 9180 bytes. Mutiplied
+ by 1800 seconds, this yields 16524000 bytes required for REDO
+ logging, or approximately 15.75 MB. The unit used for
+ <literal>NoOfFragmentLogFiles</literal> represents a set of 4
+ 16-MB logfiles — that is, 64 MB. Thus, the minimum value
+ (3) for this parameter is sufficient for the scenario envisioned
+ in this example, since 3 times 64 = 192 MB, or about 12 times
+ what is required; the default value of 8 (or 512 MB) is more
+ than ample in this case.
+ </para>
+
+ <para>
+ A copy of each altered table record is kept in the UNDO log. In
+ the scenario discussed above, the UNDO log would not require any
+ more space than what is provided by the default seetings.
+ However, given the size of disks, it is sensible to allocate at
+ least 1 GB for it.
+ </para>
+
+ </section>
+
</section>
<section id="mysql-cluster-upgrade-downgrade">
@@ -9368,6 +9622,10 @@
TCP timeout occurs 10 or so minutes later.
</para>
+ <indexterm>
+ <primary><literal>PURGE STALE SESSIONS</literal></primary>
+ </indexterm>
+
<para>
To take care of this problem, you can use <literal>PURGE STALE
SESSIONS</literal>. Running this statement forces all reserved
@@ -9375,10 +9633,6 @@
actually connected to the cluster are then freed.
</para>
- <indexterm>
- <primary><literal>PURGE STALE SESSIONS</literal></primary>
- </indexterm>
-
<para>
Beginning with MySQL 5.1.11, timeout handling of node ID
assignments is implemented. This performs the ID usage checks
Modified: trunk/refman-common/titles.en.ent
===================================================================
--- trunk/refman-common/titles.en.ent 2006-07-11 09:21:51 UTC (rev 2697)
+++ trunk/refman-common/titles.en.ent 2006-07-11 12:28:57 UTC (rev 2698)
Changed blocks: 1, Lines Added: 2, Lines Deleted: 1; 1175 bytes
@@ -1028,8 +1028,9 @@
<!ENTITY title-mysql-cluster-change-history "Changes in MySQL Cluster">
<!ENTITY title-mysql-cluster-command-options "Command Options for MySQL Cluster Processes">
<!ENTITY title-mysql-cluster-computer-definition "Defining the Computers Making up a MySQL Cluster">
-<!ENTITY title-mysql-cluster-config-example "Example Configuration for a MySQL Cluster">
+<!ENTITY title-mysql-cluster-config-example "Basic Example Configuration for a MySQL Cluster">
<!ENTITY title-mysql-cluster-config-file "Configuration File">
+<!ENTITY title-mysql-cluster-config-lcp-params "Configuring Parameters for Local Checkpoints">
<!ENTITY title-mysql-cluster-config-params-overview "Overview of Cluster Configuration Parameters">
<!ENTITY title-mysql-cluster-config-params-ndbd "Cluster Data Node Configuration Parameters">
<!ENTITY title-mysql-cluster-config-params-mgm "Cluster Management Node Configuration Parameters">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r2698 - in trunk: refman-4.1 refman-5.0 refman-5.1 refman-common | jstephens | 11 Jul |
