The parameters discussed in
and Checkpointing and in
Memory, Index Memory, and String Memory 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
to one another in a working Cluster.
were deprecated in MySQL 5.1.6. From MySQL 5.1.6 through
5.1.11, disk writes during LCPs took place at the maximum
speed possible. Beginning with MySQL 5.1.12, the speed and
throughput for LCPs are controlled using the parameters
See Section 18.104.22.168, “Defining MySQL Cluster Data Nodes”.
In this example, we assume that our application performs the following numbers of types of operations per hour:
We also make the following assumptions about the data used in the application:
We are working with a single table having 40 columns.
Each column can hold up to 32 bytes of data.
UPDATE run by the
application affects the values of 5 columns.
NULL values are inserted by the
A good starting point is to determine the amount of time that should elapse between local checkpoints (LCPs). It is 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.
The maximum amount of data per node can be assumed to be the
size of the
parameter. In this example, we assume that this is 2 GB. The
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.
Similarly, we can calculate
in terms of the time for local checkpoints and the amount of
memory required for indexes—that is, the
that we permit 512 MB for indexes, this works out to
approximately 20 8-KB pages per 100 milliseconds for this
Next, we need to determine the number of REDO log files
required—that is, fragment log files—the
corresponding parameter being
need to make sure that there are sufficient REDO log files for
keeping records for at least 3 local checkpoints (in MySQL
Cluster NDB 6.3.8 and later, we need only allow for 2 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 log files
which should be enough to keep records covering 6 local
checkpoints (in MySQL Cluster NDB 6.3.8 and later, a number of
fragment log files accommodating 4 local checkpoints should be
It is also important to remember that the disk also handles
writes to the REDO log, so if you find that the amount of data
being written to disk as determined by the values of
is approaching the amount of disk bandwidth available, you may
wish to increase the time between local checkpoints.
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 (MySQL Cluster NDB 6.3.8 and later: 4 * 300 = 1200 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:
50000 select operations per hour yields 0 log records (and
thus 0 bytes), since
statements are not recorded in the REDO log.
DELETE 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.
UPDATE 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
INSERT 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.
So the total number of REDO log bytes being written per second
is approximately 0 + 360 + 1260 + 7560 = 9180 bytes. Multiplied
by 1800 seconds, this yields 16524000 bytes required for REDO
logging, or approximately 15.75 MB. The unit used for
represents a set of 4 16-MB log files—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.