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