The parameters discussed in
Logging
and Checkpointing and in
Data
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 parameters —
including DataMemory
,
IndexMemory
,
NoOfDiskPagesToDiskAfterRestartTUP
,
NoOfDiskPagesToDiskAfterRestartACC
, and
NoOfFragmentLogFiles
— relate to one
another in a working Cluster.
In this example, we assume that our application performs the following numbers of types of operations per hour:
50000 selects
15000 inserts
15000 updates
15000 deletes
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.
A typical UPDATE
run by the
application affects the values of 5 columns.
No NULL
values are inserted by the
application.
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 DataMemory
parameter. In this
example, we assume that this is 2 GB. The
NoOfDiskPagesToDiskAfterRestartTUP
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
NoOfDiskPagesToDiskAfterRestartACC
in terms
of the time for local checkpoints and the amount of memory
required for indexes — that is, the
IndexMemory
. Assuming that we allow 512 MB
for indexes, this works out to approximately 20 8-KB pages per
100 milliseconds for this parameter.
Next, we need to determine the number of REDO log files required
— that is, fragment log files — the corresponding
parameter being NoOfFragmentLogFiles
. We need
to make sure that there are sufficient REDO log files 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 log files which should be enough
to keep records covering 6 local checkpoints.
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 determined by the values
of NoOfDiskPagesToDiskAfterRestartACC
and
NoOfDiskPagesToDiskAfterRestartTUP
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. 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 SELECT
statements are not recorded in the REDO log.
15000 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.
15000 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
per second.
15000 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
NoOfFragmentLogFiles
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.
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.
User Comments
Add your own comment.