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
(Beginning with MySQL Cluster NDB 6.3.4:) By supplying a data placement hint when the transaction is started
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.
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. This is a
subset of Read count
. Because the
value of Simple read count
is
incremented at a different point in time from
Read count
, it can lag behind
Read count
slightly, so it is
conceivable that Simple read count
is not equal to Read count
for a
given reporting interval, even if all reads made
during that time were in fact simple reads.
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.
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 17.3.2.6, “Defining MySQL 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 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.
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 a MySQL 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 allow 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
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
MySQL Cluster.
For more information about MySQL Cluster management client commands relating to logging and reporting, see Section 17.5.4.1, “MySQL Cluster Logging Management Commands”.
User Comments
Add your own comment.