ndb_index_statprovides per-fragment statistical information about indexes onNDBtables. This includes cache version and age, number of index entries per partition, and memory consumption by indexes.

Usage

To obtain basic index statistics about a givenNDBtable, invokendb_index_statas shown here, with the name of the table as the first argument and the name of the database containing this table specified immediately following it, using the--database(-d) option:

ndb_index_stattable-ddatabase

In this example, we usendb_index_statto obtain such information about anNDBtable namedmytablein thetestdatabase:

shell> ndb_index_stat -d test mytable table:City index:PRIMARY fragCount:2 sampleVersion:3 loadTime:1399585986 sampleCount:1994 keyBytes:7976 query cache: valid:1 sampleCount:1994 totalBytes:27916 times in ms: save: 7.133 sort: 1.974 sort per sample: 0.000 NDBT_ProgramExit: 0 - OK

sampleVersionis the version number of the cache from which the statistics data is taken. Runningndb_index_statwith the--updateoption causes sampleVersion to be incremented.

loadTimeshows when the cache was last updated. This is expressed as seconds since the Unix Epoch.

sampleCountis the number of index entries found per partition. You can estimate the total number of entries by multiplying this by the number of fragments (shown asfragCount).

sampleCountcan be compared with the cardinality ofSHOW INDEXorINFORMATION_SCHEMA.STATISTICS, although the latter two provide a view of the table as a whole, whilendb_index_statprovides a per-fragment average.

keyBytesis the number of bytes used by the index. In this example, the primary key is an integer, which requires four bytes for each index, sokeyBytescan be calculated in this case as shown here:

keyBytes = sampleCount * (4 bytes per index) = 1994 * 4 = 7976

This information can also be obtained using the corresponding column definitions fromINFORMATION_SCHEMA.COLUMNS(this requires a MySQL Server and a MySQL client application).

totalBytesis the total memory consumed by all indexes on the table, in bytes.

Timings shown in the preceding examples are specific to each invocation ofndb_index_stat.

The--verboseoption provides some additional output, as shown here:

shell> ndb_index_stat -d test mytable --verbose random seed 1337010518 connected loop 1 of 1 table:mytable index:PRIMARY fragCount:4 sampleVersion:2 loadTime:1336751773 sampleCount:0 keyBytes:0 read stats query cache created query cache: valid:1 sampleCount:0 totalBytes:0 times in ms: save: 20.766 sort: 0.001 disconnected NDBT_ProgramExit: 0 - OK shell>

If the only output from the program isNDBT_ProgramExit: 0 - OK, this may indicate that no statistics yet exist. To force them to be created (or updated if they already exist), invokendb_index_statwith the--updateoption, or executeANALYZE TABLEon the table in themysqlclient.

Options

The following table includes options that are specific to the NDB Clusterndb_index_statutility. Additional descriptions are listed following the table.

ndb_index_stat system options.The following options are used to generate and update the statistics tables in the NDB kernel. None of these options can be mixed with statistics options (seendb_index_stat statistics options).

ndb_index_stat statistics options.The options listed here are used to generate index statistics. They work with a given table and database. They cannot be mixed with system options (seendb_index_stat system options).