In 7.1 we introduced NDBINFO, which is an infrastructure that enables presenting information from within the cluster in SQL format.
And here are a 4 new tables that are currently brewing
ndbinfo.transactions and ndbinfo.operations
mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$transactions'; +----------------+-----------+---------------------------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_COMMENT | +----------------+-----------+---------------------------------+ | node_id | int | node id | | block_instance | int | TC instance no | | objid | int | Object id of transaction object | | apiref | int | API reference | | transid | varchar | Transaction id | | state | int | Transaction state | | flags | int | Transaction flags | | c_ops | int | No of operations in transaction | | outstanding | int | Currently outstanding request | | timer | int | Timer (seconds) | +----------------+-----------+---------------------------------+ mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$operations'; +----------------+-----------+-------------------------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_COMMENT | +----------------+-----------+-------------------------------+ | node_id | int | node id | | block_instance | int | LQH instance no | | objid | int | Object id of operation object | | tcref | int | TC reference | | apiref | int | API reference | | transid | varchar | Transaction id | | tableid | int | Table id | | fragmentid | int | Fragment id | | op | int | Operation type | | state | int | Operation state | | flags | int | Operation flags | +----------------+-----------+-------------------------------+
- these two tables show currently ongoing transactions resp. currently ongoing operations.
- ndbinfo.transactions roughly corresponds to information_schema.INNODB_TRX
- ndbinfo.operations roughly corresponds to information_schema.INNODB_LOCKS
- the information provided is collected without any kind of locks
- the information provided is collected by iterating internal data-structures. Hence output does not necessarily represent a state that has existed (i.e not a snapshot)
one missing piece of this puzzle is how to map a ndb transaction id, to a mysql connection id.
when (if?) this information is available, one could e.g join information_schema.processlist with ndbinfo.operations too see locks are being held by a certain connection. (suggestion on how to gather/expose this is welcome).
ndbinfo.threadblocks and ndbinfo.threadstat
mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$threadblocks'; +----------------+-----------+----------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_COMMENT | +----------------+-----------+----------------+ | node_id | int | node id | | thr_no | int | thread number | | block_number | int | block number | | block_instance | int | block instance | +----------------+-----------+----------------+ mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$threadstat'; +----------------+-----------+------------------------------------------+ | COLUMN_NAME | DATA_TYPE | COLUMN_COMMENT | +----------------+-----------+------------------------------------------+ | node_id | int | node id | | thr_no | int | thread number | | thr_nm | varchar | thread name | | c_loop | bigint | No of loops in main loop | | c_exec | bigint | No of signals executed | | c_wait | bigint | No of times waited for more input | | c_l_sent_prioa | bigint | No of prio A signals sent to own node | | c_l_sent_priob | bigint | No of prio B signals sent to own node | | c_r_sent_prioa | bigint | No of prio A signals sent to remote node | | c_r_sent_priob | bigint | No of prio B signals sent to remote node | | os_tid | bigint | OS thread id | | os_now | bigint | OS gettimeofday (millis) | | os_ru_utime | bigint | OS user CPU time (micros) | | os_ru_stime | bigint | OS system CPU time (micros) | | os_ru_minflt | bigint | OS page reclaims (soft page faults | | os_ru_majflt | bigint | OS page faults (hard page faults) | | os_ru_nvcsw | bigint | OS voluntary context switches | | os_ru_nivcsw | bigint | OS involuntary context switches | +----------------+-----------+------------------------------------------+these two tables shows currently which blocks run in which thread resp. statistics per thread.
- the statistics are from data-node started, so to see trend, one need to snapshot table, and compare with snapshot.
- the fields starting with
os_ru_are gather with getrusage(RUSAGE_THREAD) (or equivalent)
a few simple rules might be that (for a non idle cluster)
- user time should be high and system should be low
- involuntary context switches should be low
- page faults should be low
STANDARD DISCLAIMER
- the exact format of the tables might (will) change before reaching a release
- it's currently unknown when(if?) they will reach a release near you
