MySQL Cluster7.5でNDBINFOに新たに便利なテーブルが追加されているので、此方にリストアップしておきました。詳細に関しては、以下のマニュアルを参照下さい。

MySQL Cluster7.5 pdfマニュアル
20.1.4 What is New in MySQL NDB Cluster 7.5

あと、オンラインでデータノードを追加したあとに、テーブルのデータを再分布する場合のコマンドがALTER TABLE ~ ONLINEではなくなりました。
MySQLと同じALTER TABLE ~ ALGORITHM = INPLACE ~に変わりました。

ALTER TABLE Changes:
NDB Cluster formerly supported an alternative syntax for online ALTER TABLE.
This is no longer supported in NDB Cluster 7.5, which makes exclusive use of
ALGORITHM = DEFAULT|COPY|INPLACE for table DDL, as in the standard MySQL Server.

New Tables in NDBINFO
メタデータの確認や、MySQL Cluster内部で何処でLOCKが発生しているか確認し易くなってます。
便利です。


/***** provides per-thread CPU statistics gathered each second, for each thread running in the NDB kernel. ******/

mysql> select * from cpustat;
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
| node_id | thr_no | OS_user | OS_system | OS_idle | thread_exec | thread_sleeping | thread_spinning | thread_send | thread_buffer_full | elapsed_time |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
|       1 |      0 |       0 |         0 |      99 |           1 |              99 |               0 |           0 |                  0 |      1022319 |
|       1 |      1 |       0 |         1 |      99 |           0 |             100 |               0 |           0 |                  0 |      1043157 |
|       1 |      2 |       1 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1044002 |
|       1 |      3 |       6 |         1 |      93 |           2 |              98 |               0 |           0 |                  0 |      1004561 |
|       2 |      0 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1021821 |
|       2 |      1 |       0 |         0 |     100 |           0 |             100 |               0 |           0 |                  0 |      1041181 |
|       2 |      2 |       0 |         0 |      99 |           0 |             100 |               0 |           0 |                  0 |      1000446 |
|       2 |      3 |       4 |         4 |      92 |           2 |              98 |               0 |           0 |                  0 |      1006593 |
+---------+--------+---------+-----------+---------+-------------+-----------------+-----------------+-------------+--------------------+--------------+
8 rows in set (0.01 sec)

/***** provides raw, per-thread CPU data obtained each second for each thread running in the NDB kernel.  ******/

mysql> select * from cpustat_1sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |         4249 |           6543 |      1008090 |      6723 |    1012159 |         0 |         0 |                0 |      1018882 |
|       1 |      0 |         3042 |           7882 |      1012472 |      6636 |    1016760 |         0 |         0 |                0 |      1023396 |
|       1 |      0 |         3779 |           6373 |      1003011 |      6882 |    1006281 |         0 |         0 |                0 |      1013163 |
|       1 |      0 |         2011 |           6151 |      1030521 |      5951 |    1032732 |         0 |         0 |                0 |      1038683 |
|       1 |      0 |         4056 |           5567 |      1039330 |      6329 |    1042624 |         0 |         0 |                0 |      1048953 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

/***** provides raw, per-thread CPU data obtained each 50 milliseconds for each thread running in the NDB kernel ******/

mysql> select * from cpustat_50ms limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |          138 |            180 |        56563 |       244 |      56637 |         0 |         0 |                0 |        56881 |
|       1 |      0 |          411 |            537 |        58241 |       626 |      58563 |         0 |         0 |                0 |        59189 |
|       1 |      0 |          124 |            630 |        52635 |       471 |      52918 |         0 |         0 |                0 |        53389 |
|       1 |      0 |            0 |            943 |        49634 |       398 |      50179 |         0 |         0 |                0 |        50577 |
|       1 |      0 |            0 |            377 |        52557 |       299 |      52635 |         0 |         0 |                0 |        52934 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql>

/***** provides raw, per-thread CPU data obtained each 20 seconds, for each thread running in the NDB kernel. ******/

mysql> mysql> select * from cpustat_20sec limit 5;
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
| node_id | thr_no | OS_user_time | OS_system_time | OS_idle_time | exec_time | sleep_time | spin_time | send_time | buffer_full_time | elapsed_time |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
|       1 |      0 |        85502 |         105281 |     19833351 |    131831 |   19892303 |         0 |         0 |                0 |     20024134 |
|       1 |      0 |        85028 |          98842 |     19820167 |    121907 |   19882130 |         0 |         0 |                0 |     20004037 |
|       1 |      0 |        77030 |         105719 |     19842259 |    121151 |   19903857 |         0 |         0 |                0 |     20025008 |
|       1 |      0 |        74431 |         101572 |     19827545 |    122715 |   19880833 |         0 |         0 |                0 |     20003548 |
|       1 |      0 |        85342 |         101835 |     19813255 |    127802 |   19872630 |         0 |         0 |                0 |     20000432 |
+---------+--------+--------------+----------------+--------------+-----------+------------+-----------+-----------+------------------+--------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about threads running in the NDB kernel. ******/
 

mysql> select * from threads;
+---------+--------+-------------+------------------------------------------------------------------+
| node_id | thr_no | thread_name | thread_description                                               |
+---------+--------+-------------+------------------------------------------------------------------+
|       1 |      0 | main        | main thread, schema and distribution handling                    |
|       1 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       1 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       1 |      3 | recv        | receive thread, performing receieve and polling for new receives |
|       2 |      0 | main        | main thread, schema and distribution handling                    |
|       2 |      1 | rep         | rep thread, asynch replication and proxy block handling          |
|       2 |      2 | ldm         | ldm thread, handling a set of data partitions                    |
|       2 |      3 | recv        | receive thread, performing receieve and polling for new receives |
+---------+--------+-------------+------------------------------------------------------------------+
8 rows in set (0.01 sec)

mysql> 


/***** provides information about current lock requests holding and waiting for locks on NDB tables in an NDB Cluster , and is intended as a companion table to cluster_operations. ******/

mysql> desc cluster_locks;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| node_id         | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance  | int(10) unsigned    | YES  |     | NULL    |       |
| tableid         | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid      | int(10) unsigned    | YES  |     | NULL    |       |
| rowid           | bigint(20) unsigned | YES  |     | NULL    |       |
| transid         | bigint(22) unsigned | YES  |     | NULL    |       |
| mode            | varchar(1)          | NO   |     |         |       |
| state           | varchar(1)          | NO   |     |         |       |
| detail          | varchar(1)          | NO   |     |         |       |
| op              | varchar(9)          | NO   |     |         |       |
| duration_millis | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num        | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for     | bigint(10) unsigned | YES  |     | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)

mysql> 



/***** provides information about counts of lock claim requests, and the outcomes of these requests on a per-fragment basis, serving as a companion table to operations_per_fragment and memory_per_fragment.  ******/

mysql> select * from locks_per_fragment limit 5;
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name                      | parent_fq_name         | type              | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       1 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            0 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| TEST_DB/def/NDB$BLOB_82_1    | TEST_DB/def/T_NDB_JSON | User table        |       83 |       2 |              1 |            1 |      0 |         0 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sys/def/24/idx_memo01$unique | TEST_DB/def/T_TABLE01  | Unique hash index |       79 |       1 |              1 |            0 |     10 |        10 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+------------------------------+------------------------+-------------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
5 rows in set (0.03 sec)

mysql> 



mysql> select * from locks_per_fragment where fq_name like '%sbtest1';
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| fq_name              | parent_fq_name | type       | table_id | node_id | block_instance | fragment_num | ex_req | ex_imm_ok | ex_wait_ok | ex_wait_fail | sh_req | sh_imm_ok | sh_wait_ok | sh_wait_fail | wait_ok_millis | wait_fail_millis |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       1 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            0 |   5080 |      5080 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
| sysbench/def/sbtest1 | NULL           | User table |       90 |       2 |              1 |            1 |   4920 |      4920 |          0 |            0 |      0 |         0 |          0 |            0 |              0 |                0 |
+----------------------+----------------+------------+----------+---------+----------------+--------------+--------+-----------+------------+--------------+--------+-----------+------------+--------------+----------------+------------------+
4 rows in set (0.03 sec)

mysql> 


/***** similar in structure to the cluster_locks table, and provides a subset of the information found in the latter table, but which is specific to the SQL node ******/

mysql> desc server_locks;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| mysql_connection_id | bigint(21) unsigned | NO   |     | 0       |       |
| node_id             | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance      | int(10) unsigned    | YES  |     | NULL    |       |
| tableid             | int(10) unsigned    | YES  |     | NULL    |       |
| fragmentid          | int(10) unsigned    | YES  |     | NULL    |       |
| rowid               | bigint(20) unsigned | YES  |     | NULL    |       |
| transid             | bigint(22) unsigned | YES  |     | NULL    |       |
| mode                | varchar(1)          | NO   |     |         |       |
| state               | varchar(1)          | NO   |     |         |       |
| detail              | varchar(1)          | NO   |     |         |       |
| op                  | varchar(9)          | NO   |     |         |       |
| duration_millis     | int(10) unsigned    | YES  |     | NULL    |       |
| lock_num            | int(10) unsigned    | YES  |     | NULL    |       |
| waiting_for         | bigint(10) unsigned | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> 

/***** provides the names and types of database objects in NDB, as well as information about parent obejcts where applicable ******/

mysql> select * from dict_obj_info limit 5;
+------+------+----------+-------+-----------------+---------------+----------------------------+
| type | id   | version  | state | parent_obj_type | parent_obj_id | fq_name                    |
+------+------+----------+-------+-----------------+---------------+----------------------------+
|    6 |   89 |        1 |     4 |               2 |            88 | sys/def/88/PRIMARY         |
|   18 |   44 |        1 |     4 |               6 |            98 | NDB$INDEX_98_CUSTOM        |
|    2 |   31 | 16777218 |     4 |               0 |             0 | TEST_DB_InnoDB/def/Country |
|    6 |   59 |        1 |     4 |               2 |            33 | sys/def/33/PRIMARY         |
|   18 |    2 |        1 |     4 |               6 |            37 | NDB$INDEX_37_CUSTOM        |
+------+------+----------+-------+-----------------+---------------+----------------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides NDB table distribution status information  ******/

mysql> select * from table_distribution_status limit 5;
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
| node_id | table_id | tab_copy_status | tab_update_status | tab_lcp_status | tab_status | tab_storage | tab_partitions | tab_fragments | current_scan_count | scan_count_wait | is_reorg_ongoing |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
|       1 |        2 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |        3 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       11 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       12 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       13 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> 


/***** provides information about the distribution of NDB table fragments   ******/

mysql> select * from table_fragments limit 5;
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
| node_id | table_id | partition_id | fragment_id | partition_order | log_part_id | no_of_replicas | current_primary | preferred_primary | current_first_backup | current_second_backup | current_third_backup | num_alive_replicas | num_dead_replicas | num_lcp_replicas |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
|       1 |        2 |   4294967295 |           0 |               0 |           0 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        2 |   4294967295 |           1 |               0 |           0 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           0 |               0 |           1 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           1 |               0 |           1 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |       11 |   4294967295 |           0 |               0 |           3 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

/***** provides information about logging, checkpointing, storage, and other options in force for each NDB table    ******/

mysql> select * from table_info where table_id = 31;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       31 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 


mysql> select * from table_info where table_id = 90;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       90 |            1 |                1 |                     1 |           1 |                0 | MEMORY       |          1 | SPECIFIC          |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
1 row in set (0.01 sec)

mysql> 

/***** provides information about fragment replicas   ******/

mysql> select * from table_replicas limit 5;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |        2 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 


mysql> select * from table_replicas where table_id = 90;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |       90 |           0 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           0 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               1 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
|       1 |       90 |           1 |       36225 |               2 |              0 |                    0 |               100360 |                 100360 |          45 |       85293 |                85293 |                     44 |           85293 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
4 rows in set (0.01 sec)

mysql> 

config_paramsとconfig_valuesをJOINした結果

パラメータ確認し易くて良い


mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.02 sec)

cluster_locksでロックを確認した場合


mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13569072998383655 | X    | W     |        | READ |            2593 |        0 |           1 |
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
3 rows in set (0.04 sec)

MySQL7.5のDefault設定値
何も設定しなければ、こちらの値が適用されます。

+------------------------------------------+----------+------------+----------+----------------+----------+
| Name                                     | Type     | Default    | Minimum  | Maximum        | Required |
+------------------------------------------+----------+------------+----------+----------------+----------+
| MaxNoOfSubscriptions                     | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfSubscribers                       | unsigned | 0          | 0        | 4294967039     | N        |
| MaxNoOfConcurrentSubOperations           | unsigned | 256        | 0        | 4294967039     | N        |
| TcpBind_INADDR_ANY                       | bool     | 0          |          |                | N        |
| HostName                                 | string   | localhost  |          |                | N        |
| NodeId                                   | unsigned |            | 1        | 48             | Y        |
| ServerPort                               | unsigned |            | 1        | 65535          | N        |
| NoOfReplicas                             | unsigned | 2          | 1        | 4              | N        |
| MaxNoOfAttributes                        | unsigned | 1000       | 32       | 4294967039     | N        |
| MaxNoOfTables                            | unsigned | 128        | 8        | 20320          | N        |
| MaxNoOfOrderedIndexes                    | unsigned | 128        | 0        | 4294967039     | N        |
| MaxNoOfUniqueHashIndexes                 | unsigned | 64         | 0        | 4294967039     | N        |
| MaxNoOfConcurrentIndexOperations         | unsigned | 8192       | 0        | 4294967039     | N        |
| MaxNoOfTriggers                          | unsigned | 768        | 0        | 4294967039     | N        |
| MaxNoOfFiredTriggers                     | unsigned | 4000       | 0        | 4294967039     | N        |
| MaxNoOfSavedMessages                     | unsigned | 25         | 0        | 4294967039     | N        |
| LockExecuteThreadToCPU                   | bitmask  |            |          |                | N        |
| LockMaintThreadsToCPU                    | unsigned |            | 0        | 65535          | N        |
| RealtimeScheduler                        | bool     | 0          |          |                | N        |
| LockPagesInMainMemory                    | unsigned | 0          | 0        | 2              | N        |
| TimeBetweenWatchDogCheck                 | unsigned | 6000       | 70       | 4294967039     | N        |
| SchedulerExecutionTimer                  | unsigned | 50         | 0        | 11000          | N        |
| MaxSendDelay                             | unsigned | 0          | 0        | 11000          | N        |
| SchedulerSpinTimer                       | unsigned | 0          | 0        | 500            | N        |
| SchedulerResponsiveness                  | unsigned | 5          | 0        | 10             | N        |
| __sched_scan_priority                    | unsigned | 6          | 1        | 6              | N        |
| TimeBetweenWatchDogCheckInitial          | unsigned | 6000       | 70       | 4294967039     | N        |
| StopOnError                              | bool     | 1          |          |                | N        |
| MaxNoOfConcurrentOperations              | unsigned | 32768      | 32       | 4294967039     | N        |
| MaxDMLOperationsPerTransaction           | unsigned | 4294967295 | 32       | 4294967295     | N        |
| MaxNoOfLocalOperations                   | unsigned |            | 32       | 4294967039     | N        |
| MaxNoOfLocalScans                        | unsigned |            | 32       | 4294967039     | N        |
| BatchSizePerLocalScan                    | unsigned | 256        | 1        | 992            | N        |
| MaxNoOfConcurrentTransactions            | unsigned | 4096       | 32       | 4294967039     | N        |
| MaxNoOfConcurrentScans                   | unsigned | 256        | 2        | 500            | N        |
| TransactionBufferMemory                  | unsigned | 1048576    | 1024     | 4294967039     | N        |
| IndexMemory                              | unsigned | 18874368   | 1048576  | 1099511627776  | N        |
| DataMemory                               | unsigned | 83886080   | 1048576  | 1099511627776  | N        |
| UndoIndexBuffer                          | unsigned | 2097152    | 1048576  | 4294967039     | N        |
| UndoDataBuffer                           | unsigned | 16777216   | 1048576  | 4294967039     | N        |
| RedoBuffer                               | unsigned | 33554432   | 1048576  | 4294967039     | N        |
| LongMessageBuffer                        | unsigned | 67108864   | 524288   | 4294967039     | N        |
| DiskPageBufferMemory                     | unsigned | 67108864   | 4194304  | 1099511627776  | N        |
| SharedGlobalMemory                       | unsigned | 134217728  | 0        | 70368744177664 | N        |
| StartPartialTimeout                      | unsigned | 30000      | 0        | 4294967039     | N        |
| StartPartitionedTimeout                  | unsigned | 60000      | 0        | 4294967039     | N        |
| StartFailureTimeout                      | unsigned | 0          | 0        | 4294967039     | N        |
| StartNoNodegroupTimeout                  | unsigned | 15000      | 0        | 4294967039     | N        |
| HeartbeatIntervalDbDb                    | unsigned | 5000       | 10       | 4294967039     | N        |
| ConnectCheckIntervalDelay                | unsigned | 0          | 0        | 4294967039     | N        |
| HeartbeatIntervalDbApi                   | unsigned | 1500       | 100      | 4294967039     | N        |
| TimeBetweenLocalCheckpoints              | unsigned | 20         | 0        | 31             | N        |
| TimeBetweenGlobalCheckpoints             | unsigned | 2000       | 20       | 32000          | N        |
| TimeBetweenGlobalCheckpointsTimeout      | unsigned | 120000     | 10       | 4294967039     | N        |
| TimeBetweenEpochs                        | unsigned | 100        | 0        | 32000          | N        |
| TimeBetweenEpochsTimeout                 | unsigned | 0          | 0        | 256000         | N        |
| MaxBufferedEpochs                        | unsigned | 100        | 1        | 100000         | N        |
| MaxBufferedEpochBytes                    | unsigned | 26214400   | 26214400 | 4294967039     | N        |
| NoOfFragmentLogParts                     | unsigned | 4          | 4        | 32             | N        |
| NoOfFragmentLogFiles                     | unsigned | 16         | 3        | 4294967039     | N        |
| FragmentLogFileSize                      | unsigned | 16777216   | 4194304  | 1073741824     | N        |
| InitFragmentLogFiles                     | string   | sparse     |          |                | N        |
| DiskIOThreadPool                         | unsigned | 2          | 0        | 4294967039     | N        |
| MaxNoOfOpenFiles                         | unsigned |            | 20       | 4294967039     | N        |
| InitialNoOfOpenFiles                     | unsigned | 27         | 20       | 4294967039     | N        |
| TimeBetweenInactiveTransactionAbortCheck | unsigned | 1000       | 1000     | 4294967039     | N        |
| TransactionInactiveTimeout               | unsigned | 4294967039 | 0        | 4294967039     | N        |
| TransactionDeadlockDetectionTimeout      | unsigned | 1200       | 50       | 4294967039     | N        |
| Diskless                                 | bool     | 0          |          |                | N        |
| ArbitrationTimeout                       | unsigned | 7500       | 10       | 4294967039     | N        |
| Arbitration                              | enum     | Default    |          |                | N        |
| DataDir                                  | string   | .          |          |                | N        |
| FileSystemPath                           | string   |            |          |                | N        |
| LogLevelStartup                          | unsigned | 1          | 0        | 15             | N        |
| LogLevelShutdown                         | unsigned | 0          | 0        | 15             | N        |
| LogLevelStatistic                        | unsigned | 0          | 0        | 15             | N        |
| LogLevelCheckpoint                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelNodeRestart                      | unsigned | 0          | 0        | 15             | N        |
| LogLevelConnection                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelCongestion                       | unsigned | 0          | 0        | 15             | N        |
| LogLevelError                            | unsigned | 0          | 0        | 15             | N        |
| LogLevelInfo                             | unsigned | 0          | 0        | 15             | N        |
| BackupDataDir                            | string   |            |          |                | N        |
| DiskSyncSize                             | unsigned | 4194304    | 32768    | 4294967039     | N        |
| MinDiskWriteSpeed                        | unsigned | 10485760   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeed                        | unsigned | 20971520   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOtherNodeRestart        | unsigned | 52428800   | 1048576  | 1099511627776  | N        |
| MaxDiskWriteSpeedOwnRestart              | unsigned | 209715200  | 1048576  | 1099511627776  | N        |
| BackupDiskWriteSpeedPct                  | unsigned | 50         | 0        | 90             | N        |
| BackupDataBufferSize                     | unsigned | 16777216   | 524288   | 4294967039     | N        |
| BackupLogBufferSize                      | unsigned | 16777216   | 2097152  | 4294967039     | N        |
| BackupWriteSize                          | unsigned | 262144     | 32768    | 4294967039     | N        |
| BackupMaxWriteSize                       | unsigned | 1048576    | 262144   | 4294967039     | N        |
| StringMemory                             | unsigned | 25         | 0        | 4294967039     | N        |
| MaxAllocate                              | unsigned | 33554432   | 1048576  | 1073741824     | N        |
| MemReportFrequency                       | unsigned | 0          | 0        | 4294967039     | N        |
| BackupReportFrequency                    | unsigned | 0          | 0        | 4294967039     | N        |
| StartupStatusReportFrequency             | unsigned | 0          | 0        | 4294967039     | N        |
| ODirect                                  | bool     | 0          |          |                | N        |
| CompressedBackup                         | bool     | 0          |          |                | N        |
| CompressedLCP                            | bool     | 0          |          |                | N        |
| ExtraSendBufferMemory                    | unsigned | 0          | 0        | 34359738368    | N        |
| TotalSendBufferMemory                    | unsigned | 0          | 262144   | 4294967039     | N        |
| Nodegroup                                | unsigned |            | 0        | 65536          | N        |
| MaxNoOfExecutionThreads                  | unsigned | 0          | 2        | 72             | N        |
| __ndbmt_lqh_workers                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_lqh_threads                      | unsigned |            | 1        | 4              | N        |
| __ndbmt_classic                          | bool     |            |          |                | N        |
| ThreadConfig                             | string   |            |          |                | N        |
| FileSystemPathDD                         | string   |            |          |                | N        |
| FileSystemPathDataFiles                  | string   |            |          |                | N        |
| FileSystemPathUndoFiles                  | string   |            |          |                | N        |
| InitialLogfileGroup                      | string   |            |          |                | N        |
| InitialTablespace                        | string   |            |          |                | N        |
| MaxLCPStartDelay                         | unsigned | 0          | 0        | 600            | N        |
| BuildIndexThreads                        | unsigned | 0          | 0        | 128            | N        |
| HeartbeatOrder                           | unsigned | 0          | 0        | 65535          | N        |
| DictTrace                                | unsigned |            | 0        | 100            | N        |
| MaxStartFailRetries                      | unsigned | 3          | 0        | 4294967039     | N        |
| StartFailRetryDelay                      | unsigned | 0          | 0        | 4294967039     | N        |
| EventLogBufferSize                       | unsigned | 8192       | 0        | 65536          | N        |
| Numa                                     | unsigned | 1          | 0        | 1              | N        |
| RedoOverCommitLimit                      | unsigned | 20         | 0        | 4294967039     | N        |
| RedoOverCommitCounter                    | unsigned | 3          | 0        | 4294967039     | N        |
| LateAlloc                                | unsigned | 1          | 0        | 1              | N        |
| MaxParallelCopyInstances                 | unsigned | 0          | 0        | 64             | N        |
| TwoPassInitialNodeRestartCopy            | bool     | 0          |          |                | N        |
| MaxParallelScansPerFragment              | unsigned | 256        | 1        | 4294967039     | N        |
| IndexStatAutoCreate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatAutoUpdate                      | unsigned | 0          | 0        | 1              | N        |
| IndexStatSaveSize                        | unsigned | 32768      | 0        | 4294967039     | N        |
| IndexStatSaveScale                       | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerPct                      | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatTriggerScale                    | unsigned | 100        | 0        | 4294967039     | N        |
| IndexStatUpdateDelay                     | unsigned | 60         | 0        | 4294967039     | N        |
| CrashOnCorruptedTuple                    | bool     | 1          |          |                | N        |
| MinFreePct                               | unsigned | 5          | 0        | 100            | N        |
| DefaultHashmapSize                       | unsigned | 0          | 0        | 3840           | N        |
| LcpScanProgressTimeout                   | unsigned | 60         | 0        | 4294967039     | N        |
| __at_restart_skip_indexes                | bool     | 0          |          |                | N        |
| __at_restart_skip_fks                    | bool     | 0          |          |                | N        |
| __debug_mixology_level                   | unsigned | 0          | 0        | 4294967039     | N        |
| RestartSubscriberConnectTimeout          | unsigned | 120000     | 0        | 4294967039     | N        |
| DiskPageBufferEntries                    | unsigned | 10         | 1        | 4294967295     | N        |
+------------------------------------------+----------+------------+----------+----------------+----------+

先日、パフォーマンス検証したMySQL Cluster(7.5.3 RC)ですが、MySQL Cluster7.5.4でついにGAになりました。高可用性、インメモリー処理、トランザクション対応したNoSQL/SQL処理、オンライン拡張性が求められるシステムを探されている場合は、是非試してみて下さい。

7.5では、パフォーマンス最適化オプションだけでなく、ndbinfoから様々な構成や稼働情報を簡単に確認する事が出来るようになっています。これまで、クラスターコンフィグファイル等で確認していた内容もクエリーで即座に複数ノードを跨った設定を確認出来るようになってますので、これまで以上に運用がし易くなるかと思います。

Changes in MySQL Cluster NDB 7.5.4 (5.7.16-ndb-7.5.4) (2016-10-18, General Availability)
http://dev.mysql.com/doc/relnotes/mysql-cluster/7.5/en/mysql-cluster-news-7-5-4.html

MySQL Cluster 7.5 パフォーマンス検証

確認:バージョン

mysql> select @@version;
+----------------------------------------------+
| @@version                                    |
+----------------------------------------------+
| 5.7.16-ndb-7.5.4-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

config_paramsとconfig_valuesを利用したMySQL Clusterパラメータの容易な確認

mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.03 sec)

mysql> 

mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name like 'max%';
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| Name                              | Node | Type     | Default    | Minimum  | Maximum       | Required | Current    |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| MaxNoOfSavedMessages              |    1 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    1 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    1 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    1 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    1 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    1 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    1 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    1 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    1 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    1 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    1 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    1 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    1 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    1 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    1 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    1 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    1 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    1 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    1 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    1 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    1 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    1 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    1 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    1 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    1 | unsigned | 0          | 0        | 11000         | N        | 0          |
| MaxNoOfSavedMessages              |    2 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    2 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    2 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    2 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    2 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    2 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    2 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    2 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    2 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    2 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    2 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    2 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    2 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    2 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    2 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    2 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    2 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    2 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    2 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    2 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    2 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    2 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    2 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    2 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    2 | unsigned | 0          | 0        | 11000         | N        | 0          |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
54 rows in set (0.01 sec)

mysql> 

dict_obj_info
The dict_obj_info table provides information about
NDB data dictionary (DICT) objects such as tables and indexes.
こちらのテーブルはNDBデータディクショナリーの情報を確認する事が可能です。


mysql> select * from dict_obj_info limit 5;
+------+------+----------+-------+-----------------+---------------+----------------------------+
| type | id   | version  | state | parent_obj_type | parent_obj_id | fq_name                    |
+------+------+----------+-------+-----------------+---------------+----------------------------+
|    6 |   89 |        1 |     4 |               2 |            88 | sys/def/88/PRIMARY         |
|   18 |   44 |        1 |     4 |               6 |            98 | NDB$INDEX_98_CUSTOM        |
|    2 |   31 | 16777218 |     4 |               0 |             0 | TEST_DB_InnoDB/def/Country |
|    6 |   59 |        1 |     4 |               2 |            33 | sys/def/33/PRIMARY         |
|   18 |    2 |        1 |     4 |               6 |            37 | NDB$INDEX_37_CUSTOM        |
+------+------+----------+-------+-----------------+---------------+----------------------------+
5 rows in set (0.00 sec)

mysql> 

table_distribution_status
The table_distribution_status table provides information about the progress of table distribution for NDB tables.
こちらのテーブルでは、NDBテーブルのディストリビューションステータスを確認する事が可能です。

mysql> select * from table_distribution_status limit 5;
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
| node_id | table_id | tab_copy_status | tab_update_status | tab_lcp_status | tab_status | tab_storage | tab_partitions | tab_fragments | current_scan_count | scan_count_wait | is_reorg_ongoing |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
|       1 |        2 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |        3 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       11 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       12 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       13 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> 

table_fragments
The table_fragments table provides information about the fragmentation, partitioning, distribution, and (internal) replication of NDB tables.
此方のテーブルでは、NDBテーブルのフラグメンテーション、パーティション、ディストリビューション、レプリケーションを確認する事が可能です。

mysql> select * from table_fragments limit 5;
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
| node_id | table_id | partition_id | fragment_id | partition_order | log_part_id | no_of_replicas | current_primary | preferred_primary | current_first_backup | current_second_backup | current_third_backup | num_alive_replicas | num_dead_replicas | num_lcp_replicas |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
|       1 |        2 |   4294967295 |           0 |               0 |           0 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        2 |   4294967295 |           1 |               0 |           0 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           0 |               0 |           1 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           1 |               0 |           1 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |       11 |   4294967295 |           0 |               0 |           3 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

table_info
The table_info table provides information about logging, checkpointing, distribution, and storage options in effect for individual NDB tables.
こちらのテーブルでは、各NDBテーブルのログ設定、チェックポイント、ディストリビューション、ストレージオプション等を確認する事が可能です。

mysql> select * from  table_info limit 5;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       31 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|       12 |            1 |                1 |                     1 |           0 |                0 | DISK         |          1 | FOR_RP_BY_LDM     |          0 |
|       82 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|       13 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|        3 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
5 rows in set (0.01 sec)

mysql> 

table_replicas
The table_replicas table provides information about the copying, distribution, and checkpointing of NDB table fragments and fragment replicas.
こちらのテーブルでは、NDBテーブルフラグメントとレプリカのコピー、ディストリビューション、チェックポイントの情報を確認する事が可能です。

mysql> select * from table_replicas limit 5;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |        2 |           0 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

これらの追加機能やテーブルを活用して、更にMSQL Clusterを活用するケースが増えればと思います。

参考: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new.html

補足:アップグレードに再度パフォーマンス検証(MySQL Cluster 7.5.4 + Sysbench 0.5)
READ_BACKUP=0
185.78 per sec

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            156310
        write:                           0
        other:                           22330
        total:                           178640
    transactions:                        11165  (185.78 per sec.)
    read/write requests:                 156310 (2600.95 per sec.)
    other operations:                    22330  (371.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0973s
    total number of events:              11165
    total time taken by event execution: 1921.4885s
    response time:
         min:                                 38.13ms
         avg:                                172.10ms
         max:                                390.91ms
         approx.  95 percentile:             243.58ms

Threads fairness:
    events (avg/stddev):           348.9062/3.30
    execution time (avg/stddev):   60.0465/0.02

READ_BACKUP=1
240.78 per sec.

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            202482
        write:                           0
        other:                           28926
        total:                           231408
    transactions:                        14463  (240.78 per sec.)
    read/write requests:                 202482 (3370.96 per sec.)
    other operations:                    28926  (481.57 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0666s
    total number of events:              14463
    total time taken by event execution: 1919.6420s
    response time:
         min:                                 66.34ms
         avg:                                132.73ms
         max:                                500.21ms
         approx.  95 percentile:             183.90ms

Threads fairness:
    events (avg/stddev):           451.9688/3.86
    execution time (avg/stddev):   59.9888/0.04

[root@CL-SLAVE01 sys03]# 

MySQL Cluster7.5からPK参照以外の参照性能が向上している。
テーブル作成時にREAD_BACKUPオプションを利用するか、ndb_read_backupオプションを設定しておくとPRIMARYレプリカだけでなく、SECONDARYレプリカからもデータが参照する事が出来るようになる。
MySQL7.4までは常に、PRIMARYレプリカからデータを参照していたので、ローカルにPRIMARYデータが無い場合は、PRIMARYデータを持っているノードまで取得しにいく必要があった。

イメージ

cluster

参照:
14.1.18.7 Setting NDB_TABLE options in table comments

抜粋: 
READ_BACKUP: Setting this option to 1 has the same effect as though ndb_read_backup were enabled; enables reading from any replica.
Setting this option to 1 automatically sets FRAGMENT_COUNT_TYPE to ONE_PER_LDM_PER_NODE_GROUP (see next item).
Starting with MySQL Cluster NDB 7.5.3, you can set READ_BACKUP for an existing table online,
using an ALTER TABLE statement similar to one of those shown here:


ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

【結論】
結果としては、READ_BACKUPをONにした方が、3割程パフォーマンスが向上していました。
非常に非力な、仮想環境での検証なので皆さんの環境でも是非検証してみて下さい。
大幅にパフォーマンスが改善する可能性があります。

READ_BACKUP=0 (OFF)
transactions: 5650 (93.91 per sec.)
transactions: 5718 (94.91 per sec.)
transactions: 5698 (94.71 per sec.)
transactions: 5490 (91.24 per sec.)

READ_BACKUP=1 (ON)
transactions: 7234 (120.17 per sec.)
transactions: 7403 (123.00 per sec.)
transactions: 7419 (123.32 per sec.)
transactions: 7264 (120.77 per sec.)

※ マシーン自体は非力なのでTPSは少ないですが、どちらも全く同じ状況で検証しています。

■ 簡単にSYSBENCH0.5のOLTP READで、参照パフォーマンスの確認を行ってみました。
メモリーが無いので、データは1万件しかいれてません。(1万 X 4テーブル)


mysql> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 5.7.13-ndb-7.5.3-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest2            |
| sbtest3            |
| sbtest4            |
+--------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> 

SYSBENCHのスレッド数と実行しているQUERY
‐ 32スレッドで60秒程実行しています。 
‐ Queryの殆どは、BETWEEN等を利用しているのでPKで特定のデータをピンポイントで持ってくる処理ではありません。


/bin/sysbench \
  --test=../lua/db/oltp.lua \
  --rand-init=on \
  --db-driver=mysql \
  --oltp-table-size=${TSIZE} \
  --rand-type=uniform \
  --oltp-read-only=on \
  --oltp-tables-count=${NTBLE}\
  --mysql-db=sysbench \
  --mysql-host=${ENDPOINT} \
  --mysql-user=bench_user \
  --mysql-password=password \
  --max-time=60 \
  --max-requests=0 \
  --num-threads=32 \




[root@CL-SLAVE01 sys03]# cat ../lua/db/oltp.lua | egrep -i select 
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
[root@CL-SLAVE01 sys03]# 

上記のテーブルとデータでDEFAULTの状態(READ_BACKUPをOFF)で検証してみます。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79100
        write:                           0
        other:                           11300
        total:                           90400
    transactions:                        5650   (93.91 per sec.)
    read/write requests:                 79100  (1314.76 per sec.)
    other operations:                    11300  (187.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1630s
    total number of events:              5650
    total time taken by event execution: 1919.4297s
    response time:
         min:                                 89.72ms
         avg:                                339.72ms
         max:                                617.21ms
         approx.  95 percentile:             442.32ms

Threads fairness:
    events (avg/stddev):           176.5625/2.45
    execution time (avg/stddev):   59.9822/0.11

[root@CL-SLAVE01 sys03]# 




[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            80052
        write:                           0
        other:                           11436
        total:                           91488
    transactions:                        5718   (94.91 per sec.)
    read/write requests:                 80052  (1328.77 per sec.)
    other operations:                    11436  (189.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2452s
    total number of events:              5718
    total time taken by event execution: 1924.6034s
    response time:
         min:                                161.28ms
         avg:                                336.59ms
         max:                                646.96ms
         approx.  95 percentile:             441.26ms

Threads fairness:
    events (avg/stddev):           178.6875/2.28
    execution time (avg/stddev):   60.1439/0.07

[root@CL-SLAVE01 sys03]# 

READ_BACKUPをONにしています。ALTERテーブルでテーブル毎に設定変更可能です。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPオプションをONにしたので、同じテストを実行してみます。


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101276
        write:                           0
        other:                           14468
        total:                           115744
    transactions:                        7234   (120.17 per sec.)
    read/write requests:                 101276 (1682.33 per sec.)
    other operations:                    14468  (240.33 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1997s
    total number of events:              7234
    total time taken by event execution: 1919.6510s
    response time:
         min:                                 61.99ms
         avg:                                265.37ms
         max:                                515.43ms
         approx.  95 percentile:             350.01ms

Threads fairness:
    events (avg/stddev):           226.0625/0.97
    execution time (avg/stddev):   59.9891/0.10

[root@CL-SLAVE01 sys03]# 



[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103642
        write:                           0
        other:                           14806
        total:                           118448
    transactions:                        7403   (123.00 per sec.)
    read/write requests:                 103642 (1722.05 per sec.)
    other operations:                    14806  (246.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1852s
    total number of events:              7403
    total time taken by event execution: 1923.2176s
    response time:
         min:                                143.96ms
         avg:                                259.79ms
         max:                                421.40ms
         approx.  95 percentile:             328.68ms

Threads fairness:
    events (avg/stddev):           231.3438/0.59
    execution time (avg/stddev):   60.1006/0.05

[root@CL-SLAVE01 sys03]# 

■ 再テストでREAD_BACKUPをOFFにしてみます。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPをOFFにした状態で再度検証してみると、TPSは落ちています。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            79772
        write:                           0
        other:                           11396
        total:                           91168
    transactions:                        5698   (94.71 per sec.)
    read/write requests:                 79772  (1325.97 per sec.)
    other operations:                    11396  (189.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1611s
    total number of events:              5698
    total time taken by event execution: 1920.7135s
    response time:
         min:                                118.55ms
         avg:                                337.09ms
         max:                                600.45ms
         approx.  95 percentile:             440.47ms

Threads fairness:
    events (avg/stddev):           178.0625/1.71
    execution time (avg/stddev):   60.0223/0.09

[root@CL-SLAVE01 sys03]# 


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            76860
        write:                           0
        other:                           10980
        total:                           87840
    transactions:                        5490   (91.24 per sec.)
    read/write requests:                 76860  (1277.31 per sec.)
    other operations:                    10980  (182.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1734s
    total number of events:              5490
    total time taken by event execution: 1922.5091s
    response time:
         min:                                171.24ms
         avg:                                350.18ms
         max:                                801.92ms
         approx.  95 percentile:             478.98ms

Threads fairness:
    events (avg/stddev):           171.5625/1.32
    execution time (avg/stddev):   60.0784/0.06

[root@CL-SLAVE01 sys03]# 

READ_BACKUP設定を再度ONにしています。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

もう一度、READ_BACKUPをONにしてパフォーマンスを確認してみました。
やはり、ONにした方が今回の環境ではパフォーマンスが良いようです。

[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            103866
        write:                           0
        other:                           14838
        total:                           118704
    transactions:                        7419   (123.32 per sec.)
    read/write requests:                 103866 (1726.47 per sec.)
    other operations:                    14838  (246.64 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1609s
    total number of events:              7419
    total time taken by event execution: 1920.0195s
    response time:
         min:                                 91.51ms
         avg:                                258.80ms
         max:                                463.14ms
         approx.  95 percentile:             351.79ms

Threads fairness:
    events (avg/stddev):           231.8438/0.83
    execution time (avg/stddev):   60.0006/0.13

[root@CL-SLAVE01 sys03]#


[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            101696
        write:                           0
        other:                           14528
        total:                           116224
    transactions:                        7264   (120.77 per sec.)
    read/write requests:                 101696 (1690.77 per sec.)
    other operations:                    14528  (241.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1476s
    total number of events:              7264
    total time taken by event execution: 1921.1800s
    response time:
         min:                                 77.60ms
         avg:                                264.48ms
         max:                                454.03ms
         approx.  95 percentile:             343.88ms

Threads fairness:
    events (avg/stddev):           227.0000/0.79
    execution time (avg/stddev):   60.0369/0.06

[root@CL-SLAVE01 sys03]# 

MySQL Cluster 7.5はMySQL5.7ベースのMySQLが利用可能なので、JSONが利用出来たり色々な改善が加わっています。
これまでより、多くのケースで活用出来る場面が増えてくるかと思います。詳細は以下のURLを参照下さい。
19.1.4 What is New in MySQL Cluster NDB 7.5

例) MySQL Cluster7.5のndbinfoから,こちらのようにテーブルの情報も確認出来るようになってます。


mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.09 sec)

mysql> 

MySQL Clusterに関しても、セミナーで紹介していたりするので都合が付けば是非参加下さい。
https://www-jp.mysql.com/news-and-events/events/


MySQL Clusterの検証をIBM(Softlayer)さん、株式会社インフィニットループさんと共同で行って、
結果を先日のCEDECで発表した時の資料です。インフィニットループさんがOpenSourceのゲームで検証してくれているので、内容的には参考になるかと思います。

1. MySQL Clusterを稼働させるにはネットワークが非常に重要
2. MySQL Clusterを稼働させる場合、仮想環境のCommit Ratioがコントロール出来ない場合はベアメタルが良い。
3. 仮想、ベアメタル共にSSDを利用していてもISCSIよりもやはりローカルの方が断然良い。
4. MaxNoOfExecutionThreadsより、ThreadConfigで丁寧に調整した方がパフォーマンス良い(この検証では1.2倍)
5. 今回のSYSBENCH0.5での検証では、データの偏りがあったので思うようにパフォーマンスが出なかった。(パーティションで対応可能)
6・ Sysbench0.5のOLTP R/Wの処理は、5割程がMySQL Cluster向きで残りの5割はInnDB向きの処理。(データノードを跨る処理が多かった)

今回は2週間でしたが、もう少し時間があればより最適なパラメータ構成を設定出来たかと思います。
皆さんも利用する場合は、事前に最適なパラメータ確認を実施して下さい。

MySQL Cluster Community Edition Download
http://dev.mysql.com/downloads/cluster/

Commercial Edition追加ツールとサポート
https://www-jp.mysql.com/products/cluster/


NoSQL with MySQL Cluster
本日、話をさせて頂いた、MySQL Clusterにおけるトランザクション対応NoSQLについての資料です。

MySQL ClusterはMySQLとは基本的には異なるデータベースです。
NDB(Network Database)に対して、MySQLからもNoSQLからもデータ処理出来るデータベースがMySQL Clusterです。
MySQLからはもともとストレージエンジンを選択出来るデータベースシステムですが、
MySQLはndbclusterというストレージエンジンとしてNDBを利用しています。

NDBはNDB API (C++)を経由してアクセスする事で,MySQLから独立して利用する事が可能です。
MySQLサーバの観点からは,NDB Clusterは行のテーブルを格納するためのストレージエンジンです。
NDB Clusterの観点からは、MySQLサーバインスタンスがクラスタに接続されているAPIのプロセスの一つです。

概要図
ndb_diag

Storage Engine
storage_engines

ndbinfo
データは、冗長化と拡張性の為にデータノード間で分散されています。
その状況は、ndbinfoを確認する事でも確認可能です。

ndbinfo

検証
先ずは、MySQL Clusterをダウンロードして頂き、MySQL Clsuterを設定して下さい。
設定が終了したら、MySQL ClusterにSQLで接続しスキーマとテスト用のテーブルを作成して下さい。
NDB APIからNoSQLでもNDBにコマンドでテーブルを作成出来ますが、その場合はMySQLからオブジェクトを見る事が出来ません。
MySQLとNoSQL両方から使いたい場合は、MySQLにて先ずはオブジェクト作成して下さい。

Download MySQL Cluster
https://dev.mysql.com/downloads/cluster/

18.2.2. Linux での MySQL Cluster のインストール
https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-install-linux.html

5分で作るMySQL Cluster環境
http://www.ospn.jp/osc2013-kyoto/pdf/osc2013kyoto_mysql2.pdf


mysql> use TEST_DB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `first` varchar(64) DEFAULT NULL,
  `last` varchar(64) DEFAULT NULL,
  `municipality` varchar(64) DEFAULT NULL,
  `started` varchar(64) DEFAULT NULL,
  `ended` varchar(64) DEFAULT NULL,
  `department` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_u_hash` (`first`,`last`) USING HASH,
  KEY `idx_municipality` (`municipality`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> 

ClusterJ サンプルソース
MySQLClusterJ_Sample

MySQL Clusterへの接続文字列は適宜変更して下さい。
— com.mysql.clusterj.connectstring
— com.mysql.clusterj.database=TEST_DB

[SELL]

[root@Labs01 java]# cat clusterj.properties
# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

com.mysql.clusterj.connectstring=192.168.56.114:1186
com.mysql.clusterj.database=TEST_DB
com.mysql.clusterj.connect.retries=4
com.mysql.clusterj.connect.delay=5
com.mysql.clusterj.connect.verbose=1
com.mysql.clusterj.connect.timeout.before=30
com.mysql.clusterj.connect.timeout.after=20
com.mysql.clusterj.max.transactions=1024
[root@Labs01 java]#

[/SHELL]

コンパイル
※ javacにてコンパイル時には、clusterj-api-7.x.x.jarを含める必要があります。
※ パスは適宜書き換えて下さい。


javac -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-api-7.4.6.jar:. Main.java Employee.java

実行例


java -classpath /mysql-cluster-gpl-7.4.6/746bin/share/java/clusterj-7.4.6.jar:. -Djava.library.path=/mysql-cluster-gpl-7.4.6/746bin/lib Main

上記の実行内容は、先にスキーマをSQLから作成してあるのでSQLでも見る事が出来ます。

del_in

それ以外の方法としては、NDB用にMySQL Clusterに用意されている以下のコマンドを利用すると良いでしょう。

ndb_show_tables

ndb_show_tables

ndb_select_all

ndb_select_all

SQLが得意な処理は、SQLで処理して、Primary Keyベースでの特定のデータに対しての処理などは、
MySQL ClusterでのNoSQLで処理するという方法も選択出来ます。
NDB APIはC++ですので、C++が得意な方はそのままC++で書いて高速な処理を検証してみるのも良いかと思います。
※ネットワークは重要です、トラフィックが多い場合は10Gなどの高速なネットワークをご利用下さい。


flexAsynchによるMySQL Clusterベンチマーク

MySQL Benchmark Tool
https://dev.mysql.com/downloads/benchmarks.html
―概要―
FlexAsynch is a benchmark specifically developed to test scalability of MySQL Cluster.
It is found in any MySQL Cluster source tarball under storage/ndb/test/ndbapi. The features required to
run it in this parallel manner requires a MySQL Cluster 7.x version released after the 15th of October 2011.
The DBT2 Benchmark Tool can be used to run distributed tests with many MySQL Cluster Data nodes and many
flexAsynch benchmark programs in a completely automated fashion.

Mikaelさんが、MySQL Clusterでのベンチマーク方法についての手法を書かれているので、
他のツール含めて検証される場合は此方を参考にすると良さそうです。
Mikael Ronstrom

自分の環境にはSourceやJavaが無かったのでまずダウンロードしてインストールしました。
その他、コンパイラーなどは必要に応じてインストールしてください。
あとは、コンパイルして基本的な動作確認が出来たら、自分が変化を判断し易いように、
標準オプション決めて実行し、古いバージョンや古いシステムから入れ替える時にどれだけ
パフォーマンスが変化していくか確認用に使ってみるのも良いかと思っています。

1) クラスターソースコードのダウンロード

  wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4.6/mysql-cluster-gpl-7.4.6.tar.gz

2) Javaのインストール (今回は、1.8で上手くいかなかったので1.6で実行しました)

[root@Labs01 mysql-cluster-gpl-7.4.6]# yum search jdk
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.fairway.ne.jp
 * extras: mirror.fairway.ne.jp
 * updates: mirror.fairway.ne.jp
==================================================================== N/S matched: jdk ====================================================================
java-1.6.0-openjdk.x86_64 : OpenJDK Runtime Environment
java-1.6.0-openjdk-demo.x86_64 : OpenJDK Demos
java-1.6.0-openjdk-devel.x86_64 : OpenJDK Development Environment
java-1.6.0-openjdk-javadoc.x86_64 : OpenJDK API Documentation
java-1.6.0-openjdk-src.x86_64 : OpenJDK Source Bundle
java-1.7.0-openjdk.x86_64 : OpenJDK Runtime Environment
java-1.7.0-openjdk-accessibility.x86_64 : OpenJDK accessibility connector
java-1.7.0-openjdk-demo.x86_64 : OpenJDK Demos
java-1.7.0-openjdk-devel.x86_64 : OpenJDK Development Environment
java-1.7.0-openjdk-headless.x86_64 : The OpenJDK runtime environment without audio and video support
java-1.7.0-openjdk-javadoc.noarch : OpenJDK API Documentation
java-1.7.0-openjdk-src.x86_64 : OpenJDK Source Bundle
java-1.8.0-openjdk.x86_64 : OpenJDK Runtime Environment
java-1.8.0-openjdk-accessibility.x86_64 : OpenJDK accessibility connector
java-1.8.0-openjdk-demo.x86_64 : OpenJDK Demos
java-1.8.0-openjdk-devel.x86_64 : OpenJDK Development Environment
java-1.8.0-openjdk-headless.x86_64 : OpenJDK Runtime Environment
java-1.8.0-openjdk-javadoc.noarch : OpenJDK API Documentation
java-1.8.0-openjdk-src.x86_64 : OpenJDK Source Bundle
ldapjdk-javadoc.noarch : Javadoc for ldapjdk
icedtea-web.x86_64 : Additional Java components for OpenJDK - Java browser plug-in and Web Start implementation
ldapjdk.noarch : The Mozilla LDAP Java SDK

  Name and summary matches only, use "search all" for everything.
[root@Labs01 mysql-cluster-gpl-7.4.6]# yum install java-1.6.0-*
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.fairway.ne.jp
 * extras: mirror.fairway.ne.jp
 * updates: mirror.fairway.ne.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ java-1.6.0-openjdk.x86_64 1:1.6.0.35-1.13.7.1.el7_1 を インストール
--> 依存性の処理をしています: tzdata-java のパッケージ: 1:java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64
--> 依存性の処理をしています: libpulse.so.0(PULSE_0)(64bit) のパッケージ: 1:java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64
--> 依存性の処理をしています: libpulse.so.0()(64bit) のパッケージ: 1:java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64
---> パッケージ java-1.6.0-openjdk-demo.x86_64 1:1.6.0.35-1.13.7.1.el7_1 を インストール
---> パッケージ java-1.6.0-openjdk-devel.x86_64 1:1.6.0.35-1.13.7.1.el7_1 を インストール
---> パッケージ java-1.6.0-openjdk-javadoc.x86_64 1:1.6.0.35-1.13.7.1.el7_1 を インストール
---> パッケージ java-1.6.0-openjdk-src.x86_64 1:1.6.0.35-1.13.7.1.el7_1 を インストール
--> トランザクションの確認を実行しています。
---> パッケージ pulseaudio-libs.x86_64 0:3.0-30.el7 を インストール
--> 依存性の処理をしています: libsndfile.so.1(libsndfile.so.1.0)(64bit) のパッケージ: pulseaudio-libs-3.0-30.el7.x86_64
--> 依存性の処理をしています: libsndfile.so.1()(64bit) のパッケージ: pulseaudio-libs-3.0-30.el7.x86_64
--> 依存性の処理をしています: libasyncns.so.0()(64bit) のパッケージ: pulseaudio-libs-3.0-30.el7.x86_64
---> パッケージ tzdata-java.noarch 0:2015d-1.el7 を インストール
--> トランザクションの確認を実行しています。
---> パッケージ libasyncns.x86_64 0:0.8-7.el7 を インストール
---> パッケージ libsndfile.x86_64 0:1.0.25-9.el7 を インストール
--> 依存性の処理をしています: libvorbisenc.so.2()(64bit) のパッケージ: libsndfile-1.0.25-9.el7.x86_64
--> 依存性の処理をしています: libvorbis.so.0()(64bit) のパッケージ: libsndfile-1.0.25-9.el7.x86_64
--> 依存性の処理をしています: libogg.so.0()(64bit) のパッケージ: libsndfile-1.0.25-9.el7.x86_64
--> 依存性の処理をしています: libgsm.so.1()(64bit) のパッケージ: libsndfile-1.0.25-9.el7.x86_64
--> 依存性の処理をしています: libFLAC.so.8()(64bit) のパッケージ: libsndfile-1.0.25-9.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ flac-libs.x86_64 0:1.3.0-5.el7_1 を インストール
---> パッケージ gsm.x86_64 0:1.0.13-11.el7 を インストール
---> パッケージ libogg.x86_64 2:1.3.0-7.el7 を インストール
---> パッケージ libvorbis.x86_64 1:1.3.3-8.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================== 
Package                                       アーキテクチャー          バージョン                                      リポジトリー                容量
==========================================================================================================================================================
インストール中:
 java-1.6.0-openjdk                            x86_64                    1:1.6.0.35-1.13.7.1.el7_1                       updates                     42 M
 java-1.6.0-openjdk-demo                       x86_64                    1:1.6.0.35-1.13.7.1.el7_1                       updates                    1.9 M
 java-1.6.0-openjdk-devel                      x86_64                    1:1.6.0.35-1.13.7.1.el7_1                       updates                     15 M
 java-1.6.0-openjdk-javadoc                    x86_64                    1:1.6.0.35-1.13.7.1.el7_1                       updates                     13 M
 java-1.6.0-openjdk-src                        x86_64                    1:1.6.0.35-1.13.7.1.el7_1                       updates                     39 M
依存性関連でのインストールをします:
 flac-libs                                     x86_64                    1.3.0-5.el7_1                                   updates                    169 k
 gsm                                           x86_64                    1.0.13-11.el7                                   base                        30 k
 libasyncns                                    x86_64                    0.8-7.el7                                       base                        26 k
 libogg                                        x86_64                    2:1.3.0-7.el7                                   base                        24 k
 libsndfile                                    x86_64                    1.0.25-9.el7                                    base                       149 k
 libvorbis                                     x86_64                    1:1.3.3-8.el7                                   base                       204 k
 pulseaudio-libs                               x86_64                    3.0-30.el7                                      base                       556 k
 tzdata-java                                   noarch                    2015d-1.el7                                     updates                    144 k

トランザクションの要約
==========================================================================================================================================================
インストール  5 パッケージ (+8 個の依存関係のパッケージ)

総ダウンロード容量: 111 M
インストール容量: 367 M
Is this ok [y/d/N]: y
Downloading packages:
(1/13): gsm-1.0.13-11.el7.x86_64.rpm                                                                                               |  30 kB  00:00:00     (2/13): flac-libs-1.3.0-5.el7_1.x86_64.rpm                                                                                         | 169 kB  00:00:02     (3/13): java-1.6.0-openjdk-demo-1.6.0.35-1.13.7.1.el7_1.x86_64.rpm                                                                 | 1.9 MB  00:00:06     (4/13): libasyncns-0.8-7.el7.x86_64.rpm                                                                                            |  26 kB  00:00:00     (5/13): libogg-1.3.0-7.el7.x86_64.rpm                                                                                              |  24 kB  00:00:00     (6/13): libsndfile-1.0.25-9.el7.x86_64.rpm                                                                                         | 149 kB  00:00:00     (7/13): libvorbis-1.3.3-8.el7.x86_64.rpm                                                                                           | 204 kB  00:00:00     (8/13): pulseaudio-libs-3.0-30.el7.x86_64.rpm                                                                                      | 556 kB  00:00:00     (9/13): java-1.6.0-openjdk-devel-1.6.0.35-1.13.7.1.el7_1.x86_64.rpm                                                                |  15 MB  00:00:59     (10/13): tzdata-java-2015d-1.el7.noarch.rpm                                                                                        | 144 kB  00:00:01     (11/13): java-1.6.0-openjdk-javadoc-1.6.0.35-1.13.7.1.el7_1.x86_64.rpm                                                             |  13 MB  00:01:04     (12/13): java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64.rpm                                                                     |  42 MB  00:01:49     (13/13): java-1.6.0-openjdk-src-1.6.0.35-1.13.7.1.el7_1.x86_64.rpm                                                                 |  39 MB  00:02:12     ----------------------------------------------------------------------------------------------------------------------------------------------------------合計                                                                                                                      816 kB/s | 111 MB  00:02:19     Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : 2:libogg-1.3.0-7.el7.x86_64                                                                                              1/13   インストール中          : flac-libs-1.3.0-5.el7_1.x86_64                                                                                           2/13   インストール中          : 1:libvorbis-1.3.3-8.el7.x86_64                                                                                           3/13   インストール中          : gsm-1.0.13-11.el7.x86_64                                                                                                 4/13   インストール中          : libsndfile-1.0.25-9.el7.x86_64                                                                                           5/13   インストール中          : tzdata-java-2015d-1.el7.noarch                                                                                           6/13   インストール中          : libasyncns-0.8-7.el7.x86_64                                                                                              7/13   インストール中          : pulseaudio-libs-3.0-30.el7.x86_64                                                                                        8/13   インストール中          : 1:java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                      9/13   インストール中          : 1:java-1.6.0-openjdk-demo-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                10/13   インストール中          : 1:java-1.6.0-openjdk-src-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                 11/13   インストール中          : 1:java-1.6.0-openjdk-devel-1.6.0.35-1.13.7.1.el7_1.x86_64                                                               12/13   インストール中          : 1:java-1.6.0-openjdk-javadoc-1.6.0.35-1.13.7.1.el7_1.x86_64                                                             13/13   検証中                  : libsndfile-1.0.25-9.el7.x86_64                                                                                           1/13   検証中                  : libasyncns-0.8-7.el7.x86_64                                                                                              2/13   検証中                  : 1:java-1.6.0-openjdk-demo-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                 3/13   検証中                  : flac-libs-1.3.0-5.el7_1.x86_64                                                                                           4/13   検証中                  : tzdata-java-2015d-1.el7.noarch                                                                                           5/13   検証中                  : pulseaudio-libs-3.0-30.el7.x86_64                                                                                        6/13   検証中                  : 1:java-1.6.0-openjdk-javadoc-1.6.0.35-1.13.7.1.el7_1.x86_64                                                              7/13   検証中                  : gsm-1.0.13-11.el7.x86_64                                                                                                 8/13   検証中                  : 2:libogg-1.3.0-7.el7.x86_64                                                                                              9/13   検証中                  : 1:java-1.6.0-openjdk-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                     10/13   検証中                  : 1:java-1.6.0-openjdk-src-1.6.0.35-1.13.7.1.el7_1.x86_64                                                                 11/13   検証中                  : 1:java-1.6.0-openjdk-devel-1.6.0.35-1.13.7.1.el7_1.x86_64                                                               12/13   検証中                  : 1:libvorbis-1.3.3-8.el7.x86_64                                                                                          13/13 
インストール:
  java-1.6.0-openjdk.x86_64 1:1.6.0.35-1.13.7.1.el7_1                        java-1.6.0-openjdk-demo.x86_64 1:1.6.0.35-1.13.7.1.el7_1                    
  java-1.6.0-openjdk-devel.x86_64 1:1.6.0.35-1.13.7.1.el7_1                  java-1.6.0-openjdk-javadoc.x86_64 1:1.6.0.35-1.13.7.1.el7_1                 
  java-1.6.0-openjdk-src.x86_64 1:1.6.0.35-1.13.7.1.el7_1                   

依存性関連をインストールしました:
  flac-libs.x86_64 0:1.3.0-5.el7_1      gsm.x86_64 0:1.0.13-11.el7          libasyncns.x86_64 0:0.8-7.el7            libogg.x86_64 2:1.3.0-7.el7            libsndfile.x86_64 0:1.0.25-9.el7      libvorbis.x86_64 1:1.3.3-8.el7      pulseaudio-libs.x86_64 0:3.0-30.el7      tzdata-java.noarch 0:2015d-1.el7     
完了しました!
[root@Labs01 mysql-cluster-gpl-7.4.6]# which java
/bin/java
[root@Labs01 mysql-cluster-gpl-7.4.6]# /bin/java -version
java version "1.6.0_35"
OpenJDK Runtime Environment (IcedTea6 1.13.7) (rhel-1.13.7.1.el7_1-x86_64)
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)
[root@Labs01 mysql-cluster-gpl-7.4.6]# 


2) ソースコードの解凍(tar xzf mysql-cluster-gpl-7.4.6.tar.gz) とクラスターのビルド
※746binは、インストール先用に用意したフォルダーですので何でも良いです。

[root@Labs01 mysql-cluster-gpl-7.4.6]# echo $PATH
/sbin:/bin:/usr/sbin:/usr/bin
[root@Labs01 mysql-cluster-gpl-7.4.6]# readlink $(readlink $(which java))
/usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
[root@Labs01 mysql-cluster-gpl-7.4.6]#
echo "export JAVA_HOME=/usr/lib/jvm/jre-1.6.0-openjdk.x86_64" >> /root/.bashrc
[root@Labs01 mysql-cluster-gpl-7.4.6]#

[root@Labs01 mysql-cluster-gpl-7.4.6]# cmake . -DCMAKE_INSTALL_PREFIX=/addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin -DWITH_NDB_TEST=ON 
[root@Labs01 mysql-cluster-gpl-7.4.6]# make -I /usr/lib/jvm/java-1.6.0-openjdk-1.6.0.35.x86_64/include
/bin/ar: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/libmysqld/libmysqld.a を作成しています

Hup....

[ 98%] Built target mysqlserver
Scanning dependencies of target mysql_client_test_embedded
[100%] Building C object libmysqld/examples/CMakeFiles/mysql_client_test_embedded.dir/__/__/tests/mysql_client_test.c.o
Linking CXX executable mysql_client_test_embedded
[100%] Built target mysql_client_test_embedded
Scanning dependencies of target mysql_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/completion_hash.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/mysql.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/readline.cc.o
Linking CXX executable mysql_embedded
[100%] Built target mysql_embedded
Scanning dependencies of target mysqltest_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir/__/__/client/mysqltest.cc.o
Linking CXX executable mysqltest_embedded
[100%] Built target mysqltest_embedded
Scanning dependencies of target my_safe_process
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process
[root@Labs01 mysql-cluster-gpl-7.4.6]# 


[root@Labs01 mysql-cluster-gpl-7.4.6]# make install
[  0%] Built target INFO_BIN
[  0%] Built target INFO_SRC
[  0%] Built target abi_check
[  1%] Built target zlib
[  2%] Built target yassl
[  4%] Built target taocrypt
[  6%] Built target edit
[  8%] Built target strings
[ 13%] Built target mysys
[ 13%] Built target dbug
[ 13%] Built target mysys_ssl
[ 13%] Built target comp_err
[ 13%] Built target GenError
[ 13%] Built target csv
[ 13%] Built target csv_embedded
[ 14%] Built target myisammrg
[ 15%] Built target myisammrg_embedded
[ 15%] Built target federated
[ 15%] Built target federated_embedded
[ 21%] Built target innobase
[ 27%] Built target innobase_embedded
[ 27%] Built target archive
[ 27%] Built target archive_embedded

Hup....

-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/innotest2
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/server-cfg
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/test-insert
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/innotest2a
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/innotest1
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/bench-count-distinct
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/graph-compare-results
-- Installing: /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/sql-bench/copy-db
[root@Labs01 mysql-cluster-gpl-7.4.6]#

flex

3) クラスター管理ノードでコンフィグファイルを編集して、テスト用に今回ビルドしたAPIノードを追加。

[api]
NodeId=60
Hostname=192.168.56.108

コンフィグファイルをリロードして新しいノードIDがロードされている事を確認


[root@cluster74_01 data]# /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/ndb-config.ini --reload

show

4) 実際にflexAsynchを実行してみます
ここでは、オプションの指定をせずに接続先のみ指定して実行してみます。

[root@Labs01 mysql-cluster-gpl-7.4.6]# export LD_LIBRARY_PATH=/addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/lib
[root@Labs01 mysql-cluster-gpl-7.4.6]# export NDB_CONNECTSTRING=192.168.56.114:1186


[root@Labs01 mysql-cluster-gpl-7.4.6]# /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/bin/flexAsynch 

FLEXASYNCH - Starting normal mode
Perform benchmark of insert, update and delete transactions
  1 number of concurrent threads 
  32 number of parallel operation per thread 
  500 transaction(s) per round 
  1 iterations 
  Load Factor is 80%
  1 tables 
  25 attributes per table 
  0 ordered indexes per table 
  1 is the number of 32 bit words per attribute 
  Tables are with logging 
  Transactions are executed with hint provided
  No force send is used, adaptive algorithm used

Key Errors are disallowed
Temporary Resource Errors are allowed
Insufficient Space Errors are disallowed
Node Recovery Errors are allowed
Overload Errors are allowed
Timeout Errors are allowed
Internal NDB Errors are allowed
User logic reported Errors are allowed
Application Errors are disallowed
Using table name TAB0_1807968432
CPU for this connection: 0
  NdbAPI node with id = 60

Waiting for ndb to become ready...
Creating TAB0_1807968432...
numFragments = 2
number of nodes = 2

All NDB objects and table created


Loop # 1

Executing inserts
16000 transactions, 16000 insert total time = 4376 ms
Average 0.273500 ms/transaction, 0.273500 ms/insert.
0.228519 transactions/second, 3656.307130 inserts/second.

Executing reads
16000 transactions, 16000 read total time = 1668 ms
Average 0.104250 ms/transaction, 0.104250 ms/read.
0.599520 transactions/second, 9592.326139 reads/second.

Executing updates
16000 transactions, 16000 update total time = 3913 ms
Average 0.244562 ms/transaction, 0.244562 ms/update.
0.255558 transactions/second, 4088.934321 updates/second.

Hup...

Executing deletes
16000 transactions, 16000 delete total time = 3818 ms
Average 0.238625 ms/transaction, 0.238625 ms/delete.
0.261917 transactions/second, 4190.675746 deletes/second.

--------------------------------------------------
Dropping table TAB0_1807968432...
insert average: 3656/s min: 3656/s max: 3656/s stddev: 0%
update average: 4088/s min: 4088/s max: 4088/s stddev: 0%
delete average: 4190/s min: 4190/s max: 4190/s stddev: 0%
read   average: 9774/s min: 9592/s max: 9956/s stddev: 1%

NDBT_ProgramExit: 0 - OK

[root@Labs01 mysql-cluster-gpl-7.4.6]# 

run

FLEXASYNCHオプション


FLEXASYNCH
   Perform benchmark of insert, update and delete transactions
 
Arguments:
   -t Number of threads to start, default 1
   -p Number of parallel transactions per thread, default 32
   -o Number of transactions per loop, default 500
   -l Number of loops to run, default 1, 0=infinite
   -load_factor Number Load factor in index in percent (40 -> 99)
   -a Number of attributes, default 25
   -c Number of operations per transaction
   -s Size of each attribute, default 1 
      (PK is always of size 1, independent of this value)
   -simple Use simple read to read from database
   -dirty Use dirty read to read from database
   -write Use writeTuple in insert and update
   -n Use standard table names
   -no_table_create Don't create tables in db
   -temp Create table(s) without logging
   -no_hint Don't give hint on where to execute transaction coordinator
   -adaptive Use adaptive send algorithm (default)
   -force Force send when communicating
   -non_adaptive Send at a 10 millisecond interval
   -local 1 = each thread its own node, 2 = round robin on node per parallel trans 3 = random node per parallel trans
   -ndbrecord Use NDB Record
   -r Number of extra loops
   -insert Only run inserts on standard table
   -read Only run reads on standard table
   -update Only run updates on standard table
   -delete Only run deletes on standard table
   -create_table Only run Create Table of standard table
   -drop_table Only run Drop Table on standard table
   -warmup_time Warmup Time before measurement starts
   -execution_time Execution Time where measurement is done
   -cooldown_time Cooldown time after measurement completed
   -table Number of standard table, default 0
   -num_tables Number of tables in benchmark, default 1
   -num_indexes Number of ordered indexes per table in benchmark, default 0
   -receive_cpus A set of CPUs for receive threads, one per connection, comma separated list with ranges, e.g. 0-2,4

例)オプションを変更してみると以下のように同時接続、トランザクション数等が確認出来る。
必要に応じて、パラメータを変更してください。


[root@Labs01 mysql-cluster-gpl-7.4.6]# /addisk/flexAsynch/mysql-cluster-gpl-7.4.6/746bin/bin/flexAsynch -t 1 -p 30 -l 2 -o 10 -c 100 -n -a 2

FLEXASYNCH - Starting normal mode
Perform benchmark of insert, update and delete transactions
  1 number of concurrent threads 
  30 number of parallel operation per thread 
  10 transaction(s) per round 
  2 iterations 
  Load Factor is 80%
  1 tables 
  2 attributes per table 
  0 ordered indexes per table 
  1 is the number of 32 bit words per attribute 
  Tables are with logging 
  Transactions are executed with hint provided
  No force send is used, adaptive algorithm used

flexAsynchのCPU Loadもきちんとデータノード間で、分散されている事が確認出来る。
htop

参考: 
Mikael Ronstrom
Benchmark MySQL Cluster using flexAsynch
The installation and use of flexAsynch testing tool of MySQL clusters


MySQL Clusterの良いところの一つに、Rolling Upgradeが可能なので、
サービスを停止せずに、MySQL Clusterのアップグレードが可能な部分があると思います。
やはり、アップグレードにサービス停止を伴うと、インフラ管理者もサービス担当者と
メンテナンス時間の調整をしなければならず、なかなか対応がが難しいと思います。

アップグレードには、MCM(MySQL Cluster Manager)というCommercial版のツールを使えば、
コマンド一つでアップグレード可能ですが、今回はマニュアルで一つ一つアップグレードしています。

MySQL Cluster Managerは、基本的な管理ダスクを自動化することによって、
MySQL データベースの作成と管理を簡易化します。
MCM参照:
https://www-jp.mysql.com/products/cluster/mcm/
その他:
How MySQL is able to scale to 200 Million QPS – MySQL Cluster
http://highscalability.com/blog/2015/5/18/how-mysql-is-able-to-scale-to-200-million-qps-mysql-cluster.html

■ 現状の構成確認
本番環境ではお勧めしませんが、デモ環境なので2台のサーバー上に共存して構成しています。

SQL Node x 2
Data Node x 2
Management Node x 1

+------+   +------+
| SQL1 |   | SQL2 |
+------+   +------+
+------+   +------+
| NDB1 |   | NDB2 |
+------+   +------+

※この検証環境では、管理ノードはSQL1, NDB1と同居してます。

before_one_node

1) アップグレード前の事前動作確認

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
+----+---------------------------------------------+
6 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('rolling upgrade cluster step by step');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319 order by id;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  7 | rolling upgrade cluster step by step        |
+----+---------------------------------------------+
7 rows in set (0.00 sec)

mysql> explain select * from T20150319 where id = 1;
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | T20150319 | eq_ref | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------+--------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from T20150319 where memo like 'This is MySQL%';
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | T20150319 | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where with pushed condition |
+----+-------------+-----------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> 

2) Active/Active型のClusterなので、当然データは他のノードからも参照出来ます。

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T20150319 order by id;
+----+---------------------------------------------+
| id | memo                                        |
+----+---------------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証            |
|  2 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  3 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  4 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  5 | This is MySQL Cluster 7.4.4 検証 Restored   |
|  6 | SQL Connection 2015-03-24                   |
|  7 | rolling upgrade cluster step by step        |
+----+---------------------------------------------+
7 rows in set (0.00 sec)

mysql>

■ Rolling Upgradeの為、1/2のデータノードを停止しました。

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 2 stop
Node 2: Node shutdown initiated
Node 2: Node shutdown completed.
Node 2 has shutdown.

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2 (not connected, accepting connect from 192.168.56.115)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

—- Node 2で、SQLインスタンスも停止しました。

問題無く、アップグレード対象ノードが停止している事を確認。

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2 (not connected, accepting connect from 192.168.56.115)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> 

■1/2ノードにてバイナリーダウンロードと入れ替え

[root@cluster74_02 local]# tar zxvf /home/admin/mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64.tar.gz 

Snip

mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/plugin_audit.h
mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/mysql_com.h
mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/include/my_dir.h
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root root     6  6月 10  2014 bin
drwxr-xr-x.  2 root root     6  6月 10  2014 etc
drwxr-xr-x.  2 root root     6  6月 10  2014 games
drwxr-xr-x.  2 root root     6  6月 10  2014 include
drwxr-xr-x.  2 root root     6  6月 10  2014 lib
drwxr-xr-x.  2 root root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root    51  3月 17 12:28 mysql -> mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root  4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root root    46  3月 17 11:29 share
drwxr-xr-x.  2 root root    70  3月 17 12:28 src
[root@cluster74_02 local]# 

[root@cluster74_02 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@cluster74_02 local]# ln -s mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/ mysql
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root root     6  6月 10  2014 bin
drwxr-xr-x.  2 root root     6  6月 10  2014 etc
drwxr-xr-x.  2 root root     6  6月 10  2014 games
drwxr-xr-x.  2 root root     6  6月 10  2014 include
drwxr-xr-x.  2 root root     6  6月 10  2014 lib
drwxr-xr-x.  2 root root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root    51  5月 14 00:23 mysql -> mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 root root  4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root root    46  3月 17 11:29 share
drwxr-xr-x.  2 root root    70  3月 17 12:28 src
[root@cluster74_02 local]# chown -R mysql:mysql mysql/
[root@cluster74_02 local]# chmod -R 755 mysql/
[root@cluster74_02 local]# ls -l
合計 8
drwxr-xr-x.  2 root  root     6  6月 10  2014 bin
drwxr-xr-x.  2 root  root     6  6月 10  2014 etc
drwxr-xr-x.  2 root  root     6  6月 10  2014 games
drwxr-xr-x.  2 root  root     6  6月 10  2014 include
drwxr-xr-x.  2 root  root     6  6月 10  2014 lib
drwxr-xr-x.  2 root  root     6  6月 10  2014 lib64
drwxr-xr-x.  2 root  root     6  6月 10  2014 libexec
lrwxrwxrwx.  1 root  root    51  5月 14 00:23 mysql -> mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64/
drwxr-xr-x. 15 root  mysql 4096  3月 20 18:13 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64
drwxr-xr-x. 13 mysql mysql 4096  5月 14 00:21 mysql-cluster-advanced-7.4.6-linux-glibc2.5-x86_64
drwxr-xr-x.  2 root  root     6  6月 10  2014 sbin
drwxr-xr-x.  5 root  root    46  3月 17 11:29 share
drwxr-xr-x.  2 root  root    70  3月 17 12:28 src
[root@cluster74_02 local]# 

■旧バージョンからファイルのコピー
– DATAフォルダー
– MYSQL Cluster用フォルダー
– BACKUPフォルダー


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp mysql-cluster/ /usr/local/mysql/
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/
合計 140
-rwxr-xr-x.  1 mysql mysql 102986  4月  2 02:09 INSTALL-BINARY
-rwxr-xr-x.  1 mysql mysql   2730  4月  2 02:09 LICENSE.mysql
-rwxr-xr-x.  1 mysql mysql   1446  4月  2 02:09 README
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 bin
drwxr-xr-x.  3 mysql mysql     17  5月 14 00:21 data
drwxr-xr-x.  2 mysql mysql     69  5月 14 00:21 docs
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 include
drwxr-xr-x.  3 mysql mysql   4096  5月 14 00:21 lib
drwxr-xr-x.  4 mysql mysql     28  5月 14 00:21 man
drwxr-xr-x.  2 root  root      27  3月 18 10:31 mysql-cluster
drwxr-xr-x. 10 mysql mysql   4096  5月 14 00:21 mysql-test
drwxr-xr-x.  2 mysql mysql     29  5月 14 00:21 scripts
drwxr-xr-x. 32 mysql mysql   4096  5月 14 00:21 share
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 sql-bench
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 support-files
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# 


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp data/ /usr/local/mysql/
cp: `/usr/local/mysql/data/test/db.opt' を上書きしますか? y
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/data/
合計 111192
drwx------. 2 mysql mysql       59  3月 24 16:53 NDB01
-rw-rw----. 1 mysql mysql       56  3月 18 09:46 auto.cnf
-rw-r-----. 1 mysql root    156430  5月 13 22:23 cluster74_02.err
-rw-rw----. 1 mysql mysql 50331648  5月 13 22:23 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648  3月 18 09:45 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912  5月 13 22:23 ibdata1
drwx------. 2 mysql mysql     4096  3月 20 22:13 mysql
-rw-r--r--. 1 root  root         4  5月 13 21:55 ndb_2.pid
drwxr-x---. 9 root  root        72  3月 19 23:26 ndb_2_fs
-rw-r--r--. 1 root  root    426545  5月 13 22:14 ndb_2_out.log
drwx------. 2 mysql mysql     4096  3月 20 22:13 ndbinfo
drwx------. 2 mysql mysql     4096  3月 19 23:45 ndbmemcache
drwx------. 2 mysql mysql     4096  3月 18 09:45 performance_schema
drwxr-xr-x. 2 mysql mysql       19  3月 17 12:26 test
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# 


[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# cp -rp BACKUP/ /usr/local/mysql/
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]# ls -l /usr/local/mysql/
合計 144
drwxr-x---.  3 root  root      19  3月 20 18:21 BACKUP
-rwxr-xr-x.  1 mysql mysql 102986  4月  2 02:09 INSTALL-BINARY
-rwxr-xr-x.  1 mysql mysql   2730  4月  2 02:09 LICENSE.mysql
-rwxr-xr-x.  1 mysql mysql   1446  4月  2 02:09 README
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 bin
drwxr-xr-x.  9 mysql mysql   4096  5月 13 22:23 data
drwxr-xr-x.  2 mysql mysql     69  5月 14 00:21 docs
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 include
drwxr-xr-x.  3 mysql mysql   4096  5月 14 00:21 lib
drwxr-xr-x.  4 mysql mysql     28  5月 14 00:21 man
drwxr-xr-x.  2 root  root      27  3月 18 10:31 mysql-cluster
drwxr-xr-x. 10 mysql mysql   4096  5月 14 00:21 mysql-test
drwxr-xr-x.  2 mysql mysql     29  5月 14 00:21 scripts
drwxr-xr-x. 32 mysql mysql   4096  5月 14 00:21 share
drwxr-xr-x.  4 mysql mysql   4096  5月 14 00:21 sql-bench
drwxr-xr-x.  2 mysql mysql   4096  5月 14 00:21 support-files
[root@cluster74_02 mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64]#

■データノードとSQLノードを起動


[root@cluster74_02 mysql]# chown -R root .
[root@cluster74_02 mysql]# chown -R mysql data
[root@cluster74_02 mysql]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --nostart
2015-05-14 00:34:31 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-05-14 00:34:32 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 mysql]# /usr/local/mysql/bin/mysqld_safe &
[1] 3801
[root@cluster74_02 mysql]# 150514 00:34:50 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150514 00:34:50 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 mysql]# 



[root@cluster74_02 mysql]# ./bin/mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_apply_status                             OK
mysql.ndb_binlog_index                             OK
mysql.ndb_index_stat_head                          OK
mysql.ndb_index_stat_sample                        OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
NDB01.T20150319                                    OK
ndbinfo.blocks                                     OK
ndbinfo.config_params                              OK
ndbinfo.dict_obj_types                             OK
ndbmemcache.cache_policies                         OK
ndbmemcache.containers                             OK
ndbmemcache.demo_table                             OK
ndbmemcache.demo_table_large                       OK
ndbmemcache.demo_table_tabs                        OK
ndbmemcache.external_values                        OK
ndbmemcache.key_prefixes                           OK
ndbmemcache.last_memcached_signon                  OK
ndbmemcache.memcache_server_roles                  OK
ndbmemcache.meta                                   OK
ndbmemcache.ndb_clusters                           OK
OK
[root@cluster74_02 mysql]# 

After_one_node

■MySQL Cluster管理ノードにて確認してみる
– バージョンが混在している状態


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, not started)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> ndb_mgm> 2 start 
Node 2: Start initiated (version 7.4.6)
Database node 2 is being started.

ndb_mgm> Node 2: Started (version 7.4.6)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

Version
mysql

Data操作
insert_diff_version

■ アップグレード中
管理ノードをarbitratorに設定しているので、こちらは特に変更が無い事を確認。


mysql> select * from arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 08320002001a414c | Yes           | ARBIT_RUN |
|       2 |        100 | 08320002001a414c | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.01 sec)

mysql> select * from  arbitrator_validity_summary ;
+------------+------------------+---------------+-----------------+
| arbitrator | arb_ticket       | arb_connected | consensus_count |
+------------+------------------+---------------+-----------------+
|        100 | 08320002001a414c | Yes           |               2 |
+------------+------------------+---------------+-----------------+
1 row in set (0.01 sec)

mysql> 

■ 2/2のノードをアップグレード開始
– 対象ノードをSTOPコマンドで停止


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 1 stop
Node 1: Node shutdown initiated
Node 1 has shutdown.

ndb_mgm> Node 1: Node shutdown completed.

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1 (not connected, accepting connect from 192.168.56.114)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 100 stop
Node 100 has shutdown.
Disconnecting to allow Management Server to shutdown

ndb_mgm> 

arbitorator_shutdown1

その他の作業は、最初のノードと同様の手順でバイナリーの入れ替え
コマンドで管理ノード、データノード、SQLノードを起動します。


[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndb_mgmd -f /usr/local/mysql/mysql-cluster/ndb-config.ini
MySQL Cluster Management Server mysql-5.6.24 ndb-7.4.6
[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --nostart
2015-05-14 02:19:06 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-05-14 02:19:06 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 mysql]# /usr/local/mysql/bin/mysqld_safe &
[1] 5066
[root@cluster74_01 mysql]# 150514 02:19:17 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150514 02:19:17 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 mysql]# 

全ノードのMySQLのバイナリーが同じバージョンになっている事を確認
– STOPしているノードをSTARTコマンドで開始


[root@cluster74_01 mysql]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, not started)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 1 start
Node 1: Start initiated (version 7.4.6)
Database node 1 is being started.

ndb_mgm> Node 1: Started (version 7.4.6)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

after_upgrade_fin


[root@cluster74_01 mysql]# ./bin/mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_apply_status                             OK
mysql.ndb_binlog_index                             OK
mysql.ndb_index_stat_head                          OK
mysql.ndb_index_stat_sample                        OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
NDB01.T20150319                                    OK
ndbinfo.blocks                                     OK
ndbinfo.config_params                              OK
ndbinfo.dict_obj_types                             OK
ndbmemcache.cache_policies                         OK
ndbmemcache.containers                             OK
ndbmemcache.demo_table                             OK
ndbmemcache.demo_table_large                       OK
ndbmemcache.demo_table_tabs                        OK
ndbmemcache.external_values                        OK
ndbmemcache.key_prefixes                           OK
ndbmemcache.last_memcached_signon                  OK
ndbmemcache.memcache_server_roles                  OK
ndbmemcache.meta                                   OK
ndbmemcache.ndb_clusters                           OK
OK
[root@cluster74_01 mysql]# 

アップグレード最終確認


ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)
id=2    @192.168.56.115  (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.24 ndb-7.4.6)
id=51   @192.168.56.115  (mysql-5.6.24 ndb-7.4.6)

ndb_mgm> 

基本動作確認し、ログを見てエラーが無い事を確認して終了
confirmSQLfinal

メモ: 本番では、arbitratorの設定をもう少し明確に設定しておく。

参照: 18.5.5 Performing a Rolling Restart of a MySQL Cluster


MySQL SlapをNDB対して実行して、メモリー割り当て変化の簡単な確認

[root@misc bin]# /usr/local/mysql/bin/mysqlslap --no-defaults --create-schema=SLAP --engine=ndb --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndb --number-int-cols=3 --number-char-cols=5 --concurrency=10 --auto-generate-sql-write-number=10000 --auto-generate-sql-execute-number=10000 --auto-generate-sql-load-type=mixed -h 192.168.56.114 -u admin -p
Enter password: 
Benchmark
        Running for engine ndb
        Average number of seconds to run all queries: 135.323 seconds
        Minimum number of seconds to run all queries: 135.323 seconds
        Maximum number of seconds to run all queries: 135.323 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[root@misc bin]# 

ndb

Node, alloc_bytes,free_bytesを確認する限りだと、ノード間のデータは同期されているので均等に分かれている。
こちらに、追加でノードグループを増やしたときのテーブル分割を次回確認してみる。

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       1 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2129920 |         65 | 134217728 |        4096 |
|       2 | Index memory        |  475136 |         58 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.02 sec)

mysql> select * from resources;
+---------+---------------------+----------+------+-------+
| node_id | resource_name       | reserved | used | max   |
+---------+---------------------+----------+------+-------+
|       1 | RESERVED            |     4113 | 5495 | 13703 |
|       1 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       1 | DISK_RECORDS        |        0 |    0 |     0 |
|       1 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       1 | JOBBUFFER           |        0 |    0 |     0 |
|       1 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       1 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       1 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       1 | QUERY_MEMORY        |        0 |    0 |     0 |
|       1 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
|       2 | RESERVED            |     4113 | 5495 | 13703 |
|       2 | DISK_OPERATIONS     |        0 |    0 |     0 |
|       2 | DISK_RECORDS        |        0 |    0 |     0 |
|       2 | DATA_MEMORY         |     6152 | 2158 |  6152 |
|       2 | JOBBUFFER           |        0 |    0 |     0 |
|       2 | FILE_BUFFERS        |     1152 | 1096 |  1152 |
|       2 | TRANSPORTER_BUFFERS |        0 |    0 |     0 |
|       2 | DISK_PAGE_BUFFER    |     2240 | 2240 |  2240 |
|       2 | QUERY_MEMORY        |        0 |    0 |     0 |
|       2 | SCHEMA_TRANS_MEMORY |       64 |    1 |     0 |
+---------+---------------------+----------+------+-------+
20 rows in set (0.01 sec)

mysql> select * from memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       1 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 3735552 |        114 | 134217728 |        4096 |
|       2 | Index memory        |  524288 |         64 |  67371008 |        8224 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       98304 |       6624 |
|    1 |    1 |       98304 |       4080 |
|    2 |    0 |       98304 |       6624 |
|    2 |    1 |       98304 |       4032 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      524288 |      26928 |
|    1 |    1 |      524288 |      27984 |
|    2 |    0 |      524288 |      26880 |
|    2 |    1 |      524288 |      27984 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |      819200 |      27888 |
|    1 |    1 |      819200 |      30720 |
|    2 |    0 |      819200 |      27888 |
|    2 |    1 |      819200 |      30672 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |     1212416 |      22272 |
|    1 |    1 |     1212416 |      16080 |
|    2 |    0 |     1212416 |      22272 |
|    2 |    1 |     1212416 |      16032 |
+------+------+-------------+------------+
4 rows in set (0.02 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%t1%';
Empty set (0.02 sec)

mysql> 

今回は、検証用に2台のノードにMySQL Clusterを導入してみました。
検証目的なので2台で設定してますが、本番ではデータノードは分けた方が良さそうです。

————————-
Node1: 192.168.56.114
Node2: 192.168.56.115
————————-

mysql> select * from ndbinfo.nodes;
+---------+--------+---------+-------------+-------------------+
| node_id | uptime | status  | start_phase | config_generation |
+---------+--------+---------+-------------+-------------------+
|       1 |   3994 | STARTED |           0 |                 1 |
|       2 |   3948 | STARTED |           0 |                 1 |
+---------+--------+---------+-------------+-------------------+
2 rows in set (0.05 sec)

mysql> 

mysql> select * from ndbinfo.arbitrator_validity_detail;
+---------+------------+------------------+---------------+-----------+
| node_id | arbitrator | arb_ticket       | arb_connected | arb_state |
+---------+------------+------------------+---------------+-----------+
|       1 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
|       2 |        100 | 082d0001000710fe | Yes           | ARBIT_RUN |
+---------+------------+------------------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> 

既に、起動済みなのでNDBストレージエンジンでTABLEを作成してみる。
cluster74_02で作成

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_02 |
+--------------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE NDB01 default character set utf8mb4;USE NDB01;
Query OK, 1 row affected (0.06 sec)

Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE T20150319(id int not null auto_increment, memo varchar(100), primary key(id) ) ENGINE=ndb;
Query OK, 0 rows affected (0.47 sec)

mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc T20150319;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| memo  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> 

他のノードにてデータ確認
Node: cluster74_01

mysql> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| cluster74_01 |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| NDB01              |
| mysql              |
| ndb_1_fs           |
| ndbinfo            |
| ndbmemcache        |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

mysql> show tables from NDB01;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

ここで一度バックアップを取得
※リストアすると上記の状態に戻る。

ndb_mgm> backup start

DBに追加でデータを追加すると同時にリソースアロケーションを確認。

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-3');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-4');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-5');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T20150319;
+----+--------------------------------------+
| id | memo                                 |
+----+--------------------------------------+
|  3 | This is MySQL Cluster 7.4.4 検証-3   |
|  5 | This is MySQL Cluster 7.4.4 検証-5   |
|  1 | This is MySQL Cluster 7.4.4 検証     |
|  2 | This is MySQL Cluster 7.4.4 検証-2   |
|  4 | This is MySQL Cluster 7.4.4 検証-4   |
+----+--------------------------------------+
5 rows in set (0.01 sec)

mysql> desc ndbinfo.memory_per_fragment;
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| fq_name                | varchar(512)        | YES  |     | NULL    |       |
| parent_fq_name         | varchar(512)        | YES  |     | NULL    |       |
| type                   | varchar(512)        | YES  |     | NULL    |       |
| table_id               | int(10) unsigned    | YES  |     | NULL    |       |
| node_id                | int(10) unsigned    | YES  |     | NULL    |       |
| block_instance         | int(10) unsigned    | YES  |     | NULL    |       |
| fragment_num           | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_bytes  | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_size_bytes  | int(10) unsigned    | YES  |     | NULL    |       |
| fixed_elem_count       | bigint(20) unsigned | YES  |     | NULL    |       |
| fixed_elem_free_count  | decimal(16,0)       | YES  |     | NULL    |       |
| var_elem_alloc_bytes   | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_free_bytes    | bigint(20) unsigned | YES  |     | NULL    |       |
| var_elem_count         | bigint(20) unsigned | YES  |     | NULL    |       |
| hash_index_alloc_bytes | bigint(20) unsigned | YES  |     | NULL    |       |
+------------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32608 |
|    1 |    1 |       32768 |      32512 |
|    2 |    0 |       32768 |      32608 |
|    2 |    1 |       32768 |      32512 |
+------+------+-------------+------------+
4 rows in set (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-7');
Query OK, 1 row affected (0.00 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-8');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-9');
Query OK, 1 row affected (0.01 sec)

mysql> insert into T20150319(memo) values('This is MySQL Cluster 7.4.4 検証-10');
Query OK, 1 row affected (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag,
    -> fixed_elem_alloc_bytes alloc_bytes,
    -> fixed_elem_free_bytes AS free_bytes
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |       32768 |      32480 |
|    1 |    1 |       32768 |      32480 |
|    2 |    0 |       32768 |      32480 |
|    2 |    1 |       32768 |      32480 |
+------+------+-------------+------------+
4 rows in set (0.01 sec)

mysql> select node_id,memory_type,used,used_pages,total,total_pages from ndbinfo.memoryusage;
+---------+---------------------+---------+------------+-----------+-------------+
| node_id | memory_type         | used    | used_pages | total     | total_pages |
+---------+---------------------+---------+------------+-----------+-------------+
|       1 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       1 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       1 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
|       2 | Data memory         | 2293760 |         70 | 134217728 |        4096 |
|       2 | Index memory        |  491520 |         60 |  68419584 |        8352 |
|       2 | Long message buffer |    2304 |          9 |  67108864 |      262144 |
+---------+---------------------+---------+------------+-----------+-------------+
6 rows in set (0.01 sec)

mysql> 

ここからは、データのリストア
1. Clusterを停止(管理・データ)
2. 管理ノードを起動
3. データノードを–initial付きで起動
4.SQLノード停止
5. Restore作業1(1台目だけは-mオプション付き)
6. Restore作業2 (2台目は-mは不要)
7. データ確認して完了

[root@cluster74_01 BACKUP]# pwd
/usr/local/mysql-cluster-advanced-7.4.4-linux-glibc2.5-x86_64/data/BACKUP
[root@cluster74_01 BACKUP]# ls -l
合計 0
drwxr-x---. 2 root root 72  3月 19 22:25 BACKUP-1
[root@cluster74_01 BACKUP]# 


[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> shutdown
ndb_mgm> exit
[root@cluster74_01 cluster]# 

SQLノードも停止しました。

[root@cluster74_01 cluster]# /home/admin/cluster/start_management.sh 
Start MySQL Management Node
MySQL Cluster Management Server mysql-5.6.23 ndb-7.4.4
Management Prompt
/usr/local/mysql/bin/ndb_mgm
[root@cluster74_01 cluster]# 

[root@cluster74_01 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:25:38 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:25:38 [ndbd] INFO     -- Angel allocated nodeid: 1
[root@cluster74_01 cluster]# 

[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndbd --connect-string=192.168.56.114 --initial
2015-03-19 23:26:51 [ndbd] INFO     -- Angel connected to '192.168.56.114:1186'
2015-03-19 23:26:51 [ndbd] INFO     -- Angel allocated nodeid: 2
[root@cluster74_02 cluster]# 



[root@cluster74_01 BACKUP]# /usr/local/mysql/bin/ndb_restore -b 1 -n 1 -m -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 1
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Successfully restored table `NDB01/def/T20150319`
Successfully restored table event REPL$NDB01/T20150319
Successfully restored table `ndbmemcache/def/ndb_clusters`
Successfully restored table event REPL$ndbmemcache/ndb_clusters

省略....
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 0
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 0
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.1.log'
File size 52 bytes
Restored 15 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_01 BACKUP]# 



[root@cluster74_02 cluster]# /usr/local/mysql/bin/ndb_restore -b 1 -n 2 -r /usr/local/mysql/data/BACKUP/BACKUP-1
Backup Id = 1
Nodeid = 2
backup path = /usr/local/mysql/data/BACKUP/BACKUP-1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.ctl'
File size 46920 bytes
Backup version in files: ndb-6.3.11 ndb version: mysql-5.6.23 ndb-7.4.4
Stop GCP of Backup: 2447
Connected to ndb!!
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1-0.2.Data'
File size 31068 bytes

省略...
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table(24) fragment 1
_____________________________________________________
Processing data in table: mysql/def/ndb_apply_status(9) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/containers(18) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/cache_policies(16) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/key_prefixes(20) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/demo_table_tabs(28) fragment 1
_____________________________________________________
Processing data in table: ndbmemcache/def/memcache_server_roles(14) fragment 1
Opening file '/usr/local/mysql/data/BACKUP/BACKUP-1/BACKUP-1.2.log'
File size 52 bytes
Restored 14 tuples and 0 log entries

NDBT_ProgramExit: 0 - OK

[root@cluster74_02 cluster]# 

SQLノードを起動してデータ確認

[root@cluster74_01 cluster]# ./start_mysql.sh 
Start MySQL Node
[root@cluster74_01 cluster]# 150319 23:45:00 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_01.err'.
150319 23:45:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_01 cluster]# 


[root@cluster74_02 cluster]# 150319 23:45:12 mysqld_safe Logging to '/usr/local/mysql/data/cluster74_02.err'.
150319 23:45:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

[root@cluster74_02 cluster]# 


ndb_mgm> show
Connected to Management Server at: 192.168.56.114:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50 (not connected, accepting connect from 192.168.56.114)
id=51 (not connected, accepting connect from 192.168.56.115)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.56.114  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=2    @192.168.56.115  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=100  @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=50   @192.168.56.114  (mysql-5.6.23 ndb-7.4.4)
id=51   @192.168.56.115  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm> 



mysql> use NDB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_NDB01 |
+-----------------+
| T20150319       |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from T20150319;
+----+------------------------------------+
| id | memo                               |
+----+------------------------------------+
|  1 | This is MySQL Cluster 7.4.4 検証   |
+----+------------------------------------+
1 row in set (0.01 sec)

mysql> select node_id AS node, fragment_num AS frag, 
    -> fixed_elem_alloc_bytes alloc_bytes, 
    -> fixed_elem_free_bytes AS free_bytes 
    -> from ndbinfo.memory_per_fragment where fq_name like '%T20150319%';
+------+------+-------------+------------+
| node | frag | alloc_bytes | free_bytes |
+------+------+-------------+------------+
|    1 |    0 |           0 |          0 |
|    1 |    1 |       32768 |      32608 |
|    2 |    0 |           0 |          0 |
|    2 |    1 |       32768 |      32608 |
+------+------+-------------+------------+
4 rows in set (0.06 sec)

mysql> 

restore

参照:
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbinfo.html