What is ndb doing?

In MySQL cluster each SQL statement is translated inside the NDB storage engine to NDB low level protocol that is sent to the data nodes. For the performance it is most interesting how many data is moved between data nodes and MySQL. To monitor this there are several NDB status variables that you can monitor. See this link for more documentation.

(There are also the NDBINFO tables that reflect cluster status. But these are only global values. The status variables also show session status. More about NDBINFO is here.)

To easily report the NDB status on an individual SQL statement, I wrote a little script that gives you the ndb status variables and automatically calculates the diffs before and after the statement in question:

#! /bin/bash

./mysql -t -h olga64 test <<EOF

CREATE TEMPORARY TABLE tmp_before LIKE INFORMATION_SCHEMA.SESSION_STATUS;

CREATE TEMPORARY TABLE tmp_after LIKE INFORMATION_SCHEMA.SESSION_STATUS;

INSERT INTO tmp_before SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'ndb_api%session%';

$1

INSERT INTO tmp_after SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'ndb_api%session%';

SELECT tmp_before.VARIABLE_NAME, tmp_after.VARIABLE_VALUE - tmp_before.VARIABLE_VALUE AS 'VALUE' FROM tmp_after INNER JOIN tmp_before USING (VARIABLE_NAME) WHERE tmp_after.VARIABLE_VALUE <> tmp_before.VARIABLE_VALUE;

EOF

And here are some small examples of understanding what NDB does:

[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t;"

+----------+

| COUNT(*) |

+----------+

|    32771 |

+----------+

+--------------------------------------------+----------+

| VARIABLE_NAME                              | VALUE    |

+--------------------------------------------+----------+

| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION     |        3 |

| NDB_API_WAIT_META_REQUEST_COUNT_SESSION    |        2 |

| NDB_API_WAIT_NANOS_COUNT_SESSION           | 19495775 |

| NDB_API_BYTES_SENT_COUNT_SESSION           |      132 |

| NDB_API_BYTES_RECEIVED_COUNT_SESSION       |      280 |

| NDB_API_TRANS_START_COUNT_SESSION          |        1 |

| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |

| NDB_API_TABLE_SCAN_COUNT_SESSION           |        1 |

| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |

| NDB_API_READ_ROW_COUNT_SESSION             |        2 |

| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION |        2 |

| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION |        1 |

+--------------------------------------------+----------+

 So SELECT COUNT(*) only returns two rows (NDB_API_READ_ROW_COUNT_SESSION). Probably one row per fragment. (I have a two node cluster). COUNT(*) is optimized! But if you add a WHERE condition:

[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t WHERE a<100;"

+----------+

| COUNT(*) |

+----------+

|       99 |

+----------+

+--------------------------------------------+----------+

| VARIABLE_NAME                              | VALUE    |

+--------------------------------------------+----------+

| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION     |        3 |

| NDB_API_WAIT_META_REQUEST_COUNT_SESSION    |        2 |

| NDB_API_WAIT_NANOS_COUNT_SESSION           | 18267962 |

| NDB_API_BYTES_SENT_COUNT_SESSION           |      140 |

| NDB_API_BYTES_RECEIVED_COUNT_SESSION       |     3248 |

| NDB_API_TRANS_START_COUNT_SESSION          |        1 |

| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |

| NDB_API_RANGE_SCAN_COUNT_SESSION           |        1 |

| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |

| NDB_API_READ_ROW_COUNT_SESSION             |       99 |

| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION |       99 |

| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION |        1 |

+--------------------------------------------+----------+

 'a' is the primary key. This statement sends all 99 rows, well only the primary keys and mysqld is counting. This one can get more expensive, depending on the row count and primary key size. Look at NDB_API_BYTES_RECEIVED_COUNT_SESSION: 3248 bytes sent. This is roughly 32 bytes per row that is sent.