21.5.6.3 Using CLUSTERLOG STATISTICS in the NDB Cluster Management Client

The NDB management client's
CLUSTERLOG STATISTICS
command can provide a number of useful statistics in its output.
Counters providing information about the state of the cluster
are updated at 5-second reporting intervals by the transaction
coordinator (TC) and the local query handler (LQH), and written
to the cluster log.

Transaction coordinator statistics.
Each transaction has one transaction coordinator, which is
chosen by one of the following methods:

In a round-robin fashion

By communication proximity

By supplying a data placement hint when the transaction is
started

Note

You can determine which TC selection method is used for
transactions started from a given SQL node using the
ndb_optimized_node_selection
system variable.

All operations within the same transaction use the same
transaction coordinator, which reports the following statistics:

Trans count.
This is the number transactions started in the last
interval using this TC as the transaction coordinator. Any
of these transactions may have committed, have been
aborted, or remain uncommitted at the end of the reporting
interval.

Note

Transactions do not migrate between TCs.

Commit count.
This is the number of transactions using this TC as the
transaction coordinator that were committed in the last
reporting interval. Because some transactions committed in
this reporting interval may have started in a previous
reporting interval, it is possible for Commit
count to be greater than Trans
count.

Read count.
This is the number of primary key read operations using
this TC as the transaction coordinator that were started
in the last reporting interval, including simple reads.
This count also includes reads performed as part of unique
index operations. A unique index read operation generates
2 primary key read operations—1 for the hidden
unique index table, and 1 for the table on which the read
takes place.

Simple read count.
This is the number of simple read operations using this TC
as the transaction coordinator that were started in the
last reporting interval.

Write count.
This is the number of primary key write operations using
this TC as the transaction coordinator that were started
in the last reporting interval. This includes all inserts,
updates, writes and deletes, as well as writes performed
as part of unique index operations.

Note

A unique index update operation can generate multiple PK
read and write operations on the index table and on the
base table.

AttrInfoCount.
This is the number of 32-bit data words received in the
last reporting interval for primary key operations using
this TC as the transaction coordinator. For reads, this is
proportional to the number of columns requested. For
inserts and updates, this is proportional to the number of
columns written, and the size of their data. For delete
operations, this is usually zero.

Unique index operations generate multiple PK operations and
so increase this count. However, data words sent to describe
the PK operation itself, and the key information sent, are
not counted here. Attribute information
sent to describe columns to read for scans, or to describe
ScanFilters, is also not counted in
AttrInfoCount.

Concurrent Operations.
This is the number of primary key or scan operations using
this TC as the transaction coordinator that were started
during the last reporting interval but that were not
completed. Operations increment this counter when they are
started and decrement it when they are completed; this
occurs after the transaction commits. Dirty reads and
writes—as well as failed operations—decrement
this counter.

The maximum value that Concurrent
Operations can have is the maximum number of
operations that a TC block can support; currently, this is
(2 * MaxNoOfConcurrentOperations) + 16 +
MaxNoOfConcurrentTransactions. (For more
information about these configuration parameters, see the
Transaction Parameters section of
Section 21.3.3.6, “Defining NDB Cluster Data Nodes”.)

Abort count.
This is the number of transactions using this TC as the
transaction coordinator that were aborted during the last
reporting interval. Because some transactions that were
aborted in the last reporting interval may have started in
a previous reporting interval, Abort
count can sometimes be greater than
Trans count.

Scans.
This is the number of table scans using this TC as the
transaction coordinator that were started during the last
reporting interval. This does not include range scans
(that is, ordered index scans).

Range scans.
This is the number of ordered index scans using this TC as
the transaction coordinator that were started in the last
reporting interval.

Local reads.
This is the number of primary-key read operations
performed using a transaction coordinator on a node that
also holds the primary replica of the record. This count
can also be obtained from the
LOCAL_READS counter in the
ndbinfo.counters table.

Local writes.
This contains the number of primary-key read operations
that were performed using a transaction coordinator on a
node that also holds the primary replica of the record.
This count can also be obtained from the
LOCAL_WRITES counter in the
ndbinfo.counters table.

Local query handler statistics (Operations).
There is 1 cluster event per local query handler block (that
is, 1 per data node process). Operations are recorded in the
LQH where the data they are operating on resides.

Note

A single transaction may operate on data stored in multiple
LQH blocks.

The Operations statistic provides the number
of local operations performed by this LQH block in the last
reporting interval, and includes all types of read and write
operations (insert, update, write, and delete operations). This
also includes operations used to replicate writes. For example,
in a 2-replica cluster, the write to the primary replica is
recorded in the primary LQH, and the write to the backup will be
recorded in the backup LQH. Unique key operations may result in
multiple local operations; however, this does
not include local operations generated as a
result of a table scan or ordered index scan, which are not
counted.

Process scheduler statistics.
In addition to the statistics reported by the transaction
coordinator and local query handler, each
ndbd process has a scheduler which also
provides useful metrics relating to the performance of an NDB
Cluster. This scheduler runs in an infinite loop; during each
loop the scheduler performs the following tasks:

Read any incoming messages from sockets into a job buffer.

Check whether there are any timed messages to be executed;
if so, put these into the job buffer as well.

Execute (in a loop) any messages in the job buffer.

Send any distributed messages that were generated by
executing the messages in the job buffer.

Wait for any new incoming messages.

Process scheduler statistics include the following:

Mean Loop Counter.
This is the number of loops executed in the third step
from the preceding list. This statistic increases in size
as the utilization of the TCP/IP buffer improves. You can
use this to monitor changes in performance as you add new
data node processes.

Mean send size and Mean receive size.
These statistics enable you to gauge the efficiency of,
respectively writes and reads between nodes. The values
are given in bytes. Higher values mean a lower cost per
byte sent or received; the maximum value is 64K.

To cause all cluster log statistics to be logged, you can use
the following command in the NDB
management client:

ndb_mgm> ALL CLUSTERLOG STATISTICS=15

Note

Setting the threshold for STATISTICS to 15
causes the cluster log to become very verbose, and to grow
quite rapidly in size, in direct proportion to the number of
cluster nodes and the amount of activity in the NDB Cluster.