Thursday, October 13, 2011

Brewing in MySQL Cluster 7.2.x

Admittedly MySQL Cluster have some way to go before monitoring becomes best in class. But, we are progressing!
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)
lots of numbers! and I'm honestly not quite sure how to interpret them
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

2 comments:

Frazer said...

The transactions and operations tables look interesting - it would be great to see some example VIEWs on them, or queries, especially showing the meaning of the transactions.state, transactions.flags and operations.op, operations.state, operations.flags columns.

Can I use the operations table to find which transactions are holding locks? Can I see which kind of locks are being held? Can I use it to find the queries causing deadlocks timeouts? What do table and index scans look like in these tables?

What is the timer in transactions.timer ?

Can I use MySQL events to capture and store historical snapshots of this data?

Jonas Oreland said...

1) Can I use the operations table to find which transactions are holding locks?

yes

2) Can I see which kind of locks are being held?

yes

3) Can I use it to find the queries causing deadlocks timeouts?

probably

4) What do table and index scans look like in these tables?

Not good (yet)

5) What is the timer in
transactions.timer ?

If outstanding > 0, how long it has been waiting for an op
Else how long it has been idle

6) Can I use MySQL events to capture and store historical snapshots of this data?

sure