![variable.jp [データベース,パフォーマンス,運用]](http://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
先日、パフォーマンス検証した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> 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]#