22.5.17 NDB API Statistics Counters and Variables

A number of types of statistical counters relating to actions performed by or affecting Ndb objects are available. Such actions include starting and closing (or aborting) transactions; primary key and unique key operations; table, range, and pruned scans; threads blocked while waiting for the completion of various operations; and data and events sent and received by NDBCLUSTER. The counters are incremented inside the NDB kernel whenever NDB API calls are made or data is sent to or received by the data nodes. mysqld exposes these counters as system status variables; their values can be read in the output of SHOW STATUS, or by querying the INFORMATION_SCHEMA.SESSION_STATUS or INFORMATION_SCHEMA.GLOBAL_STATUS table. By comparing the values before and after statements operating on NDB tables, you can observe the corresponding actions taken on the API level, and thus the cost of performing the statement.

You can list all of these status variables using the following SHOW STATUS statement:

These status variables are also available from the SESSION_STATUS and GLOBAL_STATUS tables of the INFORMATION_SCHEMA database, as shown here:

Each Ndb object has its own counters. NDB API applications can read the values of the counters for use in optimization or monitoring. For multithreaded clients which use more than one Ndb object concurrently, it is also possible to obtain a summed view of counters from all Ndb objects belonging to a given Ndb_cluster_connection.

Four sets of these counters are exposed. One set applies to the current session only; the other 3 are global. This is in spite of the fact that their values can be obtained as either session or global status variables in the mysql client. This means that specifying the SESSION or GLOBAL keyword with SHOW STATUS has no effect on the values reported for NDB API statistics status variables, and the value for each of these variables is the same whether the value is obtained from the equivalent column of the SESSION_STATUS or the GLOBAL_STATUS table.

You can obtain values for a particular set of variables by additionally filtering for the substring session, slave, or injector in the variable name (along with the common prefix Ndb_api). For _session variables, this can be done as shown here:

To obtain a listing of the NDB API mysqld-level status variables, filter for variable names beginning with ndb_api and ending in _count, like this:

Not all counters are reflected in all 4 sets of status variables. For the event counters DataEventsRecvdCount, NondataEventsRecvdCount, and EventBytesRecvdCount, only _injector and mysqld-level NDB API status variables are available:

_injector status variables are not implemented for any other NDB API counters, as shown here:

The names of the status variables can easily be associated with the names of the corresponding counters. Each NDB API statistics counter is listed in the following table with a description as well as the names of any MySQL server status variables corresponding to this counter.

To see all counts of committed transactions—that is, all TransCommitCount counter status variables—you can filter the results of SHOW STATUS for the substring trans_commit_count, like this:

From this you can determine that 1 transaction has been committed in the current mysql client session, and 2 transactions have been committed on this mysqld since it was last restarted.

You can see how various NDB API counters are incremented by a given SQL statement by comparing the values of the corresponding _session status variables immediately before and after performing the statement. In this example, after getting the initial values from SHOW STATUS, we create in the test database an NDB table, named t, that has a single column:

Now you can execute a new SHOW STATUS statement and observe the changes, as shown here (with the changed rows highlighted in the output):

Similarly, you can see the changes in the NDB API statistics counters caused by inserting a row into t: Insert the row, then run the same SHOW STATUS statement used in the previous example, as shown here:

We can make a number of observations from these results: