MySQL doesn’t offer a documented and easy way to check the usage of resource, like Tables, Indexes, Scan, and etc.

Fortunately, there is an internal table in NDBINFO database, named ndb$pools can be used to check the current and historical high usage of most of resources.

I list the description and the name of each resource.

Use the following statement to check:

ndb_mgm> 2 DUMP 2514 0
select count(*) from ndbinfo.cluster_operations;
select count(distinct fq_name) from ndbinfo.memory_per_fragment where type = 'Ordered index';
select * from ndbinfo.ndb$pools where pool_name in(
'Attribute Record',
'Index Op',
'TC Scan Record',
'Scan Fragment',
'SubOp',
'Fired Trigger',
'Subscriber',
'Subscription',
'Table Record',
'Trigger Record',
'Index') and block_instance=0;
show engine ndbcluster status;

MaxNoOfAttributes

  • Default: 1000
  • Describe: Table, Secondary Unique Hash Index, Ordered Index and BLOB will consume attributes.
  • Usage: Attribute Record

MaxNoOfConcurrentIndexOperations

  • Default: 8192
  • Describe: For queries using a unique hash index at the same time This record is allocated only while executing a part of a query. As soon as this part has been executed, the record is released.
  • Usage: Index Op

MaxNoOfConcurrentOperations

  • Default: 32768
  • Describe: In a transaction, the DML on each row needs a entry on each replica. So, update 4 rows on data node 1 and with 2 replica, needs 8 entities.
  • Usage: ndbinfo.cluster_operations for current

MaxNoOfConcurrentScans

  • Default: 256
  • Describe: All operations but primary or unique key lookups are scan operations. In addition, a scan record is also required for full table scans. It is recommended to set to 500 which is the upper limit
  • Usage: TC Scan Record

MaxNoOfLocalScans

  • Default: 0 = MaxNoOfCuncurrentScans * number_of_data_nodes * 4 + 2
  • Describe: In contrast to scan record, which is used for taking care of scan accessing the entire cluster, local scan record is responsible for accessing data in its own data node.
  • Usage: Scan Fragment

MaxNoOfConcurrentSubOperations

  • Default: 256
  • Describe: Max no of concurrent subscriber operations
  • Usage: SubOp
    • mysql> show engine ndbcluster status;
| ndbcluster | NdbOperation          | created=332, free=332, sizeof=944

MaxNoOfConcurrentTransactions

  • Default: 4096
  • Describe:
    • It doesn’t represent the number of transactions. It represents the number of transaction records instead. A transaction record is a memory buffer used by Transaction Coordinator (TC). One TC on a certain data node takes care of each transaction throughout its lifecycle. Every data node has TC on it. Transaction records is used for coordinating transactions between the data nodes.
    • Each active transaction needs a record. And each table the thread access in the transaction needs a record.
    • The data nodes do cache the transaction objects. So if you had a (mysqld) connection that has used more transaction objects than is usually required for the connections, you can reconnect to release the cached transaction objects.
    • It is recommended to set it as max_connections * (average_number_of_tables_accessed_per_transaction + 1) * number_of_sql_nodes / number_of_node_groups
  • Usage:
mysql> show engine ndbcluster status;
| ndbcluster | NdbTransaction        | created=5, free=0, sizeof=368   

ndb_mgm> 2 DUMP 2514 0
-- log on the data node.
Start of ApiConnectRec summary (4096 total allocated)
Api node 4 connect records seized : 6 stateless : 0 stateful : 1 scan : 0
Api node 5 connect records seized : 0 stateless : 0 stateful : 0 scan : 0

MaxNoOfFiredTriggers

  • Default: 4000
  • Describe: A record is created when an operation is performed that affects a unique hash index. Inserting or deleting a record in a table with unique hash indexes or updating a column that is part of a unique hash index fires an insert or a delete in the index table.
  • Usage: Fired Trigger

MaxNoOfOrderedIndexes

  • Default: 128
  • Describe: Total number of ordered indexes that can be defined in the system
  • Usage:

select count(distinct fq_name) from ndbinfo.memory_per_fragment where type = ‘Ordered index’

MaxNoOfSubscribers

  • Default: 0 == 2 * MaxNoOfTables
  • Describe: For NDB replication only. There is a replication subscription for each table each direction. So, the default value is enough for two nodes with bidirectional replication. But, for a circular replication setup using three Nodes, we need to change this value to 3 * MaxNoOfTables
  • Usage: Subscriber

MaxNoOfSubscriptions

  • Default: 0 = MaxNoOfTables
  • Describe: Each NDB table in an NDB Cluster requires a subscription in the NDB kernel.
  • Usage: Subscription

MaxNoOfTables

  • Default: 128, but it is not the exact number you can have, the actually number of objects is calculated based on this value.
  • Describe: Every objects, including:Table, Secondary Unique Hash Index, Ordered Index and BLOB, will consume one entry of this resource.
  • Usage: Table Record. The exact limit will also be here.

MaxNoOfTriggers

  • Default: 768
  • Describe: Total number of triggers that can be defined in the system. The number of required triggers is adjusted internally using the following options: MaxNoOfTables, MaxNoOfOrderedIndexes, and MaxNoOfUniqueHashIndexes.
  • Usage: Trigger Record

MaxNoOfUniqueHashIndexes

  • Default: 64
  • Describe: Total number of unique hash indexes that can be defined in the system. But this parameter is not working, it doesn’t restrict the number of Unique Hash Indexes in the system.
  • Usage: Index